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.