Finding duplicates that Remove Duplicates will not find.

Remove Duplicates compares the columns you tick and requires an exact match across all of them. That works on a list somebody exported twice in a row, and it fails on every list that has had humans or a web form attached to it.

The reason is that real duplication is not repetition. It is the same person arriving through two doors: once at checkout with a work address, once through a newsletter form with a personal one. Nothing in those two rows is byte-identical except, sometimes, the surname.

The consequence is a list that reports zero duplicates and contains hundreds. That is worse than reporting none at all, because it is now been checked.

Start with email, lowercased and trimmed, because it is the one field people rarely mistype and never abbreviate. That alone typically finds a third of them.

Then match on name together with address, allowing for the flat number being present in one and absent in the other, and for a first name that is Bob in one row and Robert in the other. Two fuzzy fields agreeing is far stronger evidence than one exact field agreeing.

Then match on behaviour, which is the one most people never try. Two records that never appear in the same year, whose orders or gifts alternate, are one person taking turns.

A father and his adult son at one address are not a duplicate. Neither are two colleagues sharing a company email domain and a building. No threshold reliably separates those from a maiden name, and a rule confident enough to catch every duplicate is confident enough to merge two real people.

So split the output in two. Merge what is above the line and list every merge. Hold what is below it as a decision with the evidence for and against each pair, and accept that this pile is not small.

The test for whether your threshold is right is not how many pairs it catches. It is whether you would be comfortable explaining any individual merge to the person it happened to.

Deduplication is only interesting for what it changes, so measure that rather than the count. Retention computed on records is a measurement of your data entry, and it moves by whole points once people are resolved.

The other number worth producing is how many people were on a win-back list they should never have been on, because that is the one somebody can act on this week.