Data migrations are never as straightforward as you think they’ll be. I should know—I’ve partnered with clients on dozens. That means I’ve learned a lot of things about migrations—some of them the hard way.
My biggest takeaway is that some discovery work and pre-planning can save you both pain and unpleasant surprises. While that’s not a ground-breaking idea—and it obviously applies to more than just migrations—there are some crucial things to consider during your migration prep.
In my experience, people tend to run into the same issues again and again, and they’re always preventable. That’s why I compiled the most common mistakes in data migration. Read on to learn more!
1. Failure to identify and specify data to migrate
Everyone wants to migrate every field and record to the new database—and that’s fine, if you truly need all of it. (Spoiler: if you take a long, hard look at what you’ve used in the past few years, you’ll realize that you probably don’t.)
In most cases, especially with big databases, you’ll find you don’t need to bring everything over, but with some smaller databases, it may make sense. Before you do a migration, identify what’s crucial.
At Imagine, we usually advise defining “active” records in your database—for example, records with updates or activity in the past 12 months—and then limiting the migration to those records. While it can be stressful to winnow down fields, look at it this way—this may, in fact, be a great opportunity to clean up and start fresh.
2. Lack of a data map
Documentation is crucial. If you’re not clearly mapping each field and documenting that before the migration, it’s a safe bet that you’ll run into unexpected issues. While many fields may seem obvious, it’s likely that you will uncover some that aren’t. Mapping every field forestalls issues and also provides documentation in case you have staff turnover.
In fact, aside from determining what data to migrate, mapping may be the most important step you can take for a smooth transition.
3. Believing every object will “translate” one-to-one
Not every database has a one-to-one transference for objects in the database. For example, let’s look at a company that’s migrating from Salesforce to HubSpot. Salesforce has the following objects: leads, companies, contacts, and opportunities. HubSpot only has companies, contacts, and deals—so, there's no delineation between leads and contacts. This is where mapping gets really important.
Are you putting all of the leads into the contact record? That’s typically Imagine’s recommendation, but does that mean that you need a protocol to delineate between leads and contacts? Plus, if there are additional objects, do you have objects created for those in the new database? For example, if you had a site object in the old database, does that object transfer over? If so, it needs to be created ahead of time in the new database. This would be included in your mapping process, but it’s important enough to merit its own call-out.
4. A lack of understanding that you’ll lose some data (no matter what)
So, I know earlier in this blog, I said that you could, in certain cases, migrate every record, but remember, database functionality doesn’t necessarily carry over on a one-to-one basis. The hard truth is that, sometimes, there’s information in your old database that simply doesn’t translate. For example, HubSpot doesn't allow you to migrate historical marketing activities such as website visits or marketing email views into the system. In that case, there are workarounds, but it adds an extra process that requires planning.
The hard truth is this: you’ll usually lose some data, whether it’s due to a lack of one-to-one functionality, older/untouched records, or something else. The key is being fully aware of what you may be losing ahead of time versus having an unpleasant surprise once the migration is complete.
5. Not creating duplication protocols
Duplicate records are like death and taxes: inevitable. So, whether you’re migrating an existing database, a spreadsheet, or a CRM, you must determine a protocol for handling duplicates. This means thinking about which field will be the “source of truth.” If you don’t do this in advance, you’ll find yourself scrambling to figure it out. Avoid stress and eliminate the potential for error.
When you make these protocols, remember that systems vary. For example, HubSpot doesn't allow duplicate contacts with the same email address. And records can be more complicated than that: sales opportunities often require a combination of the name, the opportunity’s owner, and the amount. That’s often something to figure out in a spreadsheet or via a data quality tool, like Insycle.
6. Overlooking the need for a comprehensive testing/QA plan
There is a (mistaken) perception that migration will be perfect the first time—but that almost never happens. QA is always necessary because you don’t want to discover issues after a launch.
That’s why, whenever Imagine does a migration, we migrate a sample of the migration and QA it before we do all the records. Typically, a small database sample might be 10% of the total records, while a large database might need a hundred records of each type of record: companies, contacts, opportunities, and more.
This is where data mapping is crucial. For example, with one client, our team realized the mapping was off. When we investigated, we realized the file the client had delivered was totally different from the file that all of us (including our client contact) had expected. As a result, we performed a remapping exercise and forestalled a much bigger issue. It took a little time, but far less than redoing the entire migration.
7. Forgetting to include a unique identifier in your data mapping
The vast majority of databases have a unique identifier that functions as a master ID. For example, HubSpot, Salesforce, and Sugar all have a contact ID for each contact record company ID. You’ll want to be sure that you migrate that record ID into the new database because, if you encounter issues later—whether you realize your migration was off or you want to append data—it makes it immensely easier to clean things up via a V lookup in a spreadsheet.
8. Not identifying records without owners/not creating rules of ownership
If you’re migrating a legacy database, it’s likely that your existing records don’t account for staff turnover. In other words, your existing database is probably rife with deactivated users. If a particular salesperson entered records, but left the company and is still listed as the owner, who should inherit those records?
Before you migrate anything, it’s important to determine the rules of ownership so that these records don’t enter “no man’s land.” Most companies don’t think about it in advance; the problem is that it’s much easier to clean these records up ahead of time as opposed to afterward.
Get all your owners set up in the system ahead of time and assign your rules of ownership. If, for some reason, you can’t get all the users in the system ahead of time, there are other workarounds, but it’s best to tackle this prior to the migration if possible.
9. Underestimating the time and effort involved in a migration
There is no such thing as a simple migration. (We know, this is sad news.) In other words, there’s always a challenge of some kind. Obviously, if you’re going from a spreadsheet into a totally different system, there are going to be more issues to resolve, but even seemingly straightforward migrations can throw you a curveball for a number of reasons.
For example, you may have been living with a messy database, and while you knew there were issues, you weren’t aware of just how messy things had become. Plus, even well-maintained databases have degradation of data. When you do a migration, every data issue is exposed. That’s a great opportunity to start fresh, but it requires forethought and planning to do it the right way.
In short, a migration will usually have some bumps in the road, but they can be minimized through planning. Lastly, assess whether you have the staff and the resources you need to complete a migration in-house. If your employees are already stretched thin or lack migration experience, you may want to think about working with a partner who can make things more efficient and less frustrating.