What’s data cleansing?

Data cleansing is finding and fixing the errors in business data: duplicates, gaps, spelling variations, wrong codes and records that contradict each other. The aim is data your people can use without checking it first.

Every business has some of this. A supplier set up three times because three people entered it. A customer whose address changed in the CRM and not in the ERP. A product described as “SS bolt M10”, “M10 Bolt S/S” and “Bolt, stainless, 10mm”. Each one is small. Together they’re why two reports on the same question come back with different answers.

What cleansing usually involves

• Finding duplicates: the same customer, supplier or product recorded more than once, often with small differences
• Standardising: one way to write units, states, phone numbers, product descriptions and company names
• Filling gaps: missing ABNs, payment terms, categories or contacts, found in other records or flagged for someone to complete
• Correcting codes: old cost centres, retired product codes and classifications that no longer match how the business runs
• Checking against rules: an invoice dated before its order, a negative quantity, a customer with no sales territory

For customer and contact records it’s also a privacy obligation. The OAIC’s guidelines on Australian Privacy Principle 10 list recording personal information in a consistent format, and promptly adding updated information to existing records, among the reasonable steps a business can take to keep it accurate.

Cleaning the file vs fixing the process

Most cleansing is done once, on a spreadsheet, by a capable person under time pressure. It works until the next lot of data arrives. Six months later the same duplicates are back, because nothing changed about how they got there.

The lasting approach is to write down the rules the clean-up followed and run them automatically. Which fields decide that two suppliers are the same? What’s the standard format for a product description? What happens when a record fails a check? Once those rules are agreed, the clean-up can run again next month in minutes, and the person who used to do it by hand gets their week back.

The National AI Centre uses the 1-10-100 rule of thumb to make the same point: preventing data issues early costs far less than fixing problems later.

Cleaning a spreadsheet once doesn’t solve the problem that produced it.

Who decides what “clean” means

Software can find the likely duplicates. It can’t decide which of two addresses is right, or whether two similar product codes really are the same item. Those are business decisions, and they belong with the people who know the customers and the products.

Good cleansing work sends those cases to a person with the evidence laid out, records what they decided, and turns the decision into a rule so nobody has to answer the same question twice.

When it’s worth doing

• Before a system migration, so the mess doesn’t follow you into the new ERP
• Before an AI project, because AI gives confident answers built on whatever it’s given
• When reports keep disagreeing and nobody trusts the numbers
• When someone spends days every month preparing the same file
• After an acquisition, when two sets of customers, suppliers and products need to become one

How long it takes

Usually less than people expect, if the scope is tight. Start with the one data set that causes the most pain, often the customer or supplier master. Profile it to see how bad it really is, agree the rules with the people who own it, fix it, and set the rules to run again. Then move on to the next one.