Getting the data clean, and getting it across intact

What this actually solves

Two quite different situations lead here.

The first is a system that has become unusable through accumulation. The same customer exists three times in the CRM with slightly different spellings. A mail merge goes out addressed to a name in inconsistent capitalisation with trailing spaces. Every report gives a different total because every report deduplicates differently, or does not. Nothing is broken exactly — the software works — but nobody trusts what comes out of it, so people have started keeping their own side spreadsheets, which makes the underlying problem worse every week.

The second is a system change, and it carries pressure the first one does not. Moving off an old CRM, an old accounting package, an old website. The business will be running on the new system from a specific date, and if the data does not arrive intact, the new system is useless from the first morning and everyone will conclude the new system was a mistake. The migration is the part of that project most likely to fail and the part nobody wants to talk about, because it is not the exciting bit.

This work is unglamorous and it is bought constantly, which is precisely why it is worth doing properly rather than quickly. The risk profile is unusual too — most technical work fails visibly, whereas a botched migration produces a system that looks fine and is subtly wrong in ways discovered months later.

What we do, and in what order

1. Audit the existing data, as a paid piece of work in its own right. How many records, which columns, what proportion of each field is populated, where the formats disagree, how many probable duplicates, and what will not survive the move as it stands. The full quote follows this rather than preceding it. Two to four days.
2. Study the destination and write the mapping document. Every field in the old system mapped to a field in the new one, with an explicit decision wherever there is no clean equivalent — combined, split, defaulted, or deliberately dropped. Mandatory fields in the destination are identified here, because they are what causes records to be rejected. You sign this off before anything moves. Three to five days.
3. Clean. Duplicates resolved on defined rules, formats standardised, dates and phone numbers normalised, names clustered and matched with uncertain cases flagged rather than assumed, blanks marked as missing rather than filled with a guess. Three days to two weeks, driven almost entirely by how dirty the data turns out to be.
4. Run a small test migration and have you check it. A hundred records into the real destination, reviewed by the people who will use it daily — not by us, because they will spot a wrong field in seconds that we would not recognise as wrong at all. This step is never skipped, however tight the timeline. One to two days.
5. Run the full migration. In the agreed order, with dependencies respected, with the old system exported and preserved first. One day to a week.
6. Reconcile and hand over. Counts on both sides, an itemised list of anything that did not transfer with the reason, the retained backup, and a written record of every transformation applied so anyone can trace how a value in the new system came to be. Two to three days.

What moves the price

Volume and the number of systems involved. A few thousand records in one system is a different job from several hundred thousand across four, mostly because reconciliation and testing scale with the number of relationships between objects, not with the row count.

Cleaning only, or cleaning plus a move. A migration adds the destination schema, the mapping sign-off, the test run, the cutover and the reconciliation — and it adds a deadline that cannot slip, which is its own cost.

How dirty it actually is, and whether it holds personal data. These are the two factors that vary most and are least visible from the outside. Dirt drives effort directly. Personal data adds handling requirements, restricted environments and slower, more careful work at every stage. Both are exactly why the audit comes first.

What usually goes wrong

Somebody works on the original file. A find-and-replace runs across a column, it matches more than intended, and there is no earlier version to return to. The rule is absolute — the source file is never edited, work happens on copies, a new copy is taken before each significant operation, and every step is written down. It is tedious and it is the single practice that prevents the worst possible outcome in this work.

Names are matched by assumption. Fuzzy matching cheerfully merges two separate companies whose names differ by a suffix, and once their orders, invoices and contacts are combined under one record, unpicking it is effectively impossible without the pre-merge backup. The correct bias is toward not merging — an unresolved duplicate is an annoyance, a wrong merge is a genuine loss.

No reconciliation is produced. The migration finishes, you ask whether everything came across, and the honest answer available is a shrug phrased as a yes. What is needed is a number and a list — how many existed, how many arrived, which specific records did not and what was missing from each of them. Without that, the first person who cannot find their account assumes the whole migration failed, and there is nothing to show them that says otherwise.