Cleaning Messy Business Data for Machine Learning

Cleaning for a model is not cleaning for a report

A finance report needs totals to reconcile. A machine learning model needs something subtler: every record prepared the same way, today and in six months, so the patterns it learned still apply when new data arrives.

That changes what 'clean' means. A report can tolerate someone manually correcting last quarter's figures in a spreadsheet. A model cannot, because the correction will not be there next time the data flows through. Cleaning done by hand is cleaning you will have to do again, and the second time someone will forget a step.

The mess we usually find in smaller businesses

• The same customer entered three times with slightly different names and postcodes
• Dates stored as text in two formats, often American and British in the same column
• Product codes that changed after a system migration, with no mapping kept
• Placeholder values such as 0, 999 or 01/01/1900 standing in for 'unknown'
• Free-text fields carrying information that should have been a dropdown
• Test orders, internal accounts and staff purchases mixed into real sales
• Currency and VAT handled inconsistently between regions

Every one of these will distort a model in a specific way. Placeholder zeros are the sneakiest: a model trained on order values will happily learn that a large group of customers spends nothing.

The order we clean in

Order matters, because some fixes depend on others and doing them out of sequence creates new errors.

1. Remove what is not real. Test records, internal accounts, cancelled duplicates. Agree the rules with the people who know.
2. Fix types and formats. Dates as dates, numbers as numbers, one currency, one unit.
3. Turn placeholders into genuine missing values. Zero and 'unknown' are different things and must be stored differently.
4. Resolve identities. Decide which records refer to the same customer, product or site.
5. Standardise categories. Map free text and old codes to a controlled list, keeping the original alongside.
6. Handle outliers deliberately. Investigate the extreme values before deciding whether they are errors or your best customers.
7. Check the result against reality. Totals should roughly match known figures from finance.

Do not over-clean

There is a strong temptation to fill every gap. Resist it until you understand why the gap exists.

Missing data is often informative. A customer with no phone number recorded may be an online-only buyer who behaves differently. A missing delivery date may mean the order was collected. Filling those gaps with an average removes a signal the model could have used. We usually keep a flag saying the value was missing, alongside any filled-in value.

Duplicates and identity: the slow bit

Working out that 'J. Smith Plumbing Ltd', 'Smith Plumbing' and 'John Smith' at the same postcode are one customer is called entity resolution, and it is usually the most time-consuming part of cleaning business data.

Exact matching catches little. A sensible approach combines several fields such as postcode, email domain, phone number and a fuzzy name comparison, scores each candidate pair, merges the confident matches automatically and puts the uncertain ones in front of a person. For a business with 20,000 customer records, the manual review is often a day or two, not weeks.

If the duplicates come from how data enters your systems in the first place, it is worth fixing that at source. Our piece on master data management for smaller businesses covers the longer-term version.

Make the cleaning repeatable

Every cleaning step should live in code, usually Python or SQL, run in the same order every time, and be kept in version control. When a new month of data arrives, it passes through exactly the same steps as the training data did.

Alongside the cleaning, add automated checks that fail loudly: row counts within an expected range, no future dates, no negative quantities, category values from the known list. We describe these in more detail in data quality checks that run automatically. A model fed silently broken data does not crash. It just starts making worse predictions, and nobody notices for weeks.

How long does it take, and is it worth it?

On most of our projects, understanding and cleaning the data takes more time than building the model. Anyone who quotes a machine learning project with a token week for data preparation has either seen your data already or has not thought about it.

The good news is that the work is rarely wasted. Clean, joined data improves reporting, forecasting and day-to-day operations whether or not the model succeeds. When SpiderHunts scopes a project through our data science service, we price the cleaning as its own stage so you can see it and stop there if the findings suggest a model is not worth building.

Frequently asked questions