Standardize Job Title, Industry, State, Country, or Any Other Free-Text Field

mixtape

Inconsistently Formatted Data Makes It Difficult to Find and Use Records

Your team needs to segment CRM data to send personalized messaging to prospects and customers and generate effective reports, but are having trouble surfacing all of the relevant records because of data inconsistencies.

Standardizing field data in your CRM makes it easier to perform tasks like segmenting data, personalizing campaigns, creating reports, or routing and scoring leads. This is also helpful when you have two integrated CRM systems, but inconsistent field values create errors.

Insycle has several tools that could be used for standardization.

Which Module to Use When

The Cleanse Data module is an analysis-focused tool that allows easy bulk updating. You should use it to understand the variations in your database or for quick bulk updates to specific variations within fields (Chief Executive Officer to CEO).

The Transform Data module is perfect for one-time bulk standardization operations or ongoing, automated standardization. You can use the Cleanse Data module to analyze your data, and the Transform Data module to standardize in bulk, or automate standardization. 

Use the Magical Import module when you have data from an external source in a CSV file format and want to make the data consistent with what already exists in your CRM before importing it.

Standardizing Data with the Cleanse Data Module

With Insycle, you can use the Cleanse Data module to explore your data, identify opportunities for standardization, and bulk update records in your CRM. 

Process Summary

  1. Choose a field to explore and analyze.
  2. Identify variations to update within that field.
  3. Filter records down to only those that need changing.
  4. Select records to modify.
  5. Specify what changes to make.
  6. Apply the changes to the CRM.
Step 1: Select a Field

Navigate to the Cleanse Data module, and select the correct record type (contacts, companies, etc.) in the top menu.

Search for the field to be standardized. In this example, we'll be standardizing job titles, so enter "Job Title" in the search field of Step 1.

job title

The results table will show statistics for the field, including the field type and the number of unique values within the field across your database.

Check the box next to the field you'd like to explore, (i.e., Job Title). This will automatically add that field under Step 2.

pick a field

Once a field is selected, the underlying values are viewable in the Record Viewer at the bottom of the page.

buckets
Step 2: Filter the Results

In the Filter tab on Step 2, we can filter our data further. Here we filter the Job Title field down to only those records that include the phrases "CEO" or "Chief." We use the bar "|" character to separate values. When you click the Search button, the Record Viewer at the bottom of the page will be updated.

filter

You can select one of the buckets by checking the box.

bucket check

This opens up a secondary Record Viewer below the first, allowing you to view individual records within that bucket.

record viewer

 

Troubleshooting
For a complete guide to troubleshooting issues with Insycle, please refer to our article on Troubleshooting Issues

Records not showing up?

First, make sure that you have clicked the Search button.

Second, double-check that your filter is accurate and not too specific. For instance, if you are using the "is" operator in your filter, you might consider using "contains" or "starts with" to identify other records with minor differences.

If you don't see data in the relevant field in Insycle, but the field contains data in your CRM, it is likely a field syncing issue. In this case, contact support through live chat to trigger a manual sync for the field.

standardize-deals-troubleshooting-6.png
Step 3: Select Records, Specify the Change to Make, and Update the CRM

You can select entire buckets of records, or you can select individual records, or both, from the Record Viewers on the bottom.

For our example, we will standardize everyone with the "Chief Executive Officer" job title down to the acronym, "CEO."

mceclip0.png

Then, we set our function for updating the field. 

update

When you click the Update button, you will be prompted to confirm. When you do, the update will be applied to your CRM database, updating all chosen records' job title to be "CEO."

  Note that there is no Preview option in the Cleanse Data module. Once you click the Update button the changes will be made in your CRM database.

To review changes after applying them in the CRM, use the Activity Tracker page.

Troubleshooting
For a complete guide to troubleshooting issues with Insycle, please refer to our article on Troubleshooting Issues
Records not showing up?

First, make sure that you have clicked the Search button.

Second, check to ensure that your filter is accurate, and not too specific. For instance, if you are using the "is" operator in your filter, you might consider using "contains" or "starts with" to identify other records with slight differences.

If you don't see data in the relevant field in Insycle, but the field contains data in your CRM, it is likely a field syncing issue. In this case, contact support through live chat to trigger a manual sync for the field.

standardize-deals-troubleshooting-6.png

All settings here can be saved in a Template. You can run similar processes and create Templates for fields like Industry (for Contacts and Companies), U.S. State, Zip Code, or Country.

Standardizing Data in Bulk with the Transform Data Module

With the Transform Data module, you can standardize job titles, industries, locations, and any free-text field in your CRM in bulk.

Process Summary

  1. Set filter criteria.
  2. Select records to update.
  3. Pick fields to modify and set standardization rules.
  4. Preview the changes. 
  5. Apply the standardization changes to the CRM records.
  6. Set up automation to keep the data consistent.
Step 1: Filter to Show Desired Records

Navigate to the Transform Data module, select the database, and the record type in the top menu.

To get started quickly, explore the default templates. There might already be a template for the fields you want to standardize. For this example, select the Standardize State Address template.

address template

Under Step 1, set up a filter to narrow down the database records to show only what you want to change. In this example, we'll standardize contacts that are from the state of New York.

filter state

With your filter set, a preview of the records with fields that match this filter will populate the Record Viewer at the bottom of the screen.

Here, you can view and select individual records for updates using the checkboxes. When you run the operation, you'll have the option to run the template on all of the records selected by your filter as well. mceclip1.png

Step 2: Pick Fields and Action to Perform on Each

Select the fields to be standardized and what rules to follow in the standardization process.

In this example, we are formatting job titles, industries, and states.

functions
Setting up the Functions

First, select the Fields to be standardized, then select a function to apply to each field.

  • For the State/Region field, select the pre-built Standardize US States, CA provinces function. This standardizes states to their full name.
    • NY→ New York
    • New york → New York
  • For the Job Title field, select the Map function. In the Existing Text field, input "vp sales|vp of sales|vice president sales|vice president of sales." In the New Text field, enter "VP of Sales," which is what all the existing job titles will be updated to if they match one of the inputs in the Existing Text field. This will update your Job title values.
    • vp sales→ VP of Sales
    • vp of sales → VP of Sales
    • vice president sales → VP of Sales
    • vice president of sales → VP of Sales
  • For the Industry field, select the Map function. In the Existing Text field, input "Software|tech|Computer Software." In the New Text field, enter "Technology." The values will update:
    • software → Technology
    • tech → Technology
    • Computer Software → Technology
Step 3: Preview in CSV or Update CRM

Once ready, click the Review button.

First, you'll choose whether you want to run this operation in Update Mode or Preview Mode.

In Preview mode, you'll be able to generate a CSV report that details all of the changes that will be made to your data. Preview Mode does not push any data updates to your live database.

Update mode runs the operation and updates the data in your CRM.

preview or update

On the Notify tab, you can select recipients for the email report. You can also add additional context on this screen.

On the When tab, you choose whether you want to run the module one time, immediately (Run Now), or recurring on a regular, set schedule (Automate). In Automate, you can choose an hourly, daily, weekly, or monthly schedule for your standardization processes.

automate or run now
Troubleshooting
For a complete guide to troubleshooting issues with Insycle, please refer to our article on Troubleshooting Issues

There are some common pitfalls with standardizing in bulk with the Transform Data module that you should be aware of.

Too many variations?

Mapping many variations can clutter your template and make it difficult to maintain.

It may be a good idea to analyze your data and do some initial standardization to cut down on total variations by standardizing using the cleanse data module. Then, with fewer variations to fix, you can further standardize in bulk and automate using the Transform Data module.

Records aren't being standardized?

When using the Map function in the Transform Data module, you must account for every variation in your field that you would like to standardize. Even small differences, such as extra whitespace or errant keystrokes, must be accounted for in the Existing Text field.

standardize-deals-troubleshooting-9.png

To account for these variations, you can:

  • use the Cleanse Data module to examine the variations
  • include them in the Existing Text field
  • use other Insycle functions before mapping to do things like clearing non-letters, removing whitespace, or formatting the field in bulk before importing.

standardize-deals-troubleshooting-10.png

They will be executed in the order listed. If you use functions to clean a field before mapping, they should be first on the list.

The above example standardizes multiple fields in one template for demonstration purposes, but it is best practice to create a separate template for each field.

Standardizing Data from a CSV with the Magical Import Module

With the Magical Import module, you can import a CSV file, set standardization rules for fields in Insycle, then apply those changes on import.

Process Summary

  1. Upload the CSV file.
  2. Map the CSV fields to the CRM fields, set import mode for each field. 
  3. Select the unique identifier.
  4. Specify how the imported data should be handled.
  5. Set up functions to standardize the data.
  6. Import the data into the CRM.

In this section, we'll focus on setting up functions to standardize the data. Review the Import New Records or Update From CSV article for details on the rest of the import process.

Standardizing import data happens in Step 3 of the Magical Import module.

Step 3: Set Up Functions to Standardize the Data

In this step, you select fields and set rules to make the field values consistent. To do this, use the Functions tab in Step 3. In this example, we are standardizing:

  1. Specific industries
  2. Specific job titles
  3. US States (or Canadian Provinces)
prepare data step 3
Doing this ensures that your data is standardized on its way into your CRM database while importing. 
Setting up the Functions

First, select the Fields to be standardized, then select a function to apply to each field. In the Existing Text field, enter the text that is expected to be in the CSV file.

For example, for the Industry field, select the Map function. In the Existing Text field, input "tech|Tech.” You can add as many inputs as needed to the Existing Text field. In the New Text field, enter "Technology & Software" as the replacement value for Existing Text matches.

Expected results of Existing Text → New Text

  • tech|Tech Technology & Software
  • vp sales|vp of sales VP Sales
  • NYNew York

Standardizing options are available for any field in your import CSV using the Map function. The are many functions. To find functions that might apply to a specific field, use the search feature in the Function dropdown.

phone
Filtering Out Records That Don't Match

The Filter tab of Step 3 allows you to filter data out of your import based on fields and conditions that you set.

filter

In the example above, we filter our CSV import down to only contacts with a job title containing the word “Founder.” Because we use the “contains” condition, this will include records that have job titles such as “CEO and Founder,” “Co-Founder,” etc. The term “Founder” must be present anywhere in the field.

Troubleshooting
For a guide on general troubleshooting, please refer to our article on Troubleshooting Issues
Some records aren't being standardized?

First, make sure you click the Apply button in Step 3, after setting up your standardization functions. If you do not click Apply, the changes will not be made. 

When using the Map function in the Magical Import, you must account for every variation in your CSV data. Small differences, such as extra whitespace, must be accounted for in the Existing Text field.

standardize-deals-troubleshooting-11.png

To account for these variations, you can:

  • fix them in the CSV
  • include them in the Existing Text field
  • use other Insycle functions to do things like clearing non-letters, removing whitespace, or formatting the field in bulk before importing.

standardize-deals-troubleshooting-8.png

By saving the configuration as a template, your mapping settings will be preserved, improving consistency and saving you time on every import.

Frequently Asked Questions

Are there modules that let me bulk standardize fields?
Yes, both the Cleanse Data and Transform Data modules offer bulk standardization solutions.
Is it best practice to create a different template for each field?
Yes. For organizational purposes, it is best to create a new template for each field that you would like to standardize.
What functions are available for standardization?
Insycle offers many functions that can be used in standardization process.
  • Format proper case person
  • Format proper case company
  • Format lowercase
  • Format uppercase
  • Format phone USA (multiple formats)
  • Format phone E. 164 Standard
  • Map
  • Map terms
  • Concatenate
  • Remove terms
  • Remove leading/trailing whitespace
  • Remove successive whitespace
  • Remove symbols
  • Remove non-letters
  • Remove non-digits
  • Remove letters
  • Remove digits
  • Remove any and all whitespaces
  • Standardize US States 
  • Standardize Country Name
  • Standardize Country Code
  • Suffix Remove/Add

Additional Resources

Related Help Articles

Related Blog Articles