If you sync your Salesforce and HubSpot databases then you probably have dealt with the pain that non-matching state and country fields can cause.
For every contact record in your database, your state and country fields have to match, exactly, between HubSpot and Salesforce.
If they don’t, the sync breaks and data for the record is not shared between the two platforms.
This can be a serious problem, particularly if you are syncing the two platforms after having used both for quite some time. Unless you have been practicing strict standardization across both databases, there is a good chance that you will have many records with non-matching state or country fields — and that means records that aren’t being synced.
That robs your sales, marketing, and support teams of important context from other fields that they require to be effective and engage with customers in a way that resonates.
That means that you have to fix and standardize those records to ensure that all records shared between HubSpot and Salesforce have state and country fields that match exactly.
Shouldn’t be too hard, right? Well, not always.
Anyone that has tried to standardize their state and country fields knows that there is more than meets the eye, especially if you are using free text fields (also known as single-text fields) or allowing customers to enter this data on their own. There will be many variations.
In this article, we’ll break down exactly what the process for doing this is currently, and then look at how Insycle helps to simplify the process and streamline data management for HubSpot and Salesforce syncing.
If you are just getting started with your Salesforce and HubSpot sync, the country and state matching issue might come as an initial shock, both because of the huge effect that this can have on your teams and because actually fixing the issue can be quite complicated.
Naturally, you want to convert any free text field to picklists to ensure the fields consistently match on both platforms. That eliminates a lot of variables. With free text — and especially when your own teams or the customers themselves are responsible for entering the data — leads to a lot of variations.
Those variations can take several forms. Commonly, they are just formatting differences, such as New York vs. NY vs. N.Y. or New York State. Other variations could include typos, corrupted data, or a variety of issues that are hard to account for. Anytime that a human is responsible for typing in the data, rather than selecting it from a picklist, you can bet that your data will be filled with errors and standardization issues. Or you may import unstandardized data from an outside source — such as from a CSV import from a webinar, or through an integration from a different app.
In the past, things were a bit simpler. HubSpot allowed you to change country and state fields from free text to picklists.
But in 2020, HubSpot made some changes. Now, you can’t outright change a free text field to a picklist when the field already contains data. You’ll receive an error that prevents this if you try.
HubSpot requires that you wipe all data from the field across all records before changing the field type.
For companies that want to fix their HubSpot and Salesforce sync, this means that you will need to backup data in those fields, clear that data, standardize it, then re-import it. Most companies will use Excel to do this, and that means a complicated process. Trying to identify and fix all of the potential state and country variations from a large database in Excel can take dozens of hours and require a lot of manual checking.
Let’s break down what this process looks like today into steps:
In order to change a free text field to a picklist, you’ll need to clear data in the field. First, make sure that before you clear the data that you back it up or hold onto an original version of your export.
Then, take the data and clear all of the values in the country and state fields.
With the data cleared, you can now import the blank field data back into HubSpot.
With no data in the state and country fields, you can now freely change the fields from free text to picklist.
A picklist is ideal for these fields to minimize human input and limit variations in your data that will ultimately break the sync. In this HubSpot article, they show you a simple way that you can access all of the values so that you won’t need to define all of the correct state formats manually.
Now comes the hard part.
Before you re-import your historical data, you need to standardize it (otherwise it will just break the sync between HubSpot and Salesforce for non-matching records again). Additionally, re-importing your data before standardizing it will create new picklist options for all of your variations. That is definitely something that you want to avoid.
Fixing the values and standardizing the data in your CSV through Excel can be extremely time-consuming and complicated.
Imagine using “Find & Replace” for every potential variation in your database. New York/NY/N.Y./New York State, etc.
You’ll also have to account for misspellings and typos and it is almost impossible to account for every typo you’ll find in your database using only Excel find & replace feature. That means that you’ll end up having to search and fix values in many records by hand. In big databases, that will take hours (or days).
Once you finally have all of your state and country data consistent and standardized, you can then import it back into your database. Picklist values will automatically be created for every variation included in your data.
Hopefully, with some elbow grease and luck, there won’t be too many overlooked variations that break the sync between HubSpot and Salesforce. In larger databases, you can count on there being some fields that still contain errors and therefore will not sync between HubSpot and Salesforce without being identified and fixed manually.
Another potential approach for this process is to create a separate new custom field that you can use to sync with Salesforce.
For example, in this approach, you might have two “State” fields in HubSpot. One would be the default “State” field, and another might be named something like “Salesforce State.” Then you use the “Salesforce State” field to sync the databases.
There are many disadvantages to this approach, but in some specific situations, this might be an option to consider. But it still requires that you standardize your data and keep syncing the two fields on a continuous basis to avoid breaking workflows and automation. It also may cause confusion among your teams that use the data, along with other personalization and standardization issues. Which field should we rely on for creating campaigns?
The process of fixing and standardizing your state and country fields so that you can change the field from free text to picklist and Sync Salesforce and HubSpot is time-consuming and aggravating — but Insycle is here to help. Insycle is the Swiss army knife of data management and can help teams maintain their HubSpot and Salesforce integration in multiple ways.
Insycle simplifies the process and frees you from mundane and time-consuming data work in Excel. The entire process can be completed in Insycle in a fraction of the time.
Let’s dive into what this process looks like using Insycle.
First, start by creating a temporary field to hold our historical data. We’re doing this so that we hold onto the historical data and have a safe place to work on the standardization process while clearing data from those fields so that we can convert them from free text to picklist.
You can move all data from your existing state or country fields in Insycle using the Transform Data tool and the “Move” function.
The “Move” function will move data from one field to another. This will clear all data in the existing state and country fields (which we need to do anyway to change the field from free text to picklist in HubSpot) while placing the data in the new temporary field that we’ve created.
Here’s an example of what it will look like when you move data from your “State/Region” field to a custom field.
After using the “Move” function, you should have no data in your existing state and country fields (whichever fields you are currently working on fixing).
With no data in the state or country fields, you will be able to change the field type to picklist without issue.
With Insycle, standardizing your state and country fields is simple and visual. It’s much easier and more accurate than trying to do the same thing with Excel.
Not only does Insycle provide a number of standard templates that you can use to standardize data, but it also provides advanced functionality and features that would not be possible through Excel.
While there are multiple methods you could potentially use, the easiest way to standardize state and country field data in Insycle for HubSpot and Salesforce syncing is using the “Fields Cleanse” tool.
In this tool, you’ll have a bird’s eye view of all the data inside your database, and particularly in the state and country fields.
You can see all fields, with additional statistics statistics about each field. For example, how many different values exist, and how many records have empty values. If you’d like to share this data with your team or the Salesforce admins click to download it to CSV.
Let’s start by getting a top-down view of our data. In this example, we’ll standardize the “State” field.
Type “state” into the search bar that says “Type to search for any field, then select it:”.
The “State/Region” field will appear in the list below. Select it.
Once selected, you will see all of the variations for the different states and values that are contained within this field.
This data viewer shows you all of the variations for your selected field. In this example, we are using a small dataset to keep things simple. In a large database, you might have hundreds or thousands of variations. At the bottom of the data viewer, you will see a “Rows Per Page” option which will allow you to see more data on one screen without having to flip through pages.
As you can see in the image above, we have 20 records in HubSpot with “NY” as their listed state, and 2 records with “New York.” Let’s assume that in our Salesforce database, we are using the full name for states with no abbreviation.
In that case, “New York” is the correct format. The 20 fields using “NY” would not sync between HubSpot and Salesforce and those records would no longer be updated as changes occurred — which is a problem at all stages throughout the customer lifecycle.
Now let’s standardize the data.
Select the different variations of the same state that you would like to standardize. We’ll start with New York.
On this screen you can also drill down into your data to view the actual records contained in each bucket.
At the bottom of the screen, the records selected in each of these buckets will appear.
Then, at the top of the screen in the “Update” section, we will tell Insycle what we would like these values changed to. In this case, we are changing all values to “New York”.
With the right value entered, you can select “Update All Contacts.” A popup will ask you to confirm. Once you confirm, all of the selected records will be standardized for the new value.
This provides you with an easy, visual way to standardize state and country fields without having to dig through messy Excel files, go through two separate exporting and importing processes, or analyze your data in Excel to figure out how to map things.
Now that the values in the temporary field are standardized, you can use the “Move” function again to move values into the original field that now accepts standard picklist values.
When you’re done, remove the temporary field.
Syncing your HubSpot and Salesforce data is so critical for delivering consistent and informed customer experiences throughout the customer lifecycle.
Sharing data breaks down data silos and empowers your team to engage with customers with full context of all of their interactions, across platforms and teams.
However, maintaining that sync can be difficult. Even something as simple as non-matching state and country fields can completely break the syncing of affected records and leave your sales and support teams in the dark.
Using Insycle, you can easily convert free text fields to picklists and standardize them using Insycle’s visual interface that works directly on the data without requiring a separate export and import . This way, you can keep your HubSpot and Salesforce synced without having to spend dozens of hours using Excel functions and find & replace features to identify and fix variations in your data.
Want to improve your HubSpot and Salesforce data sync today?
Sign up for Insycle’s free trial using the form below.