Most of the clients I have helped with their Microsoft Dynamics CRM implementations believed that data import is easy. It is one of the problems we regularly face when quoting for data import. Clients simply don’t believe it is going to take a lot of time to do this task properly.
It is true that the Dynamics CRM data import tool is easy to use and quite functional. But the physical act of importing data is not what we are talking about here. The old adage of garbage-in-garbage-out applies equally well to a CRM system as any other.
Don’t even bother importing data into Dynamics CRM unless you are prepared to clean it beforehand. It is very difficult to clean data once it is in Dynamics CRM (or any other system for that matter). I have worked with clients who have simply dumped data into their new CRM system (despite recommendations to the contrary), only to regret it a few months later when they realise the negative impact on their business.
So what is clean data?
- Accurate – correct name, address, phone number, email address etc, with no duplicate records.
- Complete – the data represents the data sets for the whole of the business area that will use Dynamics CRM. For example, if you are importing accounts you should import at least a complete list of all current customers.
- Related – each data set in Dynamics CRM is related to other data in some way. For example, contacts have parent accounts. So your contacts for import should all be checked to ensure the parent account is in the account dataset you will import.
How do you clean data?
There is no hard and fast way to clean data. But what works for me is to use MS Access as a tool to help me identify where there could be problems.
The first step is to organise the data you want to import into separate spreadsheets or worksheets within one spreadsheet. Then import each data set separately into its own table in MS Access. Each client’s data presents its own challenges, but generally I always do the following:
- Use group query to find duplicates in each data set. If, for example you import duplicate accounts, when you try to import contacts, the contacts which refer to those accounts will fail.
- Join parent and child data sets, and data with lookups to other data, e.g. account list and contact list to check all contact parents are in the account list, or opportunity customer in the opportunity data exists in the account table.
- Create a table for each option set field used in the data, then use query to join option values to the data set to ensure there is a matched value in the option set table. By default the Microsoft data load creates extra option set values if they don’t already exist in Dynamics CRM. This is normally a bad business practice.
- Export users from Dynamics CRM into a spreadsheet, import into a table in Access, then use query to join data to the user table to see that all record owners in the data match a user in the user table.
You will probably not be surprised to hear that I have never had a client who presented data for import that did not have a number of problems in more than one of the above areas.
If you have your ‘consultant’ hat on, make sure you do not fix the data for the client. The consultant should never take this responsibility as data is owned by and is the responsibility of, the client.
When the ‘fixed’ data is given to you, go through the above data checks again by freshly re-importing the data into the MS Access tables and re-running the queries you have saved for each check.
You can read more about Professional Advantage and Microsoft Dynamics CRM here.