In the first two articles of this series, I looked at how to devise a Hubspot CRM strategy and how you can import data from your most critical sources. In this post I’ll look at how you can massage and manipulate the data you’ve imported, so that it is coherent and valuable for you.

By now you probably have quite a large number of fields that you’ve added to your default set within Hubspot. Now we need to start cleaning!
To clean and manipulate my CRM data I used a combination of spreadsheet functionality and the ability to create customised views within Hubspot.

Use spreadsheet to clean (Filters, Pivots, VLOOKUP, IF)

In my experience spreadsheets are pretty wonderful when it comes to cleaning CRM data. They’re flexible and can work through large amounts of data simply. And having completed the manipulation of my date , I simply reimported the cleaned data back into Hubspot.

With this in mind my first step was to export the Contacts and Companies records (all properties, of course) as two separate CSV files and load each into a spreadsheet (I used Excel). I’m not proposing to go into great detail on the filters and formulae that I used. There’s plenty of tutorials on this on the web already if some of my descriptions need greater explanation.

Filters can be used to isolate blank fields. For instance, I filtered the Company sheet by Industry to show which fields were blank in Hubspot. I then looked across to the Industry field provided by Lead Forensics, and where relevant copied the field value across. I took the same approach to complete blank employee size, address or turnover fields from Lead Forensics, or to populate Job title or other contact information from Google Contacts.

For segmentation purposes I wanted to be able to filter my contacts by industry, employee size or turnover. Unfortunately these fields are not present in the Contacts view, but via use of a VLOOKUP formula I was able to copy the fields across from the Company view within my spreadsheet. If you’re not particularly adept at using more complex formulae such as these, there are plenty of worked examples just a Google search away.

With some patience and clear-thinking I was able to create a much cleaner, more complete and more consistent set of data. I then simply re-imported the two Contacts and Company spreadsheets back into Hubspot (mapping all the columns to existing or new fields)

Create Hubspot CRM Views for Easy Filtering

One of the most useful features of Hubspot is the ability to easily create personalised views. These are useful both for further cleaning of data, or for segmentation. For instance I’ve a view that only shows contacts for whom I have a business address. To do so I have simply created a view that filters our contacts where the email address contains gmail.com or yahoo.com etc. To do this you simply create a statement in the style of the example on the left.

A similar approach can be used to create a selection of contacts by job seniority. While your records contain a job title that might be in multiple formats – e.g. CMO, Marketing Director, Director of Marketing, VP marketing might all be used to describe a senior marketing decision maker. But by creating a custom you view it’s easy to isolate them – and because this is a live view, it will still continue to pull relevant results as your contact list grows. Below is an example I’ve created to produce a list of board-level contacts in my lists.

TOP TIP: When you create lists from complex statements such as this, you may want to create a new field to label all the results. For instance, I created a new field called JOB LEVEL, and have “Board Level” as one of the selectable options. Whenever I run the query I then select all the results and do a bulk update of the Job Level field. This then makes it easier to select all these contacts when they are to be used as part of another complex view, rather than having to recreate all those literals again.

Now that my Contact records contain Job Level, Industry, Employee size and Annual Revenue, I have easy access to the most common segmentation variables for targeting and data analysis. I can’t overstate how useful this is to me – well worth the effort.

But having gone to all of that effort to create a usable set of CRM data, I want to keep it that way. In the final part of this series I’ll discuss some of the simple tools that I have deployed to help me do that with the minimum of effort.