CRM Deduplication and the Write Paths That Cause It

Start with the write path, not the database: Duplicates are output. Something between four and seven paths create records in your CRM: a web form, a list import, a calendar integration, a support tool, a partner spreadsheet, a rep in a hurry. At least two of them create without checking whether the record already exists. Clean the database, leave those open, and you have bought a quarter of quiet. One furniture retailer I worked with had 41,000 records de-duplicated by a previous supplier; 6,200 duplicates were back within eleven weeks. So the merge is the second half of this engagement. Never the first. The audit: I trace every path that can create a Contact, Company or Lead, including the ones nobody remembers authorising, and produce a table: source, monthly volume, what it matches on today, what it should match on. Usually two or three paths get closed outright and one gets a matching step it never had. The matching logic: Written as a specification you can read rather than a setting in a vendor UI. Email normalised: lowercased, plus-addressing stripped, dots handled per provider. Company domain derived from email, with free-mail domains excluded, which is the single rule that stops every Gmail contact collapsing into one account. Phone in E.164. Company names normalised against legal suffixes including the Irish ones: Ltd, Teoranta, cpt, plc, GmbH, SARL. Fuzzy comparison on names happens only inside a matching domain, never across. Three bands, agreed before anything runs. Above the upper threshold, merge automatically. Between the thresholds, a human review queue. Below, leave alone and stop asking. The middle band is where the judgement lives, and I would rather it were wider than a vendor default. Survivorship, field by field: Original source and created date go to the older record. Lifecycle stage goes to the most recent human edit rather than the most recent edit, because integrations touch records constantly and would otherwise always win. Empty loses to non-empty. Owner follows your routing policy, not the merge. Every rule written down and signed off, because on the day somebody asks why a customer's start date changed, "the tool decided" is not an answer. The merge itself: Executed through the HubSpot merge API, or Salesforce Matching and Duplicate Rules with DemandTools where the volume warrants it, in batches, with each merge writing full before-and-after state to your warehouse. Any merge can be explained, and reversed inside the retention window we agree. Monitoring, so it does not come back: A dbt model on Snowflake, BigQuery or Postgres counting new duplicate candidates per source per week, alerting when a source starts producing them again. This is the part that makes the cleanup hold, and it is why I do not sell the cleanup on its own. Not included: Probabilistic matching on person names. Deleting records rather than merging them. Enrichment as a substitute for matching, since Apollo and Clearbit supply a second version of the truth rather than a resolution of the first. Data entry. Migration between systems. Who this is not for: Teams mid-migration who want the cleanup done inside the migration. Do it afterwards, on one system, or you will debug mapping errors and matching errors at once and be certain of neither. Also not for anyone who wants a record count as the deliverable; the deliverable is a duplicate rate that stays flat for a quarter. On GDPR: Merges make erasure requests harder, not easier, and merge logs are themselves personal data. The log is built with a retention period and a documented path for erasure, and I will ask who your data protection contact is on the first call.