A number everyone trusted was overstated by a third — not because anyone lied, but because the query summed groups that overlap. A human eventually caught it. An AI pointed at the same metric would have inherited the error silently, on every decision, at machine speed.
When you deploy an LLM, an agent, or an AI feature on top of your metrics, it doesn't question the number — it acts on it. If the same metric means three different things across your stack, the model learns the wrong signal and applies it confidently, at scale, invisibly. A wrong dashboard tile is one bad meeting; a wrong number wired into AI is a wrong decision automated across every customer. The time to check your definitions is before the model ships, not after it's been wrong for a quarter.
Demo Retail runs three acquisition channels in the same quarter — Paid Search, Email, and Affiliate. Each campaign produces a cohort: the set of customers it reached, and the revenue attributed to those customers.
Leadership asked a reasonable question: "How many customers did we reach this quarter, and how much revenue did the campaigns bring in?" The dashboard answered it, built the obvious way:
total_customers = customers(paid) + customers(email) + customers(affiliate)
attributed_revenue = revenue(paid) + revenue(email) + revenue(affiliate)Clean SQL. Ran fast. Reconciled to each campaign's own report. Everyone trusted it.
The campaigns overlap. A customer who clicks a paid ad in July and opens a promotional email in August is a real, common case — that is exactly the customer a marketing team wants. But when you add the cohorts together, that customer is counted twice. Their revenue is added twice.
Adding the per-campaign numbers answers a different question than the one leadership asked. SUM across overlapping groups measures campaign touches, not distinct customers. The tile looked like a headcount and a revenue figure; it was actually a count of (customer × campaign) pairs.
| Campaign | Customers | Attributed revenue |
|---|---|---|
| Paid Search | 4,120 | €512,400 |
| 3,540 | €431,800 | |
| Affiliate | 1,860 | €240,000 |
| Figure | Naive sum | Distinct (correct) | Overstatement |
|---|---|---|---|
| Customers reached | 9,520 | 7,180 | +32.6% |
| Attributed revenue | €1,184,200 | €874,600 | +35.4% |
The gap is 2,340 duplicated memberships — customers who appear in more than one campaign. Note that revenue overstates more than headcount does (35.4% against 32.6%). That is not a coincidence, and it is the detail most teams miss: customers reached by several campaigns tend to be your higher-spending customers, so the double-counted rows carry above-average revenue. The error is worst precisely where the money is.
The error is invisible at the tile level. Each campaign's own number is correct. The total is wrong. Nothing in the dashboard tells you the groups overlap.
Not by staring at the SQL — the SQL was "correct" in the sense that it did what it said. It was caught by asking a question the tile could not answer:
"Is this a count of customers, or a count of campaign touches? If I
COUNT(DISTINCT customer_id) across all three campaigns, do I get the same
number as the sum of the cohorts?"
4,120 + 3,540 + 1,860 = 9,520. The distinct count came back 7,180. That 2,340 gap
is the double-count. The moment the two numbers disagree, you know the cohorts
overlap, and the total cannot be a SUM.
Two changes, both small:
COUNT(DISTINCT customer_id). For revenue, first union
and de-duplicate the orders at the defined grain, then SUM the one
canonical amount for each order. One customer contributes once to a customer total,
regardless of how many campaigns reached them.-- wrong: sums overlapping cohorts
SELECT SUM(cohort_customers) FROM campaign_summary;
-- right: set union at the entity grain
SELECT COUNT(DISTINCT customer_id) FROM campaign_membership;The corrected tile shows: per-campaign detail, plus a distinct total that is always ≤ the sum. If someone asks "why doesn't the total equal the parts," that is the feature, not the bug — it is the overlap made visible.
This is not an industry-specific problem. It is an overlapping-cohort problem, and it hides in the most common metrics operators cite — the same ones an AI will read:
You cannot SUM counts or amounts across groups that can share the same
underlying entity. For counts, take the set union with COUNT(DISTINCT …).
For amounts, union and de-duplicate at the metric's defined grain, then SUM
one canonical amount per record. If a "total" equals the sum of overlapping parts, it
overstates the unique population or amount.
The one-line checks: for counts, does
SUM(part counts) equal COUNT(DISTINCT entity)? For amounts,
does the naive sum equal the canonical post-de-duplication SUM(amount) at
the same grain? If not, the parts overlap, and the naive total is wrong.
A human eventually catches this one — someone asks the right question in a meeting. That safety net disappears the moment you point AI at the metric. An LLM, an agent, or an AI feature doesn't ask "is this a count of customers or a count of touches?" It reads the number and acts: it prioritises, it routes, it answers a customer, it reallocates spend. If the definition is wrong, the model is now wrong at machine speed, on every decision, and it sounds confident doing it.
Metric trust is not "is the SQL correct." It is "does this number answer the question the decision-maker — or the model — thinks it answers." Those are different tests, and only the second one catches this class of error before it scales.