Duplicate records: an incident report

Incident summary

The symptom is always reported the same way. A manager pulls a number, a rep says the number is wrong, and the rep can prove it. What follows is usually a search for a broken report. The report is fine. The record set under it is forked.

A forked record set means one real customer exists twice. Half the emails hang on copy A. The won deal hangs on copy B. Neither copy is complete, and neither copy is flagged as incomplete, which is what makes this failure mode so persistent. Nothing errors. Nothing turns red. The account simply reports a version of reality that never happened.

How duplicates form

Five vectors account for nearly everything we find. They are worth naming individually, because the fix differs per vector.

Vector one, the unmatched import. A list is uploaded without a match key, so every row becomes a new record instead of updating an existing one. This is the single largest producer of duplicates, and it creates them in bulk within seconds. Every import rule you write exists to prevent this one vector.

Vector two, the visibility wall. A rep searches, finds nothing, and creates a record. The record existed, but permissions hid it. The duplicate is created in good faith and is indistinguishable from a genuine new contact.

Vector three, spelling drift on organisations. Acme B.V., Acme BV, Acme Holding and ACME each look distinct to an exact match. Company names carry legal suffixes, ampersands, abbreviations and trailing whitespace. This is why organisation duplicate rates run higher than person rates.

Vector four, the shared mailbox. Several people at one company all use info@ or a role address. Matching on email alone then merges two different humans into one, which is the mirror image of the problem and considerably harder to undo.

Vector five, integration echo. A form, a chat widget or a calendar sync writes a record on every submission instead of matching first. Low volume, but constant, so it compounds over years.

Damage assessment

Work an example. Take an account with 20,000 person records and a duplicate rate of four percent. That is 800 forked customers. Assume a third of those forks carry a deal on the wrong side, so around 270 deals sit against a record that no report about the customer will ever reach.

Now count the consequences. Customer counts overstate by up to four percent, which looks harmless until it is the denominator. Win rate shifts, because the deal count and the customer count no longer describe the same population. Attribution splits, since the first touch lives on copy A and the closed deal on copy B, so the channel that actually produced revenue is credited with nothing. Anything built on top of this, including the dashboards described in the reporting guide, inherits every one of those distortions without warning.

There is a human cost too, and it is larger than the statistical one. Two reps call the same company in one week. A customer receives a proposal for something they already bought. Each incident is small. Together they teach the team that the CRM cannot be trusted, and a team that does not trust the CRM stops maintaining it. That is the real failure state.

Detection: three keys, not one

Matching on a single key fails in both directions. Email alone splits on role addresses and misses anyone who changed employer. Phone alone collides on a company switchboard. Name alone drowns in common surnames and legal suffixes.

So the three keys are scored separately and then weighed. Email is normalised, lowercased and stripped of plus addressing before comparison. Phone numbers are reduced to digits in international form, so a local notation and a country prefixed one resolve to the same value. Names are compared after removing punctuation, legal suffixes and accents, with a fuzzy distance rather than an exact test, because Jansen and Janssen are two different people and Acme B.V. and Acme BV are one company.

The output is a ranked list of candidate pairs with the reason attached: matched on normalised email, matched on phone and surname, matched on organisation name only. The reason matters more than the score, because the reason is what a reviewer judges.

Why review before merging is not optional

A merge is destructive in one specific way. History combines, which is safe, but conflicting field values do not. The primary record wins, and the loser's value is gone. If the primary has a stale job title, an old address and a blank owner, those blanks and staleness are what survives.

The second risk is a false positive. Two people share a switchboard number and a surname. A confident match on those two keys is wrong, and combining them destroys the record of two separate relationships. Undoing it means reconstructing from email threads by hand. The cost of one bad merge is far higher than the cost of reviewing a hundred pairs.

This is why our tooling puts every candidate pair in front of a person, with both records side by side and the field level conflicts highlighted. The reviewer picks the survivor, resolves the conflicting fields, and confirms. Nothing is combined without that decision, and no batch runs unattended.

When this is not your problem

Be honest about scale. Under roughly five thousand records, with one intake route and one person doing imports, the merge feature inside Pipedrive plus a monthly hygiene pass is enough. Buying tooling for that is overhead with a licence attached.

The threshold moves when any of three things is true. You are consolidating two accounts, which is its own project and is covered in the consolidation guide. Your record count runs into six figures, where manual review of an unranked list is no longer finishable. Or several systems write into the CRM at once, in which case the duplicate rate is a function of integration design rather than of user behaviour, and it will not stop on its own.

Whichever applies, the closing note is the same one every audit ends on. Merging is the repair, not the fix. If intake still creates duplicates, the pairs return next quarter. Find the tap, then clean the floor.

Questions