1. Start with the decision the record must support
Do not begin with “clean the entire CRM”. Choose one workflow: releasing an order to the warehouse, preparing a purchase proposal or producing a daily sales report. Identify who uses the output, which action they take and what happens if the data is wrong. An item code, quantity and delivery address may be critical for dispatch; acquisition source matters for channel analysis. A field has different importance in different decisions.
The Government Data Quality Framework connects quality with fitness for use. The practical implication is to protect a specific decision rather than create an abstract cleanliness score. For each critical field, record its source, consumer, acceptable state and clarification owner. If nobody can explain why a field is mandatory, revisit the process first. Otherwise people will enter arbitrary values to pass validation. Keep the initial scope narrow enough that the people doing the work can examine real examples and confirm what an acceptable record means.
2. Document the dataset you actually have
Record the source system, entity, extraction date, time zone, filters and row count. “All orders” often means only open orders, one store or records visible to the person exporting them. Without these boundaries, two files with identical names cannot be compared reliably. Do not combine snapshots from different times without preserving their effective dates. Note whether related records were exported at the same moment.
Use the minimum necessary fields in an approved environment. Testing identifier uniqueness rarely requires full customer addresses or private message contents. Preserve an access-controlled original snapshot so findings can be reproduced, but do not create an indefinite personal-data archive just in case. Document export limitations too: omitted archived records, truncated values, formulas instead of results or codes converted into numbers. An item code with a leading zero must retain that zero. Before analyzing thousands of rows, compare a handful against the source interface to verify that the extraction has not introduced the defects you intend to measure.
3. Separate different kinds of defects
Completeness means required information is present; it does not prove accuracy. A phone number can have a valid format and belong to the wrong person. A date can be valid while contradicting the event sequence. Uniqueness also requires a business definition: are you identifying a customer, an account or an order within a particular channel? A technically unique row identifier does not prove that the business entity appears only once.
Track missing values, invalid values, duplicates, cross-field inconsistencies, stale information and disagreement with authoritative evidence separately. Give each measure a clear denominator. One record can have several defects, so the sum of defect counts is not the number of affected records. Do not call a database “95% correct” after checking whether three columns are populated. State which properties were tested automatically, which were checked manually on a sample and which remain untested. That distinction gives a launch decision more useful evidence than a single attractive quality percentage.
4. Profile before writing complicated rules
Start with empty values, distinct values, common categories and unexpected types. Inspect date extremes, negative quantities, spaces in identifiers and alternative spellings of statuses. Power Query provides column-profiling tools; check the scope being analyzed in the interface. Findings from a preview do not automatically describe the full history. If the dataset is large, record the sampling method and the limitations of the resulting evidence.
Break findings down by source, period and creation method. A small overall missing-value rate may be concentrated entirely in a newly connected sales channel. That concentration is more actionable than the average. Keep a few anonymized examples of each issue and review them with someone who understands the workflow. A negative quantity might represent a legitimate return. An audit that automatically replaces every unusual value can damage valid records. Classify and explain first, then apply an approved correction. Retain the rule and its rationale so the same legitimate exception is not repeatedly flagged as an error.
5. Define identifiers and status meanings
An integration needs a reliable way to recognize the same object. Product names and phone numbers generally do not replace stable identifiers. If two stores can produce the same local order number, the integration key must include the source. Agree how product variants are identified: model, colour and size may represent separate stock items rather than arbitrary text in a comment. Check that the relationship remains stable when a display name changes.
Create a short dictionary for fields that drive the exchange. For “ready”, name the actual event: picked, checked, handed to the carrier or merely assigned a shipping label. Specify permitted transitions and legitimate reversals. Different state models require explicit mapping, not just translated labels. Also align units, currencies, precision and time zones. A correct number expressed in the wrong unit remains wrong for the operation. Confirm the dictionary with owners on both sides of the exchange, not only the integration developer. Unresolved meaning is a process decision, not a formatting problem.
6. Turn observations into executable rules
A useful rule includes a condition, scope, severity and response to a violation. “Address is mandatory” is too broad: collection may require a pickup location rather than a delivery address. “Amount must be positive” may exclude legitimate free transactions or adjustments. Describe allowed exceptions before launch rather than adding unrelated exceptions after every complaint. Keep the rule understandable to the person expected to resolve its failures.
Separate blocking defects from warnings. An unknown item code may block warehouse release; a missing marketing tag need not delay a paid order. For each rule, prepare a valid example, an invalid example and an allowed exception. Run them again after configuration changes. If a rule cannot be explained in a clear sentence, a business decision may be missing. The worksheet below illustrates a control-card format, not universal CRM settings. Agree who may change each rule and preserve its previous version so a sudden change in the reported defect rate can be explained.
A data-quality rule card
Teaching rules for order transfer. Agree thresholds and exceptions with your system owners.
Scroll horizontally to read the table.
| Rule | Evidence | Failure response |
|---|---|---|
| Item exists in the approved reference list | Match by stable key, not a similar name. | Hold transfer and route to the reference-data owner. |
| External order key does not create duplicates | A repeated key and source have been tested. | Inspect the existing outcome rather than blindly creating a record. |
| Fulfilment method has required details | Collection and delivery are tested separately. | Request missing information without inventing values. |
Takeaway. A rule must be verifiable on a record. Allowed exceptions also need reference examples.
7. Do not merge similar records without evidence
Two records sharing a phone number are not necessarily duplicates: a household or organization may use a shared number. Similar item codes may identify different sizes. Put suspected matches in a review queue with an explanation instead of automatically merging them in bulk. Agree who can confirm identity and which linked documents must be checked. Make uncertainty visible rather than hiding it behind a confident similarity score.
Before correction, preserve old-to-new identifier mappings and verify recovery options. Deleting a record may orphan historical orders or alter previous-period reports. Start with a small controlled group and reconcile dependent systems afterwards. “Keep the latest value” is not a universal conflict rule: newer information is not necessarily more authoritative. Source and confirmation determine precedence. For unresolved cases, an explicit clarification state is safer than an invented plausible value that silently enters automation. Track the decision and its evidence so another employee can understand why the records were merged, retained or marked for further investigation.
8. Fix recurring defects where they originate
A one-off cleanup will not help if tomorrow’s form permits the same defect. Trace recurring problems to manual entry, imports, obsolete status mappings, integrations or unclear instructions. The intervention may be simple: reference-list selection, validation before saving, a clearer field explanation or a visible hold on incomplete records. Test that the control works for legitimate exceptions as well as ordinary cases.
An exception queue needs an owner, arrival time, reason, next action and resolution state. Operators need a way to return corrected records to the workflow without creating duplicates. Decide whether a source correction updates an already transferred object and how the result will be verified. Do not hide business-content problems in a technical log accessible only to developers. Conversely, do not copy entire customer records into error notifications. A safe identifier, issue category and access-controlled record link are usually a better starting point. Keep the route from detection to resolution short enough that the queue does not become a second neglected database.
9. Set launch criteria and continuing controls
Make the launch decision against critical rules for the chosen workflow. Specify unacceptable defects, issues that permit manual handling and the volume the team can realistically process. A global quality percentage cannot replace that agreement. A small proportion of incorrect payment details presents a different risk from missing optional tags. Explicitly describe what happens when the manual queue exceeds available capacity.
After launch, monitor new defects, unresolved exception age and recurrence of previously corrected causes. Compare equivalent periods and volumes, and annotate rule changes. Repeat reference-record checks when a channel or dictionary changes. A completed audit leaves more than a clean file: it leaves a dataset description, dictionary, validation rules and clear ownership. Those artifacts make quality maintainable without repeated emergency cleanup. Review them alongside process changes rather than treating the original audit as a permanent certificate of correctness.
- Dataset boundaries and critical-field sources are known.
- Identifiers, states and units agree across systems.
- Each blocking rule has tested valid and exception cases.
- An owner handles the exception queue and safe reprocessing.
- Recurring causes are corrected at their source.
Official sources
Primary sources for definitions and further reading. Routio’s practical examples are educational, not customer case studies. Verify system configuration against the documentation for your version.