Five People Called Anna Meier

The retention slide has one number on it: 70.55% of our customers buy once and never come back. The proposal is a €12 win-back voucher to all 251,421 of them. Everybody agrees the number is bad. Nobody asks where it came from.

It came from count(distinct customer_id), which is the right query against the wrong assumption. customer_id is not a person. It is a row created whenever somebody checks out with an email address we have not seen before — and over twenty-four months, 45,414 people (15.14%) did that more than once, for boring reasons: guest checkout then registration, an address typed in capitals, a Gmail dot, a @privaterelay.appleid.com address from Apple sign-in, a marriage, a house move.

A primary key identifies a record. A customer is an entity in the world. Nothing in a database enforces the relationship, and no test can check it, because a test can only compare rows to other rows. Entity resolution is the job of deciding which records refer to the same thing: normalise → block → compare → score → cluster → survivorship.

Merging two customer rows does not create, delete or change a single order. So every additive fact is exactly right, and the error is confined to metrics with a customer in the denominator. The same queries over seven different groupings — raw customer_id through full resolution — returned GMV identical to the cent every time.

The property that hides it. Every number the board looks at weekly — revenue, orders, basket size, margin — is a sum over orders, and a sum over orders cannot notice how the orders are grouped. Every number that is wrong here is a ratio with customers underneath it. That is arithmetic, not bad luck, and it is why this survives indefinitely in a company that watches its revenue closely.

One order can only ever create one identity. Every additional order is another chance to arrive by another route. So fragmentation is a function of how much someone buys, and the error lands hardest exactly where the money is.

Figure 1 — surplus customer rows per person, by spending decile. The top tenth of customers by lifetime spend hold 1.7106 rows each (51,318 rows for 30,000 people) and account for 38.72% of GMV. The bottom decile holds exactly 1.0000: they bought once, so they could not fragment.

Ranked by lifetime spend, 8 of the ten biggest real customers do not appear in the measured top ten at all. The largest true customer has spent €1,823.36; the measured leaderboard tops out at €1,385.88, because her spend is split across four rows. Every VIP programme and loyalty tier is drawn from that leaderboard.

See also — Lesson 05. The star schema fixed the grain of the fact table. Nobody ever wrote down the grain of the dimension. It is not one row per person; it is one row per checkout identity, and that is a different thing that nothing in the model records.

On 31 October 2025 we shipped one-tap sign-in — Apple and PayPal. Checkout conversion improved; the release was a success and still is. It also gave every returning customer a second front door, and a relay email address that matches nothing we already hold.

Figure 2 — monthly repeat-purchase rate, as measured and as it really was. In the month after the release the measured line falls 4.35 pp (33.85% → 29.49%) and never recovers. The true line moves 0.05 pp (42.12% → 42.08%) and keeps climbing. Both series come from the same 512,298 orders; only the grouping differs.

Two dashboards moved that quarter and the story that reconciled them was wrong in both halves. New customers jumped; retention dropped; the explanation on the page was “the new sign-in is bringing in a lot of first-time shoppers, and they haven’t come back yet.” In fact the share of “new customers” who had already bought from us went from 9.85% before the release to 19.71% after it. Acquisition was overstated and retention understated by the same mechanism, at the same moment, in opposite directions.

And paid acquisition is bid against lifetime value, which is understated by 18.79% — so the ceiling everybody bids under is 18.79% too low. The metric error does not make the company reckless. It makes it timid, which is harder to notice.

Figure 3 — precision against recall, in two panels: a zoom on 88–100% precision where the named rules live, and the full scale where the threshold sweep falls off a cliff. Loosening τ from 6 to 4 buys the last 11.88 pp of recall and merges 291,867 pairs of strangers to get it.

Three things the ladder says:

• Email hygiene is not the fix. Lower-casing, trimming and canonicalising Gmail dots and +tags — the whole family of rules people reach for first — recovers 31.01%. Most people who bought twice under two identities did not misspell an address; they used a different one.
• There is a ceiling, and it is not a tuning problem. The best matcher finds 88.12%. The missing 11.88% came back with a new email, a new address and a new phone: there is no evidence in the warehouse linking them, so no algorithm can. You cannot infer an identity you never collected. The fixes for that slice are upstream — a login, a stored payment token, an order-lookup flow.
• Even the safest rule is not perfectly precise. Exact email match merges 646 pairs of different human beings, because 1,407 people share 703 mailboxes with somebody else. Families do that. Email is not a person either.

Why you do not pick τ by F1. A missed merge produces a wrong number. A false merge produces an incident: one customer sees another’s order history in “my account”, the marketing consent of the louder record wins, and an erasure request for A deletes B’s data. Those costs are not on the same scale. Use two thresholds — auto-merge above τ_high, never merge below τ_low, and put the band between them in a review queue.

Adding phone number is the obvious next rung and worth more than everything before it: recall 31.01% → 65.36%. Added naively — every pair sharing a phone joined, then connected components — it produces this:

1,833 accounts carry the phone number +49 30 000000 — a placeholder somebody types when the field is required. Transitive closure does the rest: A shares a phone with B, B with C, and the component grows until it has swallowed 1,835 unrelated people and 3,081 of their orders.

Figure 4 — how many accounts carry each phone number (log scale). Of 269,538 distinct phone values, the busiest real one sits on 8 accounts and never belongs to more than two people. The placeholder sits on 1,833 — 229× further out — and there is nothing in between. The threshold is not a tuning parameter; it is a canyon.

The fix is one clause: ignore any identifier value carried by more than k accounts. A value appearing 1,833 times is not an identifier, it is a category. It turns 2,242,589 wrong merges into 1,079 — a factor of 2,078 — and costs no recall.

Blocking

356,360 customer rows make 63,496,046,620 pairs — 17.6 hours of CPU at a very optimistic million comparisons a second, growing with the square of the table.

The middle row is the trap, and it is the cheapest-looking one. Adding the surname to the block key throws away a third of the duplicates, because a name change is one of the things you are trying to catch. Never block on the field you are trying to correct.

See also — Lesson 18. A single value on 1,833 rows is exactly the hot key that flattened the seller job. The value that ruins a join and the value that ruins a match are the same value, found by the same ten-second query.

The series so far: the event (06–10), the constant (11), the drift (12), the unwatched (13), the reversible (14), the unreproduced (15), the bundled (16), the transient (17) and the extremal (18).

The referential failure. Every row is a true statement. customer_id is unique, not null, correctly typed and correctly joined. Freshness is fine, volumes are fine, the contract holds, the reconciliation balances. No test over rows can fail, because no row is false. What is wrong is the assumed correspondence between a key and a thing in the world — and that correspondence is not stored anywhere, so nothing can check it.

Once you have the shape you find it everywhere: an account_id that is really a workspace, counted as a company; a store_id that is really a till; a sku that is really a colourway; a device id counted as a reader; an IP address counted as a household. In every case the joins run, the tests pass, and every per-entity number is wrong by the entity ratio — here 18.79%.

See also — Lesson 14. An Article 17 request names one email address. Delete that customer row and, for the 45,414 people here who hold more than one, 56.83% of their order history stays in the warehouse — 87,643 of 154,217 rows. A person you deleted once is not deleted.

Measuring duplicates seems to need the answer already. It does not — it needs a lower bound: how many customer rows created this month collide with a row that already existed? One query over data you already have, no ground truth.

Figure 5 — the observable proxy against the truth. The flagged share tracks the true share of “new customers” who had bought before: 9.21% against 9.85% before the release, 18.32% against 19.71% after. It is a floor, not an estimate — it only sees collisions the keys can see.

Two honest notes on detection. First, the Lesson 06 band test is useless here: both series break their own trailing twelve-month envelope from month 12 onwards and never come back inside, because both are trending. As in Lesson 13, the first crossing is never the finding. Second, what does single the month out is the size of the move: +5.07 pp, which is 3.10× the next largest month-over-month change in twenty-four months. In the new-customer count — the series everybody already watches — the same month ranks 9th of 23 for growth. Unremarkable, and celebrated.

with canon as (
  select customer_id,
         case when split_part(lower(trim(email)),'@',2) in ('gmail.com','googlemail.com')
              then replace(split_part(split_part(lower(trim(email)),'@',1),'+',1),'.','')
                   || '@gmail.com'
              else split_part(split_part(lower(trim(email)),'@',1),'+',1)
                   || '@' || split_part(lower(trim(email)),'@',2)
         end                                                    as e_canon,
         regexp_replace(phone,'[^0-9]','','g')                  as p_digits,
         lower(first_name)||' '||lower(last_name)||'|'||postcode as n_addr
  from   dim_customer),
hub as (                       -- an identifier on 50+ rows is a category, not an id
  select p_digits from canon
  where  length(p_digits) >= 9 group by 1 having count(*) > 50),
acct as (
  select c.customer_id, c.e_canon, c.n_addr,
         case when c.p_digits not in (select p_digits from hub)
              then c.p_digits end                               as p_key,
         (select min(order_date) from fct_order o
           where o.customer_id = c.customer_id)                 as created
  from   canon c),
ranked as (
  select a.*,
         min(created) over (partition by e_canon) as e_first, count(*) over (partition by e_canon) as e_n,
         min(created) over (partition by p_key)   as p_first, count(*) over (partition by p_key)   as p_n,
         min(created) over (partition by n_addr)  as n_first, count(*) over (partition by n_addr)  as n_n
  from   acct a)
select date_trunc('month', created)                             as month,
       count(*)                                                 as new_rows,
       count(*) filter (where (e_n > 1 and created > e_first)
                           or (p_key is not null and p_n > 1 and created > p_first)
                           or (n_n > 1 and created > n_first))  as collides_with_existing
from   ranked group by 1 order by 1;

And the ten-second one that would have found the placeholder phone:

select regexp_replace(phone,'[^0-9]','','g') as v, count(*)
from   dim_customer group by 1 having count(*) > 50 order by 2 desc;

Four questions worth asking the team

1. “When a report says customers, which table’s rows is it counting?” If the answer is dim_customer, it is counting checkout identities, and you now know the ratio to apply.
2. “What percentage of customer rows created last month collided with one that already existed?” One query, above. If nobody has ever run it, that is the finding.
3. “For each identifier we join or match on, what is its most frequent single value and how many rows carry it?” Placeholder phones, noreply@, test@, the default postcode, the unknown member. Same query that finds join skew.
4. “When an erasure request arrives, what do we search for besides the email address in the request?” If the answer is “nothing”, the deletion is partial by construction.

Where the answer lives, architecturally

Not in each mart. Matching is a job, not a query: it runs in silver, writes one table — bridge_customer_person(customer_id, person_key, method, score, matched_at) — and every mart joins to it. That buys three things a case when in a dashboard never will: it is versioned, so last quarter’s report is still reproducible (Lesson 09); it is testable, because precision and recall are measurable against a reviewed sample; and it is reversible, because unmerging is an update rather than an archaeology project. Ship the bridge before the clever matcher — an identity map containing only the exact-email rule already beats none.

Twenty minutes, hands on

1. Run the generator in the artifact appendix. It prints the lesson’s figures; check a few.
2. Set SPLIT_AFTER = SPLIT_BEFORE — the release that never happened — and watch the repeat-rate lines stay parallel.
3. Set HUB_RATE = 0, run the phone rule without frequency suppression, and see 2.04% precision come back to 97.75%.
4. Move τ from 6 to 4 and look at what the largest cluster becomes (28 accounts). Then decide which of those merges you would show to a customer.
5. Run the two queries in §08 against your own customer table. The second takes ten seconds and has never once come back empty.

Takeaway

A key identifies a row. Only a login, a payment token or an explicit match identifies a person. Everything measured per customer depends on which of those three you are actually counting — so write the correspondence down as a table with a score and a date, and treat every rule that infers identity as what it is: a model, with a precision, a recall, and a cost of being wrong that is not symmetric.

Vocabulary

• entity resolution / record linkage — deciding which records refer to the same real-world thing. “Deduplication” is the same job within one table.
• normalisation — collapsing meaningless variation before comparing: case, whitespace, punctuation, +tags, phone formatting, street abbreviations.
• blocking — only comparing records that agree on a cheap key, so you do not compare all 63,496,046,620 pairs. Trades recall for time; a bad block key is the commonest cause of missed matches.
• pairwise precision / recall — of the pairs you merged, how many were right; of the pairs you should have merged, how many you found. Report both; F1 hides the asymmetry.
• transitive closure / connected components — if A matches B and B matches C, A and C are the same person. Powerful and unforgiving: one false edge merges two entire clusters.
• hub value — an identifier value carried by far too many rows (a placeholder phone, a shared service email). Suppress by frequency before matching or joining.
• survivorship — after merging, which attributes win: newest address, most complete name, the most restrictive consent record.
• identity graph / bridge table — the persisted map from source keys to a resolved person key. The artefact this discipline exists to produce.
• deterministic vs probabilistic matching — exact agreement on a key, versus a score built from partial agreements with a threshold. Real systems run both, in that order.
• Jaro–Winkler — a string similarity rewarding a shared prefix; the usual default for names. anna meier against ana meier scores 0.9733.

• Deterministic. Every count, share, precision and recall comes from the generator shipped in the artifact appendix: modular arithmetic and closed-form quantile tables, no random seeds.
• Executed, not asserted. The SQL in §08 was run in DuckDB against the generated tables (356,360 customer rows, 512,298 orders). The headline query returns 356,360 customers, 29.45% repeat and €70.31 LTV; the same query over the bridge table returns 305,039, 38.55% and €82.14 — with GMV of €25,057,326.18 in both, to the cent. The hub query returns exactly one value on 1,833 rows. The monthly monitor reproduces the Python figures exactly in all 24 months (max difference 0.0000 pp). Pairwise precision and recall recomputed in SQL return 98.1723% and 88.1168%, matching to four decimals.
• Cross-checked. The Jaro–Winkler implementation was compared against DuckDB’s jaro_winkler_similarity on eight pairs including the standard MARTHA/MARHTA and DWAYNE/DUANE cases: identical to 1e−9.
• A stated model, not a measurement. The population is synthetic and its fragmentation mechanism is an assumption: 1,407 people share a mailbox with somebody else, 6 accounts in 1,000 carry the placeholder phone, and 6% of identity splits were built to be unrecoverable — which is what sets the 88.12% ceiling. Change those and the ladder changes; the shape of the argument does not.
• The one modelled money figure. €23,300.06 of voucher value handed to people who were going to buy anyway = 28,554 mis-targeted customers who bought in the last 90 days × a stated 6.8% redemption × €12. The redemption rate is an assumption; the 70,141 mis-targeted rows and the 28,274 duplicate sends to 24,802 people are counts.

The generator was extracted back out of the rendered page, run in a clean directory, and diffed key by key against the published figures: 88 keys, 0 differences.