The importance of regular data curation and how to automate aspects of the process 

Your B2B analytics, optimisation and sales efforts are only as good as the data that goes into them. Poor or incomplete data means distorted analysis, missed opportunities and lost business. In a B2B context, where there tend to be fewer and more valuable leads and where the resources dedicated to one lead may be quite substantial, your CRM data needs to be flawless. So, curating and cleaning that data is a core function in the pre-sales process that you need to get right. 

The good news is that much of this process can be automated in Excel, making regular cleaning of your CRM records relatively painless. In addition, a number of external tools can be used to help fill any gaps or errors in your data set. 

Here we’ve put together our top tips for taking the drudgery out of data-curation and for ensuring your B2B lead data is as accurate and complete as possible.  

Set up clear rules on mandatory data

First and foremost your data curation process relies on an agreed set of standards. What are the mandatory fields you need, what format is required for each field etc. For example, all phone numbers should adhere to a set format, including international dialling code (an entry you could also use to identify the location and language of the lead, if needed). Likewise, all addresses need to adhere to a set format, with standardised fields and separators.

The best place to solve all data inputting issues is at the point of input. So, with most data from online forms it should be possible to ensure that users can only progress if they input the data in the correct format and don’t make up entries (like email addresses).

A common inconsistency with manual data inputting is the company url field. Here many people copy paste the link they are viewing, whereas, the home page is the better choice for keeping consistent records. Again, the best place to solve data issues is at the point where the data is inputted.

Use the Power Query feature in Excel

Chances are though, that even with all the strictest policies and best websites, your data is still going to need a little cleaning and consolidation. This is where Microsoft’s Power Query feature comes in. The Power Query feature helps take a lot of the manual labour out of regular data curation, cleaning and reporting. It is a powerful ‘add-in’ to Excel (now a default feature in the latest versions) that you can set up to help you pull your data into one spreadsheet from multiple sources, organise your data better and to clean it of things like duplicates, text formats for numbers, blanks and white space. It’s an essential tool to get familiar with if you have complex data curation tasks to routinely perform. With Power Query you can save the operations you use, so that, having set the process up once, you will be able to repeat it every time at the touch of a button.

Not only can Power Query automate the cleansing of data, it can also flag mistakes/gaps for further investigation/resolution. When you receive your outputted table from Power Query every column will contain a record of the number of errors in that column, allowing you to click through to see where those errors are.

Note: Power Query can also be very useful for migrating data between CRM systems, helping you reformat data to suit your new software. We found it very useful when migrating one client from pipedrive to HubSpot.

Do you need support with automating your CRM? Contact us and talk to our CRM automation experts!

Run regular health checks on CRM data

Once you have your Power Query set up, make sure you use it. The frequency of your data-cleaning processes should reflect the size of the data set and the amount of manual resource needed to address any issues. Where you need to manually fill in hundreds of records each week, the task may be a full-time job. More typically, and where the best automation processes are in place, it should be a small part of the pre-sales process.

Leverage online tools to help you fill in the holes

Sometimes manual research will be the only way to complete your records, but the aim of a good curation process should be to keep this manual effort to a minimum. Luckily, there are several online tools available to help you fill in the gaps and retrieve information such as company names or urls and auto-populate incomplete records. In a B2B context it is especially important to ensure that individual leads/users are correctly associated with the companies they work for.

You might find that your CRM system has some built in tools that can auto-populate fields like this (the ‘Insights’ feature in Hubspot, for example). Whether or not this is so, you can also access one of the wealth of online tools that are available – often free – to help. One of our favourites, and one that is free to use for up to 50 records at a time, is

Company URL Finder ( This tool helps you to retrieve company domain data where that is missing from your original record.

Healthy data, healthy mind

Don’t let those nagging doubts about the quality of your data get you down. With so many powerful tools available to help you gather and manipulate your records, there is no excuse for not setting up robust curation procedures. Get it right and enjoy the peace of mind that comes with being able to trust your data sets.