The same customer under four names breaks every number downstream

The question that exposes this is always a boring one. Someone asks how many customers the business had last quarter, and three people answer with three different numbers. Nobody is lying. The CRM counts rows in a contacts table, the billing system counts accounts that were invoiced, and the loyalty scheme counts sign-ups. Each of those is a defensible definition. The trouble is that all three are inflated by the same underlying fault, which is that a single real customer appears more than once inside each of them.

The duplicates are never exotic. Acme Ltd, Acme Limited, and ACME Ltd. with a full stop at the end. A person who ordered once as a guest using a personal address and later created an account with a work address. A supplier onboarded twice because procurement raised a new vendor rather than searching for the existing one, and the second record has the registered office while the first has the factory. A phone number stored once with a country code and once without. None of this is anyone's fault in particular. It is the predictable output of letting humans type into a box, which every business does, because the alternative is refusing the order.

Once you accept that, the shape of the work changes. Deduplication stops being a cleanup project with an end date and becomes a resolution step that runs on every load, forever, with a small amount of human attention attached to it. Teams that treat it as the former do the cleanse, celebrate, and find the counts drifting again within two quarters, because the channels that created the duplicates are still open and still producing them.

Deterministic matching goes further than most teams try

Before reaching for anything statistical, exhaust the identifiers that are actually identifiers. In India that usually means GSTIN and PAN for businesses. Elsewhere it is a VAT number, a company registration number, or a DUNS. For individuals it is an email address, a phone number, or a government identifier where you are permitted to hold one. When two records carry the same value in one of these fields, they are the same party, and you do not need a similarity score to say so.

The work here is normalisation, not cleverness. Lowercase the email and strip the plus-addressing suffix that Gmail allows. Parse the phone number into E.164 so that a leading zero and a country code stop being two different customers. Strip legal suffixes, punctuation and extra whitespace from company names before comparing. Transliterate consistently if you handle more than one script. Most of the duplicate pairs in a typical customer table fall out at this stage, and they fall out with a reason you can explain to a finance director in one sentence, which matters more than people expect when someone challenges a number.

The probabilistic part is a queue, not an algorithm

Whatever is left after deterministic matching is genuinely ambiguous, and the honest way to handle ambiguity is to route it rather than to resolve it. Score the candidate pairs with whatever suits the field: a string similarity such as Jaro-Winkler for personal names, token overlap for company names where word order moves around, a parsed comparison for addresses so that Flat 2 and Apartment 2 do not read as different buildings. Then set two thresholds instead of one.

Above the upper threshold, merge automatically. Below the lower one, leave the records apart and stop thinking about them. The band in between goes to a person, in a queue, with both records shown side by side and one button each way. That queue is the part teams try hardest to avoid building, and it is the part that decides whether the system is trusted a year later. It also produces labelled examples, which is the only reliable way to tune the thresholds for your particular data rather than for the data in somebody's tutorial.

Anyone counting customers without this is reporting on typing habits.

The pattern, stated plainly

Keep the merge itself as data

The most damaging way to do this is the one that feels most satisfying, which is to pick a winning record, overwrite the loser, and delete it. It feels like progress and it destroys your ability to recover from a mistake. Wrong merges are not hypothetical. Two people with the same name at the same address are frequently a parent and a child. Two companies with near-identical names and a shared registered office are often genuinely separate legal entities that happen to share an accountant. You will get some of these wrong, and you need the mistake to be a row you can delete rather than a record you have to reconstruct from a backup.

One trap deserves naming because it catches careful people. If you treat matching as transitive and take connected components across your pairs, A matching B and B matching C will pull A and C together even when nothing about them is alike. In a table of any size this produces one enormous blob containing several thousand unrelated parties, usually assembled around a shared placeholder value such as an info@ address or a default postcode. Cap component size, exclude values that appear implausibly often before matching, and look at your largest clusters by hand before anyone reports off them.

What breaks downstream, and what it costs to fix later

• Customer counts and retention A returning customer recorded under a new identity is counted as an acquisition, so retention looks worse and acquisition cost looks better than either really is.
• Supplier spend Spend split across four vendor records hides your true exposure to one supplier and quietly weakens your position at renewal.
• Credit and risk Exposure limits assessed per record rather than per counterparty are not limits at all.
• Anything a model is trained on Duplicate parties leak the same entity across training and test splits, which flatters the evaluation and disappoints in production.
• Deletion requests Honouring a request against one of a customer's four records leaves the other three in place, which is a compliance problem rather than a reporting one.

The cost of retrofitting is mostly in the reports that already exist. Once entity IDs change, every historical figure computed on the old grain moves, and somebody will notice that last year's number is no longer last year's number. This is survivable, but only if you can explain it. Keeping the crosswalk versioned lets you reproduce the old figure and the new one side by side and show which merges account for the difference. Deleting the losing records leaves you saying the numbers changed because the data got better, which nobody has ever found convincing.

When not to build this

If you have a few hundred customers and one person who knows all of them, a spreadsheet and a quiet afternoon will beat any pipeline, and you should do that instead. Resolution machinery earns its keep when records arrive faster than a human can look at them, from more than one channel, into systems that different teams own. Below that threshold you are building infrastructure to solve a problem that a conversation would close.

The other case for restraint is regulatory. In a lending or payments context, automatically merging two records is a decision about identity, and getting it wrong is a KYC incident rather than a data quality ticket. The safer default there is to link rather than merge: record that two records are believed to be the same party, expose that link to analytics, and leave the operational systems holding them separately until a human with the authority to do so confirms it. Slower, and correct.

None of this is glamorous work, and it does not demo well. It shows up instead as the absence of an argument, the meeting where three systems agree on the customer count and the conversation moves on to what to do about it. That is the whole return, and in our experience it is worth considerably more than it looks.