crm_data_hygiene v1.0.0

name: crm_data_hygiene description: "Clean CRM contact and company data: normalize names, companies, emails, phones, and domains to fixed standards, detect duplicates with scored match rules, build merge plans with explicit survivorship rules, and run a recurring hygiene pass so bad records never reach outreach lists. Triggers when someone asks to clean a CRM, dedupe contacts or companies, fix messy HubSpot, Salesforce, or Pipedrive data, prep a list before a campaign, or set field standards for imports."

CRM Data Hygiene

Purpose

Keep a CRM usable: one record per real person and company, every field in a standard form, and a repeatable pass that catches new mess before it ships in outreach. This skill covers the standards, the matching and merge rules, and the cadence. It ships a working script, bin/crm_hygiene.py, that normalizes a contacts CSV, audits it, scores duplicate pairs, and writes a merge plan. The script never merges or deletes anything itself. It produces a plan a person approves and a CRM or API step executes.

Workflow

1. Export and snapshot. Pull contacts and companies to CSV with IDs, created and updated timestamps, owner, and lifecycle stage. Save the raw export untouched. Every later claim about what changed traces back to it.
2. Set the field standards first. Adopt the standards in references/field-standards.md before cleaning anything: E.164 phones, lowercased email domains, bare domains with no protocol or www, legal suffixes stripped from company match keys, USPS Publication 28 suffixes for US addresses. Cleaning without a written standard just moves the mess around.
3. Run the hygiene script. python3 bin/crm_hygiene.py contacts.csv --cleaned cleaned.csv --duplicates dupes.csv --merge-plan plan.csv. Read the JSON summary: audit counts (missing, invalid, unreachable records), pair counts by action, and cluster count.
4. Fix at the source before fixing records. Sort findings by cause: one import, one form, one integration usually explains most of them. A zap or sync that creates records without searching by email or domain first will refill the CRM with duplicates the week after a cleanup. Fix that path, then clean.
5. Work the duplicate pairs by band. Merge candidates (score 90 and up, built on exact email, exact E.164 phone, or name plus domain plus company all agreeing) go into the merge plan. Review pairs (70 to 89) get a human decision, one pair at a time. Below 70, treat as distinct and move on.
6. Approve and execute merges. A person approves the merge plan before anything runs. Execute in the CRM UI or API, cluster by cluster. After merging, spot check three clusters: associations, activities, and deals from both originals should sit on the survivor.
7. Gate the outreach list. Before any campaign send, run the script on the target list. Block records with no valid email from email sends, records with no working phone from call lists, and any record created since the last pass that has not been normalized yet.
8. Run the recurring pass. Weekly: script run on new and changed records, review queue cleared. Monthly: merge plan executed, audit trend compared to last month. Quarterly: standards reviewed, match thresholds retuned against the review decisions people actually made.

Output Contract

Every hygiene run produces:

• Cleaned CSV: original columns plus match keys (email_key, phone_e164, company_key, domain_key, person_key) so anyone can re-run or audit the matching.
• Duplicates CSV: one row per candidate pair with both IDs, score, action (merge_candidate, review), and the plain-language reasons for the score.
• Merge plan CSV: one row per cluster with the survivor ID, the IDs it absorbs, and the field-by-field values the merged record carries, chosen by the survivorship rules in the reference file.
• Audit summary: counts of missing emails, invalid emails, unreachable records (no email and no usable phone), role addresses, free-mail domains on company contacts, unparseable phones, and missing companies.
• A short run note: what caused the new dirt (import, form, integration, manual entry), what got fixed at the source, and the duplicate rate before and after.

Operating Rules

• A wrong merge is a data incident, a missed merge is an annoyance. Thresholds stay biased toward review. Only strong keys (exact email match key, exact E.164 phone, a shared external ID) justify the merge-candidate band on their own.
• Merges are effectively permanent in HubSpot and Salesforce. The plan is the audit trail: keep the raw export, the duplicates file, and the approved plan together for every run.
• Never delete a losing record outside the CRM merge flow. Merging moves associations and activity history to the survivor; deleting loses them.
• Fix the creation path before the cleanup, always. Note which import, form, or integration made each duplicate cluster when the pattern is visible, and say so in the run note.
• Email case: lowercase the domain always (DNS is case insensitive, per RFC 5321). Lowercase the local part only inside the match key, never in the stored value, because the standard treats the local part as case sensitive.
• Phone numbers are only trustworthy in E.164 form or in the national format of the default country code passed to the script. A UK number written as 020 7946 0958 with no country code is ambiguous; flag it in the audit, do not guess.
• Free-mail domains (gmail.com, yahoo.com, and similar) never become company domains, and contacts on free-mail domains never auto-associate to a company by domain. Role addresses (info@, sales@, support@) are flagged, not merged into a person's record.
• Nicknames are a matching problem, not a merge reason. Bob and Robert with no shared email, phone, domain, or company signal stay two records and a review note.
• Keep the standards file under version control with the queue of accepted exceptions. A standard nobody can find will not survive the next import.