All work
Customer identity reconciliation
One customer, one record
A membership business stops locking out people who paid, and stops serving people who cancelled.
StripePayPalCRMCommunity accessCanonical identity
- Business type
- Paid membership and coaching
- Systems
- Stripe, PayPal, CRM, community platform, Airtable, Make, Python
- My role
- Designed, directed, verified and operated
- Shown here
- The method on made-up data. I run it daily in production.
01The situation
One person can pay with one email, book calls with another and join the community with a third. Every tool holds part of the truth, and none of them agree.
When they disagree, a team either blocks someone who paid or keeps serving someone who left. Usually nobody notices until the member complains.
- Paymentbilling email
- CRMsales email
- Communitylogin email
- Mismatchfound by complaint
02What I found
- A changed email created a new “person”, so one member could look like three.
- Refunds could be counted as payments, which made people look paid when they were not.
- I rejected matching on names: two different people can share one.
- I rejected automatic merging on a close match: it can join two people’s histories, which is worse than leaving them apart.
03What I built
- One member ID, issued in one place and never reused.
- Every email, payment ID and account is stored as evidence that points to that ID, so a person can have many of each.
- Exact identifiers decide first, known business links second, and a person decides the rest.
- A daily check compares what each member paid for with what they can actually access.
- Each mismatch becomes one action with an owner and a reason, and the ID is written back to the CRM.
- All sourcespayments, CRM, bookings, access
- Identity registryone member ID
- Daily checkpaid vs access
- Action listowner and reason
04How it fails safely
- Before each run it checks that the payment data still has the shape it expects, and stops if it has changed instead of guessing.
- Uncertain matches go to a person. The system is allowed to say “I don’t know”.
- Each write to the CRM is read back to confirm it landed, and logged.
05Result
- 28 made-up records across six systems become 4 resolved customers, 2 possible identities held for a person, and 5 actions.
- The actions cover a paying member who lost access, a failed payment before a session, and a cancelled plan that still had access.
- $3,350 a month in plan value needs attention. That is scenario value, not money saved.
06Lessons
The hard part is not matching. It is refusing to guess.
A system that says “a person should look at this” earns more trust than one that is right most of the time and silently wrong the rest.
For engineers: how it is put together
- A registry issues IDs; identifiers are many-to-one evidence rows; merges are recorded as supersessions, never overwrites.
- Runs go through a guarded entry point: pinned release, run lock, clean state and a schema check before any read is trusted.
- Writes are one key, one record, blank fields only, followed by a read-back and an append-only log.
Have a system that keeps drifting?