--- name: auditing-crm-data-hygiene description: >- Runs a structured CRM data-quality audit across stage definitions, required fields, duplicate accounts and contacts, stale pipeline, close-date integrity, and picklist consistency, then produces a severity-ranked remediation plan with owners, point-of-entry validation rules, and recurring hygiene workflows. Use when the user says CRM data hygiene, dirty CRM, data quality audit, clean up Salesforce, HubSpot cleanup, duplicate accounts, dedupe, missing fields, field completion rate, stale opportunities, sandbagged close dates, close date integrity, stage definitions, picklist values, lifecycle stage mess, data governance, or "our reporting cannot be trusted". Do NOT use for computing retention or efficiency metrics (see modeling-saas-revenue-metrics), for making a forecast call from the pipeline (see forecasting-pipeline-coverage), or for territory and quota design (see designing-territories-and-quotas). metadata: version: "1.0" --- # Auditing CRM data hygiene Baseline the current state of CRM data quality against documented standards, quantify each defect, and hand back a severity-ranked remediation plan with named owners and prevention rules. One job: the audit and the remediation plan. Executing bulk merges or field updates, and interpreting the resulting metrics, are out of scope. ## Workflow Copy this checklist into your reply and tick items as you finish: ``` - [ ] 1. Define what "clean" means and lock the standard - [ ] 2. Pull the baseline audit extracts - [ ] 3. Test the six defect classes and quantify each - [ ] 4. Trace each defect to its downstream consequence - [ ] 5. Rank by severity and write the remediation plan artifact - [ ] 6. Validate the plan; fix and re-validate before any write - [ ] 7. Specify point-of-entry validation and recurring workflows - [ ] 8. Assign ownership and a quarterly rules review ``` **1. Define what clean means and lock the standard.** Do this before measuring anything, because a defect count without a documented standard is an opinion. Document what clean means, lock required fields, standardize field formats and job-title casing, define an industry taxonomy, and define consistent lifecycle stages ([Default](https://www.default.com/post/crm-data-hygiene)). Clean data is accurate, complete, current, reliable, structured, consistent, and aligned across teams and systems (same source). Write the standard as a table of field, record type, required-when condition, allowed values, and format, because "required" with no condition is unenforceable on records created by inbound forms. **2. Pull the baseline audit extracts.** Baseline for missing fields, duplicate contacts and accounts, outdated lifecycle stages, and inconsistent picklist values, and segment the audit by record type, region, and source system ([Default](https://www.default.com/post/crm-data-hygiene)). Segmentation is load-bearing: an aggregate 82% field completion rate can hide one inbound source at 30%, and only the segmented view identifies the process to fix. **3. Test the six defect classes.** Measure each and record a count, a rate, and the affected pipeline dollars. Exact tests are specified because the definitions must be reproducible next quarter: 1. **Stage definitions.** For each stage, check that a written exit criterion exists and is observable in the CRM (a logged meeting, an attached document, a named economic buyer). Count stages with no observable exit criterion, and count open opportunities whose stage exit criterion is not satisfied. 2. **Required fields.** Compute completion rate per required field per record type and source system: complete records divided by records where the field is required. Report weighted by pipeline value as well as by count. 3. **Duplicates.** Match on email, then company domain, then fuzzy match on normalized name, and document field-priority rules such as "last updated wins" before merging ([Default](https://www.default.com/post/crm-data-hygiene)). Report duplicate clusters, not raw duplicate rows, because a three-record cluster is one remediation action. 4. **Stale pipeline.** Flag records with no activity in a defined number of days ([Default](https://www.default.com/post/crm-data-hygiene)). Set the threshold at the segment's own average sales-cycle length; also flag any open deal aged beyond 2x the average sales-cycle length, since those should be discounted or removed from pipeline ([Clari](https://www.clari.com/blog/pipeline-coverage-best-practices/)). 5. **Close-date integrity.** Count open opportunities with a close date in the past, close dates falling on the final day of a month or quarter at a rate above the base rate, and the number of times each deal's close date has been pushed. Report pushes of three or more separately, because that population predicts slip better than stage. 6. **Picklist and lifecycle consistency.** Count distinct values per picklist against the approved list, count free-text values in fields that should be constrained, and count records whose lifecycle stage contradicts their linked objects (for example a closed-won opportunity on a lead-stage account). **4. Trace each defect to a downstream consequence.** Name the specific business break, because remediation gets funded on consequence and not on defect rate. The documented consequences are broken scoring models, broken email sequences, broken routing, skewed funnel-conversion metrics, wasted outbound budget, and damaged deliverability ([Default](https://www.default.com/post/crm-data-hygiene)). Tie stale-pipeline findings to coverage directly: a team reporting 4x coverage with 30% stale deals actually has 2.8x ([Clari](https://www.clari.com/blog/pipeline-coverage-best-practices/)). **5. Rank by severity and write the plan artifact.** Severity = affected pipeline dollars or affected record count, times blast radius (how many downstream systems consume the field), divided by remediation effort. Judgment step: order the plan by that ranking but override it where a cheap prevention rule stops a defect from recurring, and say why. Produce the plan as a file before any records change, because bulk merges and mass field updates are irreversible in most CRM configurations. **6. Validate the plan, then re-validate.** Run all seven checks against the plan artifact. If any fails, correct the plan and re-run all seven. Only authorize a write, merge, or bulk update after every check passes. ``` - [ ] Every defect has a count, a rate, and a reproducible query or filter - [ ] Every duplicate merge lists surviving record ID and field-priority rule - [ ] Every bulk action has a record count and a sample of 10 spot-checked rows - [ ] Every bulk action states its rollback path (export taken, or reversible) - [ ] No action touches records outside the audited segment - [ ] Each action has one named human owner and a due date - [ ] Each defect class has a matching prevention rule, not just a cleanup ``` Sequence merges before enrichment and enrichment before field-completion reporting, because enriching duplicates doubles the vendor cost and then merges discard the paid-for values. **7. Specify prevention.** Validate at the point of entry by enforcing required fields, applying format validation including valid email domain, and restricting open text, with rules varying by record type, source, and intent level ([Default](https://www.default.com/post/crm-data-hygiene)). Enrich incomplete data with multiple vendors under waterfall logic, prioritizing high-intent records and ICP segments (same source). Align integration logic so enrichment, marketing automation, and sales-engagement tools share field mappings, update logic, and funnel definitions (same source). Schedule recurring weekly or monthly hygiene workflows that flag stale records with no activity in X days, alert owners on missing firmographics, and auto-update from trusted sources (same source). **8. Assign ownership.** Assign ownership by record type, team, or system, with RevOps owning cross-functional QA and process governance and frontline owners such as SDR managers owning data discipline ([Default](https://www.default.com/post/crm-data-hygiene)). Document field mappings, enrichment rules, and scoring logic in one place, maintain a data-rules playbook in RevOps documentation, revisit the rules quarterly, and include hygiene standards in onboarding for SDRs, AEs, and marketers (same source). Address the named root causes explicitly in the plan: no clear data ownership, manual entry under pressure, disconnected tools, no point-of-entry validation, inconsistent fields and lifecycle definitions, legacy data bloat, no routine audits or dedupe, and high rep turnover (same source). ## Thresholds No numerical thresholds are published for field completion rates, duplicate volumes, or enrichment match rates ([Default](https://www.default.com/post/crm-data-hygiene)), so set targets from this instance's own trailing baseline and label every target in the output as local convention rather than benchmark. State the baseline value next to each target so a reader can see the improvement being asked for. The only externally grounded threshold available for pipeline records is the 2x average-sales-cycle age limit for open deals ([Clari](https://www.clari.com/blog/pipeline-coverage-best-practices/)). ## Output format Use this exact section order and column set, because the audit is re-run and diffed against the prior period. Commentary wording is yours to adapt. ``` ## Audit scope System(s) | Record types | Segments | Extract date Standard version | Records in scope ## Findings | # | Defect class | Test / filter | Count | Rate | Pipeline affected | Downstream break | Severity | |---|---|---|---|---|---|---|---| ## Remediation plan | # | Action | Type (merge/update/config/process) | Records | Owner | Due | Rollback | Prevention rule added | ## Prevention and governance Point-of-entry rules: Recurring workflows: Ownership: -> Rules review: quarterly, owner ## Targets (local convention, not benchmark) | Metric | Baseline | Target | By when | ``` ## Gotchas - Fuzzy name matching over-merges before it under-merges. Franchise locations, regional subsidiaries, and reseller-plus-end-customer pairs look like duplicates and are not, so require a domain or address agreement before auto-merging anything above a set ACV. - Merging accounts silently reparents opportunities and can move closed-won revenue between segments, breaking historical attainment reporting. Snapshot segment-level closed-won before any merge batch. - A push counter is more diagnostic than a stale-activity flag, because a rep can keep a dead deal fresh with logged internal activity while the close date slides every month. - Clustering of close dates on the final day of a quarter is a data-entry artifact, not buyer behaviour. Treat those dates as unset when computing cycle length, because they compress the measured cycle and make every velocity number optimistic. - Adding a required field without a backfill plan converts a completeness problem into an edit-blocked problem: reps cannot save existing records, so they clone new ones and duplicate volume rises. - Enrichment match rate is not a data-quality measure. A vendor can match 95% of records and still write the wrong industry values, so spot-check a sample of 25 enriched records against the source before trusting a field for routing or scoring. - Never define "clean" from the CRM's own required-field settings, because those reflect what an admin once configured and not what current reporting consumes. Derive required fields from the fields that live dashboards, routing rules, and scoring models actually read.