Data standardization is a bit of a buzzword in the MOPs industry, but what is it really? Why should MOPs professionals care about it and what are the risks if it’s not done properly? Let’s dive into data standardization and discuss some processes for ensuring the data that enters your marketing automation platform (MAP) or CRM is clean, error-free, and ready to use for marketing efforts.

What is data standardization in Marketing and Sales?

Data standardization is the process of cleaning and normalizing data that is used by your MAP and/or CRM. There are a number of steps you can take to standardize your data once it is in your MAP, but the easiest and most important way to keep your MAP populated with clean data is developing a data standardization process for data before it enters your system.

Why does data standardization and data quality matter in Marketing and Sales?

Messy data lead to endless business inefficiencies; business inefficiencies lead to lost revenue. Say you want to pull a list of prospects located in Washington state, but you don’t have a system for data standardization in place.

You may have records that have State field data ranging from WA, Washington, WASH, or any number of variations or misspellings. Or, say you’d like to personalize an email send to prospects by using their first name in the subject line, but your database includes first names that are in all caps, all lowercase, proper case, or contain data that isn’t a first name – like “John – Left Company” or “Accounts Payable.”

Not having a process for how to manage data standardization before the data enters your MAP or CRM is going to snowball until your data is no longer considered reliable for marketing efforts. Putting some simple processes in place, like a list import template with required fields and a list scrubbing checklist, are going to ensure that any data added to your systems has been reviewed and vetted for cleanliness and accuracy.

How do companies clean their marketing/sales data?

There are two approaches to data standardization: (1) “retrospective” normalizing data that already exists in your system, and (2) “on-going” data normalizing before it enters your system. The former can be done with operational processes that look for incorrect data and retroactively process the records to align with accepted values–basically the “fix it up in Excel and overwrite the messy data in the system” approach that you hopefully only have to do once. The latter (on-going marketing data cleansing) is what we will discuss here, using list import templates and list scrubbing processes.

The first step is to develop a list import template that will be used across your organization. This template should contain all the relevant fields that may be included in a list import. Required fields (either by your system requirements or by your MOPs department) should be marked with an asterisk in the column header. A separate tab in the list import template spreadsheet can be added to hold accepted values for fields like country, state, industry, job level, or any other field that needs standardized values.

The second step is to develop a list scrubbing process, often accompanied by a list scrubbing checklist. This will ensure that whenever a list is received, every field is checked for accuracy and alignment with your organization’s data standardization processes.

Here are some examples of items to include in a list scrubbing checklist:

• Are all required fields populated?
• Are all first and last names proper cased?
• Are all emails formatted correctly, with an @ symbol and a .com or equivalent?
• Are there any duplicate emails listed?
• Are all state and country values abbreviated/spelled correctly?
• Are all telephone numbers formatted uniformly?
• Are all industry values validated against accepted values?
• Has an opt-in value been provided for all records?

There are a few extra-helpful Excel formulas for Sales Operations and Marketing Operations professionals, and you can use them for speeding up the list scrubbing process:

• PROPER – Use this to reformat first and last names, job titles and company names. (Be careful with company names, however, as this formula will reformat LLC/LLP incorrectly.)
• LOWER – Use this to reformat email addresses to all lowercase.
• XLOOKUP/VLOOKUP – This can be extremely helpful to quickly update state or country values from abbreviated to spelled out, or vice versa. Use a separate tab with all of the state/country values matched to their abbreviations as your return values.
• TEXT – Use this to reformat phone numbers from a number into a phone number format. Note that this will only work if the phone number values contain just numbers with no characters.
• SUBSTITUTE – Use this to strip characters from phone numbers to leave just numbers.
• CONCAT – This is helpful if you need to combine separate address columns into one address field.
• TEXTBEFORE/TEXTAFTER – These are helpful if you need to strip an employee count from a range – use TEXTBEFORE to retain the lower end of the range and TEXTAFTER to retain the higher end of the range.
• LEN – Use this to get a character count of a field. Helpful if you need to import anything into fields with character restrictions.

How do you manage clean data moving forward once you’ve implemented a list import template and list scrubbing checklist?

If you import lists directly into your MAP, usually from events, trade shows, or content syndication programs, delegate someone on your MOPs team to own the list scrubbing and import process. Verifying that the data is clean before manually importing is a simple process using the steps and guidelines listed above.

If you work with vendors for content syndication programs and have leads flowing into your MAP using APIs or webhooks, work with your vendor to ensure the data they provide is in a format you will accept. Are states and countries spelled out or abbreviated? Are employee count values provided as a number or a range? Do industry values match the industries your system will accept?

If leads are entering your system from form fills directly from your prospects via webinar registrations, demo requests, etc., review your form setups and ensure that standardized values are available in dropdown picklists.

Data standardization is a critical process in the core of Lifecycle Processing that can significantly impact the effectiveness of marketing efforts across the board. Developing a standardized process for data entry and implementing a list scrubbing checklist can help ensure that all data in your MAP or CRM is accurate and reliable. With the use of a list import template and an arsenal of Excel formulas to speed up the scrubbing process, you can ensure that your data is clean, accurate, and ready to use for successful marketing campaigns. Welcome to the world of Platform Operations enabling Marketing Intelligence to standardize data for upcoming marketing reports and marketing dashboards.