CRM migration
Data cleansing and deduplication before a CRM migration
Migrating does not clean data: it moves it with all its problems. Here is how to profile, normalise and deduplicate before the first load without losing traceability.
Before migrating to HubSpot, data needs cleaning in four steps: decide what will not be migrated, normalise formats, deduplicate with agreed keys and rules, and keep a merge map so you do not lose traceability. Doing it before the first load, rather than after cutover, is what lets you repeat the migration with the same rules and start in HubSpot with data the team trusts.
A migration cleans nothing on its own. It moves the data with all its problems, and in a new CRM those problems are more noticeable. That is why on every HubSpot migration cleansing is a phase in its own right, with its rules written down.
Why cleansing comes before the first load
There is an understandable temptation: migrate as is and clean up afterwards in HubSpot, which has tools for the job. The problem is that a migration is not loaded just once. There are test loads, a validation, perhaps mapping corrections and a delta load on cutover day. If the cleansing is done by hand in the destination, every new load undoes it.
When the cleansing rules are part of the transformation (in the script or in the file preparation process), they are applied the same way on every load. And on day one in HubSpot, sales reps find one record per customer, which is what matters most for adoption.
Before
- Three records for the same customer
- Phone numbers in five formats
- Countries typed by hand
- Contacts with no company
After
- One record with its full history
- Phone numbers with country code
- Countries from a closed list
- Every contact associated with its company
Step 1. Profile: what is really in the CRM
Before correcting anything, you need to measure. For each object and each relevant field, find out how many records there are, what percentage is filled in, how many distinct values it has and which are the most frequent. That profiling almost always uncovers the same things: fields nobody fills in, dropdowns that have turned into free text, impossible dates and test records someone created on day one.
Profiling also gives you the baseline numbers for the later validation. If you know how many companies share a tax ID, you will be able to explain why HubSpot has fewer companies than the source system.
Step 2. Decide what will not be migrated
The cheapest way to clean is not to migrate. The usual candidates:
- Leads with no activity or commercial relationship for years and no legal basis for continuing to process them.
- Test records, internal contacts and dummy companies.
- Unused fields, or fields that were only filled in by an integration that no longer exists.
- Automatic activities with no value (system notifications, field changes logged as notes).
What is not migrated is not deleted: it stays in the full export of the old system, which gets archived. And it has a financial impact, because HubSpot bills part of its products based on marketing contacts, so loading thousands of contacts that will never receive an email makes no sense.
Step 3. Normalise formats
Normalising means converting values to a single format before loading them. The typical cases:
- Phone numbers with the international dialling code and no spaces or hyphens.
- Countries, regions and industries mapped to closed lists, not free text.
- Company tax IDs (in Spain, the CIF or NIF; elsewhere, the VAT or registration number) in capitals, with no hyphens or spaces, so they can serve as a key.
- Domains without protocol or "www", and kept apart from generic email domains (gmail, hotmail), which should never be used to group companies.
- Names with consistent capitalisation and no titles or job roles mixed into the field.
Every destination property in HubSpot should already have its type and options defined before you normalise. Otherwise you end up creating text properties for everything, which is one of the most common and costly mistakes in a HubSpot portal.
Step 4. Deduplicate with keys and survivorship rules
Deduplicating properly requires two decisions per object: which key indicates that two records are the same and which values survive when they are merged.
| Object | Common duplicate keys | Watch out for |
|---|---|---|
| Contacts | Email; name and phone as a secondary key | Shared emails (info@, accounts@) |
| Companies | Tax ID; website domain | Groups with several legal entities and the same domain |
| Deals | Company, amount and a close date nearby | Legitimate renewals that look like duplicates |
| Products | Reference or SKU | Discontinued products whose reference has been reused |
Survivorship rules say which value wins in each field on a merge: the most recent, the one from the record with the most activity, the non-empty one or the one from a specific source. Write them down and validate them with whoever knows the data; they are business decisions, not technical ones.
HubSpot deduplicates contacts by email and companies by domain in imports, and lets you use a custom unique-value property as the identifier. Companies created through the API are not deduplicated by domain: if you migrate by script, deduplication has to be solved in the transformation.
Step 5. Keep the merge map
Every time two records are merged, one disappears. If you do not keep track of which source IDs were absorbed into which surviving record, you lose two things: the ability to reassign activities and associations from the absorbed record, and the explanation of why the counts do not add up.
- 01Profile
- 02Exclude what is not migrated
- 03Normalise
- 04Deduplicate with rules
- 05Merge map and load
In practice, the map is a table with the surviving ID and the list of absorbed IDs. The migration script looks it up when loading activities and associations, and the validation uses it to reconcile figures. It is the same idea we apply in all our migrations: every HubSpot record keeps its source ID, and every source ID has a known destination.
What about data protection?
Migrating personal data is one more act of processing, and a migration is a good moment to apply the data minimisation principle that GDPR sets out: if you have neither a legal basis nor a use for a piece of data, do not take it into the new system. Check with whoever handles data protection in your company which contacts are migrated and on what basis.
What does need migrating carefully is consent status: unsubscribes, subscription preferences and the evidence of when and how consent was given. In HubSpot, with the data privacy settings switched on, this is reflected in subscription types and in each contact's legal basis. A contact who unsubscribed in the old system has to arrive in HubSpot unsubscribed from the first load, not later.
After cutover: keeping it clean
Cleansing lasts only as long as it takes for new data to come in unchecked. Forms with required fields and closed lists, validation rules on properties, workflows that normalise formats and a regular duplicate review keep the database in order. If you need to automate part of that work, Data Hub includes data quality tools, and it is part of what we do in support and RevOps.
With clean data, the next step is planning the day of the switch. If you are coming from Salesforce, we cover the full process, from mapping to delta and cutover, in migrating from Salesforce to HubSpot without losing history.
