PREPARING TO MIGRATE DATA TO SALESFORCE
So you’ve been awarded the important task of migrating data to Salesforce? You’re lucky to have an opportunity to work on this exceptionally important task – and show your team that this task often perceived as complex and boring, can be executed effectively with some key knowledge. This article has been written from a Cirrico Salesforce implementation consultant’s perspective, and will explain some of the fundamentals, and provide some useful guidance to help you make the process smoother. In this article we are focusing on the planning and preparation side of things.
What types of data?
One of the starting points that we often get asked is “what types of data should we migrate?” – an important and valid question. This question is one that you should consider early in your Salesforce project investigations, and it will influence how the Salesforce data model is structured (effectively this means how data is related, the outcome of which is the different pages that you will see in Salesforce and what goes where).
You should think from a bird’s-eye view initially – what are the kinds of data we want to keep in Salesforce. To answer that question we’re going to dive into a sub-question – why keep data in Salesforce in the first place?
Well, the answer tends to be relatively simple – because Salesforce is one of the most secure, scalable and innovative platforms around, that you and your team can access from a desktop computer or mobile device. Assuming you have a backup solution in place, any data in Salesforce will be automatically backed up, and if you have solutions enabled like two-factor authentication, then Salesforce is going to be a much more secure place to keep data than a local or even cloud spreadsheet. You also have other benefits, such as being able to run visual reports on the data, tracking historical changes to it and even trend analysis. You also have the benefit that Salesforce has been designed as a ‘social database’ – that is a cloud-based database, where you and your team can collaborate and have discussions and share files – in the context of a specific record.
These benefits mean that lots of organisations are broadening their thinking in terms of how they use Salesforce, and where possible a spreadsheet can be ‘upgraded’ into a Salesforce database table (or even an app). Larger organisations particularly have been taking advantage of having this single secure, scalable, manageable platform, it drastically improves their governance ability when compared to team members using lots of different apps, spreadsheets and more. So we’re always excited when our customers want to broaden their perspective. We’ve not mentioned GDPR (General Data Protection Regulation) yet – but using Salesforce to keep all personal data tied back to a data subject, and to have clear real time visibility of their consent is super useful for compliance.
Back to the ‘what types of data’ question. So you’ve identified the types of data you want to track in Salesforce (for our nonprofit customers this could be people, donations and events), we usually refer to these higher level types of data as “objects”. An object is a table in the Salesforce database, that has fields on it – like how an Excel spreadsheet has columns. For each of these to-be objects, you will want to think about the specific fields you want to capture. For example, for a person we’ll definitely want to keep track of things like First Name, Last Name, Email, Phone. But we may also want to track other data, such as their address, interests, how engaged they are with your organisation.
You should list out all key ‘fields’ you want to track, and share it with your team - so everyone can cross reference to make sure the data they need to be able to access is there.
Once you’ve identified the ‘fields’ you may want to start thinking about what type of field it will be. This is something your implementation consultant will help you with, but it’s always great to get a head start. One key consideration with the type of field is to bear in mind that it’s an opportunity for you to improve the quality of your data by using Salesforce. For example, if you are tracking the address of one of your donors (contacts), the “Country” field could be a text field – so any user can just type in the country manually – however, we’ve got a better solution for that; a picklist field. A picklist field is a set of predefined options in a dropdown list. The benefit of pre populating the country list in Salesforce is that it is quicker for a team member to select a country, and it also ensures the country will be stored consistently (so you won’t see variations such as “UK”, “United Kingdom”, “England” as well as misspellings of these too!). Salesforce will automatically validate other types of data too – for example if you choose “Email” as the type of field, Salesforce checks to make sure it’s an email address before letting the record be saved.
Here’s an example of how a draft may look for one object (Contact), note each field listed is typically a column in a spreadsheet.
To wrap up this first section “What types of data?” we’ll end with a checklist, you can use this article as a working guide, and check off each item as you go!
💡Bonus tip: the feeling of progress is essential on these kinds of projects, particularly when working on concepts like data. So set yourself up with small milestones you can definitely achieve in the preparation journey, to make sure that you and the team can stay positively focussed on this important work you are doing.
✅We’ve identified key types of data we want to store in Salesforce (also known as objects).
✅We’ve taken some time to think about other data we may want to store in Salesforce that wasn’t obvious at first.
✅We’ve written down each field we want to track on the object.
✅We’ve identified what we think is the best “Field Type” for each field.
Preparing the data
Once you’ve clarified the objects and fields you want to migrate to Salesforce, you’ll need to come up with a data migration strategy. How this strategy looks depends on how your organisation works, the volume of data, and the effort and complexity in required to identifying the data and getting it into Salesforce. For some organisations with very complex requirements, they may need a strategy in place just to identify the data. We’ve worked with education customers who for example have 100+ databases, each with its own tables, used by 1,000+ employees to track tens of thousands of columns of data. Most of our customers such as small and medium sized nonprofits would have around 10 different objects to import, with an average of 50 or so fields on each object.
So when it comes to preparing your data, the first step is to think about how much of a strategy and plan you need. Even if it’s simple you’ll want to write down the sources of data, volume, how it can be extracted from the current system, who will do the work and more. Let’s look into some of these key sub-questions a little further.
Sources of data tends to be an easier question to answer most of the time – the source is where the data is currently stored. It could be in a spreadsheet, on another database, in software, or even on paper. For each type of data to be migrated to Salesforce you’ll need to be clear on the source of the data so you can prepare further. Make sure you also understand the volume of data to be extracted from that system to be imported into Salesforce – is it hundreds, thousands, millions of records? You’ll want to know this as this will allow you to better estimate the effort required for the technical task of extracting the data, and also any transformation (or changes to the data or it’s structure) that you’ll need to do. Make sure you know how the data can be extracted, and have a plan to do so. Whether it’s a spreadsheet being provided to you, or even an external 3rd party extracting data from a piece of software you use, you should have a solid plan – which includes testing the export as soon as possible, and validating it. We use a similar method when importing data – we always test import data first to make sure it imports as expected, ahead of the ‘live’ data import.
Make sure you plan your team’s time realistically, and leave contingency time for unknowns and risks - the best way to plan time is to estimate based on the knowns, and then bear in mind there are always unknowns that will come along.
Now we’ve looked at some of the more common questions, let’s think about how the data is actually prepared. First off we would usually assume that data to be imported into Salesforce should be in a CSV format (that is a type of excel spreadsheet – comma separated values is what it stands for), effectively this is a spreadsheet that won’t have formatting, hidden columns or any other Excel ‘magic’ that could get in the way of a smooth import.
In the first question in this article “What types of data?”, we looked at different types of fields. This is something you will need to be conscious of during your data preparation planning. A good example is the country example – let’s say you have a current spreadsheet where people have typed in the “country” value on a Contact – this is bound to have misspellings as well as variations. Couple that with a piece of software having another format, and as you build up this list of “country” options you’ll suddenly have a potentially unwieldy list with many variations. Therefore, one of the key exercises to undertake is to consider, analyse and plan how to get to the point where your data is consistent. This usually means you will need to ‘transform’ your data (basically update it between exporting it from the current solution and importing it into Salesforce). During this transformation you can standardise records, e.g. changing “UK” to “United Kingdom”.
One key tip is in Excel to select any column of data you are going to import into Salesforce, perhaps copy it into a new sheet, and then remove duplicates. This allows you to see a list of unique values, which you can then compare to your planned list of values to store in Salesforce – in a picklist field for example. Because Salesforce automatically validates data you try to import, you’ll need to make sure you prepare it properly so as not to delay the import process.
To wrap up this section we’ll end with a checklist, you can use this article as a working guide, and check off each item as you go!
✅We’ve identified how complex the data preparation exercise will be, based on how many sources of data there are, number of fields and other complexities.
✅We’ve drafted a plan of how we can extract the data from the current sources, and identified the volumes.
✅We’ve cross checked field values to make sure the data we have in each field is going to be compatible with our planned fields and field types.
This concludes the preparation guidance – the kind of steps you’ll follow afterwards depend on what your implementation partner is doing, but routinely include. Good luck!