CRM data quality audit: measure the problems before you clean up

Before you start: choose the job the data must do

Imagine a CRM with thousands of beautifully completed contacts and twelve active deals that nobody owns. Its overall completeness score might look reassuring. The sales manager trying to assign those deals would probably disagree.

Data quality depends on the job. An account address needed for delivery deserves a different check from an optional field no one uses. Start with a decision or workflow: routing inbound enquiries, preparing a forecast, handing over a new customer or managing a renewal.

This guide works with a CRM’s saved views, an authorised spreadsheet export or a query tool you already use. You need record IDs, field definitions, relevant history and a business owner who can confirm what correct data looks like. You do not need to buy a cleaning tool to begin.

The UK Government Data Quality Framework distinguishes completeness, uniqueness, consistency, timeliness, validity and accuracy. We use those dimensions below and add a practical relationship check for CRM associations. The measurement procedure and CRM examples are our recommendations; all example counts are illustrative.

• UK Government: data quality dimensions and fitness for purpose ↗

1. Define the eligible records and take a snapshot

Write the population in enough detail that someone else can select the same records. “Deals” is not sufficient. “Open new-business deals in the Netherlands pipeline, as of 09:00 Europe/Amsterdam on the audit date, excluding labelled test records” is a workable starting point.

Decide whether you are assessing a current snapshot or records created during a period. A snapshot answers what is wrong now. A creation cohort helps assess whether a process keeps introducing defects. Preserve that distinction throughout the audit.

1. Name the business use, CRM object, pipeline or segment, timestamp, timezone and exclusions. Identify which related objects are needed for the checks.
2. Save the selection and a permitted dated extract or result. Include stable record IDs and the fields needed for the audit; avoid collecting unrelated personal information.
3. Count distinct eligible IDs. If joins create several rows per deal, establish one row per audited deal or count distinct deal IDs before measuring anything.
4. Record access limits and missing history. If you can see only one team’s records, label the result as that team’s population.

2. Turn “good data” into explicit rules

Choose a few fields or relationships that the workflow depends on. For each, write what passes, what fails and what is genuinely not applicable. A blank next-action field can be a defect; a field filled with “TBD” can still be unusable. Agree how both will be counted.

Keep completeness and accuracy separate. A populated amount may be valid numeric data while disagreeing with the signed agreement. A correct-looking date can be the wrong event date. The check needs to say which question it answers.

1. Give every check an ID and a plain-language rule, such as DQ-01: eligible open deals must have an active owner or an approved queue exception.
2. Specify the eligible population for that rule. An association requirement for company deals may not apply to a different consumer sales motion.
3. Write the defect predicate: owner missing, owner inactive, value outside the accepted range, or required association absent. Keep these categories separate where they need different fixes.
4. Have the business owner confirm the rule and the exception policy before measuring against it. Record a version so future changes are visible.

• UK Government: data quality dimensions and fitness for purpose ↗

3. Run the checks and show the arithmetic

For each rule, count affected eligible IDs and divide by the count of all eligible IDs. Defect rate = affected eligible records ÷ eligible records × 100. Show the counts beside the percentage. If no records are eligible, report “not applicable: zero eligible records”, not a perfect score.

Save the IDs behind each result as well as the total. That makes it possible to investigate and later verify the same records. Keep the measurement date, query or filter definition, rule version and any manual judgement beside the result.

1. Run each check against the saved population. Treat whitespace-only values and agreed placeholders consistently; do not silently replace unknowns with defaults.
2. Store one result per check with affected count, eligible count, defect rate and the IDs or view that produced it.
3. Review a few passing and failing records to confirm the rule behaves as intended. A query can be perfectly repeatable and still test the wrong field.
4. Keep issue rates separate. To report records with any defect, take the union of affected IDs and count each record once.

4. Validate accuracy and review duplicate candidates

Some defects can be found across the whole population with a filter. Accuracy often needs a comparison with a trusted source. Choose the source for each field with its business owner: an executed agreement for contract terms, for example, or the customer’s confirmed information for a contact detail.

Use an investigative sample to understand a particular pattern. Use a random sample, or a documented sample across relevant groups, if the purpose is to estimate the wider error rate. The appropriate sample size depends on the precision and confidence required; there is no universal “check 20 records” rule.

1. Define the source of truth for each accuracy check and record when it was verified. Do not assume a second database is correct simply because it is external.
2. For every inspected record, record match, mismatch or unable to verify, plus the evidence. Keep unverifiable records separate rather than treating them as correct.
3. Build duplicate candidate groups using the identifiers available for that entity. Normalise comparison values consistently while preserving the original values for review.
4. Compare each candidate’s identity, associations and intended record model. Similar names alone do not prove duplication; several subsidiaries can legitimately share a brand or domain.
5. Record the reviewed outcome and the intended surviving record only after identity is confirmed. Keep the merge decision separate from executing it.

5. Trace where each defect enters the CRM

Group affected records by creation source, update source, import batch, team and time. Compare the defect rate within each eligible group, not just its raw number of defects. The largest source of records will often have the largest count even when its process is relatively reliable.

Then trace examples back to their origin. A required field may be requested before the information exists. An integration may map the wrong option. A team may record activity in another system. These causes require different repairs, even if each appears as the same blank cell.

1. Break the baseline down by relevant source or cohort using the same rule. Preserve the denominator for every group.
2. Inspect affected record history and compare with passing records from the same source. Look for the event or configuration change that explains the difference.
3. Ask the person doing the work to show the actual creation or update path. Check whether the rule can reasonably be satisfied at that point.
4. Write the suspected cause, evidence supporting it, alternative explanation and next check. Promote it to confirmed only when the evidence supports that conclusion.

6. Choose what to fix and define the correction

Prioritise by the decision or workflow that breaks. Unowned active enquiries can block work today. Incorrect contract dates can distort renewal planning. Historical formatting inconsistencies may be less urgent unless an active integration or report depends on them.

Separate correcting the records from changing the input process. Fixing 12 records and fixing the rule that created those 12 records are different tasks. Both need an owner, and the right order depends on urgency and the confirmed cause.

1. For each issue, record the affected job, count, consequence, confidence, urgency, dependencies and effort. Leave unsupported revenue-loss estimates out of the priority argument.
2. Define how the correct value will be obtained. Use a verified source or owner review; do not manufacture plausible values to make the completeness percentage improve.
3. Specify the treatment of exceptions and unknowns. A legitimate exception should remain explicit and reviewable rather than disappearing through a hidden filter.
4. Choose the smallest repair batch that can be reviewed and checked. For duplicates, decide how associations, history and downstream systems will be handled before merging.

• HubSpot: record merge behaviour and limitations ↗

7. Test the repair and rerun the original checks

Begin with a reviewed subset and record before-and-after values. Check that the correction fixes the business problem as well as the original defect. Assigning an owner who cannot access the record may satisfy a nonblank check while leaving the handoff broken.

Compare the same population and rule version before and after the repair. Report changes to the population explicitly. If new clean records arrive, a falling percentage alone cannot show that any original defect was corrected.

1. Define expected outcomes and recovery options. Test the normal case, a valid exception, an already-correct record and a repeated update in a suitable environment.
2. After the approved repair, rerun the baseline checks on the original record IDs. Track repaired, unresolved, removed and newly affected records separately.
3. Check dependent workflows, associations and reports for unexpected effects. Confirm the corrected values remain correct after the next relevant sync or update.
4. Run the same check on a new-record cohort to evaluate prevention. Record its own numerator, denominator and observation period.

8. Make the checks part of the working routine

Keep the rules that protect important work and give them a home. A dashboard is useful only if someone knows what to do when it changes. Tie the review to the workflow: import checks after an import, routing checks around new enquiries, and contract checks before a renewal planning cycle.

Agree thresholds from the needs and consequences of the process. A shared blanket target such as “95% clean” can hide a serious defect in a small, important group. When definitions change, keep the old result and explain why the next one is not directly comparable.

1. Assign a business owner for each rule and a technical owner for the check. Record where results and affected IDs can be found.
2. Set the frequency, threshold, exception policy and response. Specify who investigates, who approves a correction and when unresolved issues are escalated.
3. Track both the current backlog and new defects by source. This shows whether the team is clearing history or continually creating the same problem.
4. Review the rules when the sales motion, integration or data model changes. Remove checks that no longer protect a real use, and document changes to active ones.