Getting your data into your CRM system is one of those problems people don’t think about when planning their CRM project either because they are concentrating on the features and how they are going to use their new CRM system or because they just do not realise what is involved.
Data Migration can be tricky for a number of reasons, such as:
- multiple data sources which need to be merged which can lead to difficulty matching and end up with duplicate data
- existing systems do not have export or full export functions so getting the data can be difficult
- the structure of the data in the existing systems does not match the CRM target structure
- individual fields on existing systems do not have equivalent fields on CRM e.g. you may have a free text field on the legacy system but are planning a picklist for the dame data on CRM
- the quality of the data – a lot of legacy systems do not enforce data integrity which makes the migration process difficult
- misuse of data fields – users will use fields for different purposes and it is not uncommon to find "codings" embedded in fields especially where the legacy system is hard to customise
There issues mean that data migration can take 30-40% of your CRM project budget and it you haven’t allowed for it then you will miss your implementation date and having a system without data is far from useful.
Data Migration can be hard if you just dive in without planning and working what you need to do. By following a well defined process you are more likely to success in getting your data into CRM ready for go-live. There is a generic method for data migration with three stages known as ETL which breaks the data migration down into separate problems
- Extract – getting the data out of the legacy systems
- Transform – this is the conversion of the data from its legacy structures into the structure CRM expects
- Load – this is the actual process of importing the data into CRM
[You can add data cleansing into the above but I find that cleansing tends to be performed in one or all stages depending on the situation]
In the Extract stage you need to get the data in a neutral format e.g., Excel, Text file or Access. How you do this depends on the legacy system. Some have export functions but beware that some of these exports do not export all the data you need e.g. in ACT! the export only covers groups and contacts not activities and opportunities. So you may need an alternative approach and which may require a third party utility all of which adds to the costs.
In the Transform stage you need to bring together all the data sources and create data that "looks like" CRM i.e. Accounts, Contacts, Activities, Opportunities, Orders etc. Generally this requires processing and logic and either a specific program needs to be developed or a tool used.
In the Load stage you need to use CRM’s functions to import the data into the CRM database.
Microsoft CRM provides four methods for importing data:
- Within CRM you can import Leads and Contacts from Excel – this is of limited use in data migration as often there is more data needed. This function is more aimed at importing lists of people you have either collected at trade shows or have purchased from a commercial list
- Within CRM you can import Contacts from Outlook – this is useful where the only data source is Outlook but again you will not be able to import tasks and will have to manually create Accounts as these are not created from the contacts.
- SDK – Microsoft provides an API for creating (and updating) data in CRM. You could use this to create the records in CRM using software you
- Data Migration Framework – a set of tools and documentation provided for importing data into CRM
What we have migrated into CRM using the above approach:
- Sage Line 50
- Bespoke solutions based on SQL, Superbase, Access, Filemaker and Excel
Part II of this document will go into the migration process in more detail, tell you what tools we use in each of the stages and explain the features of Microsoft CRM Data Migration Framework.