The join that does not exist
The single most common blocker we meet on data projects is not volume or quality. It is that the customer in the CRM, the account in the ERP and the visitor on the website cannot be reliably connected.
Each system was implemented separately with its own identifier, and the link lives in people's heads or in a spreadsheet somebody maintains. Any model needing a full customer view runs into this immediately.
Do not join inside the model
The instinct is to write a join with fuzzy matching inside the data pipeline. This buries a set of judgement calls in code where nobody can inspect them, and the same record may match differently between runs.
The better pattern is an explicit mapping table: a durable artefact listing which record in system A corresponds to which in system B, with a confidence level and a note on how the link was established.
- It can be reviewed and corrected by people who know the customers
- Corrections persist rather than being redone on every run
- Downstream work can filter by confidence rather than accepting everything
- Coverage becomes measurable, which turns a vague worry into a number
Building the mapping
- Start with anything deterministic - a company registration number, an email address, an account reference appearing in both systems even inconsistently formatted.
- Normalise aggressively before comparing: case, punctuation, company suffixes, address formats.
- Apply probabilistic matching on the remainder, scoring across several fields at once.
- Set a high threshold for automatic acceptance and route the middle band to review.
- Record confidence and method for every link, so downstream users can judge.
The techniques are the same as for finding duplicate customer records; the difference is that here you are linking across systems rather than within one.
Be honest about coverage
Every cross-system dataset should report what share of records were matched, and whether the unmatched ones differ systematically from the matched.
That second question is the important one. If matching succeeds mainly for large, long-standing accounts, then any model built on the joined data describes those customers and not the others. Presenting it as a view of all customers would be misleading.
| Check | Why |
|---|---|
| Match rate by value | Coverage of revenue, not just of rows |
| Match rate by age of account | Older accounts often match better |
| Match rate by channel | Online-only customers frequently match worst |
| Unmatched profile | Whether the gap is random or systematic |
The permanent fix
A mapping table is a workaround. The durable answer is a shared customer identifier written into each system at the point of creation, so future records link by construction.
That is a systems change rather than an analytics one and it takes longer, but it removes the problem permanently rather than requiring maintenance forever. If a data project keeps hitting the same join, that is the business case for making the change.
A join rebuilt from scratch every night is a decision nobody is reviewing.