Have you ever tried to utilize your CRM data in some way, only to realize that it is improperly formatted, messy, or contains errors that reflect poorly on your brand?
We’ve all been there.
It could be something simple. Maybe you want to use first names in your marketing personalization. The problem? Many of your first names don’t have proper capitalization. Some are all lowercase. Some are all uppercase. Others have random capitalization in the middle of the name.
Or maybe the First Name field contains the middle name or last name, or for some reason has parenthesis or phone numbers all lumped together into one field and you need to find a way to separate things — hopefully not manually, one-by-one.
Or perhaps you want to segment customers based on their industry to deliver targeted messages during the sales process. Except some of your companies have their industry listed as “Winter Sports” while others are listed as “Ski Retail.”
There are many reasons why your data may be in an unusable state for marketing and sales activities. You can almost use it, but without fixing those issues you cannot personalize your message without negatively impacting your brand reputation.
The data in your CRM needs to be properly formatted and standardized to be usable at all points throughout the customer lifecycle. Unstandardized data can lead to broken software integrations, poor segmenting and filtering, unreliable reporting, and ineffective marketing personalization among other issues.
But preparing your data is a long and arduous process for most companies. 91% of C-level executives believe that preparing data for insight ultimately costs their business in terms of resources and efficiency.
These types of issues are caused by inaccurate form submissions, lists being imported, and a web of marketing technology integrations and data providers that are unique to your organization.
In this article, we’ll break down why transforming and formatting data is so difficult using standard processes and take a deep-dive into all of the different ways that you can use the Transform Data tool to alter your data in bulk and on an automated ongoing basis.
Table of Contents
- Why Formatting CRM Data is So Difficult Using Standard Processes
- How Insycle Makes Transforming, Formatting, and Standardizing CRM Data Easy
- Formatting Suffixes & Prefixes (Replace/Remove/Add)
- Formatting Names, Proper Case, and Capitalization
- Formatting Phone Numbers
- Standardizing Addresses, States, and Zip Codes
- Removing Common Data from a Field
- Mapping and Formatting Data Flexibly
- Copying, Moving, and Concatenating Values
- Date Arithmetics
- Transforming Data to Abbreviations or Initials
- Combining Functions
- Automating Ongoing Formatting and Standardization
- Case Study: Putting It All Together
- Insycle — The Swiss Army Knife of CRM Data
Why Formatting CRM Data is So Difficult Using Standard Processes
If you’ve ever tried to format all data across all records for a field in your CRM, you know what a pain it can be. It’s time-consuming, often confusing, and sometimes results in mistakes that you then have to go back and fix.
Let’s start with a simple example.
Let’s say you wanted to fix the capitalization of first names across all of your contact records in your CRM. There are many reasons why you would want to do this, chiefly for marketing personalization.
How is this done? What is the current process for this?
Well, most companies would start by exporting all of their data so that they can work on it in Excel. This can be a problem in itself, especially in larger databases. Microsoft Excel and Google Sheets are only designed to handle so many records.
Larger databases that are exported and edited in Excel run a high-risk of suffering a failure or freezing the program without being able to save changes to your data. There is nothing worse than spending hours on a task and losing all of your hard work to the app crashing.
Then, with the data successfully exported, you can begin the process of using complicated Excel functions to format and standardize your data.
Now, ensuring that first names are properly capitalized should be relatively straight-forward, you would think. But as you sift through your data, you realize that there are all sorts of problems you didn’t expect.
Some names don’t have any capitalization. Others are completely capitalized. Some have random capitalization in the middle of the word. Others include special characters (!?^*) or numbers.
Fixing each of these problems requires using a different Excel function — or even several Excel functions in some advanced cases.
With the functions run, you then begin the process of sifting through your data to make sure that your changes are accurate. To do this well, you’ll have to do it, at least in part, by hand. This will take hours for big databases and is mundane and monotonous work.
And if you find an error? Maybe one of your functions didn’t cause the changes you thought that it would? Back to the drawing board. Hopefully, you can revert those changes with another Excel function. If not, you might have to start from scratch with your original export. Ouch.
Once you have checked the data to ensure the changes went as intended, you can then re-import the data back into the system. Here you just have to hope that the import doesn’t encounter any unexpected errors and that you did a good job of checking the accuracy of the data. If you re-import your data with errors in it, you’ll have to start the process all over again with a fresh export.
This process is required for updating your customer data, unless you want to do it all by hand. But there are a lot of problems. Beyond the technical issues, those responsible for updating the data are forced to deal with a lot of anxiety and uncertainty. Customer data is so important. Most people in their company need to access it in some way to do their jobs. Getting it right is critical.
Alternatively, you can call on developers for help. They can use APIs to dig through data and bulk update specific fields. That’s one solution to the problem.
Except a developer’s time is valuable. Your situation is constantly evolving as you enact new standards, integrate new software, and find new ways of using customer data. You can’t reasonably expect to use a developer anytime that you would like to alter your customer data. They might be keen to help a couple of times but are going to quickly tire of being pulled away from their work over and over again for maintenance and evolving requirements.
Luckily, there is a better way.
How Insycle Makes Transforming, Formatting, and Standardizing CRM Data Easy
The ‘Transform Data’ tool includes dozens of pre-built functions that allow you to solve common customer data issues in a variety of ways while customizing their application to your exact data needs.
You might be familiar with data preparation and ETL (Extract, Transform, Load), the traditional process of copying, formatting, and standardizing data before it hits your data warehouse — Insycle essentially allows you to focus on the “Transform” part of that process and eliminates the dreadful Extract and Load.
If you’re a business person from operations, sales, marketing, or customer support team, and you know what you’d like changed in your data, you can use Insycle’s visual user interface to configure rules for fixing your data independently, without requiring coding knowledge or assistance from developers.
It lets you run the rules over the data in "Preview mode" to ensure that they're working as expected. You'll receive a CSV report with the values before and after for comparison and as a backup if needed for future reference.
You can even set Insycle to update your fields using your set rules automatically on an ongoing basis. These are time-consuming processes that you can completely eliminate from your task list.
Our mission is to help you get your CRM database in the best shape possible to improve your sales and marketing efforts. You do not need to worry about how many updates are needed in order to get you there because all plans include unlimited number of operations and updates.
Let’s break down some of the different functions that are available in Insycle’s ‘Transform Data’ tool and provide some simple examples of how they might be used.
Formatting Suffixes & Prefixes (Replace/Remove/Add)
Have you ever wished that you could remove “Inc.” and “LLC” from company names, or standardize the website URL field by removing http://, https://, or www? These suffix-related functions are the perfect tool for doing so.
Let’s take a look at some examples of how suffix replace, remove, and add could be used to transform and standardize your CRM data.
Suffix Remove
A simple way to remove data at the end of your field. Here’s an example of using Suffix remove to remove company incorporation acronyms.
- Acme, Inc. ⇒ Acme
- Acme, LLC. ⇒ Acme
- Acme Incorporated ⇒ Acme
You could also use this function to remove suffixes that appear in “Last Name” fields.
- Jimmy Johnson Jr. ⇒ Jimmy Johnson
- Jimmy Johnson Esq. ⇒ Jimmy Johnson
- Jimmy Johnson III ⇒ Jimmy Johnson
Suffix Replace
Suffix replace allows you to replace the suffix at the end of your data in any field. You could use it to replace suffixes on names, or fix free email account misspellings.
- jane@gmil.com ⇒ jane@gmail.com
- jane@yaho.com ⇒ jane@yahoo.com
- Jimmy Johnson 2nd ⇒ Jimmy Johnson II
Just like Suffixes, Insycle also offers similar functionality for prefixes.
Prefix Add
Prefix Add allows you to add new data to the beginning of a field. Below are examples of standardizing website URLs or making changes to names in your CRM data.
Prefix Remove
Prefix Remove removes data from the beginning of the field. Below is another example of standardizing website URLs.
- www.acme.com ⇒ acme.com
- Mrs. Jane Doe ⇒ Jane Doe
- President George Washington ⇒ George Washington
Formatting Names, Proper Case, and Capitalization
Tired of sending marketing emails without customer first names capitalized? Insycle’s case formatting functions are here to help.
The “Format proper case person” function allows you to properly format names that are using any case configuration. Whether your current data includes no capitals, fully capitalized, or random capitalization — these functions ensure that your contact name data is formatted correctly.
Format Proper Case Person
Use “Format proper case person” to appropriately format people’s names. Unlike Excel PROPER function, this function goes beyond simply capitalizing the first letter and considers relevant naming conventions to ensure your capitalization is correct.
- jane doe ⇒ Jane Doe
- CoNan o’briEn ⇒ Conan O’Brien
- EMILY EWING ⇒ Emily Ewing
Format Proper Case Company
This function works in a similar way, except that it’s optimized for company names and acronyms. Using this function you can automatically format company names in your database using standard conventions.
- AAA INSURANCE => AAA Insurance
- microsoft incorporated ⇒ Microsoft Incorporated
Format Lowercase
Take all of the data in a field and make it all lowercase.
- New York City ⇒ new york city
- Real Estate Technology ⇒ real estate technology
Format Uppercase
Take all of the data in a field and make it uppercase.
- New York City ⇒ NEW YORK CITY
- washington ⇒ WASHINGTON
- wa ⇒ WA
Formatting Phone Numbers
Formatting phone numbers is critical for making your data sortable, powering integrations with software solutions (like auto-dialers for sales), in marketing personalization, and generally providing a good experience to your employees who use your data. Adopting a phone number standard also helps you to limit duplicate contact and company records.
Insycle offers a number of functions for formatting phone numbers in different ways. While “E. 164” is the international standard, you can choose from our list of custom-built functions to ensure your contact and company phone numbers follow the right standard for your company.
USA Standard Format +1xxxxxxxxxx
- 202-212-4589 ⇒ +12022124589
E.164 Standard +xxxxxxxxxx
- +(44) 161-868.8000 ⇒ +441618688000
Custom International Phone Format
- 442012345678 with parameters 2,2,4 and - ==> 44-20-1234-5678
- The first parameter is a list of grouping positions, for example: 2,2,4 means after 2 digits, again after 2 digits, and again after 4 digits.
- The second parameter is the separator character: dash, dot, space or any other.
Format Phone USA +1 (xxx) xxx-xxxx
- 202-212-4589 ⇒ +1 (202) 212-4589
Format Phone USA xxx-xxx-xxxx
- 2022124589 ⇒ 202-212-4589
Format Phone USA (xxx) xxx-xxxx
- 2022124589 ⇒ (202) 212-4589
Using these functions, you can format contact and company phone numbers in the way that makes the most sense for your company.
You can also use a combination of other functions to create custom phone number formatting templates.
For instance, binomialSUR, a marketing agency in Uruguay, had a very specific phone number formatting problem. They wanted to reformat and rewrite phone numbers to include their county’s (Uruguay) dialing code, +598. Without injecting the dialing code, their sales reps would not be able to use click-to-dial software.
To do this, they used the “Prefix Replace” function to replace the existing calling code and place the country’s dialing code in front of it.
This template helped them to re-write and format their phone numbers, in bulk, so that their sales team could use their CRM phone number data with their existing auto-dialing software.
Standardizing Addresses, States, and Zip Codes
Are contact and company addresses in your CRM messy and unorganized? You need addresses to use a consistent format for filtering data, delivering marketing materials through direct mail, and for marketing personalization purposes.
Insycle provides a number of functions that can help you to format different components of addresses that are often separated into different fields.
Standardize Street Address
Use the "Map terms" function to standardize the street address with a consistent convention. In this example, when the "Street Address" field contains "Street" or "St." as a term (standalone word), it is replaced with "St".
The "Existing Text" parameter is case-insensitive and would match Street, STREET, and any other case variation.
Standardize State
This function formats state names to their full name (not abbreviated) using standard conventions. This function will detect common ways to represent a state in data.
- new york ⇒ New York
- N.y. ⇒ New York
Standardize State Abbrev.
Format US state data to its abbreviated form.
- new york ⇒ NY
- N.y. ⇒ NY
Standardize Country Name (to Code 2)
Format country names to their two-letter, Code 2 standard. Alpha Code 2 and Alpha Code 3 are ISO 3166 standards for country codes, as designed by the International Organization for Standardization.
- United States of America ⇒ US
- Canada ⇒ CA
Standardize Country Name (to Code 3)
Format country names to their three-letter, Code 3 standard.
- United States of America ⇒ USA
- Canada ⇒ CAN
Standardize Country Name Code 2 to Code 3
Format data from two-letter (Code 2) to three-letter (Code 3).
- US ⇒ USA
- CA ⇒ CAN
Standardize Country Name Code 3 to Code 2
Format data from two-letter (Code 3) to three-letter (Code 2).
- USA ⇒ US
- CAN ⇒ CA
Standardize Country Name Code 3 to Name
Format data from two-letter (Code 3) to its standard full name.
- USA ⇒ United States of America
- CAN ⇒ Canada
Removing Common Data from a Field
Sometimes you’ll have data included in a field where it just doesn’t belong. A good example might be the word “Company” in your company names field. Insycle delivers multiple functions that make it easy to remove unwanted additions to a field and standardize across all fields in your database.
Removing Terms
With the “remove terms” function, you can remove any term contained within a field by setting the words or phrases you would like to remove. A term is a standalone word surrounded by space. In the first example, we remove “The” and “Company.” In the second example, we use the Removing Terms function to remove “City.”
- The Microsoft Company ⇒ Microsoft
- New York City ⇒ New York
Remove Leading or Trailing Whitespace
The “remove leading or trailing whitespace” function removes any excess spaces from a field, both in front or behind the data. There are also separate functions for only removing leading whitespace, or only removing trailing whitespace.
- (space)New York(space) ⇒ New York
Remove Successive Whitespace
The “remove successive whitespace” function makes it easy to remove back-to-back (or 3+) consecutive spaces in a data field. This is great for fixing odd spacing issues and ensuring that your software integrations can effectively use your data.
- New York City ⇒ New York City
- Emily Ewing ⇒ Emily Ewing
- The Juice Company ⇒ The Juice Company
Remove All Whitespace
For fields that you are certain should contain no spaces, you can remove all whitespace. Zip codes would make a good example.
- 98 101 ⇒ 98101
- 425 321 6543 ⇒ 4253216543
- N. Y. ⇒ N.Y.
Remove Symbols
If you’ve ever dug through your data, you know how common it is to find strange characters and symbols where they don’t belong. Often, we don’t want to collect this information. This function provides an easy way to remove non-letter and non-digit characters from any field in your CRM.
- New York (City) 2020! ⇒ New York City 2020
- James Johnson! ⇒ James Johnson
Remove Non-Letters
Similarly, we have the “remove non-letters” function, which removes symbols and numbers from a data field.
- New York (City) 2020! ⇒ New York City
- Jane Doe (Born 1988!) ⇒ Jane Doe
Remove Non-Digits
On the other side of the coin, we could use the “remove non-digits” function to remove all numbers from a data field.
- New York (City) 2020! ⇒ 2020
- Jane Doe (Born 1988!) ⇒ 1988
- October 1988 ⇒ 1988
Or, simply remove just the letters or digits included in a field:
Remove Letters
- New York City 2020!!! ⇒ 2020!!!
- Seattle Washington 98101 ⇒ 98101
- 98101q ⇒ 98101
Remove Digits
- New York City 2020!!! ⇒ New York City !!!
- Seattle Washington 98101 ⇒ Seattle Washington
- 98101q ⇒ q
Find and Replace any occurrence
Unlike the “Remove terms” function, which identifies and transforms words, this function scans text and replaces any matching data with the new data that you provide. It can be used to transform data inside of a word or phrase, as well as entire words and phrases themselves.
- “ACME Deal 2020/Q1” with parameters “Q1” and “Q2” ⇒ “ACME Deal 2020/Q2”
Mapping and Formatting Data Flexibly
The “Map” function allows you to standardize free text values to a consistent form that you can later use in segmentation, campaigns, lead scoring and many other use cases. It lets you locate data within your database and replace one value with another. This is a flexible function that makes it easy to format and standardize data within your CRM, even when there are no pre-built functions or templates for dealing with that field directly.
Map
The "Map" function would compare the existing value and if it matches the "Existing Text" value, it is replaced by the value in the "New Text".
- Chief Executive Officer ⇒ CEO
- Health And Wellness ⇒ Health
The "Existing Text" parameter is case-insensitive and would match any case variation.
Map Contains Word
Sometimes, when you're looking to map and standardize free text fields into picklists, there are slight variations in the values, perhaps it's an additional adjective. In those cases you can use the "Map contains word" function.
Let's say that you'd like to map any value that contains "color" or "shade" into the value "Color". For example "light color" or "DARK SHADE" should both map to "Color". In these situations you can use the "Map contains word" function with the parameters "color|shade" and "Color".
Map Contains Substring
In some situations, you may need to match based on a partial match. For example, to map the country field based on the website URL. You can match any URL that contains any of .co.uk OR .ac.uk OR .gov.uk to UK, and copy that value to the Country field.
Map Terms
Unlike the "Map" function that compares and replaces an entire value, the "Map Terms" function compares any terms (standalone word) that is contained within a value and replaces it, see the Standardize Street Address example above.
Map Numeric Range
You can apply a label to a number that falls within a range. For example, if the value in field X is between 100-200 apply the label "Alpha" to field Y. If the value is between "200-300", apply the label "Beta", etc.
You can use it to map "Number of Employees", "Annual Revenues", or "MRR" field from exact value to a labeled range for reporting.
Map with Multiple Variations
You can map multiple values, using the “|” symbol between each variation to account for all potential possibilities.
- "Health & Wellness|Pharmaceutical|Supplements" ==> Health
- “C.E.O.|C.E.O|Chief Executive Officer|Chief Executive” ⇒ CEO
The map function is a powerful tool for accounting for multiple popular variations in fields. This is a common problem among industries and job titles.
Map Default
When the value doesn't match any of the mapped values, you can define a default value to apply, see above in the Map Contains Substring example.
Copy, Move, and Concatenate Values
Copy Function
The “copy” function will make a direct copy of the data in a chosen field, and transfer it to another field.
You might transfer “Company Headquarters” to “Company Address.”
This can be helpful for copying data so that you can work on it safely in a new field, or integrate software that requires data in fields with specific names or formats See the case study below for an example of using “copy” together with other functions to format names.
Move Function
This move function works similarly to “copy” in that it copies data from one field to another targeted field. The key difference here is that the data in the original field is deleted once the process has been run.
So using “move,” if you moved data from “Company Headquarters” to “Company Address,” the data would now be contained in the “Company Address” field but the “Company Headquarters” field would be empty (the field itself is not deleted).
The “move” function is helpful when you want to change field titles or data types (like free text to picklist) without creating redundant data in your database.
If you have multiple redundant fields, you can consolidate them into one using the “move” function.
Concatenate Function
The "concatenate" function lets you combine values from multiple fields into one. It can be used to standardize naming conventions, or enable quick search and lookup using just one field. You can specify the delimiter for each part of the concatenation, it can be a symbol or text.
In this example, we combine the values of the City, Country, and Postal Code fields, add a a prefix, and copy the combined values into the My Custom Property field.
And here is the result of the concatenation. Like any other function you can first run it in Preview mode and review the CSV report to make sure it works as expected prior to actually updating the values.
Date Arithmetics
Do you need to calculate the number of days between two date fields? Or calculate the number of days from a certain date to today? Use the date calculation functions and copy the result into a different field.
Transforming Data to Abbreviations or Initials
You can also use the ‘Transform Data’ tool with the “initials” function to create abbreviations and initials from your existing data. This is most often used with names but can be used in other ways as well.
Initials
- Jane Doe ⇒ JD
- New York City ⇒ NYC
- Chief Executive Officer ⇒ CEO
This provides a simple way to abbreviate data and can be combined with functions like “move” to transform the data and move it to a new field, such as from the “City” field to “City (Abbreviated)” field.
Extracting Data From a Field
Sometimes your fields will contain data that is valuable but does not belong in that field. Or, maybe you just want to remove a piece of the stored data for other uses.
Let’s take a look at some of the data extraction functions that you can use to pull data from a field in your CRM.
Extract Domain from Email
Pulls the domain from a contact or company email. Useful for populating URL or domain fields.
- john@acme.com ⇒ acme.com
- jane@microsoft.com ⇒ microsoft.com
Extract Domain from URL
Or maybe you just want the company domain name, without all of the extra appendages that are included in your URL field. Insycle has a function for that as well. With this function, you can extract just the domain name from the URL.
- www.Microsoft.com ⇒ microsoft.com
- https://www.insycle.com/ ⇒ insycle.com
If you need a more flexible option for extracting data, the “extract regex copy” function is a great option. This allows you to flexibly pull data from a field.
Extract Regex Copy/Move
- "New York City 2020-10-05!" with regex "[0-9]{4}-[0-9]{2}" ==> copy "2020-10"
In the regex, ("[0-9]{4}-[0-9]{2}"), the “[0-9]” is telling Insycle to extract just numbers from the field, while the {4} tells Insycle how many digits to pull. You can see the same format in the second half of the regex “[0-9]{2}”, where it is telling Insycle to extract two digits.
There are two different versions of this function, “Extract Regex Move” and “Extract Regex Copy.” Like the “move” and ”copy” standalone functions, these are telling Insycle whether the data should be moved (the data in the original field is erased), or copied (the data in the original field is kept).
Combining Functions
Now that is a lot of functions that you can use to format, standardize, and alter customer and company data in your CRM.
Combing through the different functions, you’ll probably be able to think of many different ways that you could potentially use each one to improve your data health and boost your marketing, sales, or support teams.
But functions don’t have to be standalone.
You can combine multiple functions together, and they will be processed in order, on the same field. Each function will apply itself on the output of the previous function.
Let’s look at a few examples. About is an example from Insycle’s pre-built “Format First Names” template.
This template combines the “Format proper case person” function with “Remove terms” and “Remove leading/trailing whitespace.” The result is a complete solution for formatting and standardizing the way that you handle first names in your database.
Here’s another example, from Insycle’s “Rationalize Titles” template.
This template combines two map functions to re-write job titles in a consistent and standardized way. With this in place, you’ll have an easier time searching and working with your data, segmenting customers for marketing campaigns, and an easier time identifying duplicate records.
Automating Ongoing Formatting and Standardization
The ‘Transform Data’ isn’t just about fixing data as a one-off task. It’s about installing rules and standards for how you will format and store data across your database. Then, you can use Insycle’s automation features to enforce those rules on an ongoing basis.
You can schedule any templates in the “Transform Data” tool to run at regular intervals and keep your data properly cleaned, formatted, and standardized.
Case Study: Putting It All Together
Recently, we had a new user reach out to our support team about using functions in the ‘Transform Data’ tool to solve a very specific and unique data problem that their company was having.
They were having a problem with first names. They were showing up with suffixes that included parentheses. Like this:
- Jamie (J)
- Daniel (D)
- CJ (Billings, Montana)
They wanted to remove the parentheses and leave only the correct first name behind.
First, we started by filtering the data.
Here, we tell Insycle to filter contact data where the first name exists, and contains the left parentheses. This will pull all contacts that are having the issue.
Now, we will use our functions to transform the data.
Here is the multi-step function template we built to deal with this issue.
The first function, Extract regex copy, extracts data and puts it into another field. In the “parameter” field, you see we use “[a-zA-Z]+”.
Essentially what this parameter is doing is telling Insycle to pull all data from the field that contains lowercase or uppercase letters in the first word contained in the field. This will remove all extra materials after the space, including the parentheses and any information included within them. It would also remove any non-letter inclusions in the first word, such as numbers of symbols.
After running, this is what the changes to the data looked like in the CSV preview report:
This is just one example of how you can use the ‘Transform Data’ tool to fix ad-hoc data issues automatically.
Have you ever encountered a similar data issue? Insycle almost surely can help. Start a chat with us (in the bottom right) and let us know what problem you are running into. Our team will help you get set up to tackle it and show you how Insycle can fix many common data errors in the CRM.
Insycle — The Swiss Army Knife of CRM Data
Insycle’s ‘Transform Data’ tool is perfect for formatting, normalizing, standardizing, and transforming your CRM data to make it more consistent.
Data is the most valuable asset in your organization. It is the primary driver of growth — but only if it is usable.
In the four phases of customer data management article we share our defined process for going from “undefined and chaotic” CRM data management, to fully standardized and optimized. Using all of Insycle’s tools and features together, you can make that leap to the next rung on the ladder and get more value out of your customer data.
Need help formatting, standardizing, and creating rules for storing data in your CRM?
Sign up for Insycle’s free trial by filling out the form below.
