How to ensure data quality when importing into a CRM system

This post is actually long overdue. Data quality is such an important matter because on a mission critical platform, you live and die by that data. Bad data just makes life miserable for the users and who wants a miserable life!? 🙂

We continually add advice and tips and tricks to our support site at docs.runmags.com. For you benefit we have created a whole page on how to go about importing data into RunMags, or any other CRM system for that sake. We have also created templates for use with Google Sheets or Microsoft Excel that you can use.

Because of the importance of the subject, we’re also posting the entire article as a blog post. Please let us know if you have additional advice!

The support article

If you want to bulk import company and contact information for advertisers, agents, and subscribers, we can help you do that. We can also import production data and magazine data from legacy systems, Please initiate a support case on the right hand side of this page, hand over a file and we’ll make sure the file is in good order and then import it.

Please take good care to clean the data before an import. User adoption will be much higher if the contacts are imported under the correct company name, if addresses and phone numbers are correct, etc.

How to ensure great data

Cleaning your data before an import is actually much easier than most people think. While it indeed is a cumbersome and time consuming process, there’s no black magic to ensuring you only import data that can be readily used by all employees. If you come to us with a list of some 300-500 advertisers to import, it’s not very difficult to open that up in Microsoft Excel or Google Sheets and manually change and add data in each field. It just takes a few hours. If you come to us with 5 different lists with 10,000 – 50,000 subscribers in each file, tools like Excel is not ideal. In that case, we’ll use Microsoft Visual Studio to open up the files and find duplicates, etc.

In the text below we’ll cover how you can clean your own data in Microsoft Excel or Google Sheets before you send it to us. Since you know your advertisers much better than we’ll ever do, you’re better equipped to clean your data than we are. Plus you save some money when you do it yourself.

When we talk about cleaning data, we simply refer to filling to the process of creating a single Microsoft Excel or Google Sheets file that can be imported. In the perfect world, that file:

Has some 15-30 columns and many hundreds of rows, one row per Contact (i.e. that you want to import and each column represent data for that contact, for example Organization name or the contact’s email address.
Has each and every cell filled out only with data that should be imported. Since you want the Organization name to be correct, you want to have the exact letters, numbers and dots that make up the legal entity. The legal entity for our company is exactly “RunMags Inc.” and not “Runmags, Inc.” or “RUNMAGS INC”.
Is sorted by Organization name and if you have several Contacts per Organization, you should make certain the Organization Name is identical for all those Contacts.

Tips & Tricks for catching bad data

If you know how to program code, you can write scripts that catch errors, finds duplicates and eliminate them. If you don’t know how to program, you can use your eyes and a healthy dose of common sense. For a list with 500-1,000 rows, it takes about the same time to clean the data and you will get better quality doing it manually rather than by code. Why? Because you are more adaptable to various issues than the computer. You can spot a problem simply by looking at it, while the programmer will have to go through an iteration of creating various rules for catching and correcting the issues.

Set yourself up so that you can work without getting disturbed for a few hours.
If possible, work with your data on a large monitor as this will help you view several columns at the same time.
Start by sorting the data by Organization name, scroll though the file to find Organizations that really should have the exact same name and correct it.
Then sort by Country, State/Region and Zip code. When you do this you’ll quickly find that you can add a lot of data quickly.
Then sort by the Contact’s email address. When you do this you typically find a lot of duplicates that you can delete.
Finally, sort by the Contact’s first name and last name to find the last batch of duplicates that you can delete.
While you are going through the steps above, correct missing data, remove dots and split up data into the right columns.

After a while you will see that the file start to look better and better. If you see fit after completing the step above, reiterate the process in order to catch the last few items.

There’s one other trick that may help you and that is that you can use the templates in the next section to create your import files.

File formats for importing

We have prepared files you can use as a template for building your import file. You can populate these files with data from legacy systems as well as exports from email platforms such as Gmail or Outlook. Just delete the sample data row and paste the information into the corresponding columns. Don’t worry about formatting the data as the import will not recognize this.