CRM Data Migration: Cleaning, Mapping and Cutover

A CRM data migration rarely fails on the day of the switch. It fails three weeks later, when a salesperson opens their biggest account and finds the notes gone, the owner set to "Admin" and two copies of every contact. By then the old system is switched off and nobody trusts the new one.

Almost all of that is preventable with unglamorous preparation. This is the order we work in, whether the destination is a packaged CRM or one we have built.

Take an inventory before exporting anything

Start with a list of what exists in the old system: every object (leads, contacts, companies, deals, activities, custom objects), the number of records in each, and every field with how often it is filled in. If the CRM cannot report field usage, export a sample and count blanks in a spreadsheet.

The inventory usually shows many fields that are empty, abandoned or duplicated ("Phone", "Phone 2", "Mobile (old)"). Decide now what stays behind:

• fields nobody has filled in for years,
• leads with no contact details or no activity since a date you choose,
• test records, spam submissions and bounced addresses,
• records you no longer have a reason to keep. Data protection rules in many places expect you to delete personal data you do not need, so ask whoever handles privacy before carrying old contacts across.

Every record you leave out is one less to clean, map and check. Archive the full export somewhere safe so "left behind" does not mean "lost".

Clean in a staging copy, not in the new CRM

Load the export into a staging database or, for small volumes, a set of spreadsheets, and clean there. Cleaning after import is slower, and by then automation in the new system may have emailed your duplicates.

Deduplication

Pick matching keys that are reliable. For people, a lower-cased, trimmed email address is the best key. For companies, the website domain works better than the name, since "Acme Ltd", "ACME Limited" and "Acme" are one company. Phone numbers match only after you have normalized them to one format with a country code.

Never merge automatically on name alone. Auto-merge the exact key matches and send the fuzzy ones to someone who knows the accounts. Agree a survivor rule first: which record wins (usually the most recently active), and that the loser's notes, deals and activities move to the survivor.

Normalization

Make values consistent before they hit required fields and picklists: one spelling per country and state, one date format, consistent capitalization of names, and a fixed list for free-text fields such as "Industry" and "Lead source" that have collected dozens of variants. Check the export's character encoding as well. Save CSV files as UTF-8 and make sure quotes and line breaks inside notes are escaped the way the CSV format in RFC 4180 describes, or long text fields will spill into the wrong columns.

Build the field mapping sheet

The mapping sheet is the specification for the whole migration. It has one row per source field, and the people who use the data sign it off.

Source field	Target field	Type	Conversion	If empty or invalid
Contact: Full Name	First name, Last name	Text	Split on the last space, review exceptions	Put the whole value in Last name
Deal: Stage	Opportunity stage	Picklist	Old values mapped one by one to new stages	Reject the row and report it
Company: Country	Billing country	Picklist	Free text converted to standard country names	Leave blank
Deal: Amount	Amount, Currency	Number	Strip symbols, set currency from the account	Zero, flagged for review
Contact: Opted out	Email opt-out	Checkbox	"Yes", "Y", "1" become true	Treat as opted out

Pay particular attention to picklists and multi-select fields (every old value needs a destination), fields that are required in the target but often blank in the source, text longer than the target allows, and dates stored without a time zone. Consent and opt-out flags deserve the most cautious default, as in the last row.

Keep the old IDs

Add a "Legacy ID" field to every object in the new CRM and load the old system's record ID into it. This one field does three jobs. It lets you rebuild relationships, because contacts point to companies by old ID and are linked after both are loaded. It makes the load repeatable, since you can update by legacy ID (an upsert) and avoid creating a second copy on a re-run. Salesforce, for example, calls this an External ID field, and most CRMs with an import API have an equivalent. And it lets you trace any record back to its source when a user questions it.

Load in dependency order: users first, then companies, contacts, deals, deal line items, activities and notes, and attachments last.

Owners, dates and history are what users notice

Owners. Build a user mapping table from old user to new user before loading anything. Decide what happens to records owned by people who have left: reassign to their manager, to a named queue, or to an inactive user if the new CRM permits it. Leaving it to the default makes the importing admin the owner of half the database.

Dates. By default, most CRMs stamp imported records with the import date and the importing user. Some let you set the original created date during import if a specific permission is switched on. Others do not, and you will need custom "Original created date" fields. Find out which applies before the first test load, because reports on lead age and sales cycle length depend on it.

History. Notes, logged calls, meetings, tasks and email threads are the context salespeople rely on. They are also the largest and messiest part of the data. Decide how far back to go, whether closed tasks come across, and whether emails are imported as activities or left in the archive. Stage-change history is rarely importable as real history. A summary note on each deal is a workable substitute.

Attachments and emails travel separately

Files are rarely part of a standard export. They come out through an API or a separate bulk download, often with rate limits, and go in the same way. Treat them as their own workstream: estimate the total size, check storage limits in the target, keep a manifest that maps each file to its parent record by legacy ID, and verify counts and sizes after upload. Links inside note text will still point at the old system, so search for the old domain and decide whether to rewrite them.

Rehearse the CRM data migration with test loads

Use a sandbox or a trial instance of the new CRM. Run a small load first (a few hundred records per object, chosen to include awkward cases), fix the mapping, then run the full volume at least once and time it. The timing tells you how long the real freeze must be.

After each rehearsal, reconcile:

• record counts per object, source against target, with every rejected row explained,
• totals that should match, such as open pipeline value by owner and by stage,
• a sample of records compared field by field, including some with long notes and attachments.

Then ask three or four users to open the accounts they know best. They will find things no count will.

Freeze, cut over, validate

1. Switch off automation in the target. Workflows, assignment rules, welcome emails and integrations must be disabled during the load, or thousands of old leads will get a "thanks for your inquiry" message.
2. Freeze the old system. Set it to read-only at an announced time. If a hard freeze is impossible, record the cutoff and plan a delta load of whatever changed afterwards.
3. Take the final export and run the scripts you rehearsed. No new conversions on the day.
4. Reconcile with the same checks as the rehearsal.
5. Re-point everything that feeds the CRM: website forms, email sync, telephony, the marketing platform, reporting tools.
6. Turn automation back on and let users in.
7. Keep the old system available read-only for a few months, and keep the archive export for as long as your retention policy says.

Doing it in-house or with help

A few thousand contacts moving between two mainstream CRMs, with no custom objects and little history, is a job for the vendor's import tool and a careful administrator. Bring in developers when there are custom objects, several source systems to merge, many attachments, or a target with its own data model. Scripted, repeatable loads are then the only safe approach. That is routine work in our migration and modernization projects and on the CRM and ERP systems we build, including custom PHP CRMs. If you are still choosing the destination, read custom CRM or off-the-shelf first. Techniques for a very short freeze are in database migration with minimal downtime.

Frequently asked questions

Should we migrate everything or start fresh?

Neither extreme works well. Bring active customers, open deals and recent history into the new system, and keep the rest in a searchable archive. Migrating every stale lead carries old mess forward, and starting empty throws away context your team uses daily.

Can we keep working in the old CRM during the migration?

Yes, until the freeze. Rehearsals run on exported copies and do not disturb daily work. Only the final export needs the old system to stop changing, and a rehearsed load keeps that window short.

Why do rows fail during an import?

Usually because the target rejects them at validation: a required field that is blank, a picklist value that does not exist, or a parent record that was not loaded yet. A mapping sheet with a rule for empty and invalid values, plus loading in dependency order, removes most of them before the real run.