Why Data Migration is so Hard – and What to do About It

By: Rob Jordan, CEO and Founder of Idealist Consulting

Imagine you’re the proud owner of a new house. You’ve researched loans, taken care of that pesky electric issue, and packed up all your boxes from your old apartment. It’s finally moving day. There’s just one problem – most of your old furniture is totally out of place in your new home.

Now imagine your new Salesforce database is that new house, and your data is the furniture. You have to move, or migrate, your data from your old database into Salesforce, but the process is not as simple as it may sound – just like that awkward wraparound couch, your data will need to be reimagined (or replaced) in its new surroundings. In this post, we’ll address four of the most common data issues we see as Salesforce consultants, and what you can do as an organization to minimize the pain in your next data migration process.

During a Salesforce deployment, data migration can typically consume as much as 60% of the total labor. When you consider configuration, training, and project management, data migration stands out as one the biggest costs of deployment. There are four fundamental reasons for this:

1) Your data is probably a mess.

And that’s OK! Almost every organization’s data is a mess in the beginning, and this is what consultants assume when they engage with you. Duplicates and inconsistent/incomplete data are common culprits for the mess. Sometimes referred to as “data froth,” when you have more than 5% duplicates and numerous inconsistent data, things start to “dirty.” Searches don’t work properly and reports begin to fail, giving you a database full of “dirty data.” The data has to be cleaned, and this takes time. Data deduplication solutions help speed up the process, and data can be deduped post-migration, reducing the overall cost of migration. The bigger challenge, however, is addressing data inconsistencies and incompletions, which are more difficult to fix globally and must be resolved PRIOR to migration if records are to be matched up properly.

2) Your data is not formatted properly for your new database.

The fact that your data resides in a familiar format (such as Excel spreadsheets) means very little to the migration process. In fact, it can often be more challenging to extract data from these formats. Here is why: often times people will load two field values into a single field (first name and last name, for example). Before import can occur those values must be split (parsed) before they can be migrated into their proper fields in your new database. It is also important to mention that the opposite can occur where two field values (such as street address and suite number) need to be merged (concatenated) before import can begin. The process of concatenation and parsing is time consuming because it can not be reconciled globally – it must be assessed and addressed on a field-by-field basis. Automation can be used to help speed up the process, but only if your data is consistent.

3) In order to migrate the data you must “map” where it goes.

What is mapping? Essentially, mapping is the process for choosing a destination for your data in your new database from where it currently lives in your legacy system. Where you may have had one field in your legacy database, you may now have two in the new database, and vice versa. In addition, you may have decided to change the field names altogether which can cause confusion in the migration process if this renaming has not been documented. Finally, you may have decided to completely change your business workflow (which usually happens when you move to a more powerful CRM), which means you have added tables and triggers which can make the mapping process all the more complex.

4) Once your data is cleaned, formatted, and mapped, you are then tasked with importing the data.

When most people move to a new database, they are looking for three fundamental improvements: better search capability, better reporting, and better user interface. In order to get these features you are going to need a database with more tables and field values. The process of importing often requires a separate “import session” for each new table in the database. An import session requires you to import the data, assign a record number for each piece of data and its associated table, export that data for matching to related tables, and then importing the matched data back into the database as a bundle. What this boils down to is that the more tables you have, the more import sessions are required. This process becomes more time consuming with each new table you have in your database. In some cases, import tools can be utilized to help reduce the number of sessions required for import.

In an effort to reduce costs of data migration it is not uncommon for some vendors to recommend fewer tables. But be warned! Yes, that will reduce the cost of the overall data migration, largely because it will reduce the need for parsing your data. However, what you will not discover until later is that you may be unable to produce the reports you require, ultimately finding yourself in the exact same position you found yourself in your legacy database.

This leads to our top three recommendations for how you can best prepare yourself for success before data migration:

1. Provide your consulting group with a list of reports you wish to compile before migration begins. This will give the consultants a sense of what tables and how many tables you require before they begin configuration.
2. Have realistic expectations about the amount of time and effort it may take to prepare and migrate your data. Your consultants can help set these expectation after a data review.
3. Engage staff members who know your data well to help with preparations and data migration validation.

At the end of the day, data migration may not be the most exciting part of setting up your shiny new database, but it is every bit as important as the design and building of the database. You simply can not have one without the other any more than you can have a new home with improperly fitted furniture. Following these tips will be your first step towards feeling comfortable in your new home.

So tell us, what tips can you add to make data migration go smoothly?

About the Author
Rob Jordan is the CEO and Founder of Idealist Consulting (@IdealistCons), a certified B Corporation and Inc5000 company. Idealist Consulting provides progressive Salesforce implementations for nonprofits, business and government.

The vision for Idealist Consulting was inspired by Rob’s experiences living abroad, first as a Peace Corps Business Development Volunteer in the Ukraine and then as a director for a nonprofit organization in Costa Rica. Rob is passionate about humanitarian relief and an active member of the Portland business community.