A “dirty” Customer Relationship Management (CRM) system can be paralyzing for marketing, sales and services teams. When the information you need becomes unusable due to the accumulation of errors in your data, it slows everyone down.
The worst part? It’s inevitable.
- Leads fill out forms on your website inaccurately.
- Business development reps add contacts to your database based on the best information they can find.
- Marketing captures and appends data as they nurture leads.
- Sales connects with leads, sometimes multiple times and adds data as they gather it over the phone.
With each interaction, data gets added to your CRM, but not always in the right format, or in the right spot.
So, you have to clean it up. You need a single customer view. But where do you start? Which issues should you focus on? Which should you ignore?
Figuring out how to actually make sense of your database is a time-consuming and expensive task. To make this easier for you, we partnered with Databox to ask more than two dozen marketers to share their advice on the most effective ways for cleaning a CRM database.
Unfortunately, we discovered the problem was bigger than we even knew.
More than 55% of our respondents clean up their database on a weekly or monthly basis.
Nearly 70% of our respondent pool spends at least 1 hour each time they do it, with nearly 30% spending more than 4 hours each time.
We also asked them which CRM cleanup activities they each do. Here were the most popular things they do.
And here are the tips they shared for making these 7 things a lot faster. We grouped them into four big areas so you can clean up your database in a methodical way -- and keep it clean.
- Fix Formatting Issues & Standardize Formats
- Consolidate and Standardize Data Fields
- Merge Duplicate Records
- Whatever You Do, Create a System and Use It Often
1. Fix Formatting Issues & Standardize Formats
You can go about this in different ways. Some of the marketers we talked to use tools to do it automatically. Others, like Oriol Bel of Inboundcycle, do it by hand. “We do it manually when reviewing our most qualified leads.”
The marketers we talked to brought up two specific items that need to be formatted correctly.
Name Capitalization
The data-cleaning process often starts with fixing a simple problem: name capitalization.
“Unfortunately it is a bit of a manual process going through and checking to see whose name is uncapitalized, opening the contact record and making the change right there,” says Campaign Creators’ Shelby Heath.
“Once you identify that a lot of people aren't capitalizing their name, just do routine checks when a new lead comes in to make sure they capitalized the first letter of their name.”
This might seem like more work than it’s worth, but Heath says it pays off:
“We use personalization a lot in emails, so when someone receives an email that has their name in lower case it looks a little funky. Cleaning up their name can lead to a more positive impact rather then the lead receiving an email that looks like it may not be meant for them.”
Instead of making this check in the CRM, Beverley Barnes from Media Junction exports the data to Excel and cleans it there.
“For example, first name fields that include first & last name, are uncapitalized, have fields mismatched, etc. can more easily be sorted and corrected using Excel and then re-uploaded to update.”
“The impact of fixing just this one issue (first name field) can have a ripple effect through your entire process by allowing the use of personalization tokens throughout.”
“From Smart content on pages to utilizing first name tokens in marketing and sales communications can allow for a more personalized and human feel for marketing interactions.”
“At Nextiny Marketing, We use Insycle to quickly identify when names are not capitalized and fix them with one click.” said Gabriel Marguglio, Founder and CEO.
ZIP Codes
Many companies upload Excel spreadsheets full of contacts to their CRMs. And it usually works. But there’s one place where Excel can struggle: ZIP codes that start with zero.
“According to 2016 census info approx. 27,000,000 people live in zips with leading zeros, which amounts to 8% of the US population,” says Thomas Bonneau from gb|sterling. And because Excel can have trouble with zero-initial ZIP codes, that’s a problem.
“[I]f you have a bad data file you could be ignoring nearly 10% of your nationwide dataset.”
Even if you’ve already dealt with this problem, it can pop up again, says Bonneau:
“If you have a data file in Excel containing zip code and the Column and the column is formatted correctly (Special Zip Code or Text), everything will look great if you have a zip with a leading zero (e.g. 02739). However, when you save this file into a CSV to import into your CRM and re-open it again to edit, the leading zero will drop off because the CSV ignores any prior Excel formatting that was preserving this zero.”
So how do you fix this issue?
Fix the Excel file before you upload it to your CRM:
-
Select the Column with Zip and go to Format > Cells
-
Stay on the Number tab and choose the Special Category
-
Select Zip Code and hit OK
-
Now the entire row will add leading zeros to the zip codes that are missing them
-
Make any other edits needed and then re-save your file as a CSV
-
Do NOT open the file again in Excel and directly import it into the CRM. When you see a preview of the data file in your CRM import tool, it should show the preserved leading zero”
Stephanie Baiocchi from IMPACT has dealt with this problem, too. But she’s found an even easier way to solve it: “I've been using Insycle! Previously, I had to create a list in HubSpot and scrub through them manually.”
And Baiocchi has seen an improvement. “We've been able to send out direct mail (even just things like client gifts) without having to proof every address or dealing with a lot of returned mail.”
|
Related articles 4 Best Practices for Salesforce Data Cleansing Find & Fix CRM Data Issues using the Data Health Assessment Tool How to Clean HubSpot Import Contacts and Improve Your ROI |
2. Consolidate and Standardize Data Fields
There are all sorts of reasons that you might have low-quality contact data in your CRM.
For example, Cliptoo sometimes has difficulty tracking the type of email communication that a contact has subscribed for, says Jesse Hendriks. And that can be problematic when you’re trying to stay GDPR-compliant.
“We commonly find that a CRM database becomes messy/dirty from our client’s demand generation campaigns when the sales team aren’t accurately updating the contact data in the right spot and on a regular basis,” says Chak Ng from Alchemise Consulting.
So marketers should do what they can to fix and avoid this situation.
“When we cleaned the CRM database of a multinational vendor organisation in the IT industry,” says consultant Peter Strohkorb, “we found that the sales reps had invented no less than EIGHT different ways of representing the Australian state New South Wales! The CRM field contained variations, such as: NSW, N.S.W., New SW, New South Wales, NSWALES, etc.”
“[T]his particular CRM system interpreted the different spellings as sovereign states, which made sales pipeline reports very interesting indeed.”
“Once the issue was identified, ideally they would have used a software de-duping tool to correct it. However in the end it was so messy, the company had to employ a bunch of students to manually re-key the data field at considerable expense,” says Strohkorb.
“In hindsight it would have been easy for the CRM in this business to present a predefined choice of States for reps to select from. However in this unfortunate case the vendor had not thought that far ahead until it was too late.”
“Once again, Insycle makes the process of standardizing the way certain data like cities, states, countries, job titles, company departments, etc quite easy. Insycle will automatically determine the number of times a specific value exists in a field and make it easy for you to change it to a standard. For example, if 100 leads or salespeople entered Virginia for their State, but you want it to read, VA, you can quickly update all 100 rows with one update.” added Marguglio.
“I recommend to create a database washing machine,” says Oleg from DevCom, “unifying all the possible field values according to a pre-made naming convention.”
“For example, a database I was cleaning out had different values for ‘Country’ field like ‘United States,’ ‘USA,’ the United States,’ etc. I created an elaborate automation system predicting all the possible errors and unifying them into one value like ‘USA.’”
“Unfortunately, the same elaborate task had to be done for all the other fields and values. Yet, I had a clean and working database at the end of the day.”“Creating a unified naming convention and a washing machine cleaning out the database is a must for large databases if you want to send out proper email campaigns. Otherwise, a lot of contacts may be missing from your mailing list.”
Oleg wasn’t the only marketer who recommended automating your database-cleaning process.
Brian Serocke from Beacons Point had a similar problem: “Multiple data values that aren't standardized for a particular data point (i.e. California, CA, Ca, ca, Cali all listed as possible values for ‘state/region’).”These fields “were not standardized with a drop-down menu on web forms. In addition, offline data sources meant there was no standardization when data was collected.”
“In those instances, we needed to export data to a spreadsheet, manually check the field for multiple values, and edit each field to standardize the value. We would then need to reformat the spreadsheet (depending on the source) to upload successfully to our CRM.”
“We have since utilized marketing automation tools to run workflows that allow us to easily make data values consistent across all of our contacts.”And Beacons Point has seen great results.“We've reduced the time spent maintaining data by 30% by leveraging automation to do the heavy lifting. Plus, it has reduced potential errors and removed the need to task that duty to an employee.”
“Clean data means you can get more personal and operate more efficiently.”
Standardize Fields Upfront
Some companies have found that upfront CRM data standardization works best for their needs.
“To eliminate issues with your naming conventions, create a QA system that builds the right habits with your sales team,” says Kimmie Champlin from Clutch.co.
“By glancing at your team's data once per week, you'll easily identify problems in the data and even trends as to who is respecting the naming conventions and who is ignoring them.”“Additionally, make your naming conventions as standardized as possible. General guidelines, like always including the company name or opportunity pipeline when naming an opportunity, can go a long way. This is particularly important if your CRM also contains sales resources, like email templates. ““Once guidelines are in place and a QA system is agreed upon by your team, post the correct naming conventions in appropriate locations in your office or on a shared resource that your team can find easily.”“When your CRM is new,” continues Champlin, “naming conventions can seem unimportant. When you have 200,000+ pieces of data, however, you'll be surprised about how those early records confuse every member of your team.” LyntonWeb includes database cleaning in the discovery phase with stakeholders, says Jennifer Lux.
“What we usually see is that sales people updating different properties, and deal stages and therefore the reporting is messy and there is a lack of visibility into the nuances of the sales process.”
After the discovery, and an audit, sales and marketing teams receive “holistic recommendations.”
“This approach not only standardizes the CRM’s use, but provides the reporting needed by leadership to make data-informed decisions.”
Limit The Use of Free-form Text Fields
“I have seen so many CRM systems where sales people used comment fields for very important information on their customers or deals,” says Gabriel Gheorghiu of G2 Crowd.
“Except for [those salespeople], no one could even find, let alone understand, the relationship with the customer. And when sales people leave the company, the information becomes pretty much useless.”
Gheorghiu’s recommendation: “Custom fields should only be created by an admin. It's also important to clearly define what the new field is supposed to do, how it will be used, and who benefits.”
And, when you can, limit text fields. “Text fields are the worst.”
“Adding new options to existing lists is also a bad idea. I had a customer who created a country called Scandinavia because they had only a few customers in Denmark and Sweden. When that changed, they started using the two country names, but old transactions still used Scandinavia as country so reports weren't accurate.”
It’s also a good idea to limit text fields on customer-facing forms, says Grou’s Martha Madero.
“We've encountered databases that only use fields like email and name and then there are this open fields like message or ‘what are you looking for’ that allow the user to just write whatever they want.”
“This is hard to use because you need to be order to analyze profiles, needs and other things like timing and budget and without the structure in the way you capture the information, you will have a hard time to identify consistent keywords that allow you to detect trends.”
“The solution to this is basically to correctly structure the fields you use in your forms and try to have closed option answers.”“It is important to determine the correct amount of fields you want to use in a form and the correct piece of information you will be asking them,” notes Madero.
“Don't just ask anything because you can, determine what you need to analyze and then ask that piece of information.”
Re-engage Users to Get Updated Information
Esc creates “an engaging editorial plan with surveys and premium content to download” to get updated information from customers, says Davide Fornasiero.
“If a lead is engaged he'll update his [contact information].”
Fornasiero’s agency has put this tactic to good use: “We cleaned a 5000 contacts database that was completely abandoned by re-engaging almost 3500 contacts through a dedicated editorial plan.”
Task People to Update Fields As Your Database Grows
Scale My Empire uses the Copper CRM, which scrapes information from email signatures and Google, says Paul Higgins. But sometimes that information is incorrect.
“We have a Virtual Assistant who checks the address against company website to make sure it is accurate. This helps for future reporting and also allows us to send geographic targeted campaigns. ““We also have custom fields to be completed. These could include the person’s expertise, what industry they are from, how they found us etc. The Virtual Assistant will fill out what they know and then task the sales person responsible within the sales CRM to complete the rest.”“This has lead to an increase in sales conversions by 10%.”
If you want to make this process easier, Peter Caputa of Databox recommends creating views of your CRM data where you want someone to update the records manually. “For example, if you have a database of contacts you have built, and you need to know something about them that you can only find out from looking at their website, you might want to hire someone to review these leads and update the information you need. For example, we hired someone from Upwork to identify the different tools marketing agencies recommend to their clients. I love how Insycle makes it easy to provide this list to someone outside our organization and gives them the ability to update the fields. Before, we had to download and upload spreadsheets like crazy.”
3. Merge Duplicate Records
“At the same time marketing keeps importing contact data (such as .XLS or .CSV format) into the CRM without correct fields or data scrubbing from ongoing events or campaigns. What happens then is that issues like contact duplication tends to get messy/dirty over time when it is not maintained or administered regularly.”
James Pollard from The Advisor Coach agrees when it comes to data cleaning:
“For example, one person could have interacted with your company using two different email addresses. If that's the case, you can send the same follow-up message two times to the same person. It sends a message to the prospect that you're unprofessional and don't know what you're doing.”
So how do marketers eliminate duplicate contacts and other duplicate data in their CRMs? We heard several suggestions.
Nathan Heider at Campaign Creators uses a manual process for HubSpot deduplication:
-
select which of the two contacts you want to hold the information
-
click ‘Actions’ under the name of the contact
-
select ‘Merge’
-
type in the name of the contact you want merged
While some companies get rid of duplicate contacts only occasionally, we heard from several marketers who do it regularly. Removing duplicate data becomes a consistent piece of their work.
“Our organization typically will reduce duplicates and fix incomplete data in our CRM once every quarter to keep the database clean and reports accurate,” says Colton De Vos from Resolute Technology Solutions.
During the cleanup process, the team will “go in and decide to either merge the duplicate deals / contacts, delete one, or keep them both. This can get a bit tricky as some of the data may be correct in both but ensuring you keep the right info can be hard if you weren't the one to enter it.”
“The clean up process often involves reaching out to the people listed as the Owner of the 'contact' or 'deal' and determining what should be kept.”
DDI Development “made a process of routinely reviewing and checking for duplicates in our workflow continuous instead of just a one-time commitment,” says Alexandra Zelenko.
“Once again, we use Insycle to identify duplicate contacts. Of course, HubSpot does not allow you to enter a second contact with the same email address. But, given that we capture leads from a variety of systems and sometimes do not always capture email addresses right away, we often end up with multiple records for the same person. We might only know their phone number initially (in the case of inbound phone calls) or they might have used a different email address when they filled out a second form. So, in Insycle, we search for contacts with the same phone number or the same first and last name and then merge them when it is appropriate. Insycle allows us to find duplicates based on any field value being similar, so we can quickly spot and merge records.” shared Gabriel Marguglio from Nextiny Marketing, a heavy user of Insycle.
Bryan Gorman also from Nextiny explained this further. For background, he told us they use software like AirCall and CallRail for capturing phone leads. “And that leads to some weird data in HubSpot. In order to create a lead in our HubSpot CRM, CallRail auto fills the email field property with [phonenumber]@call.com. This is a mess when we are creating lists and having to segment by ‘Email does not contain @call.com.’”
“Fortunately, using Insycle has reduced the time needed to fix problems like this and lifecycle issues from 2–3 hours to 30 minutes per client.” Marguglio added.
But, it is not just about saving time. CRM data cleansing plays an even bigger role.
Marketers agree that eliminating duplicate data fields is key for effective marketing and sales. “[By merging duplicate fields, we] get accurate and up-to-date data that help to improve relations with clients, maximize upselling and cross-selling, etc.” added Zelenko.
Merge & Eliminate Duplicate Data Fields
Sometimes there’s more to the problem than having two contacts for the same person. You might have information duplicated within contacts, which takes up extra space and makes your entries more complicated than they need to be.
Casey LeBrun from Revenue River told us about his agency’s process:
“For example, we get all the salespeople together to decide on which job titles we want to collect. This final list will differ from the duplicated properties and their fields that you already have.”
“From there we use automation to copy the existing property values into our new standardized values. If there is a field with no match, we will bucket that under another field.”Michele from MKT4EDU outlined a simpler process: “I define ONE main property to receive the information and clean the others (remove the value and delete).”
Salespeople certainly appreciate keeping data clean. But, the source of duplicate fields with similar information on individual records is not just salespeople or marketers.
“[A] SaaS company may be pushing usage data to the CRM about their customers' product usage with the platform/application,” says SmartBug Media’s Drew Cohen.
“With integrations like this that involve APIs, there is a tendency for things to get over-complicated. Time in platform, engagement metrics, etc. tend to be a common area we see duplication with multiple fields having similar names and data.”“When we run into this issue, we first like to audit all of the fields that are coming from the source. In this example, the source would be the SaaS platform/application. We look at all of the fields that are being pushed to the CRM from that source, and this audit is the foundation for what will turn into the cleanup exercise.”
“We determine where any duplication issues exist, then set up workflows to move data from one property to another, if necessary. With marketing automation tools/CRM workflows, this is a fairly efficient process if the audit is comprehensive and a cleanup strategy is in place!”
CRM data cleansing begins with an audit. As Drew said — cleansing that CRM is the foundation that sets the stage for better results in the future.
“Using the aggregate feature with Insycle,” says Chris Hobbs from Eyeview, “we were able to quickly and accurately transfer data from [duplicate data] fields into new fields.”
“We have better data quality to work with and reduced our field counts making input and reporting much more efficient. We saved time getting more accurate data that is easier to understand.” shared Hobbs. Cohen agress, “The positive impact of this is a drastic reduction in overall CRM fields, as well as the amount of data that sales and marketing professionals are required to sift through.”
Delete Bad Leads
Marketers recommended different criteria for identifying contacts that can safely be deleted from a CRM.
“Typically when we find incomplete data for a contact, we speak to stakeholders to determine if these are viable, qualified contacts or not,” says Adventii’s Matthew Boyle.
“More often than not, we end up deleting all incomplete contacts within the CRM, focusing on only contacts that have complete information.“Once cleaned, companies no longer waste their time following up on leads that are unqualified or icy leads.”
CRM data cleansing helps to prevent that icyness.
Gem Latimer from BabelQuest finds that “[h]aving a significant number of contacts who have either hard bounced or unsubscribes” is a common problem. You can’t email them, but they take up space in your system—which could mean you’re paying more.
“Being able to track the number of hard bounces and unsubscribes is a big plus here.”
“We use HubSpot and use the lists tool to pull them into one place, before going through and figuring out if: a) they are not needed and can be deleted b) if they are an active prospect or customer and need some outreach to ask why they unsubscribed (or update their email address if it was wrong) or c) unbounce them if it was a system error.”
Nectafy uses a HubSpot filter to find dead leads, says Gabby Shultis.
“The exact parameters vary across clients, and how their sales team identifies a dead lead,” Shultis says, but these are common criteria:
-
Contact's time of last visit > X days
-
Contact hasn't opened an email in > X days
-
Contact has not subscribed to the blog
“By removing all of the client's dead leads, they are able to shift their focus onto the contacts in their database that matter—those that actually have a chance of becoming an MQL, SQL, or potential Customer.”
4. CRM Data Cleanup: Create a System and Use It Often
No matter how you decide to clean your database, it’s important to have a system. Once you’ve created a set of rules that your sales and marketing teams can follow, make it a habit.
That’s how you’ll prevent more errors from happening in the future and keep a single customer view through data cleansing. Whether you are dealing with duplicate data, incomplete data, or bad data in your CRM system — having a step-by-step plan to follow puts you in an improved position.
Of course, there’s no way to completely obviate the need for database cleaning. CRM data will always have some bad data in it. Human-input customer data will always have typos, mistakes, and other issues. And clean data comes from persistence. But if you can create a system that works and make sure your teams use it, you’ll save yourself a huge amount of time in the long run.
Learn more about how Insycle can help cleanse HubSpot data and improve their data operations on the whole.
