Data Migration tips for 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.

  1. A name like Mr. John Smith actually goes into three different fields in any CRM – Salutation (Mr.), First Name (John) and Last Name (Smith).
  2. 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).
  3. 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.
Remember that you as a customer owns the data, not the implementer. It is in your interest to sanitize and make the best data available for import. There is no way to global search and find anomalies and auto-correct them. At best some CRM systems like Sage CRM will give you an error log. But the corrections have to be done manually.

Here are some tips to make clean data available for import.

  1. 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.
  2. 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.
  3. Have separate cells or columns for key fields like First name, Last name, City, Postcode, State, Email, Land Phone, Mobile Phone, website etc.
  4. 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.
  5. The same logic holds good for phone numbers and email ids. Each row must contain only one data element.
  6. 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.
  7. 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.
  8. 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.

Joshi Daniel Photography

Images of People Photoblog

Rotarian Economist

Analysis and Commentary for Service Above Self

Andreessen Horowitz

Software Is Eating the World

lenguaje y otras luces

ESPAÑOL PARA EXTRANJEROS, LENGUA Y LITERATURA. LEER, ESCRIBIR Y COMPARTIR EN ESPAÑOL - Blog de ELE -

C2C Organizational Development (C2C-OD)

Bringing People and Strategy Together

Rotary Service Connections

Connect, partner, and serve through Rotary

Rotary Voices

Stories of service from around the world