Case Study 1: Four Definitions of an Active Customer

"The board deck said 1.61 million active customers. The growth review that morning said 2.34 million. Both were produced by our platform, both were correct, and the meeting was about which of us had made a mistake."

Executive Summary

Kestrel's quarterly board pack reported 1,612,880 active customers. A growth review the same morning reported 2,338,401. Each was computed correctly from its own definition — and one of them was larger than the number of customers Kestrel has.

A grep found active_customer computed independently in eleven places, with four distinct definitions — 30-day activity, 90-day orders, 12-month orders net of full refunds, and one long-dead variant nobody could attribute.

The reconciliation took three weeks and was entirely conversation. The engineering was two days.

The outcome was not one definition. Three of the eleven were correctly different, and were renamed rather than reconciled: active_30d, active_90d, active_12m. The goal is not one definition — it is no two things sharing a name.

Skills applied: metric definitions and the semantic layer (§30.8); the audit that finds a second definition; the tie-breaker that resolves a metric dispute; and the limits of what a data team can decide.

Background

Nobody set out to have four definitions. Each was created by a competent person solving a specific problem:

2024-02  growth model         "active" = any event in 30 days
                              (built for a churn model; 30 days is the
                               model's prediction horizon)

2024-09  analytics dashboard  "active" = ordered in 90 days
                              (a merchandising view; 90 days is roughly a
                               purchase cycle)

2025-01  finance report       "active" = ordered in 12 months AND not
                              refunded in full
                              (matches the definition used in an investor
                               update, which is where it came from)

2025-06  a marketing export   "active" = ordered in 180 days
                              (nobody could say why; the person had left)

Every one is defensible. The growth definition is right for a churn model, the finance definition is right for a report that has already been filed, and the analytics one is right for merchandising.

The problem is not that they differ. It is that all four are called active_customer, and none of the four documents that the others exist.

The Problem

The two numbers collided in a meeting.

board pack, Q2 2026        active customers   1,612,880
growth review, same day    active customers   2,338,401
                                              ─────────
                           difference           725,521   (45%)

The meeting spent twenty minutes on which was wrong, before someone thought to ask how each was computed. Nobody in the room could say, because the question had never come up — each team had used its own number for over a year without ever seeing the other's.

⚠️ Failure Mode — two definitions are invisible until they meet

This is the property that makes metric divergence different from every other defect in this book. It produces no error, fails no test, and has no symptom at all until two people who use different definitions are in the same room looking at the same slide.

Which means the discovery mechanism is a meeting, and that has three consequences:

• It surfaces at the worst possible moment, in front of an audience, framed as somebody having made a mistake.
• It surfaces late. Kestrel's four definitions coexisted for twenty-eight months.
• The people present are usually not the people who can resolve it, because the definitions were set by whoever built each thing, at different times, for different reasons.

And the diagnostic is trivially cheap once you think to run it:

bash grep -rn "active[_ ]customer\|last_order_date\s*>" models/ dashboards/ notebooks/ exports/

Eleven hits in four minutes. The thing that is expensive is not the detection — it is that nobody runs a grep for a problem they do not know they have, and there is no signal prompting them to.

So this is one of the few things in this book worth doing prophylactically. A quarterly grep for your ten most-quoted metric names, against every place SQL is written, costs an hour and is the only mechanism that finds this before a meeting does.

The Analysis

Step 1: find them all. The grep found eleven locations:

models/marts/dim_customer.sql              90-day
models/marts/fct_customer_cohort.sql       90-day
dashboards/merchandising.sql               90-day
dashboards/exec_summary.sql                12-month
reports/board_pack.sql                     12-month
reports/investor_update.sql                12-month
ml/churn/features.py                       30-day
ml/churn/training.py                       30-day
dashboards/growth_weekly.sql               30-day
exports/marketing_segment.sql              180-day
notebooks/ad_hoc/2025_q3_analysis.ipynb    90-day

Four definitions, eleven implementations, and no two of the eleven referenced each other.

Step 2: which is right? This is the question a data team cannot answer, and the three weeks were spent establishing that and then hosting the people who could.

Step 3: what actually differs? The numbers made the disagreement concrete, which turned out to matter more than any argument:

definition                          count        vs 12-month
30-day, any event                2,338,401           +45.0%
12-month, orders net of refunds  1,612,880            baseline
180-day, any order                 841,206           -47.8%
90-day, any order                  510,417           -68.4%

The four numbers span 4.58x, which is the first thing anybody in the room had seen that made the problem look like a problem rather than like an argument.

The 30-day-any-event definition includes people who browsed and did not buy, which is correct for a churn model and is not what "active customer" means to a board.

⚠️ Failure Mode — the number that was impossible on its face

Kestrel has 1,904,221 rows in customers (Chapter 20 Case Study 1). The growth review's headline figure was 2,338,401 active customers — 434,180 more customers than the company has, a figure 22.8% above the entire customer table, published weekly for over a year.

Nobody noticed, and the reason is worth sitting with: the number was never put next to the customer count. It appeared on a growth dashboard beside other growth numbers, all of which moved plausibly, and the check that would have failed instantly — is this bigger than the number of customers we have? — belonged to no chart.

The explanation, once found, was mundane and correct. The growth model counts device-identified visitors, not registered customers; a person browsing on a phone and a laptop is two identities, and visitors who never register are counted too. For a churn model whose unit of prediction is a browsing session, this is the right population. For a slide headed active customers, it is not a population at all.

The generalizable check is a bound, not a definition. Every count has a ceiling somebody already knows — customers, orders, employees, devices — and a count that exceeds its own ceiling is wrong without any investigation into how it was computed. Chapter 23's register can hold that as an assertion in one line, and now does:

sql -- growth_weekly.engaged_30d must not exceed the customer base SELECT 1 FROM metrics_daily WHERE engaged_30d > (SELECT count(*) FROM gold.dim_customer WHERE is_current)

This is the only defect in this case study that a test could have caught, and it went uncaught for a year because nobody thought to write down a bound they considered obvious.

🔎 Read the Plan — the tie-breaker that resolves a metric dispute in one question

Three weeks of conversation was mostly two teams making reasonable cases. What ended it was a question nobody had asked:

"Which of these has already been said to someone outside the company?"

The 12-month definition had been in an investor update. That settled it in about four minutes, and it is worth understanding why the question works so well:

• It is not an argument about merit. Nobody has to concede that their definition is worse, because it is not.
• It has an objectively checkable answer. Either it appeared in an external document or it did not.
• It correctly identifies the definition with the highest cost of change. Restating a number given to investors is expensive; renaming an internal dashboard column is not.

The general form: when several defensible options exist, look for the one with an external commitment attached, and let that break the tie. It is the same move as Chapter 26 §26.8's pre-decided "stale over wrong" — finding a decision that has already been made, rather than making a new one.

And it fails cleanly when it does not apply. If no definition has left the building, the question returns nothing and you are back to a genuine decision — which is fine, because you have now eliminated the easy case in four minutes.

The Decision

Four changes, and the third is the one most metric-governance efforts miss.

One: one certified definition of active_customer, in a single model, referenced everywhere:

-- models/marts/dim_customer_activity.sql
-- CERTIFIED. This is the definition of `active_customer`.
-- Owner: #finance. Certified 2026-07-02 by finance@example.com.
-- Matches the definition used in investor communications since 2025-01.
SELECT customer_id,
       (last_order_date >= current_date - INTERVAL '12 months'
        AND NOT fully_refunded) AS is_active_customer
  FROM ...

Two: eight of the eleven now reference it. No independent computation.

Three: three were renamed, not reconciled.

📐 Design Decision — the goal is not one definition; it is no two things sharing a name

The instinct after finding four definitions is to pick one and eliminate the rest. That would have broken the churn model, whose 30-day activity window is not an alternative definition of the same concept — it is a different concept that was given the wrong name.

The three that survived, renamed:

Was	Is now	Because
active_customer (30d, any event)	engaged_30d	it counts browsing; it is an engagement measure and always was
active_customer (90d, orders)	purchasing_90d	a merchandising cycle, not a company-level metric
active_customer (180d)	deleted	nobody could attribute it and nothing read the export

engaged_30d is the important row. Forcing it to the 12-month definition would have degraded a production model to satisfy a naming convention, and the team came close to doing exactly that — the reconciliation plan's first draft listed all eleven as "migrate to the certified definition."

The check that caught it was asking, for each of the eleven: what decision is this number used for? (§30.1's test, applied to a metric rather than an artifact.) Three of them served decisions the certified definition would answer badly.

The general rule: reconcile the ones that answer the same question, and rename the ones that do not. A metric-governance project that produces one definition of everything has usually destroyed something, and the destruction is invisible because the thing it broke still runs.

Four: a quarterly grep, for the ten most-quoted metric names. metric_audit.py in platform/governance/.

Five: a bound on every published count. ⚠️ above. Eleven metrics now carry a ceiling assertion in Chapter 23's register, and writing them took an afternoon.

What Happened

The board number did not change, because the certified definition is the one it already used. The growth review's headline number changed by 45%, and the growth team's response is the part worth recording:

"Ours was never the same number. We just called it the same thing, and once it had a different name nobody cared what the board's number was."

The quarterly grep, on its first scheduled run, found two more:

churn_rate computed in three places with two definitions — one counting customers who stopped ordering, one counting subscriptions that lapsed. Renamed, both.

gross_margin computed in two places, differing in whether shipping cost is included. This one was a genuine error: one of the two was wrong, had been wrong for seven months, and had been used in a pricing decision. The grep found in an hour what no test in Chapter 23's register would ever catch, because both implementations were internally consistent, correctly typed, and passed every assertion.

🏭 From the Pipeline — the class of defect no assertion catches

Chapter 23's twenty-two assertions, plus Chapter 29's twenty-third, check that data is well-formed, complete, timely, and internally consistent. gross_margin was all four and was wrong.

Because the defect is not in the data. It is in the agreement between two pieces of code about what a word means — and there is no property of a table that reveals it.

Three things that do detect it, and none is a test:

• The grep. Crude, cheap, and it found both.
• A semantic layer, where the definition exists once and cannot be re-implemented without somebody noticing.
• Two numbers meeting in a meeting ← the current mechanism, and the reason this case study exists.

This is the strongest available argument for a semantic layer, and it is not the one usually made. The usual argument is consistency-as-tidiness; the real one is that this defect class is undetectable by every other control in this book.

Lessons

1. Two definitions of a metric produce no error, fail no test, and have no symptom — until two people using different ones are in the same room. Kestrel's four coexisted for twenty-eight months.

2. The discovery mechanism is a meeting, which means it surfaces late, in public, and framed as somebody having made a mistake.

3. A grep finds it in four minutes, and nobody runs a grep for a problem they do not know they have. This is one of the few things worth doing prophylactically — quarterly, on your ten most-quoted metric names.

4. ⚠️ A count that exceeds its own ceiling is wrong without further investigation. The growth figure was 22.8% larger than Kestrel's entire customer table and ran weekly for a year. Every published count has a bound somebody already knows; write it down as an assertion.

5. The data team cannot decide which definition is right. Its job is to make the disagreement visible, host the conversation, and implement what is agreed.

6. Showing the four numbers side by side did more than any argument. A 4.58x span — the disagreement stops being abstract.

7. 🔎 The tie-breaker: which of these has already been said to someone outside the company? It is not an argument about merit, it has a checkable answer, and it identifies the definition with the highest cost of change. Four minutes, after three weeks.

8. 📐 The goal is not one definition — it is no two things sharing a name. Three of eleven were correctly different and were renamed; forcing them to the certified definition would have degraded a production model.

9. The check that caught that: for each implementation, what decision is this used for? §30.1's test, applied to a metric.

10. A metric-governance project that produces one definition of everything has usually destroyed something, and the destruction is invisible because the thing it broke still runs.

11. 🏭 This defect class is undetectable by every control in this book. gross_margin was well-formed, complete, timely, internally consistent — and wrong for seven months, in a pricing decision. That is the real argument for a semantic layer, not consistency-as-tidiness.

Questions for Discussion

1. Four competent people created four definitions over twenty-eight months, each for a good reason. What would have surfaced the second one at the moment it was created?

2. The tie-breaker question worked in four minutes. What is the risk of a rule that resolves disputes by external commitment rather than by merit?

3. Three of eleven were renamed rather than reconciled. How would you tell, in general, which implementations answer the same question and which do not?

4. The growth team's number changed by 45% and they did not mind. Why do you think that was, and would it have gone the same way if the rename had been imposed rather than agreed?

5. gross_margin was wrong for seven months and used in a pricing decision. Should the postmortem treat that as a governance failure, a review failure, or something else?

6. The quarterly grep is crude. Design something better, and be honest about what it costs relative to an hour a quarter.

7. §"From the Pipeline" claims this defect class is undetectable by every other control in the book. Try to disprove it — is there an assertion that would catch two internally-consistent definitions?