How to Apply Formatting in Bulk So Records Have Consistent Values
Your team needs to segment CRM data to send personalized messaging and generate effective reports but is having trouble surfacing relevant records because of data inconsistencies.
With Insycle, there are several ways you can standardize field data in your CRM. To analyze the variations in your database and make quick bulk changes, use the Cleanse Data module. The Transform Data module is perfect for standardizing data in bulk, one-time, or automatically on an ongoing basis. If you have data from an external source that you want to make consistent with your CRM while importing, use the Magical Import module.
Standardize Data with the Cleanse Data Module
The Cleanse Data module allows you to explore your data, identify opportunities for standardization, and bulk update records in your CRM.
- 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 what changes to make.
- Apply the changes to the CRM.
Navigate to the Cleanse 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, 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.
When looking to fix specific values, use the filter to work with a focused 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?
First, make sure that you have clicked the Search button.
Second, double-check 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 minor 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.
For a complete guide to troubleshooting issues with Insycle, please refer to our article on Troubleshooting Issues.
Standardize Data in Bulk with the Transform Data Module
With the Transform Data module, you can standardize job titles, industries, locations, and any free-text field in your CRM in bulk.
- Set filter criteria.
- Select records to update.
- Pick fields to modify and set standardization rules.
- Preview the changes.
- Apply the standardization changes to the CRM records.
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 running the process on all records in your database.
In this example, the filter will surface records where the State/Region starts with the value, "NY."
Click the Search button and scroll down to the Record Viewer at the bottom of the screen. You will see all records that match your filter.
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 State/Region field uses the pre-built Standardize US States, CA provinces function. This standardizes states with their full name like this:
- NY→ New York
- New york → New York
In the next example, the Job Title field uses the Map function. This will look for existing values of "vp sales" or "vp of sales" or "vice president sales" or "vice president of sales," and replace those values with, "VP of Sales."
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 works as expected before those changes are pushed to your live database.
Under Step 3, click the Review button and select the Preview option.
On the Notify tab, you can select recipients for the email report and add additional context for recipients. (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.
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 Notify tab add any additional recipients, then click Next.
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 set schedule. The first time you apply these changes to the CRM, we suggest you 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.
By automating with a template, you'll ensure that your fields are consistently formatted on an ongoing basis.
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 before 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.
Standardize Data from a CSV with the Magical Import Module
With the Magical Import module, you can import a CSV file, set standardization rules for fields, then apply those changes while importing into your CRM.
- Upload the CSV file.
- Map the CSV columns to the CRM fields and specify how to import the values.
- Select the unique identifier field, and choose an action to take.
- Set up the formatting rules to standardize the data.
- Import your formatted CSV data.
Select the CSV File for Import
Select the Magical Import module from the left navigation, then select the database and the record type in the top menu.
Click the Choose button and select the CSV file that you would like to import. Uploading a file into Insycle does not import it to the CRM right away, instead, you can prepare the data in a variety of ways before actually importing it to the CRM.
Map CSV Columns to CRM Fields
After uploading your CSV data, Insycle analyzes your file and compares the columns in the file to fields in your CRM database.
A column is included for each field in the CSV, with the CSV Column header appearing as the top row. Then, Insycle automatically detects the matching CRM fields and maps them for you. The second row shows the mapped CRM field names.
If your CSV contains fields that are not matched to your CRM, a red exclamation point icon will show.
These "Not Mapped" fields need to be mapped manually by clicking the dropdown and finding the appropriate CRM field. If they remain unmapped, they will not be imported. If you choose to leave these unmapped you can remove them by hovering over the column name and clicking the “X”.
Specify How to Import Values for Each Column
With all relevant fields mapped, tell Insycle how to use the data on a field-by-field basis by selecting the Import Mode, which is the third row in the table.
Four Import Mode Options
- Update – Update CRM records with all non-empty field data contained in the CSV.
- Fill – Import CSV values only when the corresponding field in your CRM is empty.
- Overwrite – Insycle will replace any existing data in your CRM with the data in the CSV—even if the CSV field is empty.
- Append – Import values from the CSV and append (add them to) the existing data already in your CRM. A typical example of how “Append” is used would be in a “Notes” field, or to add to existing picklist data without including historical selections in your CSV.
You can also preview and edit the import data directly in Insycle by clicking the pencil icon next to any field when you hover over it.
If a record has errors, the red exclamation point notification will be shown on the left side of the record. When you hover over the red exclamation mark, an explanation of the error will be displayed.
Records that have errors in them will not be imported. Other fields will be imported as normal.
Insycle uses Identity Fields to compare your CSV to your CRM data. Identity Fields must be "unique identifiers." This is data that would only belong to a single contact, such as email addresses, phone numbers, home addresses, or ID numbers.
For example, no other contact in the database would have Maria Hernandez's email address, phone number, or ID number listed on their contact record. If a contact record contains a match for Maria Hernandez's data in one of those fields, we know that is the contact record for Maria Hernandez.
Under Step 2 on the Import tab, select the Identity Field from the dropdown.
Note that when using an email field, Insycle will automatically cross-reference any additional email fields in the records for a match. If using a domain field, Insycle will check additional domain fields.
Select one of the Records Modes to tell Insycle how the imported data should be handled during the import process.
- Update existing and create net new – If unable to find a corresponding record, a new record will be created in your CRM.
- Only update existing – If a corresponding record is found, it will be updated with the data from your CSV import. Records that are not matched with an existing CRM record will not be imported.
- Only create net new – Only records that can not be matched with an existing record in your database will be imported. Records that already exist in your CRM will not be updated.
In this step, you set rules to make the field values consistent. This ensures that your data is standardized while it is being imported into your CRM database.
Go to the Functions tab of Step 3.
Select the Column Name of the CSV field, then choose a Function, telling Insycle how the data should be formatted when it is imported. When finished, click Apply.
The above example will standardize two fields:
- US States or Canadian Provinces
The Industry field uses the Map function. The Existing Text field will look for the values "tech" or "Tech”. The New Text field specifies that the text "Technology & Software" should replace values that match the existing text.
Expected results of Existing Text → New Text
- tech|Tech → Technology & Software
- vp sales|vp of sales → VP Sales
- NY→ New York
The State field uses the pre-built Standardize US States, CA Provinces function. This standardizes states with their full name like this:
NY→ New York
New york → New York
Explore the extensive list of options in the Function dropdown to find formatting options for any field in your CSV.
For additional control over the data you import, use the options under Step 4.
- List – Add your imported data to an existing list. Only available for HubSpot.
- Bulk Update – Update a specific field on import. For example, you could update all imported records to have the Job Title of “Founder,” regardless of the data in the CSV file.
- Associate – Associate contacts to companies, deals, and custom objects automatically when importing data. Select a CSV field and the CRM field you’d like to match it with to create associations. When using Salesforce this is known as “Link.”
- Date Format – Format dates contained within your CSV file to your preferred standard.
By default, Insycle will attempt to import all of your formatted CSV data into your database. If needed, you can select individual records for import in the Record Viewer.
Note that changes are applied to your CRM immediately, there is no preview step. To import, click the Import X Contacts button under Step 5.
After the import runs, the Import Result breaks down the import information—how many records you tried to import and how many succeeded, failed, were updated, deleted, or unmodified. Click the Run ID to open a CSV record of the import.
A record of these changes can be found anytime in the Activity Tracker.
After you've seen the results in the CRM and you are satisfied with how the operation runs, you can save all of the configurations as a template, to reproduce the same formatting operation in the future.
In Magical Import, the Filter tab of Step 3 allows you to filter data out of your import based on fields and conditions you set.
In the example above, the CSV data for import will be filtered down to only contacts with a job title containing the word “Founder.” Because the “contains” Condition was used, this will include records that have job titles such as “CEO and Founder,” “Co-Founder,” etc. The term “Founder” must be present anywhere in the field.
Some records aren't being standardized?
First, make sure you click the Apply button in Step 3, after setting up your standardization functions. If you do not click Apply, the changes will not be made. Standardization changes in Magical Import must be made before importing the data.
When using the Map function in the Magical Import, you must account for every variation in your CSV data. Small differences, such as extra whitespace, must be accounted for in the Existing Text field.
To account for these variations, you can:
- fix them in the CSV
- include them in the Existing Text field
- use other Insycle functions to do things like clearing non-letters, removing whitespace, or formatting the field in bulk before importing.
For a guide on general troubleshooting, please refer to our article on Troubleshooting Issues.
Frequently Asked Questions
- Format proper case person
- Format proper case company
- Format lowercase
- Format uppercase
- Format phone USA (multiple formats)
- Format phone E. 164 Standard
- Map terms
- Remove terms
- Remove leading/trailing whitespace
- Remove successive whitespace
- Remove symbols
- Remove non-letters
- Remove non-digits
- Remove letters
- Remove digits
- Remove any and all whitespaces
- Standardize US States
- Standardize Country Name
- Standardize Country Code
- Suffix Remove/Add
Related Help Articles
- Cleanse Data
- Explore Database Fields and Values
- Map Values From One Field to Another
- Convert Field Type From Free-Text to Picklist
- Format Names, Phone Numbers, Addresses
Related Blog Articles