How to Analyze CRM Field Data and Clean It Up in Bulk

You know you need to do some data cleanup in your CRM, but you're having trouble getting an overall picture of what's currently there. To decide how the data in a field should be formatted, you must understand what issues you currently have in your database. 

The Cleanse Data module makes it easy to drill down into specific fields in your database and review them on a record-by-record level. With Cleanse Data, you'll be able to understand the variations and clutter in your database and spot opportunities for consolidation and standardization. Then, you can update records in bulk.

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 and specify what changes to make.
  5. Apply the changes to the CRM.

 

Step-by-Step Instructions

Step 1: Explore Fields and Their Properties

Before standardizing and making your data consistent, you need to know what is currently in your database. 

In the Cleanse Data module, select a record type from the top menu. 

The Field Viewer under Step 1 shows all fields for the record type. This is not the record data itself, but the meta-information about the fields in the database. 

The Viewer shows the properties for each field, including the field's name, type, if it's editable, how many different values there are, and how many are empty. Learn more about interpreting these properties in the Advanced How-Tos below.

mceclip2.png

To narrow down the list of fields, use the Search.

In the example above, the search is used to show only fields that include "job" in the Field Label. Looking at the two fields, it appears that the Job function field was erroneously added since all the record values are empty. This field might be a good candidate for removal.

The Job Title field has 21 unique values that you can explore further.

Step 2: Explore the Record Values for the Field

Under Step 2, select a field to explore from the Field Name dropdown. (This does the same thing as checking the box by the field name in Step 1.)

mceclip1.png

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

Explore the different field values to identify inconsistent or irrelevant values and identify what needs cleanup. Use the checkbox to expand a value set. 

mceclip4.png

This opens up a secondary Viewer below the first, where you can review the individual records that have the selected value.

mceclip6.png

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.

mceclip5.png

Click the Search button and the Record Viewer at the bottom of the page will be updated. 

Step 3: Select Records to Modify, Specify Changes, and Update 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.

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

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

If you are satisfied with the updated results in your CRM, you can save the configuration for future use in a Template

Tips for Cleansing Data

Cleanse Data is a great tool to use if you want to make bulk changes but don't have a clear idea of what values already exist. First, use Cleanse Data to discover and note the inconsistent variations. Then you can use the Transform Data module to change all the variations into a consistent value in a single task.

Advanced How-Tos

Understanding Field Properties

Looking at the information about each field can provide clues on data cleansing opportunities.

mceclip1.png

Field Label vs. Name

The Field Label is shown in the CRM interface while the Name is the column header in the database. 
If there are two fields with a similar Field Label, such as "Phone" versus "Phone Number," looking at the underlying field Name may reveal different information that clarifies the actual purpose. You may want to update one of the Field Labels to reflect the difference.

Type, Value, and Writable

  • Type – Field type, such as a picklist, number, text string, date, timestamp, true/false (boolean), etc.
  • Value – Describes the type of data stored in the field such as text, number, date, true/false, etc.
  • Writable – In the Insycle app, checked = True, indicating the field can be edited. 

Unique Values

This is the count of different values that appear in this field across all records. This is a great place to look and see if there is data worth exploring. You could answer a question such as, "How many different job titles do we have?" It can also indicate a problem in fields where only a few values should be used, such as Industry, or Product. This is especially relevant on fields that should be limited to a picklist, or Yes/No values. 

Empty Values 

This is the number of records that don't have any value in the field.

Any field with a high number of Empty Values could indicate the field is unused or abandoned and needs cleanup. Or, it could indicate a syncing issue between Insycle and your CRM. If you see data in your CRM but the Empty Values number is high, contact Insycle support.

Export All Field Properties

You can export all of the field meta information for the selected record type from your CRM by clicking on the Export button in Step 1. This will include all the field information, not the records. 

mceclip10.png

Viewing More Columns in the Record Viewer

If you'd like 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.

mceclip1.png

Troubleshooting

Records aren't showing up?

There are a few things to look into:

  1. Under Step 2 in the Filter tab, make sure that you have clicked the Search button.
  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.

mceclip0.png

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 request a manual sync for the field.

Seeing high numbers of Empty Values for a field you know is regularly used?

If you know a field in your CRM is used regularly but the Empty Values column for the field is high, this could indicate a syncing issue between Insycle and your CRM. Contact Insycle support for help.

cleanse-data-empty-values.png

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

Frequently Asked Questions

What does a Unique Value or Existing Value of -1 mean?

A "-1" shows up when values for a field are currently not stored in Insycle. It indicates the CRM field is not syncing with Insycle. Note the Value column also shows, "not stored."

Fields with Empty Values = -1

CRMs limit the number of fields that can be synced between Insycle and your database, so at a point, some will need to be excluded. If you discover a field that you need to sync but isn't, contact Insycle support for help. Fields can be prioritized to ensure the necessary fields are syncing.

Additional Resources

Related Help Articles

Related Blog Articles