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 data and make it 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 highly varied values, such as phone numbers, addresses, or job titles. 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. 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 Data Management > Cleanse Data, and in the top menu select the database and record type. Then explore the templates for an existing solution similar to what you need.

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.


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.


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


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


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 acts as an "OR" between multiple values. 


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.


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


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.


Records Aren't Showing Up in the Viewer

If you are sure a field contains data in your CRM but aren't seeing the values or records in the Record Viewer at the bottom of the page, it is often due to the Filter settings under Step 2.

Here are a few things to look into:

  1. Ensure there isn't anything in the filter you didn't intend to use. This often happens if you start with an existing template.
  2. Ensure that your filter is accurate and not too specific. For instance, if you are using the "is" operator in your filter, you might broaden the condition using "contains" or "starts with" to identify other records with slight differences.
  3. Make sure that you have clicked the Search button.

Step 2 Filter

If you still don't see the expected data, it is likely a field syncing issue.

To refresh the data in Insycle, navigate to Settings > Sync Status, select the account, and click the Refresh Accounts List button. Alternatively, you could log out of Insycle and then log back in.

For help re-syncing a specific field, contact Insycle support.

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 Data Management > Transform Data, and select the database and record type. Then explore the templates for an existing solution that may be close to what you need.

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 data or have errant values.

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.


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


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, 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 Field with the data you want to start with, then configure Functions Insycle will 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. Learn about the full list of functions.


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, the salutations Mr. or Mrs. will be removed, and whitespace will be removed.

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 transformations are 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.


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


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 again, this time 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.


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.

Pro Tip: Combining More than One Function

If there isn’t a single function under Step 2 that makes the change you’re looking for, you can still complete the step in one template.

Functions can be layered to make changes in a series of steps. They will cumulatively apply to the field value, executing in order from top to bottom.

For example, you can use two functions to clean up Website URL values that have multiple directories. Instead of “,” you just want to keep the main domain value, “":


  1. The Extract: Domain from URL function will remove the https:// and subdomain, but the directory values after the domain, '/data/bulk/contact/' would still be there. Leaving the Target Field blank, will write the value back to the original field.
  2. To get rid of those directory values, add the function Split: By any delimiter and keep Nth term to remove everything after the forward slash, “/”, and keep the value in the first section.

For another example, you can use several functions to populate the country value by using the email address:


  1. Select the email field with the value to start with.
  2. Select the Split: By any delimiter and pick the last term function. 
  3. In the Parameter field, enter a "." to use as the delimiter, telling Insycle which part of the email value to isolate and use for the next steps.
  4. Click the plus at the end of the row to add an additional function for this field.
  5. Now that you have the two-character country code, select the Standardize: Country Code2 to Name function to transform it to the full country name. Click the plus.
  6. Select the Copy: Value function and the Target Field the country name should be written into.

For one last example, here is a function set you might use to make street addresses consistent:


This formats and standardizes several aspects of the Street Address values:

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

You may need to do some experimentation. Use the Preview mode and CSV report to see if you get the desired result. If not, adjust your functions, maybe try reordering them, then Preview again.


Too many variations?

If the values for a field are wildly different it can be a challenge to set up functions that will catch all the variations. Trying to do it all in one template can make it difficult to keep track of what's going on.

It may be a good idea to analyze your data and do some initial cleanup 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.


Getting Help When Things Don’t Work the Way You Expect

Sometimes you may set up functions, review the Preview CSV, and get (Before) and (After) results that aren’t what you intended. If you’ve tried adjusting or reordering your functions, but still can’t figure out what’s going on, contact Insycle Support for help.

To help troubleshoot, you'll need to provide a few things:

  1. Record type
  2. Record ID
  3. Existing value (provide at least one specific example)
  4. Expected/desired value
  5. Result you are currently getting

Once you have your information together, contact support via the live chat button at the bottom of every page.


Additional Resources

Related Help Articles

Related Blog Posts