Mountain scene with globe magnifying the scene
November 9, 2018 Salesforce 0 Comments

How to prepare your data for a Salesforce import

If you’re conducting a full data migration to Salesforce or simply uploading a new dataset to your existing Salesforce account it’s tempting to jump straight to import, but taking a little time to plan and prepare your data will save you a lot of time later on. Here we share our Top 10 tips for preparing your data before importing it to Salesforce.

1. Understand the scope of what Salesforce is going to be used for

This is probably the most important step prior to any data migration and the decision needs to be taken by someone in a leadership role and agreed upon at the outset.  For example, will your HR department be migrating onto Salesforce or using their current system?

2. Conduct a data audit and visualise your data

A data audit is essential – even if it’s just a list of databases in a Word document or email, make sure it’s circulated to the team and they feel it captures what needs to be migrated to Salesforce.  This will hopefully avoid too many additional spreadsheets popping up around the time of the migration

3. Group your databases

Group your data into the following categories:

  • Data to be migrated
  • Data to be deleted
  • Data to be securely archived for a time period (e.g. 12 months) in case it is required in the new system

For the databases you are deciding to migrate, consider the list of fields in each one and categorise them in the same way.  In order to do this, you may need IT or external support. For example, if a field hasn’t been used for several years or holds limited data, it is unlikely to be useful for a migration.  Commissioning a detailed data audit will help uncover this and make the task of deciding what to keep much easier.

A visualisation of a possible data migration

4. Find a home for your data in Salesforce

Once you know what you are migrating, you can think about where it should live in Salesforce.  You are likely to match it to one of these categories:

  • On the Contact (or Lead) Record or at the Account level – this could be in a standard field, or in a custom field or custom object you create
  • As an Opportunity
  • As a Campaign
  • Somewhere else, for example Cases, Products, Quotes

It is tempting to add many custom fields to the Contact record, but for each one consider if they would be better placed elsewhere.  Custom fields on the Contact record can be a simple solution at first, but they can create more work for you down the line. For example, lots of custom fields on the Contact record will mean merging duplicate records takes a lot longer as you’ll need to decide which field to keep for every merge.  Opportunities and Campaigns are good options where there might be a ‘one contact to many’ relationship – it is easy for a single contact to have multiple Opportunities and Campaigns and all this detail is kept when merging records.

Creating your own Opportunity types may help you unlock the real power behind using Salesforce for your organisation as a whole. It’s a good idea to engage with staff to try out a potential opportunity type (e.g. with a few test records, and test reporting) and decide whether it will work before migrating all of your data in with that format.

5. Choose your custom fields carefully

If you are adding custom fields, avoid multi-select picklists, instead opting for single select picklists and additional fields to capture additional data.  Single select picklists work much better for reporting and exporting.

6. Do not use Excel’s VLookup function with Salesforce IDs

Vlookup works well if what you’re matching is unique (e.g. email addresses) but use it with caution.  For example, if email addresses are shared (which can be the case for business to business databases, e.g. info@), or other data is not unique (e.g. common names) then Vlookup may result in false matches.  If you have two data sets which include Salesforce IDs don’t use Vlookup as Salesforce IDs are case sensitive and Vlookup isn’t – so if a Salesforce ID varies by a lower case ‘n’ and an uppercase ‘N’, this will indicate a completely different Salesforce record but Vlookup will see it as the same.  To solve this problem, you can use an 18 digit ID number from Salesforce, or use a formula to convert your IDs to numbers before doing the Vlookup.  Apsona’s matching capabilities should reduce or eliminate the need for Excel’s Vlookup.

7. Add Data Protection preferences to every record

At the time you do the import, you will have the most knowledge about the data – this is the time to ensure the consent and data protection preferences are correct.  For example, if you don’t have email consent recorded in your historic database, you would ideally import all those emails as ‘No’ to Email marketing (unless they have a permission on another database, such as Mailchimp in which case you can switch them from a No to a Yes).

8. Add an Import ID number and Lead Source

Add a unique ID to all your records and migrate that to a custom field you can create called “Import ID”.  This can be incredibly useful for tracing any errors but be aware that this field will be overwritten if contacts are merged.  Ideally your source databases will have a unique ID number but don’t take this for granted – we’ve migrated from software that was designed in 1998 which did not have unique ID numbers.

You may find that many of the source files are Excel spreadsheets which don’t have ID numbers, so before you change any data at all, add an Import ID column to the Excel file with a name and number configuration (e.g. PressList2018-1, PressList2018-2 etc.).  Save this source file as your original data so that you can compare the import to it if you need to trace back any issues.

Salesforce has a field called Lead Source, you can add to this picklist and assign your import files to a Lead Source.  You may wish to consider the number of these and whether they need to be grouped.

9. Add the database name as a Campaign in Salesforce

I’ve found Campaigns really useful for data imports. You may find you have an Excel spreadsheet to import (for example VIPs) and that most are already on Salesforce, but you still need to flag that they were on that source database.  A Campaign allows you to add this detail as an exact match – so matching Salesforce records should get this flag too and your counts in Salesforce and Excel should match.

A Campaign also feels a lot less permanent than a Custom Field which is useful when there may be many separate databases born out of the necessity of the previous system (which did not offer the holistic customer view required).  In an example like VIPs, you will probably want to add this flag as a custom field too. If you have a lot of databases to import, it’s really handy to be able to click on the Campaigns tab and see where you are in the process . With this method, it will show you all your database imports with their corresponding numbers, regardless of the overlap between files.

10. Neaten your data prior to import

  • For UK postcodes:
    • Upper capitalise all postcodes using the Excel formula =Upper(a2)
    • If your postcodes are not well formatted you can follow these steps to fix the data:
      • Put a space in the right place by using ‘search & find’ to replace all spaces with nothing – this will give you postcodes with no spaces
      • After removing all spaces use this formula in the column next to your postcodes: =LEFT(A1,LEN(A1)-3)&” “&RIGHT(A1,3)
      • Check your postcodes look neatly formatted for UK data
      • When you’re happy the formula was used for all the data, copy and paste the values to get rid of the formula and delete any “#VALUE” entries which may have appeared where the postcode was an ‘X’
  • Neaten States/Counties and Countries using Pivot tables to see what data is there and creating a key to neaten them, e.g. changing Warks to Warwickshire.  Ensure the Country field is completed for all records – this will really help marketing segmentation in the new system.
  • If your data has a lot of lower case names, you can proper capitalise First Name and Last Name – for example, changing “mary” to “Mary”.  In Excel you can do this by creating a new column and using formula “=Proper(A2)” and dragging down, then copying and pasting values then deleting the original column.  This solution will upper capitalise the first letter of each word and put all others in lower case. There are cases in which it does not work perfectly for example, names like “d’Orsay”, “d’Artagnan” and “De la Renta” would need manually correcting as by default all first letters would be capitalised by the Excel function.
  • If you have more than one column for Mailing Street, you will need to merge them into one for the Salesforce import. You can do this in Excel as follows:
    • Sort the data by those that have a second address field completed
    • Create a new column after both address fields and call it Mailing Street. Select this column and format it to ‘Wrap Text’.  Note – if you miss this step, the formula won’t look like it’s worked, simply select the column and choose to Wrap the text in Excel and you’ll see it has worked
    • For the records that have two Mailing Street fields completed, use this formula: =F2&CHAR(10)&G2
    • For the other data use just the Street field that has data in it, e.g. “=F2”.  When the column is complete, copy and paste the values in Excel, save it as a new file and delete the original mailing street columns


Hopefully these tips are helpful – now you’re just left with the task of actually importing the data.  Check out our blog on why we like to use Apsona for importing data to Salesforce.