How to Standardize Industry Field Values from Outside Data Sources
You rely on the Industry field values for searching, analyzing, lead scoring, and creating reports, but you are finding many variations for the same industry such as, "Technology," "Tech," and "IT." You need an easy way to ensure these are standardized.
With Insycle, there are several ways you can standardize the Industry field data. The Transform Data module is perfect for standardizing data in bulk. If you have data from an external source that you want to make consistent with your CRM while importing, use the Magical Import module.
In this article, we'll walk through the example of finding and correcting Company records with Industry values that should be, "Information Services," but have the incorrect data from a third-party data source.
Map Industry Values with the Transform Data Module
- Filter your data down to the records you'd like to update.
- Map your Industry field values.
- Preview then apply the changes to the CRM records.
In the Transform Data module, select the database and Companies record type in the top menu. Then explore the templates for an existing solution that may be close to what you need.
Filter your database down to the records that you would like to update.
Under Step 1, configure the Filter to look in the Industry field for the values, "Information Technology and Services" OR, "Computer & Network Services." The Industry field is a picklist in your CRM, so select the values you want to find from the dropdown.
When you click Search, Insycle will list matching records in the Record Viewer at the bottom of the page.
If you make changes to the filter, click the Search button again to refresh the results in the Record Viewer.
Under Step 2, give Insycle instructions on what changes to make to the Industry field if the Information Technology and Services or Computer & Network Services values are found.
The Map function lets you locate data within your database and replace one value with another.
1. Select the field that contains the value you are looking for from the Field Name dropdown (Industry).
2. Select the Map Function. Learn more about using the Map functions in the Advanced How-Tos below.
3. In the Existing Text field, enter the value(s) you are looking for.
In this example, we're looking for the same values used in the Step 1 Filter, but they need to be typed here as, "Information Technology and Services|Computer & Network Services." Use the pipe “|” character (located just above the Enter key on your keyboard) to separate the two values.
4. In the New Text field, type the new value to be entered (Information Services).
Preview Changes in the CSV Report
With the filters and functions set up, you can preview the changes in a CSV file. 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 Preview 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.
Under Step 3, click the Review button, this time select Update mode.
On the When tab, you should use Run Now the first time you apply these changes to the CRM.
Save Templates and Setup Automation to Maintain Formatting
After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save your configuration as a template, and set up automation so this mapping runs on a set schedule. By automating with a template, you'll save time and ensure that your fields are consistently updated on an ongoing basis.
Map Industry Values from External Sources Using Magical Import
If you have values in a CSV with an Industry column, you can map and standardize those values during the import process—before the data is updated in your CRM—using the Magical Import module.
Navigate to the Magical Import module and follow Steps 1 and 2 in the Import New Records or Update From CSV article.
Then, under Step 3, select the Industry Column Name from your CSV.
Select the Map Function.
In the Existing Text field, enter the value(s) that are in your CSV that need to be changed before importing. In this example, we'd type, "Information Technology and Services|Computer & Network Services."
In the New Text field, type the new value to be entered (Information Services).
Click the Apply button to apply the changes. This updates the CSV data before importing it into your CRM.
Then continue with the Step 5 instructions to select records and import the data to your CRM.
Tips for Mapping Values
If you aren't sure what variations you have, you should do a little exploratory work to identify what non-standard values exist.
Use the Cleanse Data module to get a summary of all the data variations that are in a specific field in your database. This makes it easy to review the values that are in use and decide what needs to be cleaned up.
Learn more about using the Cleanse Data module.
The Transform Data module includes several Map functions that let you locate data within your database and replace one value with another.
All the content in the field must match the Existing Text value. If it matches, then the entire field is overwritten with the New Text data.
Look for the Existing Text term within the field. It can be any standalone word in the field's contents. If a match is found, the specified text is overwritten with the New Text data, while the rest of the content remains the same.
For example, if an address field contains "Main St." and you're looking for Existing Text "st|st." and the New Text value is "Street," then the address content will be updated to "Main Street."
Map Contains Word
Look for the Existing Text term within the field. It can be any word in the field's contents. If a match is found, the entire field is overwritten with the New Text data.
For example, if an Industry field contains "Tech Sector" and you're looking for the Existing Text "Tech" and the New Text value is "Technology," then the Industry field content will be updated to "Technology."
Map Contains Substring
Look for a specific value anywhere in the string of characters. If found, overwrite only the specified characters, the rest of the string in the field remains the same.
For example, part of the email domain has been misspelled on contacts at a company and you just need to fix the domain without impacting the recipient names. So if an Email field contains, “firstname.lastname@example.org” you can look for the Existing Text “cipherinc” with a New Text value, “cypherinc,” then the email content will be updated to “email@example.com.”
This is often used when adding several steps to one field. When the value in the field doesn't match any of the Existing Text values, you can define a default value to apply. (Shown in image below.)
Learn more about using Map functions.
If you would like to preserve the original data in the field, you can instruct Insycle to copy the Existing Text data to another, custom field.
HubSpot users can schedule their industry update Recipes directly into HubSpot Workflows. Learn more about integrating Insycle with HubSpot Workflows.
Frequently Asked Questions
Yes. Building a separate template for each industry is the best practice. You can always chain templates together and automate them to run together using Recipes.
Related Help Articles
- Copy or Move Values Between Fields
- Module Overview: Transform Data
- Bulk Update Values of Any Field
- Bulk Clear Values From Field
Related Blog Articles