When a business moves to a new CRM, job system or online shop, most of the planning goes into the new software. The data that has to go into it gets far less attention. It’s treated as a copy-and-paste job for the last week of the project. Data migration deserves more care than that.
That’s usually where things go wrong. Years of duplicates, half-filled records, inconsistent spellings and outdated contacts get poured into the shiny new system, and within a month people are complaining that they can’t trust it.
Cleaning your data before a data migration is dull. It’s also one of the best investments you can make in any new system. Here’s how to approach it.
Take stock before your data migration
Step 1: find out what you’ve got
Start with an inventory. List every place your business keeps the data that’s moving: spreadsheets, the old system, email contacts, accounts software, paper files, someone’s phone. For each, note roughly how many records there are, who maintains it and how up to date it is.
You’ll often find the same customer exists in three or four places, each slightly different. That’s normal. It’s also exactly what you’re about to fix.
Step 2: decide what to bring
Not everything needs to move. This is a chance to leave clutter behind.
- Active customers and open work. Definitely migrate.
- Recent past customers. Usually worth bringing, especially if you’d market to them again.
- Very old or inactive records. Consider archiving them separately rather than importing. Keep them only as long as you have a good reason to.
- Historic transactions. Decide how much history people really need day to day. Sometimes a summary, or read-only access to the old system for a period, is enough.
Under UK GDPR you shouldn’t keep personal data for longer than you need it. A data migration is a natural moment to apply that principle and to check you have a lawful basis for the data you keep. If you’re unsure about retention periods for your sector, take advice.
A migration is the one time moving house forces you to open every box. Don’t just carry them all into the new place.
Clean it up
Step 3: agree the rules
Before anyone starts tidying, agree how things should look. Otherwise three people will clean the data three different ways. Decide on:
- How names are written (for example, first and last name in separate fields)
- Company name conventions (Ltd or Limited, with or without “The”)
- Phone number format
- Address layout, including postcodes in their own field
- A fixed list of options for things like lead source, customer type or status, rather than free text
Write these down in a short document. It becomes the standard for the new system as well, so the data stays clean after launch.
Step 4: clean in the right order
- Remove duplicates. Match on something reliable like an email address, then check near-matches by eye. Decide which record is the “keeper” and merge any useful details into it.
- Standardise formats. Apply the rules you agreed. Spreadsheet functions, or a developer with a short script, can handle a lot of this in bulk.
- Fill the important gaps. Focus on fields the new system relies on. You don’t need every record perfect, just the ones that matter.
- Remove what shouldn’t be there. Test records, obvious junk, and anyone who’s asked not to be contacted, making sure you respect their wishes in the new system too.
Move it across
Step 5: test the import on a sample
Never import everything in one go first time. Take a sample of a few dozen records, including some awkward ones, and import them into the new system. Check them carefully. Do the fields land in the right places? Do special characters survive? Are dates the right way round? UK and US date formats are a classic trap.
Fix what’s wrong, then run the full import. Keep an untouched copy of the original data, just in case.
Step 6: freeze and switch
Data keeps changing while you clean it. Plan a cut-off point: a moment after which updates happen only in the new system. Do a final import of anything changed since your main data migration, make the old sources read-only and tell everyone.
How long will it take?
Longer than you think. Give it proper time in the project plan, and give the person doing it protected time away from their usual work. It’s often the task that decides whether a launch date holds.
Getting help
For large or messy datasets, bulk cleaning and careful importing is a job worth handing over. We handle data moves as part of digital transformation projects, and for website moves specifically, see our website migration service.
