How bad data shows up
You rarely see bad data directly. You see what it does:
- Two reports that should agree don't, and someone spends a day finding out why.
- A customer gets an invoice at the wrong price, and nobody can say where the price came from.
- Sales for a product line look low, and it turns out half of them are recorded under an old part number.
- A rep asks which of two records is the real customer.
- The owner keeps their own numbers on the side because they don't trust the system's.
The last one is the most telling. When the person running the business keeps separate numbers, the system has stopped being the source of truth.
The common kinds of bad data
Years of real business leave the same kinds of damage in almost every system. Each kind has a different cause and a different source of proof.
Duplicate records
How it happens. A new rep creates the customer again, a name is spelled differently, a business has separate billing and shipping names, or a company is bought and renamed. Products get duplicated the same way.
What it costs. The customer's history is split, so nobody sees the full relationship. Credit limits, pricing and the last order date are each wrong in a different way, and a good customer can look like one who has stopped buying.
How to fix it. Decide whether two records are one customer using addresses, contacts, tax numbers and order history, not names alone. Merge them so every order, invoice and note ends up on one record, and keep a log of what was merged. Two branches of one company may correctly be two customers. That is a business decision to make once and write down.
Facts buried in free text
How it happens. When a system has no place for something, people type it into a notes or comments field: an agreed discount, a delivery instruction, a credit hold, the real ship-to address. Over the years, important facts end up in text that nobody can search or report on reliably.
What it costs. The system can't act on a note. The discount is applied only when the right person reads it, a new rep misses the credit hold, and no report can count any of it.
How to fix it. Read through the notes and group them by what they say. Anything that recurs and affects money or shipping becomes a proper field or rule: a customer price, a hold status, a delivery address. Each note is moved into its field, and the original text is kept as history. Notes that really are one-off remarks stay as notes.
Missing or wrong dates
How it happens. Orders are entered days late, a bulk import stamps every record with the import date, or records are edited after the fact.
What it costs. Anything that depends on time breaks: sales by month, seasonal buying, how long a customer usually goes between orders, how long orders take to ship. What 15 years of order history can tell you shows how many questions depend on the real order date.
How to fix it. Go back to the document the order arrived on. For orders that came by email, the time the email was received is when the customer actually ordered. Don't fill a gap with the invoice date or a sensible-looking date. That is a guess that looks like data.
Products filed under the wrong item
How it happens. Vendors renumber their catalogs, lines are renamed, and one-off items get attached to the nearest standard product.
What it costs. Every report by product is wrong, and nothing looks broken.
How to fix it. Follow the order to the purchase order that filled it and the vendor's part number on that purchase order. Matching by name is usually how the problem started.
Clean into a model, not in place
The usual approach is to tidy records inside the existing system: someone spends a few weeks merging, editing and deleting. That has three problems. The fixes are made from memory, with no record of why. Nothing stops the same errors flowing back in the next day. And when the business later moves to a new system, the fixes are either repeated or carried across along with every old workaround.
A better approach is to clean the data on its way into a defined model of the business. First, describe what the data should look like: customers, products, vendors, orders, purchase orders and invoices, and how they link. Then map each source to that model, whether it is the old system, a database backup, an XLS or CSV export, or a folder of PDFs. Each correction is made in the mapping, not by editing records one at a time, so it is applied again every time the import runs. What an ontology is, explained for business owners covers the idea behind the model.
This only works if the import is fast enough to run again after every correction. Why your data import should be rerunnable explains how. New sources also turn up during a cleanup. We build and change CommerceServer with Buffaly, our own software engineering system. When a file arrives in a layout the agents have not seen, Buffaly writes a new reader for it and uses it in the same session. We consider this key to how Buffaly works. In Buffaly Builds Its Own Tools, an analysis of 1.2 million messages and over 380,000 tool calls from a real Buffaly instance found that 70% of newly created tools were used in the same session they were written.
Work from an exceptions list
Staff notice some bad records, mostly for the customers and products they deal with every day. To find the rest, compare the cleaned data with something independent: a backup, the original documents, the accounting records or a vendor catalog. Every difference goes on one list, the exceptions list, and stays there until it is resolved.
Before fixing anything, write down which document settles which question: the order email for order dates, the purchase order and vendor part number for products, the signed quote for prices. Then nobody argues over each record, and the fixes are consistent.
For the flooring wholesaler that left Zoho, every field brought across was compared with a backup of the old data: 293,986 fields. The comparison finished with zero differences after 43 exceptions were traced back to source documents.
| What differed | Count | How it was resolved |
|---|---|---|
| Order dates | 35 | Recovered from the time the original order email was received |
| Products | 5 | Traced through vendor part numbers |
| Other | 3 | Traced to their source documents one by one |
Some records can't be proven either way. The wholesaler had invoices that could not be proven against a source document. They were kept and labeled as historical source invoices, so only records proven against a source are presented as verified. Labeling is less satisfying than tidying, and more useful: when the uncertain records are marked, everything else can be trusted. How a flooring wholesaler left 15 years of Zoho patches in one month tells the full story.
Fixes create new problems, such as a corrected date that moves an order into a different month, so run the full comparison again after each round of fixes. What was learned moving the wholesaler became the tools for its sister company's migration: the import method and the checks run before and after it.
Keep it clean with rules in the model
Cleanup is wasted if the same problems flow back in. For each kind of bad data, find the cause and add a rule to the model so the system prevents it:
- Duplicates: check for an existing customer by address, contact and tax number before a new one is created.
- Free text: give recurring facts their own fields, so a discount or a credit hold is something the system applies, not a note someone has to read.
- Dates: record the date an order was received separately from the date it was entered.
- Products: keep the vendor's part number on each product and purchase order, and keep old numbers as history instead of reusing them.
- Prices: store agreed prices on the customer as rules, and require a reason for any manual price change on an order.
- Links: create invoices from orders and purchase orders from order lines, instead of typing them separately.
Each is a small change to how the system works, and it only lasts if the system is easy to change. Why business rules belong in the data model explains why a rule should live in the model rather than in a script or in one person's head. The flooring wholesaler now runs its own custom workflow, custom business rules and custom reporting, all supported in CommerceServer. It never had that in 15 years on Zoho.
Shortcuts that make the data worse
- A bulk remove-duplicates button, or find-and-replace without review. It merges records that aren't the same and misses ones spelled differently.
- Deleting records nobody can explain. Label them instead. Deleted records take their history with them.
- Filling gaps with plausible values. A made-up date is worse than a blank, because it looks real.
- Cleanup by whoever has a quiet week, with no proof rules. One person's judgment gets applied to thousands of records.
- Fixing only the reports. Correcting numbers in an export leaves the system wrong, and the next export is wrong again.
When to clean up
The best time is during a move to a new system. Every record has to be touched anyway, and the old system is still there to check against. Cleaning up after the old system has been switched off is much harder. The business data migration checklist lists the checks to ask for, and moving off Zoho without losing history covers keeping the older years.
If you aren't moving, start with the data that drives money: customers, prices and products. Dates and older history matter most for reporting and can follow.
Tell us which numbers you don't trust
CommerceServer is built, run and continually improved by one accountable team, with the improvement capacity agreed for each engagement. Tell us which numbers your team no longer trusts and where your data lives today, and we will tell you what cleaning it into one model of your business would involve. Tell us about your current system, or read how full service works.