It’s common for companies that have been using a older contact manager or a CRM system for many years to have inconsistent field data, including runaway lookup or pick list values. The often happens with legacy systems that allow users to add free form values to pick list fields — if a user doesn’t like any of the existing choices, he or she can simply type in a new value.
Before migrating inconsistent or invalid data into a new CRM system, there are some simple steps that can be taken to ensure that the data is cleaned up so that data issues aren’t replicated in a brand new system.
Steps for Preparing Legacy CRM Data
The first step is to extract unique field values and the number of occurrences of each unique value from the existing contact manager or CRM database and add this information into a spreadsheet (contact us for help with this). Examples of fields for which should be done are Type, Status, Industry, City, State/Province and Country.
Next, the internal CRM team can make decisions as to what should happen to each unique value during the data migration process. Then, instructions can be provided right within the spreadsheet to the person who will actually migrate the data as to how to handle each, existing unique value.
In the example below, the Account Status field was seemingly used for both Account Status and Account Type in the legacy CRM system. Column E of the spreadsheet has been used to denote what should happen to each of the legacy values.
Here’s what will happen to some of the legacy Account Status values based on what’s noted in Column E above:
- Active, Inactive and Out of Business values will be migrated “as is” into the new CRM system
- Association, Customer and Prospect will be mapped across to the Type field in the new CRM system
- Dormant will be transposed to Inactive
- Fields like National Account, New Account and New Location will not be migrated at all
- The field value Purge will be used as a record filtering criteria. Any Account records in the legacy system with a Status = Purge will not be migrated
In a similar fashion, legacy field values for fields such as Country can be updated in a spreadsheet with new values into which certain legacy values should be transformed. Inaccuracies and typos that are noted in the spreadsheet can be used to inform the person doing the migration as to what data to transform and how to transform it.
It can take some time to go through a spreadsheet and note what should happen to each unique value in a number of different fields. However, this exercise will serve to increase adoption of the new CRM system, as the old data problems won’t simply be migrated into the new CRM system.