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
- Choose a field to explore.
- Analyze the variations and decide how to standardize.
- Filter records down to only those that need changing.
- Select records to modify.
- Specify the changes to make.
- Apply the changes to the CRM.
Step-by-Step Instructions
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.
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 of the resulting records, you can alter the fields in the Record Viewer by using the Layout tab in Step 2.
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.
When you click Search, the Record Viewer table at the bottom of the page will be updated.
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:
- Make sure there isn't anything in the filter that you didn't intend to be. This often happens if you started with an existing template.
- 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.
- Make sure that you have clicked the Search button.
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.
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
- Set filter criteria.
- Select records to update.
- Pick fields to modify and set your standardizing rules.
- Preview the changes.
- Apply the standardization changes to the CRM records.
Step-by-Step Instructions
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.
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.
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.
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.
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.
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.
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.
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.
Here is an example of a function you might build to make street addresses consistent.
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).
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.
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.
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
- Standardize Job Titles, Industries, Location
- Copy or Move Values Between Fields
- Bulk Update Values of Any Field
- Bulk Clear Values From Field
- How to Cleanse Data
- Map Values From One Field to Another
- Data Health Assessment
Related Blog Articles