Data cleansing is the work of finding and fixing the errors, gaps and duplicates in your business records, and the best time to do it is before you move to a new system. Customer lists, job histories, product catalogues and supplier details all collect small problems over the years. Most of them go unnoticed day to day, then surface all at once when the data is loaded into software that expects it to be tidy.
This guide explains what clean data actually means, how to measure the state of yours, the clean-up jobs that come up on almost every project and how to stop the same problems creeping back. It is written for business owners and managers, not data specialists, and it works whether you have a few hundred records or a few hundred thousand.
Why data cleansing comes before a migration
New systems are usually stricter than old ones. A spreadsheet will accept a phone number in the date column and a customer with no name. A modern CRM or job management system will often reject those records at import, or accept them and then behave oddly. Either way, the problems you skipped now have to be fixed under time pressure, during the switch-over, with staff waiting to start using the new system.
There are other good reasons to do your data cleansing first:
- It is cheaper. Fixing a record once, before it moves, is quicker than fixing it in the new system and explaining why reports look wrong.
- Automations depend on it. Reminders, invoices and integrations in the new system will act on whatever data they are given, including the wrong email address.
- You move less. Data cleansing goes hand in hand with deciding what you no longer need, so the migration is smaller and easier to check.
- Staff trust the new system. If the first thing people see is duplicates and gaps, confidence in its data quality drops quickly.
Data cleansing is one stage in a wider project, and our data migration checklist covers the rest, from mapping fields to planning the cut-over. It is also a core part of our data structure and migration work.
What good data quality actually means
The UK Government’s Data Quality Framework was written for public bodies, but its ideas translate well to any business. It makes the point that data quality is more than just data cleaning, and it uses six core dimensions, as defined by DAMA UK, to describe what good data looks like. Here they are with small business examples:
- Completeness: the records that should be there are there, and the essential fields are filled in. Example: customers with no email address or postcode.
- Uniqueness: each customer, product or supplier appears once. Example: “Fred Smith” and “Freddy Smith” recorded as two customers when they are the same person.
- Consistency: values do not contradict each other, within one system or across several. Example: a customer marked as active in the CRM but archived in the accounts.
- Timeliness: the data is up to date enough for the job it does. Example: a contact list where nobody has recorded that people have moved on.
- Validity: values are in the expected format and range. Example: dates stored as text, or a date of birth in the future.
- Accuracy: the data matches reality. Example: a valid-looking phone number that belongs to someone else.
The framework also talks about data being fit for purpose rather than perfect. That is a useful test for a migration. You do not need flawless records, but you do need data that is good enough for the new system to do its job.
Step 1: measure before you clean
Resist the urge to start fixing things straight away. First, take an honest measurement of what you have, so that you can prioritise and later show that the work made a difference. This is often called data profiling. It is the first step in any data cleansing project, and for most small businesses a spreadsheet is enough.
For each important table or list, count:
- how many records there are in total, and how many are still in use;
- how many are missing each key field, such as email, phone, postcode or VAT status;
- how many likely duplicates there are, for example records sharing an email address or postcode and surname;
- the distinct values in fields that should come from a short list, such as job status or customer type;
- values that look wrong: dates in the future, negative quantities, postcodes that do not fit the UK format.
Write the numbers down. “1,240 customers, 96 without an email address, around 70 likely duplicates” is far more useful than “the data is a bit messy”, and it tells you where the data cleansing effort should go.
Step 2: agree the rules
Before anyone edits a record, agree what “correct” looks like. This step is easy to skip in data cleaning projects, and skipping it is why two people cleaning the same list end up with two different results. A short document, sometimes called a data dictionary, should cover:
- Formats: how dates, phone numbers, postcodes and names should be written. UK dates in day, month, year order are worth stating explicitly, because data exported from some software uses the American month-first order.
- Required fields: what every record must have before it can move.
- Allowed values: the list of statuses, categories or types the new system accepts, and how each old value maps onto them.
- Merge rules: how to decide whether two records are the same, and which values win when they differ.
- Source of truth: where several systems hold the same information, which one is trusted.
Involve the people who use the data every day. They know that “Mrs J Patel” and “Jaya Patel” at the same address are one customer, and that a job status of “on hold” really means “waiting for parts”.
Step 3: the common data cleaning jobs
Every project is different, but the same tasks come up again and again.
Merging duplicates
Duplicates are usually the biggest data cleansing job and the riskiest. Start with exact matches, such as the same email address, then move on to likely matches, such as the same surname and postcode. Review likely matches by hand before merging, and keep a log of which records were combined. Merging two different people is far worse than leaving a duplicate in place.
Standardising formats
Make postcodes upper case with a space, phone numbers a consistent format, names properly capitalised and dates real dates rather than text. Split combined fields, such as a single “name” column, into the separate fields the new system expects. Spreadsheet functions such as TRIM, which removes stray spaces, catch a surprising number of problems on their own.
Filling or flagging gaps
Some missing values can be filled from another source, such as an email address held in your accounts but not your CRM. Others cannot, and inventing them is worse than leaving them blank. Flag incomplete records instead, so they can be completed when the customer next gets in touch.
Mapping old values to new ones
Old systems collect inconsistent categories over time: “Plumbing”, “plumbing”, “Plumber” and “PLB” may all mean the same thing. Map every distinct value to one of the new system’s options, and decide what happens to anything that does not fit.
Removing what you do not need
Test records, long-dead enquiries and data kept “just in case” should be archived or deleted rather than cleaned. If the data is about people, UK GDPR expects you to hold it no longer than you need it. The ICO’s guidance on storage limitation recommends standard retention periods and regular reviews, and our guide to UK GDPR and data migration covers the data protection side of a move in more detail.
Step 4: take extra care with accounting data
Customer and supplier records in Xero, QuickBooks or another accounts package are attached to invoices, payments and tax history, so treat them with care. Merging two contacts in an accounts package can combine their transaction history, and some changes cannot be undone. Agree any accounting clean-up with your accountant or bookkeeper first.
When accounting data is part of a migration, reconcile it before and after the move: the number of open invoices, the total owed by customers and the total owed to suppliers should match. If you are connecting systems at the same time, consistent customer records matter even more, because integrations match records between systems. Our guide to connecting Xero or QuickBooks to your website and CRM explains why.
Step 5: fix the causes, not just the symptoms
Data cleansing without changing how data is captured means doing the same job again in a year. The Government Data Quality Framework recommends using root cause analysis to solve data quality issues at source, rather than applying temporary fixes, and the same advice works for a small business. For each common problem, ask how it got there:
- duplicates often come from staff not searching before adding a customer, or from two systems both creating records;
- inconsistent categories come from free-text fields where a drop-down list should be;
- missing fields come from forms that let people skip them;
- out-of-date details come from having no routine for updating them.
The new system is the ideal place to fix these: required fields, drop-down lists, duplicate warnings and validation on web forms all protect data quality as it is entered. If much of your data currently lives in spreadsheets, our article on the signs your business has outgrown spreadsheets explains why they make good data quality so hard to sustain.
Good habits during data cleansing
- Never clean the only copy. Keep an untouched export of the original data, stored securely, so any change can be checked or reversed.
- Log what you change. A simple record of each rule applied and each manual merge makes the work auditable and repeatable.
- Freeze or track new changes. If the old system stays in use during cleaning, agree how new and edited records will be caught before the final move.
- Use scripts for scale. Spreadsheet tools are fine for hundreds of records. For tens of thousands, or for data cleaning that needs repeating at every trial migration, rules written as code are faster and more consistent.
- Measure again at the end. Rerun the counts from step 1 so you can see, and show, the improvement.
An illustrative example: a heating engineer’s customer records
To make this concrete, here is an illustrative example. The business is made up, but the data quality problems are typical of what we find in small firms.
A heating and plumbing firm is moving from an old desktop job system and three spreadsheets to a cloud job management app. Profiling shows around 3,000 customer records, many without email addresses, a few hundred likely duplicates created when office staff added customers rather than searching for them, and fourteen different spellings of “boiler service” in the job type field.
The office manager and an engineer spend an afternoon agreeing the rules. Exact duplicates are merged automatically, likely duplicates are reviewed by hand over several days, and job types are mapped to six standard categories. Customers who have not booked anything for many years, and who are not needed for warranty or legal reasons, are archived rather than moved. In the new system, the customer email field is required for new bookings and job types come from a drop-down list, so the same problems do not build up again.
How long data cleansing takes
It depends far more on the state of the data than on the number of records. A tidy list of a few thousand customers might take a day or two to check and prepare. A messy set of overlapping spreadsheets and an old database can take weeks, mostly because of the manual review that duplicates and judgement calls require. The main factors are:
- how many sources need combining, and how much they overlap;
- how many likely duplicates need a human decision;
- how much history needs to move;
- how quickly the people who know the data can answer questions.
Build the time into the project plan from the start. Data cleansing squeezed into the last week before go-live is a common reason for migrations to slip. When we joined JEENI’s product team to build their new entertainment-on-demand platform for creatives, part of the job was migrating their data from the old system into the new one. You can read more in our JEENI.com case study.
Data cleansing: common questions
Should data cleansing happen before or after migrating?
Before, wherever possible. Some tidying is easier in the new system, such as adding information that the old system could not hold, but duplicates, formats and unwanted records should be dealt with before the move, so that the new system starts clean.
Can data cleansing be automated?
Partly. Formatting, exact duplicates and value mapping can be handled by rules. Likely duplicates and judgement calls usually need a person, at least to approve what the rules suggest. The best approach combines the two.
How clean is clean enough?
Clean enough for the new system to work and for staff to trust it. Focus on the fields that drive the most important processes, such as contact details, balances and statuses, rather than trying to perfect every record.
Does the law require good data quality?
For personal data, UK GDPR’s accuracy principle requires reasonable steps to keep it correct and, where necessary, up to date. The ICO’s guidance on the accuracy principle explains what that means in practice. A migration is a natural moment to take those steps.
The bottom line
Data cleansing is not glamorous, but it decides whether a new system feels like a fresh start or the same old problems in a new place. Measure what you have, agree the rules, fix the common problems, remove what you do not need and change the way data is captured so it stays clean.
If you are planning a move and are not sure how much work your data needs, tell us what you are moving from and to or call us on 01482 212499. We will take an honest look and tell you what needs cleaning, what can be left and what is not worth moving at all.
