#The challenge
The company runs a hybrid self-serve and sales-led motion, and a single customer existed simultaneously as an organization in the product database, a company in the CRM, and a customer in each of two billing systems. Nothing joined reliably. Finance quoted revenue from one system, the go-to-market team quoted from another, and product from a third, so revenue, churn and retention numbers disagreed depending on who ran them. The data lake that was supposed to be the single source of truth had nothing dependable to join on, which is why nobody was using it for a board number.
#The approach
Measure the linkage before designing anything
The first deliverable was a coverage audit across the foundational systems expressed as blunt percentages, not as a maturity opinion. Person-level identity — product user to CRM contact — was effectively solved. Organization-level identity was not: product organization to CRM company sat under half, and only a small minority of active billing customers carried a direct product-organization ID at all. That single split — person identity solved, organization identity broken — reframed the whole project. Everyone had assumed it was a general data problem; it was specifically an account-level problem.
Deterministic tiers first, fuzzy later
A tiered match ladder: exact ID match, exact email match, normalized or fuzzy email, company domain plus name heuristic, and a manual review queue as the terminal tier. Phase one shipped only the deterministic tiers, so every match in production could be explained to a sceptical finance stakeholder in one sentence. Fuzzy and AI-assisted resolution were parked, with the ambiguous population held rather than force-resolved.
Domain matching, with the exclusion list as the load-bearing part
Deriving the email domain from billing invoices and matching it against product user email domains recovered a large slice of the unmatched population. The exclusion list is what makes this safe rather than catastrophic: free and consumer mail providers and the company's own internal domains are stripped up front. Without that, domain matching collapses into joining every consumer-mail user to every other one — which was the first question the client's own engineer asked, correctly.
Classify matches as unambiguous versus ambiguous instead of forcing a verdict
Where a domain maps to exactly one organization, the match is unambiguous and auto-accepted. Where it maps to more than one, it goes into an ambiguous bucket for human confirmation rather than being resolved by a tiebreak rule. That pass took organization-level linkage from a small minority of billing customers to roughly half, with several hundred ambiguous candidates queued for review and a substantial remainder with no discoverable link at all. Publishing all three numbers — matched, ambiguous, unmatchable — is what made the result usable.
Build the method to outlive any one billing vendor
The same stitch had to be rebuilt against a second billing system, which would have orphaned the work if the method had been built around one vendor's schema. Because it was not, the rebuild took days. That platform's records carry both a payments-processor ID and a CRM company ID on a majority of rows, which makes them the strongest bridge in the entire graph; where those IDs are absent, domain matching fills in behind them. The output is a flattened table where a single user row resolves to product, CRM and billing records together.
Build the pipeline for change, not for snapshots
Billing data landed first as a bulk snapshot load to unblock analysis, then moved to a change-data-capture pipeline for incremental ingestion. The warehouse model was kept in star schema so the new source slotted into the existing semantic layer rather than forking it — the migration added a source, not a second data model.
Argue for a match-rate tolerance before arguing about technique
The most important intervention on this project was commercial rather than technical. Nobody had agreed what error rate was acceptable, which means 'improve the matching' has no finish line and the person consuming the number can reject it at any accuracy. We pushed for a stated tolerance from finance and leadership — name the acceptable error, then engineer to it — so the work had a target and an acceptance criterion instead of an open-ended improvement loop.
Say out loud that this is patching, and name the real fix
Domain matching is inherently drifty. It will never reach full coverage, and it degrades as long as upstream capture stays inconsistent and parts of the CRM-to-billing sync are done by hand. The team documented explicitly that the matching work is a patch and that the durable fix is upstream process and capture discipline with the owners of each system — which is a conversation with stakeholders, not a query.
Inventory the CRM properties before promising a source of truth
Several hundred company-level properties, the majority largely empty, a handful maintained entirely by hand — including the revenue, status and churn-date fields everyone quotes in meetings — plus conflicting naming conventions and redundant duplicate-metric fields. The deliverables here are a source-of-truth map with per-attribute precedence rules held in version control, a retirement plan for redundant properties, and scheduled cross-system reconciliation queries with alerting so drift surfaces daily instead of at quarter end.
Force the metric definitions before building any dashboard
Competing definitions of churn were in active use across the business — payment, contract, operational and usage — which means several different churn numbers, all defensible. A KPI catalogue exercise locked revenue, sales, marketing, CS and product metric definitions, and a single effective-churn-date rule, before any dashboard build started.
#Outcomes
Organization-level identity lifted from a small minority to roughly half
Direct-ID linkage on the billing side started as a small minority of customers; the domain-match pass took it to roughly half on unambiguous matches alone, with ambiguous and unmatchable populations separated so the confidence of every row is visible rather than averaged away.
A method that survived a change of billing platform
The same deterministic-then-domain ladder was re-applied to a second billing platform and produced a comparable match profile, plus a flattened table joining product, CRM and billing records per user.
Current state stated honestly rather than favourably
The confident match rate and its error band were published alongside the internal target the business had set, so the remaining gap is measured rather than asserted. That is what makes it possible to argue about.
The real blocker named as governance, not engineering
No agreed error tolerance, hand-maintained CRM fields feeding executive reporting, manual sync steps between CRM and billing, and competing churn definitions — all documented as the upstream conditions that cap what any matching work can achieve.