At 19 years old, my first internship was with a brand new venture group in Santa Monica called March Capital. They were raising their first fund AND preparing to launch a VC focused tech conference.
Our CRM data was key to both.
I was handed 60,000 records, exported from a Salesforce database, in an Excel spreadsheet which relentlessly crashed my computer. My first instinct was to use Salesforce’s inbuilt data loader to map the fields and dump the data.
STOP! Take a breath! Don’t make this easy mistake!
An inconsistent database will not magically become usable once you import it to Salesforce. Whether you’re migrating from an Excel spreadsheet or another CRM, this is an opportunity to take a hard look at all the information you have, and make a plan for how Salesforce will help preserve your institutional memory.
Here are some tips for cleaning and organizing your data:
Deduping
In my case, many of the 60,000 records were garbage. Duplicates. Stale data. Defunct startups and peer funds. This will likely be true for your data as well. Some amount of manual review will be necessary, but three main fields can reduce the cognitive load of de-duping: Client Email, Client Name, and Account.
Client Email
This is an enormously effective way to find duplicate client records – they must be unique, whereas Account names can more often be entered inconsistently.
- Use conditional formatting on the Email column of your Excel sheet to highlight duplicates.
- Whereas Google Docs requires a custom formula to format duplicates, Excel makes this super simple. I added a few screenshots below to show you how it’s done.
- Sort the range by highlighted fields, then by date added (newest first).
- This will place all of the older duplicate records next to one another, so you can easily select and delete them en masse.
Client Name
In Salesforce, names are separated to first name and last name columns.
- Create a column to the right of the name columns, and combine first name/last name into this column using the formula “=(A1&” “&B1)” substituting cell names for the columns in which your names are stored.
- Hold down the ALT key, and drag down the column to perform the merge for each row.
- Conditional format duplicates, and sort in alphabetical order, then by highlighted fields.
- As before, sort by highlighted fields, then by date added and delete.
Account
I found many of our duplicates (including Dwigt and Beff) were caused by inconsistently entering the Account name, i.e. Acme, Acme, Inc.
- Separate the Account column using the “text to columns” function, using the comma delimiter.
- From there, scroll through the sheet to see whether any columns have been split in error.
- Rinse, delete, repeat!
Great, we’ve removed the duplicates, but what’s to stop us from committing the same error once our data enters the cloud?
Salesforce will not save you if you lack the right operational processes to prevent duping.
These days there are numerous inbuilt duplication rules, a dramatic improvement since my formative years on the platform.
Be sure that the standard duplicate rules for contacts, accounts, and leads are enabled under Setup> Data> Duplicate Management> Duplicate Rules.
Also, replacing text fields with picklists will also dramatically improve the consistency of your new data. Here are a few more screenshots showing the process of creating a new picklist within an existing record (I chose Account here).
I spent more than a few late nights trying to trick Excel into doing what I wanted, and combing StackExchange for answers. Salesforce Trailhead, today’s fantastic resource for help, wasn’t released until 2014. Fortunately the migration was successful, and many of the workflows we built during that time remain in use today.
The main thing to remember is that Salesforce is incredibly powerful. Without a little forethought, though, it can easily become nothing more than a fancy, messy spreadsheet.
Stay tuned for episode two where I’ll discuss more nitty gritty field, process, and workflow building techniques, along with more confessions of my failures (from which I may stand to learn a thing or two).
P.S. If you need any help with your own Salesforce implementation, give us a shout. I’m sure we can help.
