We’ve been using Jupyter Notebook for a wide array of tasks lately where we wouldn’t need a heavy lifting tool. One recent use was our migration from a homegrown CRM program to Salesforce.

We started by planning out and building the Salesforce environment with all the associated built-in and custom objects, fields, automations and users to match our business processes.

Next, we configured an Excel spreadsheet export from the CRM program including the information we wanted to move to Salesforce.

Lastly, we wrote a notebook to read the spreadsheet into DataFrame objects and create the appropriate records in Salesforce using their API and a very convenient REST API client.

We chose to go this route because the built-in data loading tools did not include some of the objects we needed to import, including the custom objects we created in Salesforce. This approach also gave us the flexibility to use the Excel export in its original format and avoid having to do an inordinate amount of pivot table wizardry or customization to the legacy CRM program’s export. (Note: Salesforce API access is not available at all license levels)

I wrote a GIS-related introduction to Jupyter Notebook here which can help you get up and running if you’ve never used the application before. If you are an ArcGIS Pro user, the built-in python package manager is convenient and can be used for non-GIS tasks like this one just the same.

The notebook file and companion spreadsheet template can be found on Git Hub here.

SET UP THE ENVIRONMENT

The only python library you will likely have to install for this task is Simple Salesforce. You can find it here on GitHub.

TURN SUCCESS MESSAGES ON OR OFF

LOAD SPREADSHEET WITH CRM DATA INTO A DATAFRAME

Here we’ve normalized the data by making all column names lower case and replacing spaces with underscores.

INITIALIZE THE SALESFORCE CONNECTION

You will need a token to access the Salesforce API in this way along with your user credentials. If you set up a user specifically for this task you can generate the token in the user creation process. See the link in the comments for more information on acquiring the token. You may also need to create a sort of ‘app ID’ that matches between Salesforce and this script.

BUILD OUT A DICTIONARY WITH IDS OF EXISTING SALESFORCE USERS

Users are stored in a different manner than the other CRM data and the path of least resistance is to handle user creation through the Salesforce interface. You might explore other methods if you have a huge number of users to create, but for our purposes this approach worked best.

We are making sure these are in Salesforce first because our spreadsheet links Accounts and Opportunities by user/owner name and not by ID. To give Account and Opportunity objects an owner we will use the ID Salesforce gives each user, thus the look-up dictionary.

ADD FUNCTIONS TO PARSE THE DATA

MIGRATE ACCOUNTS INTO SALESFORCE

This cell starts creating Account records one by one as it loops through the data frame. It also creates a dictionary of new ids and account names it comes across for use later.

LOAD OPPORTUNITIES INTO DATAFRAME OBJECTS

The template spreadsheet has two sheets so here we are loading just the Opportunities sheet into a DataFrame object now that we have Accounts created.

MIGRATE OPPORTUNITIES INTO SALESFORCE

By parsing the Opportunities sheet and using what we know about owners/salesforce user ID’s we can create Opportunities that are related to the correct Accounts and populated with the information in the spreadsheet.

Our original notebook included a lot of custom fields and custom objects that came over in our CRM export spreadsheet. The provided notebook and template have been stripped down to only include standard objects and fields to provide a starting place for your migration.

Troubleshooting is part of every data migration and it is easy to flush the data with the Mass Delete Records tool in Salesforce and rerun the notebook.

As a company we work on large GIS data migrations regularly, so we were able to predict and front-run most of the issues before we encountered them. Hopefully, this gives you a good foundation if you find yourself in similar need of a CRM to Salesforce data migration.