Data standardization is the procedure of processing the data to transform it from the different formats to a standard format. Most people use data normalization and data standardization interchangeably. While in math, standardization and normalization is different, when dealing with data it is essentially the same thing. Data standardization or normalization is the method of classification of data into a standard form which can then be used for segmentation, analytics, territory planning and so much more. Data normalization is a part of the data cleaning initiatives. In order to avoid a costly data cleaning, you should have certain fields locked down and have drop down as field types when setting up your online forms.
Here are some examples of data that you want to standardize or normalize may include:
- Data Standardization of the country field– For example: USA, United States of America, United States, US. Data standardization will map all of the above fields to United States or using ISO code for United States: US.
- Normalization of State or Province: For example: State of New York, New York State, NY State can be standardized to “NY.
- Standardization of Industry: Industry can be normalized by NAICS or SIC . For instance: 311230 – Breakfast Cereal Manufacturing, 311313 – Beet Sugar Manufacturing and 311314 – Cane Sugar Manufacturing would be classified as “Manufacturing”.
- Data Normalization by Title: Can be split into two levels Functions and Level. For Example: CMO can be standardized to Marketing as job function and C-level for Level. CFO can be normalized to Finance and C-Level for Level.
- Other Fields you may want to normalize includes: Customer Type, Prospect Type, Employee range, Revenue Range or email domain/website.
To standardize or normalize data there are some steps that you can follow to do it yourself:
- Export you data to the Excel spreadsheet or Google sheet
- Insert Pivot table and run it based on the field you are thinking of normalizing. Sort based on count
- Come up with the desired values, for example: US (2 Digit ISO) for the country field
- Categorize each value into the desired list, you can use Vlookup to bring it back to the whole file.
- Update Historical Values using Dataloader for Salesforce or Import functions of your Marketing Automation tools.
- Implement a workflow based on contained rules or upload the look up table to be able to do a “vlookup” adjustment in the system
Normalizing fields can be a headache, we can help! StrategicDB is a full data cleansing service company which specializes in sales and marketing data cleaning projects including data normalization and standardization. Learn more about our services here.