Data standardization playbook

• Read our data management strategy playbook for help with creating a data management strategy.
  • CRM data management and strategy playbook
• The DemandTools modules referenced in this playbook are for versions 5.X.X. Please be sure to update your software.
• We recommend setting up a sandbox environment to test data manipulations prior to implementing them in a production environment. You don’t want to make changes that adversely affect your CRM data quality.
• If you are trying to solve a specific data problem, review the product training documentation in the Validity Help center or get answers to your questions from Validity’s data experts during office hours.
  • Validity Help Center
  • DemandTools office hours
  • GridBuddy Connect office hours
• For technical issues regarding your software, please contact Validity support.
  • support@validity.com

A clear data management strategy will help to improve your CRM data quality and support achieving your desired business outcomes. With a clean CRM database, many businesses achieve better: 

• Accuracy in sales forecasts and reporting
• Data privacy compliance
• Targeting for marketing
• Operational efficiency

• Deciding on your data standards
• Standardization methodology
• Standardization assessment
• Standardizing your data
• Automating standardization jobs
• What to do next

• Data governance: Developing and implementing data policies and procedures to support business goals.
• Data quality: Accurate, complete, reliable, and actionable data.
• Data standardization: Applying a common and consistent data format.
• Data hygiene: The process of cleaning data to reduce errors and improve data quality.

Decide on your data standards

Talk with your data governance team to determine or review your data standards. Your business stakeholders in sales, marketing, customer success, and finance have CRM reports and possibly legal requirements that require standardized data. 

Common unstandardized data types

Make a list of data types along with its data standard you will follow going forward. Below is a list of common fields (with examples) you need to decide on how to standardize. Apply standards according to your needs and include other important data fields that aren’t listed below. Share your list with the data governance team to ensure alignment with business goals. Consider international formats and any legal or data privacy requirements when deciding which data standards to apply. 

• Names
  • First and last name in proper case (Bill Smith)
  • Prefix (Ms. or Miss)
  • Job titles (CEO or Chief Executive Officer)
  • Business name (Sample Industries Inc or Sample Industries, Inc.)
• Phone number format (123-456-7891 or (123) 456-7891) (442012345678 or +44 20 1234 5678)
• Date format (February 1, 2022 or 02/01/2022)
• Address
  • State/Province (IA or Iowa) (AB or Alberta)
  • Country (UK or United Kingdom)
  • Zip/postal codes (80303 or 80303-6542) (B7V 8Y8 or B7V-8Y8)
• Website (https://subdomain.domain.com or subdomain.domain.com)
• Currency
  • Apostrophe or no apostrophe ($1000.00 or $1,000.00)
• Demographics
  • Gender (F or Female)
• Firmographics
  • Number of employees (less than 500 or < 500)

Standardization methodology

Before you start standardizing your data, ensure you have a plan in place to help you manage standardization activities. A plan can help you split up the amount of work you do into manageable sizes.

Standardization frequency

Decide on the frequency you need to standardize your data. How frequently you standardize depends on the rate at which you are introducing new records into your organization as well as the overall size of your database.  Determine the frequency based on your needs. No matter what frequency is required, you can automate standardization activities using DemandTools. 

• Daily: If you have hundreds of new records coming in daily from different channels, you probably need to run standardization jobs daily. 
• Weekly or monthly: If you have records that trickle in each week or come in less frequently, then you probably need to run standardization jobs weekly or monthly.
• Quarterly: Each quarter, schedule a comprehensive data standardization review and cleanse to ensure all fields are standardized as expected. New data fields may be added over time, so you want to be sure they are not missed as your business grows.
• Standardize upon import:  DemandTools allows you to standardize data during import. Be sure to train staff responsible for importing on standardization requirements and procedures.

Data may be input or changed manually in your CRM by staff in your sales, marketing, customer success, and finance teams, so you may need to adjust standardization frequencies even if your data intake rate is low.

Standardization assessment

Assess your data using the Assess, Tune or Export modules to understand the scope of work and potential sources of unstandardized data. Share any insights on sources of unstandardized data with your data governance team.

1. For each object, perform an analysis to identify specific unstandardized data types or data fields based on your new data standards.
2. With your data governance team, create a prioritized list based on the object and/or data type. As you start standardizing your data, use this list to ensure the higher priority data is standardized first and more frequently.

Standardize your data

Now you know what data standards to apply and have a plan, start standardizing your data.  

Other prebuilt scenarios you can use to standardize your data are listed below. 

• Accounts(BillingStreet) - Normalized US Address: Standardize a street address to match the USPS preferred format
• Accounts(BillingState) – LongName: Updates all US states & Canadian provinces to the long form
• Accounts(BillingCountry) - ISO 3Character: Format Countries to an ISO standard value (3-character, Long Form or ISO number)
• Accounts(BillingCountry) - ISO 2Character: Format Countries to an ISO standard value (2-character, Long Form or ISO number)
• Accounts(Name) – ProperCase: Capitalize the first letter and applies lower case to other letters within the same word
• Accounts(BillingCountry) – LongName: Converts country to long form
• Accounts(BillingCountry) - US Filler: Replaces the country name with the value “US” for the Account object.
• Accounts(BillingState) – ShortName: Updates all US states & Canadian provinces to the ISO two character abbreviation.
• Accounts(BillingZip) – ZipCodeClean: Reformat US zip codes adding a leading zero if needed. The primary purpose is to fix zip codes where the leading zero was truncated on import.
• Accounts(BillingCountry) - ISO Number: Converts country to ISO 3 digit number
• Accounts(Name) - StringReplaceCS(Incorporated): Search a field, find the string “Incorporated”, replace with “Inc.”
• Accounts(Name) - StringReplaceCS(Company): Search a field, find a string “Company”, replace with “Co.”
• Accounts(Phone) – NAPhoneFix: Takes a phone number in the Account object and formats it to match the format as entered in Salesforce.  Will also standardize an abbreviation for extension to x
• Leads(Phone) – NAPhoneFix: Takes a phone number in the Lead object and formats it to match the format as entered in Salesforce.  Will also standardize an abbreviation for extension to x
• Leads(Country) - US Filler: Replaces the country name with the value “US” for the Lead object.
• Contacts(MailingCountry) - US Filler: Replaces the country name with the value “US” for the Contact object.
• Contacts(Phone) - Matches Accounts(Phone): Replaces the phone number in the Contact “Phone” field and replaces with the value with the Account phone number
• Contacts(Phone) – NAPhoneFix: Takes a phone number in the Contact object and formats it to match the format as entered in Salesforce.  Will also standardize an abbreviation for extension to x
• Contacts(Mailing*) - Matches Accounts(Billing*): Replaces the Contact mailing street, postal code, city, and state with the Account street, postal code, City, and state

Automate your standardization changes

Schedule your standardization jobs to run automatically at a frequency you define. We recommend testing scenario automations in a sandbox prior to implementing the automations in a production environment. 

• Schedule scenario

1. Proceed to the other data playbooks to continue cleaning your data.

• Assess and Verify playbook (do this one first)
• Dedupe playbook
• Missing data playbook
• Malformed data playbook