Case study 02
One customer, one record: identity resolution with survivorship and provenance.
Café Zupas, 90+ locations / 2024 to present
The problem
Customer identity was split across the POS, the ordering platform, and the loyalty platform, with several email fields that looked interchangeable and were not. In one month, 143 email addresses were shared by multiple distinct people. Merging on email fused strangers, not duplicates.
What I built
- 01
Matching
A tiered pipeline: exact, subset, Jaccard, then fuzzy, across all three systems, with phone and email normalization feeding the match keys.
- 02
Survivorship
Documented as a precedence table, one row per rule in coalesce order, with the condition, the winning value, its source, its timestamp, and the measured count of rows each rule decides. The rules that matter: identity keys on the mapped customer ID, never email; the email a guest types at checkout is not canonical; when two canonical records collide the older one wins and the losing cluster is re-pointed.
- 03
Provenance
Every identity key carries its source system and a UTC timestamp, per order and per customer. Across 691,760 customer records with activity in the identity model (a wider net than the 550K trailing-twelve-month active customers reported elsewhere), 99.99% are populated and sourced; across 1.37 million customer records total, zero unsourced values. A post-build assertion fails the run if any mapped order lacks a source or a timestamp.
Outcomes
- Replaced a licensed customer data platform, $240K a year.
- Identity is auditable per field, which is what a Finance reconciliation or a deletion request needs.
- Downstream segmentation and the guest-checkout analysis stand on one record instead of three.
Stack
- BigQuery
- SQL
- Git
- Pulse (ordering)
- SessionM (loyalty)
- Brink (POS)