Self maid

A huge piece of the data management puzzle is understanding what you have in your database and ensuring that it is uncluttered, formatted correctly, and standardized. But before you can begin fixing issues, you first have to identify what those inconsistencies are.

For instance, it is difficult to make job titles consistent when you aren't sure what variations you have in your CRM.

Using Insycle, dig into database fields and review them on a record-by-record level to understand the variations in your data and spot opportunities for consolidation and standardization. Then, use Insycle's bulk features to format and make data consistent.

Which Module to Use When

The Cleanse Data module is ideal for field analysis and identifying and fixing inconsistencies in fields that have set values—like job titles, countries, or industries. It gives you a full view of your data, down to the individual record level. Then, you can use the module to update records in bulk.

The Transform Data module is a great choice to automate standardization or standardize fields with no set values, such as phone numbers, addresses, or locations. The Cleanse Data module is often used to help understand what is in your fields, while Transform Data is used for bulk standardization. Additionally, Transform Data allows you to setup automation for fixing data inconsistencies.

Standardizing Data in Bulk with the Cleanse Data Module

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 standardization changes to make.
  6. Apply the changes to the CRM.

 

Step-by-Step Instructions

Step 1: Explore a Field, Its Values, and Properties

Before standardizing and making your data consistent, you must have a complete picture of the field that you are working with. You need to know what variations are currently in a field that before standardizing.

Select the Cleanse Data module from the left navigation, then select your record type (Contacts, companies, deals, etc.) in the top menu. Then, search for the field you would like to explore under Step 1. In this example, we'll explore and standardize contact job titles, so search for the "Job Titles" field. 

The results shown below the search provide some basic insight into the field—what field type it is, whether it is writable, and how many unique values exist in the field.

job title search

You can export all of the field-level statistics for your CRM at any time by clicking on the Export button.

export field-level stats
Step 2: Pick a Field, and Explore the Underlying Values

Filter records down to only those that need changing.

Select a field to explore under Step 2.

job title field

Once selected, click the Search button, and the different values found in this field will populate the Record Viewer at the bottom of the page. 

bucket viewer records

Here, you can explore the values of any given field and identify opportunities to cleanse your data by identifying irrelevant values and standardizing variations.

You can filter these options further in Step 2.

filter records

In this example, we filter the Job Title field down to only those records that include "CEO" or "Chief." We use the pipe "|" character with no spaces to separate values.

Click the Search button and the Record Viewer at the bottom of the page will be updated. Select one of the buckets by checking the checkbox to explore further.

mceclip0.png

This opens up a secondary Record Viewer, allowing you to view individual records within that bucket. This provides additional context for each variation bucket.

record viewer

 

If you'd like to see more information for each of the resulting records, you can alter the fields in this Record Viewer by using the Layout tab in Step 2.

layout
Step 3: Select Records to Modify, Then Specify Changes

Now you can standardize the data to make it consistent. You can select entire buckets of records, individual records, or both, from the Record Viewers at the bottom.

For our purposes, we are going to cleanse inconsistent job titles and standardize everyone with the "Chief Executive Officer" job title down to the abbreviated, "CEO."

mceclip0.png

Then, with the buckets selected, we set our function for updating the records.

job title

You can see that the button has been updated to say "Update 15 Contacts" because we selected 15 records with the "Chief Executive Officer" title.

Apply Changes to the CRM

You will be prompted to confirm when you click the "Update X Contacts" button. When you do, the update will go live in your CRM database. Changes can be reviewed in the Activity Tracker.

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 using Cleanse Data that you should be aware of.

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

Standardizing Data in Bulk with the Transform Data Module

The Transform Data module is ideal for formatting and standardizing fields automatically in bulk or on an automated ongoing basis. Transform Data also provides advanced formatting functions for fields will not have the same value 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 standardization actions. 
  4. Preview the changes. 
  5. Apply the standardization changes to the CRM records.
  6. Set up automation to keep the data consistent.

 

Step-by-Step Instructions

Step 1: Filter to Show Desired Records

Open the Transform Data module and pick a record type, such as Contact or Company, to get started. For this example, we will be using Contacts.

For a quick start, explore the default templates. Insycle includes numerous pre-built templates that can help you format names, phone numbers, and addresses simply.

There might be a template that already solves exactly what you need. You can also use an existing template as a starting point and adjust it to your requirements or build a template from scratch.

In our example, we will be formatting phone numbers for consistency using the "Format Phone E. 164 Standard" template.

select record type

This will pre-load the template. You can add onto templates with custom additions as well, or create a brand new template from scratch.

First, filter your CRM down to the records you want to format. In this example, we will filter down to records that contain data for the fields that we would like to format.

phone number filter

This filter checks to ensure that the phone number field exists (contains data), and uses the "negate regex," function to ensure that the phone number has the correct number of digits to be properly formatted to the E.164 standard. This way, we aren't trying to format fields that don't contain any data or have errant data.

You can add any filter that you would like that is relevant to your use case — lifecycle stages, record creation dates, engagement triggers, etc.

When you click the Search button, a preview of the data will be generated at the bottom of the screen.

phone number records

You can alter the fields that appear in this preview by clicking the "Layout" tab in Step 1.

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.

The "Format Phone E. 164 Standard" template we are using for this example uses a function by the same name.

phone number format e 164 function

You can apply multiple functions to a single field, or even multiple fields. For instance, we could handle formatting the first name within the same template.

In this example, we will add the first name field and format it to the proper case—removing whitespace from the field, and removing terms or salutations (Mr., Mrs., etc.) that don't belong.

phone number and first name formatting
Additional Formatting Examples

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

street address format

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

Preview Changes in CSV Report

Now with the filters and functions set up, you can preview the changes. That way, you can check to ensure your standardization configuration is working as expected before updating your CRM. 

First, click the Review button.

review button

In the Transform Data popup, select the Preview option.

On the Notify tab, add any additional recipients who should receive the CSV (and make sure to hit Enter after each 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) columns to preview changes on a field-by-field basis.

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. 

transform data preview or update

Make your selection and click the Next button.

Then, you are taken to the notification screen. This email is generated and will have a CSV attached showing the changes. You can add colleagues and additional context here.

notify

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 regularly. The first time you apply these changes to the CRM, we suggest you Run Now.

In the Run Now tab, you can control how many records you can run the template for.

run now

 

Setup Automation to Maintain Standardization

On the Automate tab, you can schedule this template to run on an hourly, daily, weekly, or monthly basis.

automate

By automating, you can ensure that your fields are always being consistently rewritten and updated to reflect changes. When the operation runs, you'll receive the CSV in your inbox showing the changes made.

You can view all automations you have scheduled on the Automations page of your dashboard.

Learn more about setting up ongoing data maintenance automation using Recipes or by integrating with Workflows

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

Additional Resources

Related Help Articles

Related Blog Articles