- July 19, 2011
- Posted by: Gopinath Narayanan
- Category: CRM, deployment, Implementation, Microsoft Dynamics CRM, Oracle CRM on Demand, Sage CRM
The single biggest challenge for any CRM implementation, both for the customer and the implementer, is migrating data from XL sheets into CRM. The problem with XL sheets is that it can be filled in as the user likes. There are seldom any rules or pre-formatting done on XL sheets. Here are some examples of seemingly innocuous cell data that can be a challenge.
- A name like Mr. John Smith actually goes into three different fields in any CRM – Salutation (Mr.), First Name (John) and Last Name (Smith).
- An address such as 201, Meredian Towers, 328, Mahatma Gandhi Road, Bangalore 560001, Karnataka, India needs to be broken up into Address 1 (201 Meredian Towers), Address 2 (328 Mahatma Gandhi Road), City (Bangalore), Postcode ( 560001), State (Karnataka) and Country (India).
- The most complex of them will be multiple phone numbers in the same field. ‘Phone’ is a field type in CRM, and is normally restricted to 10 characters. XL will allow you to add multiple phone numbers separated by a comma or a ‘/’ but will get truncated on import. Same goes for multiple email ids.
Here are some tips to make clean data available for import.
- Use a pre-built template for data migration. All CRMs will give you templates for various entities of a CRM- contacts, accounts, leads, opportunities, customer complains aka cases and others.
- If you have data that you have cut and paste from the internet, such as URLs or mail ids, they leave a trace in the XL sheet which makes it impossible to convert into any other format such as csv or txt. You are better off pasting the url/mail id into a notepad and then copying it into XL.
- Have separate cells or columns for key fields like First name, Last name, City, Postcode, State, Email, Land Phone, Mobile Phone, website etc.
- Do not add multiple people into a same cell (using Shift+Enter allows you a line feed within a cell in XL). This is a strict no no. Most applications when they sense a Shift+Enter in a cell, assumes that it is the end of the row and ignores the rest of the cells in the same row. Or worse, takes the next cell as the start of a new row.
- The same logic holds good for phone numbers and email ids. Each row must contain only one data element.
- Ensure the date format in your XL and CRM are the same. Though your XL will take the format from your control panel, CRMs need to be configured to adhere to a specific format.
- Migrate data in a phased manner. Even if you have thousands of records, import data in an incremental fashion. Migrate a few batches of 10, verify and correct the CRM as well as the XL sheet. Up the batch size to 50, 100 and so on. I would not migrate a batch size of more than 1000 records even if there are 50,000 records to be migrated.
- If you are migrating leads and opportunities, map the fields correctly for its contents. For eg, if the lead source in your XL has options like web/phone/e-mail/referred, the same four values must be present in CRM too. Same is the case with say a field like ‘Product Interested in’. Else the CRM will ignore unmatched records, or throw up an error. It is quite hard to predict what will the system do.
It requires immense patience and understanding of the data migration process into any application. It is not an exercise in replacing XL sheets with something more expensive and what may look like a painful substitute. But the organization needs a CRM to interact with its customers, potential buyers and in building ongoing relationships.