Finding duplicate people in a 1.2M-record CRM

The CRM’s built-in duplicate matching missed people who appeared more than once under slightly different names, emails or addresses. A wrong merge is hard to undo: it combines two real people’s histories and their consent to be contacted. The organization needed to find more duplicates without raising that risk.

I wrote the business rules for the matching, analyzed how the existing matching behaved and where it missed, and ran the QA, including which ID survives as the primary record and the checks every merge file had to pass. The matching itself was run by an outside data vendor.

1. 1.Candidates

   Compare only records that share a blocking key, such as a normalized email, phone or name plus ZIP, so the work scales.

2. 2.Score

   Score each candidate pair on how closely names, emails, phones and addresses agree, with nicknames and formatting differences handled.

3. 3.Tier

   High-confidence pairs can merge automatically. The middle tier goes to a person. Low scores are not treated as matches.

4. 4.Review

   A person reviews the middle tier and anything the rules flag as risky.

5. 5.Group

   Link pairs into groups, so if A matches B and B matches C, all three become one person with one decision.

6. 6.Map and check

   Pick one primary ID per group, map every other ID to it, and run the merge-file checks before anything merges.

• Business rules for what makes two records the same person, written so the vendor could implement them and we could test them.
• An analysis of the existing matching: which kinds of duplicates it caught and which it missed, used to set the new rules.
• Confidence tiers that separate automatic merges from merges a person reviews.
• Grouping of linked pairs into one person through their connections, and a mapping from every duplicate ID to one primary ID.
• Merge-file QA: checks every file had to pass before it went back to the CRM.
• Findings written up for leadership.

Scored matching over exact rules alone

Exact matches on email are safe but miss the same person with a new email or a typo. Scoring finds more, and the tiers decide how much risk is acceptable.

Precision over recall for automatic merges

A missed duplicate costs a duplicate mailing. A wrong merge combines two people’s histories and consent, so the automatic tier only takes pairs we are very sure about.

Group first, then decide

Merging pair by pair can give conflicting answers for the same person. Grouping linked pairs first gives one decision and one primary ID per person.

Watch for weak links in groups

Linking through connections can chain two different people together through one weak pair, so groups held together by a weak link go to a person.

• Every merge file had to pass the QA checks before it went back to the CRM: for example, every duplicate maps to exactly one primary, and no ID is both a primary and a duplicate.
• The new rules were compared with the old matching to see what each found, so the increase in candidates was measured, not assumed.

• Described without employer data: the records, IDs, queries and scoring weights stay private. The example rows are made up.
• The vendor ran the matching itself. My part was the rules, the analysis and the QA.

Identity resolution is the core of CRM data quality, customer data platforms and RevOps reporting: every count of customers, members or leads depends on knowing who is the same person.

Snowflake, SQL, Snowflake Cortex, EveryAction/VAN