Self maid

How to Format and Standardize Your CRM Data for Consistency

You know you have inconsistencies in your data, but you aren't sure what the variations are. It's important to understand the full picture you have before trying to make changes. 

With Insycle, you can dig into database fields to identify inconsistencies and decide how to standardize them. Then you can format and make data consistent in bulk.

The Cleanse Data module helps you analyze what's in your database and can fix inconsistencies in fields that have set values—like job titles, countries, or industries. The Transform Data module is ideal for standardizing fields with no set values, such as phone numbers, addresses, or locations. Transform Data also allows you to set up automation for keeping things consistent, long term.

Standardize Data in Bulk with the Cleanse Data Module

The Cleanse Data module gives you a full view of your data down to the individual record level. You can explore data and identify opportunities for standardization, then make the changes in bulk.

Process Summary

  1. Choose a field to explore.
  2. Analyze the variations and decide how to standardize.
  3. Filter records down to only those that need changing.
  4. Select records to modify.
  5. Specify the changes to make.
  6. Apply the changes to the CRM.

 

Step-by-Step Instructions

1. Select a Field and Explore the Values

Navigate to the Cleanse Data module, and in the top menu select the database and record type. Then explore the templates for a pre-built solution.

Under Step 1, search for the field you want to clean up. The results table shows field properties, including the field type and the number of unique values within the field across your database.  

Click the checkbox beside the field.

in1.png

The different values that are contained in your database for the selected field will populate the Record Viewer at the bottom of the page.

Explore the different field variants to identify inconsistent or irrelevant values. Use the checkbox to expand a value set.

in2.png

This opens up a secondary Viewer below the first, where you can review the individual records and get additional context.

in3.png

To see more information for each of the resulting records, you can alter the fields in the Record Viewer by using the Layout tab in Step 2. 

in4.png

2. Filter Out Records That Don’t Need Changing

When you're looking to fix specific values, use the filter to work only with an appropriate subset of your data. 

Under Step 2, click the Filter tab and select the field to work with. Use the Condition to set the rule that the data in the selected field must meet. 

In the below example, only records that include the phrases "CEO" or "Chief" in the Job Title field will be part of this cleanup task. The bar "|" character is used to separate multiple values. 

in5.png

When you click Search, the Record Viewer table at the bottom of the page will be updated.

3. Select Records to Modify, Specify the Changes, and Update the CRM

In the Record Viewer, check the box for the values you want to update. Or to make more granular changes, select individual records in the secondary Viewer below the first.

in6.png

Under Step 3, specify what the selected field values should be changed to. In this example, all the records that have a Job Title of "Chief Executive Officer," will be updated to, "CEO."

in7.png

Click the Update button and confirm the change. Note that there is no preview step here. Once confirmed, the update will be applied to your CRM.

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

All settings here can be saved in a template. You can run similar processes and create templates for fields like Industry, U.S. State, Zip Code, or Country.

Troubleshooting

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.

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

Standardize Data in Bulk with the Transform Data Module

The Transform Data module provides advanced formatting functions for fields that don't have the same values from one record to another, such as names, phone numbers, and addresses. 

Process Summary

  1. Set filter criteria.
  2. Select records to update.
  3. Pick fields to modify and set your standardizing rules.
  4. Preview the changes. 
  5. Apply the standardization changes to the CRM records.

 

Step-by-Step Instructions

1. Find Records That Need Cleanup

Navigate to the Transform Data module, and in the top menu select the database and record type. Then explore the default templates for a pre-built solution.

Under Step 1, set up a filter to narrow down the database records to show only what you want to change. This ensures you aren't needlessly trying to format fields that don't contain any or have errant data.

In this example, the filter will limit records to only show those that have a phone number, and the value has the correct number of digits to be properly formatted to the E.164 standard. Learn more about using regular expressions and other advanced functions here.

in9.png

Click Search and scroll down to the Record Viewer at the bottom of the screen. You will see all records that match your filter.

in10.png

To alter the fields that appear in this preview, click the Layout tab in Step 1.

2. Set the Changes to Make on the Field Data

In Step 2, you'll set up functions for formatting and cleaning up your data. It is best practice to create a separate template for each field you want to modify.

Select the fields to modify and what rules to follow in the standardization process. There are pre-built functions for standardizing phone numbers, countries, states, domains, and other values, so you should explore the options in the Function dropdown.

in11.png

In the above example, the Phone Number field uses the pre-built function, Format Phone E. 164 Standard. This will yield a result like, "+442071838750."

Some fields may need more than a single clean-up function applied. In the example below, the First Name will be formatted to the proper case, whitespace will be removed, and the salutations Mr., Ms., or Mrs. will be removed.

in12.png
3. Preview Changes and Update CRM Records

Preview Changes in the CSV Report

With the filters and functions set up, you can preview the changes. It's important to verify that your formatting is working as expected before those changes are pushed to your live database.

Under Step 3, click the Review button, then select the Preview option in the popup.

in13.png

On the Notify tab, you can select recipients for the email report and add additional context to the message. (Make sure to hit Enter after each email address.)

On the When tab, click Run Now and select which records to apply the change to (in most cases this will be All), then click the Run Now button.

Open the CSV file from your email in a spreadsheet application and review the (Before) and (After) values for each field. If the results don't look the way you expected, go back to your filters and functions and try making some adjustments before previewing again.

in14.png

Apply Changes to the CRM

If everything in your CSV preview looks correct, return to Insycle and move forward with applying the changes to the live CRM data.

Click the Review button under Step 3 and select Update Mode

On the When tab you can choose to run and apply the update now, or you can set up an automation to run the template on a regular schedule. The first time you apply these changes to the CRM, you should use Run Now.

in15.png

After you've seen the results in the CRM and are satisfied with how the operation runs, you can save all of the configurations as a template, and set up automation so this formatting operation runs on a set schedule. This ensures that your fields are consistently formatted on an ongoing basis.

Additional Formatting Examples for Step 2

Here is an example of a function you might build to make street addresses consistent.

in16.png

This formats and standardizes several aspects of the Street Address field.

  • Street → St
  • Drive → Dr
  • Avenue → Ave
  • Boulevard → Blvd

There are also pre-built functions for standardizing states either to the full name (New York) or the abbreviation (NY).

Troubleshooting

If you're having trouble with the Transform Data module, here are a couple of things to look into:

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.

in17.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 prior to importing.

in18.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.

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

Additional Resources

Related Help Articles

Related Blog Articles