CRM Data Cleanup

CRM Data Cleanup

This skill turns a messy CRM export into a merge plan a human can check, plus files that are ready to import. It works the same way for any vendor, because every CRM exports CSV.

Duplicate cleanup usually fails in one of three ways. Exact-email matching misses most duplicates. Merging on shared inboxes like info@ fuses different people into one. And a merge overwrites good values with blanks, losing history that can't be recovered. Most CRM merges can't be undone, so this skill never merges anything itself. It plans, a human approves, and the CRM's own merge tool does the merging.

Files

• scripts/dedupe.py: the dry-run engine: normalize, block, score, cluster, pick a survivor, merge fields. Needs pandas and rapidfuzz. Uses phonenumbers if it's installed.
• scripts/evaluate.py: scores a run against a labeled truth file (pairwise precision and recall).
• assets/fixture/: 41 synthetic contacts and 14 companies with planted duplicates, plus truth.csv.
• references/field-standards.md: normalization rules, state and country codes, lifecycle stage mapping, and flag meanings.
• references/vendor-notes.md: what merge keeps and destroys in each CRM, import quirks, and links to the official docs.
• references/safe-rollout.md: backup, sample merge, batch execution, and rollback.

Workflow

1. Get the exports. Ask for a full-property CSV of contacts (or leads/people) and, when companies are in scope, companies (accounts/organizations) with record IDs. Record IDs matter because the cleaned files update records by ID.
2. Back up first. Before any merge, confirm the user has a dated full export that includes associations (safe-rollout.md, Phase 0). A backup is the only real rollback.
3. Run the dry run:

   pip install pandas rapidfuzz phonenumbers   # phonenumbers is optional but preferred
   python scripts/dedupe.py --contacts contacts.csv --companies companies.csv \
     --out plan/ --as-of 2026-09-22 [--default-region GB] [--gmail-canonical]

   The script detects columns from common vendor headers. Fix a wrong guess with --col email="E-mail 1" (or --company-col for the companies file).
4. Check summary.json before trusting any number. Confirm column_map (a deal Stage column mapped as lifecycle skews everything), phone_engine, and warnings, such as a block that was skipped for being too large.
5. Present the results by tier. Auto clusters: the count and a few examples. Review queue: every pair, with its reasons. Flags: counts by type. Associations: orphaned and missing links. Lead with the review queue, because it's where the user's judgment is needed.
6. Walk the review queue with the data owner. Record merge or keep in the decision column. Then look at the kept_survivor_conflict rows in merge_plan.csv: those are the values that will disappear.
7. Hand off the execution plan. Merge a sample of 5 to 10 clusters in the native tool, verify, then do the rest in batches. After merging, import the cleaned files keyed on record ID. Follow safe-rollout.md, and check vendor-notes.md for the specific CRM.

Merge-safety rules

• Never auto-merge on a role or shared identifier. info@, sales@, a household inbox, or an office main line shows that records belong to the same organization, not that they're the same person. A shared identifier goes to review at most.
• Auto-merge needs a strong ID plus a compatible name. A strong ID is an exact non-shared email, or an exact non-shared phone. A name match alone, even 100% with the same company, only goes to review, because two people named David Kim at one company is normal in large organizations.
• Re-check every pair inside a cluster. A=B by email and B=C by phone can chain two different people together. The script demotes any cluster with an incompatible pair, or with more than --max-cluster (5) members, to review.
• Never overwrite a non-empty value with an empty one. The survivor's value wins. Blanks are filled from the best-ranked loser. Create date takes the earliest, last activity the latest, and lifecycle stage the furthest along. Losing emails and phones go to additional_emails and additional_phones, so they aren't dropped.
• Pick the survivor on purpose. The default order is most_recent_activity, then most_complete, then oldest_created. Add business_email first when the work address should be the primary email. In HubSpot and Pipedrive the primary's values win every conflict, so the survivor choice decides what's kept.
• Gmail dot and plus folding is opt-in only (--gmail-canonical). Google says dots don't matter for personal Gmail, but they do matter for Workspace domains. Applied by default, it would merge real, distinct mailboxes.
• Stay a dry run. Never call a CRM API, and never write to the input files. A merge runs in the CRM's native tool, which moves activity history. Delete-and-reimport doesn't.
• Flag, don't delete. Stale, test, and invalid records go in record_flags.csv. The data owner decides whether to archive or delete them.