Clean data before or after a CRM import

Before: clean every new list before it is imported. A list cleaned in the file arrives as one record per person, in the formats your CRM expects, and there is nothing to undo later. Cleaning after the import means finding each bad record among thousands of good ones and fixing it in place, after a rep may already have called it or added it to a deal.

Most teams still clean in both places, because some jobs can only happen in one of them. Which place a job belongs in depends on where the data is and what needs to change:

Cleaning before an import means working on the spreadsheet before any of it reaches the CRM. You see every row at once, so a problem that repeats down a column is fixed once, for all of them: a country written three ways, an N/A typed into the Email column, the same person on two rows.

It is also the only point where the list is still separate from your data. Every fix changes a file and nothing else, so you can try a fix, check it, and go back to the original if it went wrong. A list that turns out to be poor can be left out altogether, which is much harder once its rows are records.

The usual worries about cleaning first are losing something along the way and having to do it again for every list. Keeping the original file and the removed rows answers the first, and running the same saved steps on each new file answers the second.

The checklist below takes the before-import jobs in order, from keeping the original file to previewing the import.

Some cleanup can only happen inside the CRM, because it needs the records themselves, not a copy of them.

Merging duplicate records. Two records for one person that both exist already each have their own activity, owner and links to deals and companies. A merge brings those together into one record, and only the CRM, or an app that works inside it, can do that. A file can't merge or delete a record.

Rules for new records. Required fields, dropdown values and duplicate rules apply to every record as it is created, whatever its source: a form, a rep typing, an integration. Those are set in the CRM.

Fixing one record at a time. When a rep spots a wrong title or a bounced address, the quickest fix is on the record.

In-CRM cleaning tools do those jobs well. Their limit is timing: by the time they see a bad record, it has already landed, and so has anything that acted on it. For a new list, that is the difference between fixing a row and fixing a record.

A row in a file is only a row. Once it is imported, other things start to depend on it: a rep logs a call against it, it joins a list or a deal, an owner is assigned, and the CRM's automation can act on it. A fix now has to account for all of that, one record at a time.

Duplicates show the cost most clearly. In the file, removing a repeated row is a single delete. In the CRM, two records for one person each collect their own emails, calls and deal links, so fixing them takes a merge, and a merge decides which value wins on every field where the two disagree. In HubSpot, the primary record's values are kept, the other record's values fill any properties the primary has empty, and you can pick the other record's value for any property. Two merged records can't be unmerged.

Automation can act on a bad value before anyone sees it. A HubSpot workflow can enroll contacts by the import they arrived in, and Salesforce's Data Import Wizard can trigger workflow rules and processes on imported records when that option is checked. So a wrong country can route a lead to the wrong rep, and a bad address can be sent the first email of a sequence, as soon as the import finishes.

Rows can also be refused on the way in. A row missing a field the CRM requires is refused, and a value a dropdown field doesn't have can be refused or dropped. Finding which rows those were means reading the import's error file against the original, then importing them again.

Old contacts pile up. A list imported whole brings its stale and test rows with it, and each one is a record someone has to recognize as junk later, one at a time.

Mixed values break what is built on a field. A list filtered on Country is United States misses the rows that say USA, and a report grouped by country counts the same country three times. Fixing that in the CRM means finding every variant first.

An import is also hard to take back. HubSpot and Salesforce let you delete the records an import created, but deleting them doesn't reverse the updates it made to records that already existed. HubSpot can roll back changes from an import within 14 days on some paid plans, and Pipedrive lets an admin revert an import within 48 hours.

Neither replaces the other. For a new list, before wins; for what is already in the CRM, you need both.

Work through the file in this order before you import it. The order matters: placeholders are cleared before the email and required-field checks, because an N/A looks like a value, and repeats are removed after emails are fixed, because a broken address hides a repeat.

A list can repeat itself, and it can repeat the CRM. Each needs a different fix at a different time, and only the last of the four below has to wait until after the import.

Repeats inside the file are removed before the import, by matching on email. Deduplicate does it in one of three modes: Exact needs the same value, ignoring capitals; Smart also treats a Gmail address with dots or a +tag, a US or Canadian phone with or without +1, and a company with or without Ltd or Inc as the same; Similarity catches typos at a threshold you set, 80 by default. It keeps the first row of each group, or the last if later rows are newer, and puts the removed rows in their own file.

A similar name or company only means a pair is worth checking. Two people at one company can share a name, and two companies can share a domain; in each case both records are right. Check the pairs Similarity finds before anything is removed, and match on email when names alone could be two people.

People who are already in the CRM under the same email are caught at the import, as long as it matches on email. HubSpot's importer updates the contact with the same email and the company with the same domain. Salesforce's Data Import Wizard can match leads and contacts by Salesforce ID, name, email or external ID, if you pick one. Pipedrive's import can merge a person with the same name and the same email, phone or organization. Operelio's push matches each person on email, so someone already there is updated, not added again. On Salesforce, it looks only in the object you push to: a lead push doesn't update a contact with the same email, though a new lead whose name and company match one is flagged first.

People who are in the CRM under a different email get past any email match. Before a push, Operelio lists the new rows that look like a record you already have, by a similar name at the same company, so you can decide before anything is created. The check needs a Company column to compare.

Duplicates that already exist in the CRM can't be fixed from a file. Merge them in the CRM: see merging duplicate contacts in HubSpot, or Salesforce duplicate rules to stop new ones. Duplicate contacts covers where duplicates come from and how to keep them out.

Here is one list taken through the checklist. It is a webinar sign-up export of 10 rows going into HubSpot, where two of the people already have records. It has no test rows, and each column already matches a HubSpot field. The names, companies and addresses are made up.

HubSpot already has Priya Shah as priya.shah@northwind.example, with no country, and Lena Fischer at Quillworks as lena.fischer@quillworks.example.

Records already in the CRM are cleaned the same way as a new list: export them, clean the export, and import it back over the originals. It is the after-import cleanup done in a file, so you get the same view of every row and a copy to go back to.

With Operelio, a push only fills blank fields unless you choose Overwrite with my data. With overwrite on, the preview lists the values that will be replaced, and on Salesforce an empty cell clears the Salesforce value, listed as Fields cleared, so push only the columns you mean to change. CRM data cleansing covers the whole round trip. If a fresh export arrives every week, a trail can do it for you: a saved set of steps that runs by itself on every file that lands in its folder. It cleans the file and holds the push for you to review before anything reaches your CRM. Operelio doesn't export records from your CRM, so a person or the CRM's own scheduled export has to put the file there.

Clean the export from the old CRM before it goes into the new one, not after. A migration imports years of records at once, so every duplicate and old format in the old CRM moves across with them, attached to their deals and history.

The before-the-import rules hold, with two more. Remove duplicates across every file you're bringing over, not only within each one, because the same person is often in two exports. And decide which records to leave behind, such as contacts nobody has touched in years, before you map a single field. Old leads are the part to be most careful with: decide by rule, with a column and a filter such as last activity before a set date, rather than record by record, so the same rule can be checked and explained later. The CRM migration checklist covers the rest of the move: what to rebuild, the order to load records in, and the cutover.

An import that finishes without errors only proves the CRM accepted the file. Values in the wrong field, placeholders such as N/A and statuses that contradict each other all load without a single error, so check what landed against what you expected from the file, then open a few records by hand.

Operelio cleans files: new lists, and exports of records already in your CRM. It checks a list, fixes what you pick, removes repeats, maps the columns to your CRM's fields, and shows what a push to HubSpot, Salesforce or Pipedrive will create, update or leave out before anything is written. CRM imports walks through that preview on a trade show list. It doesn't run inside your CRM, so merging records that already exist there, and rules that apply as records are created, stay a job for the CRM or an app that works inside it.

For the checklist above: Data Health Check finds placeholders, broken emails, mixed formats and repeats, and fixes the ones you pick. The Email Verifier checks every address. Deduplicate removes repeats. The CRM Formatter maps the columns to your CRM's fields and shows what a push to HubSpot, Salesforce or Pipedrive will do before it writes anything.

Data Health Check, Deduplicate and the Email Verifier's Quick clean are on every plan, on files up to 2,000 rows. Each run uses one job, and Quick clean uses no credits. Pushing to your CRM, Do Not Contact screening and Full verification start on Starter.