Module Overview: Cleanse Data

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

To explore fields and their properties, navigate to Data Management > Cleanse Data, then select a database and object type from the top menu. Explore the templates for an existing solution that may be close to what you need as a starting point.

Under Step 1. Review Statistics, all fields for the record type are listed. This is not the record data itself, but the metadata about the fields in the database.

The list shows the properties for each field, including the field's name, type, whether it's editable, the number of distinct values, and the number of empty values. Learn more about interpreting these properties in the Understanding Field Properties section of the Advanced How-Tos below.

You can search or browse through the fields to analyze what you have. Click the checkbox to select a field for deeper exploration.

The image above shows Step 1 (Review Statistics) of the Cleanse Data module, displaying the last page of fields, with four fields visible — Photo URL, Reports To ID, Salutation, and Title — showing each field's API name, type, value storage type, writability, unique value count, and empty value count, with the Title field selected.

Under Step 2. Explore Field, if you select a field in Step 1, it will be prepopulated in the Field Name field. Otherwise, select a field to explore from the dropdown. 

The image above shows the Field tab in Step 2 (Explore Field) of the Cleanse Data module, with the Title field selected for exploration.

The field you are exploring won't appear in the preview automatically, so you need to add it to the layout. Click the Layout tab, locate the field, then drag it into the Visible Fields list.

The image above shows the Layout tab in Step 2 (Explore Field) of the Cleanse Data module, with the Title field being dragged into the Visible Fields column from the field picker on the right.

Once a field is selected, the different values found across all records will populate 4. Value Distribution at the bottom of the page. By default, the values are sorted from the most values present to the least. To see the list sorted alphabetically, click the Value heading.

The image above shows Step 4 (Value Distribution) of the Cleanse Data module, displaying unique Title field values sorted alphabetically, with five values visible: Analyst Programmer (11 records), Assistant Manager (4 records), Assistant Media Planner (14 records), Assistant Professor (12 records), Associate Professor (8 records), and Automation Specialist I (3 records).

Click the checkbox to see the relevant records that contain the selected value. You can select multiple values to group records by all selected values. 

This opens a secondary table below the first, where you can review the individual records that contain the selected value(s). 

The image above shows Step 4 (Value Distribution) of the Cleanse Data module, displaying records that use the selected unique Title field values, with six records containing Assistant Professor or Associate Professor values.

If, after analyzing your data, you need to make straightforward A-to-B updates or deletions, you can handle them within the Cleanse Data module. 

To make more complex updates, use one of Insycle's other powerful modules. See the Tips for Cleansing Data below for suggestions.

In 4. Value Distribution, check the box for the values you want to update. Or to make more granular changes, select individual records in the bottom table.

To update the selected values, under Step 3. Cleanse Operation on the Update tab, select the field in the Field Name dropdown, then specify the new values for the selected fields. In this example, all records with a Title of "Assistant Professor" will be updated to "Associate Professor."

Click the Update button and confirm the change.

If you have determined that all of the selected records are junk, you can use the Delete tab to completely remove the selected records from your CRM. 

You can save your settings as a template so that future cleansing tasks will not need to be reconfigured.

Return to the Template menu at the top of the page and click Copy to save your configurations as a new version of the template you started with. Then click the pencil to edit your new template name.

Examining the information for each field can provide clues about data-cleaning opportunities.

The image above shows a portion of the Step 1 (Review Statistics) field list in the Cleanse Data module, displaying three country-related fields — Country Picklist, Country/Region, and IP Country — with their API names, field types, value types, writability, unique value counts, and empty value counts.

Field Label vs. Name

The Field Label is shown in the CRM interface, while the Name is the column header in the database. 
If two fields have 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 distinct values in this field across all records. This is a great place to look for 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 in 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 may indicate it is unused or abandoned and needs cleanup. Or, it could indicate a syncing issue between Insycle and your CRM.

To refresh the data in Insycle, navigate to Settings > Sync Status, and next to the account name, click the Sync changes from last day button (lightning bolt icon).

If you see data in your CRM but the Empty Values number is high, contact Insycle support.

In the Cleanse Data module, when you've identified a field with variations that need management, you can export the values as a Blueprint CSV to use with the Data Logic module. Blueprints scale to thousands of values, enforce mapping across all records continuously, and update instantly with a simple CSV edit.

1. Select the field under Step 2. Explore Field.
2. Click the Layout tab, then drag it into the field in the Visible Fields list.

3. In Step 4. Value Distribution, change the Rows per page so that all the values are visible at once (without needing to click through pages). For example, if the field has 55 different values, change Rows per page to 100.

   

4. Click the blue download icon next to the 4. Value Distribution heading to export the CSV.
5. Insycle automatically adds a Mapped column to the exported CSV. Use this column to map each incorrect value to its correct counterpart, upload the CSV as a Blueprint, and configure Data Logic to automatically standardize these values ongoing.

You can export all field information from your CRM by clicking the Export button in Step 1. Review Statistics of the Cleanse Data module. This will include all the field metadata, not the records. 

If you'd like to see more information for each resulting record, you can alter the fields in the record preview by using the Layout tab in 2. Explore Field.

The image above shows the Layout tab in Step 2 (Explore Field) of the Cleanse Data module, with the Title field being dragged into the Visible Fields column from the field picker on the right.

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.

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

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, and next to the account name, click the Sync changes from last day button (lightning bolt icon). Alternatively, you could log out of Insycle and then log back in.

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

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

HubSpot limits 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.

If you have set up the Insycle Run ID property in your CRM, every Insycle operation that updates or creates a record will update the Run ID in the record. This can be used to look up process reports in the Activity Tracker or to get help from support.

When using an Insycle Recipe that includes templates for more than one object type, such as companies and contacts, the same Run ID will appear in both CRM records.

Learn how to set up the Insycle Run ID custom field for each object type in your CRM.