How to clean, cleanse and update data with 5 data integration tips

By Henning Lund - June 10, 2020

Want to try RapidiOnline?

Integrate any Salesforce and Microsoft Dynamics systems fast


The worst thing about CRM systems is that they can get out of sync with reality. I often see companies get so excited about their CRM system’s possibilities to track and trace clever information but they end up missing the target because they do not keep their data clean and up to date. If your CRM system does not have updated information, your staff will not trust it. And if they do not trust it, they will not use it. Integrating your CRM with your ERP is a way to keep your data clean, current and up to date. Today, there are many good integration solutions that can be installed rapidly and at a reasonable price.

Generally, when you are about to invest in a CRM system like Salesforce or any other CRM system, you should not only think about what data you would like to get – you should also think about how to keep your data up to date. Having a contact’s name, e-mail and phone number and a link to the person’s related company is common. But how about social profile, birthday date, picture, title, additional prone numbers, special interests etc. The list is long, and if you keep adding details to your system, eventually you will reach a point where it can no longer be maintained.

Keeping data clean and current is a challenge

How long do people in general stay in the same job or even within the same company? According to Forbes, the average worker today stays at each of his or her jobs for 4.4 years. This is the current situation, and the future trend is that the expected tenure of the workforce’s youngest employees is about half that, so around two years. Ninety-one percent of Millennials (born between 1977 and 1997) expect to stay in a job for less than three years, according to the Future Workplace “Multiple Generations @ Work” survey of 1,189 employees and 150 managers. This means that they would have 15 – 20 jobs over the course of their working lives! This does not make the task of keeping data current easier. Basically, you need a detailed review of all of your contacts minimum once a year to have almost valid data, and this is just to have them linked to the right company (LinkedIn is a goldmine of information in that regard).

There can be many different data sources. For example, you can get new data by manually creating records with the risk of creating a doublet if you do not have very effective duplicate control. If two companies merge, their customer databases will be merged into one database with risk to have many duplicates or inconsistent data, There is always an intention to clean up later but it rarely happens. Doublets are just adding to the complexity and there is more to the story. For example, you may have created manually customers in both you ERP and CRM system.

Sometimes, your data is just a mess and at this point, data cleaning (or cleansing) is vital before you do any type of data processing.

What is data cleaning?

To start with, let's make one thing clear: data cleaning, data cleansing and data scrubbing are pretty much the same thing. It all refers to the act of finding and removing errors in data. In that process, you identify unwanted data and you can then replace, update, or delete the dirty or corrupted data. It ensures that the data you have in your database is correct, without any errors or corruptions. It is very important that you regularly clean your data set because poor quality data or "dirty data" will inevitably lead to errors and ineffective processes in your company.  

There are many simple data cleaning techniques that can help you start the data cleaning process. For example, checking your data for extra spaces, updating upper/lower case inconsistencies, doing a spell check or making sure that you delete formatting when you copy and paste data. These will get you started, but if you really want to do something about it, you can follow these 5 data scrubbing tips. 

5 tips to keep your data clean and current

Integrating your systems can be a big step towards better data quality. Specifically, how can you integrate your ERP and CRM to secure the highest data quality in both systems? Here are a few recommendations.

TIP #1: Have a clear data model in mind

Which data should be transferred? Which should remain in one system only? Leads and opportunities are fine in your CRM system, and you can do all required reporting from there. There is no reason to pollute your ERP system with this information. But, if you have existing prospect created as customers in your ERP, these should be moved to your CRM (unless they are there already). Before spending time and money on comprehensive transfers, make an evaluation of how valid the data is and if it represents a real asset for your company (data that has not been updated for a number of years is unlikely to make a difference). Maybe only a fraction of your data is relevant, then they you can limit the transfer to this.

As a general rule:
• Leads : should be kept in CRM systems only.
• Customers : are relevant to have in both your ERP and CRM system. Make sure they are integrated.
• Items and pricing : If you make quotes from your CRM, make sure that items and prices are synced.

• Sales and payment history : mostly, one-way transfer from ERP, so nothing to worry about.

Whether it should sync one way or the other (or both) depends on your business processes and in which system the data is born.

TIP #2: Clean data in the source system

If you have information in both ERP and CRM, for example leads/prospects, then you should transfer the data to CRM, delete it from ERP and do data cleansing of all these records in CRM.

When data is transferred you can try to match records by having a data loader searching for match on name, address, and phone or similar (whatever fits your data). If you engage on this road, you need to have a plan for how to deal with duplicate records, what becomes the consolidated account, whether the data gets consolidated/merged, or if one of the records is just discarded.

TIP #3: Simplify

You don’t need a hundred fields – often people use 10 fields whereof 7 are mandatory. The remaining 3 others carry a description, and additional contact methods. All other information is system generated or linked to a contact. Focus on using data that can be updated automatically or via your normal workflows. It’s amazing how much relevant information you can extract from CRM because you have related information from the ERP system such as sales history.

TIP #4: Integrate ERP and CRM

You can significantly increase efficiency by integrating CRM and ERP. This way you can eliminate double data entries, and master data can be synchronized automatically so that people have data they can trust. For the sales and marketing people operating within CRM, getting sales and payment history in itself is a goldmine of information to make better decisions, create more specific marketing target groups etc. A missing data integration is a bottleneck for future growth. With sales and payment history and other relevant information coming from ERP, the Salesforce Einstein AI has access to an important source of information for predictive activity recommendations.

TIP #5: Use a tool or page views with workflow support to keep your data clean

Usually, ERP data is not “that dirty”, unless the company has been through a merger or a similar process where databases have been merged and not cleaned up. The specific data needed to run an ERP is based on commercial relationships, so this type of data is typically kept clean. But CRM is a completely different matter. Even if you are a mid-sized company with 5,000 leads and thousands of customers and contacts, it is a full time job to keep data clean and there are good ways to simplify this. Besides discipline, one of the simplest ways to do this is to create editable page views in the CRM where you filter on incomplete records, and simply get a list. If you use, there are at least a couple of good tools on Salesforce AppExchange to automate the data cleansing. It’s also where you can find the RapidiOnline solution for data integration.

Data cleansing is often the first thing mentioned when we start talking about data quality, but frankly it will be a never-ending maintenance. Good quality source data is crucial at all time and to achieve that, it has to be anchored in your work processes, as a “data quality culture”. Unless users commit to a high data quality, it will not happen. Don’t be afraid to measure, reward and celebrate high data quality. Integrating your systems is a first step in the right direction - then you have cleaned the first set of data.

About the author

Henning Lund

Picture of
With over 25 years’ experience in strategically propelling businesses forward, Henning is considered a business development entrepreneur with a passion for transforming businesses, sales and marketing operations through out-of-the-box thinking, concepts building and process automation to improve overall performance and scalability.
HOW easily can you reap the BENEFITS of DATA INTEGRATION ? Get all the answers
   REQUEST A DEMO  We are looking forward to showing you how RapidiOnline makes data integration  EASY. Book a demo
THE RAPIDI TECHNOLOGY  At Rapidi, we make data integration and replication easier, faster, and more  secure.  Why the Rapidi technology is different:   * No programming needed.   * Easy to implement and simple to use.   * Robust and yet flexible.   * Our RapidiConnector encrypts data to keep your data safe.    * Scalable to fit your needs as you grow.   * Can be used out-of the box or customized to fit your needs.   * Top-rated support. 

Data Integration Handbook

Your business is 10 steps away from perfectly integrated data systems. Learn about key preparation, best practise and more in our data integration handbook.