How to Validate Data Between HubSpot CRM and an Email Marketing Tool

If data is the new oil, then data validation is the equivalent of oil purification. As businesses collect increasingly more data, inconsistencies are inevitable. However, implementing data standardization and validation processes can reduce data inconsistency and prevent data loss.

Let’s dive into the four best practices to validate data between HubSpot and an email marketing tool. We’ve used Mailchimp in this guide, but you can apply the same process to any other tool.

Know the Nomenclature and Data Structure

Before syncing data, it is essential to get familiarized with the nomenclature and data structure of both tools. On HubSpot, fields are known as properties, and each property has a field type. 

In the image below, plans is a property on HubSpot whose field type is radio select. It has three predefined options. 

HubSpot: A property with radio select as the field type

Properties are spread over four categories, namely contact, company, deal, and ticket.  

HubSpot: Property settings

Mailchimp’s equivalent of HubSpot properties are fields, and each field is of a certain type. In the image below, plans is a field of type radio buttons, also with three predefined options. 

Mailchimp: A radio buttons field

Understand the Field Types and Data Types

Once you are well-versed with the nomenclature and the data structure, the next step is to understand the various field types offered by each tool, as well as the data type of each field (or property). 

As you can see in the image below, both Mailchimp and HubSpot offer a number of common fields, as well as a few exclusive ones. 

Field types 

Assuming you want to sync data from Mailchimp to HubSpot, the field type of each HubSpot property must be compatible with the respective field type on Mailchimp. 

The image below shows matching field types between both tools. 

Compatible field types 

You might wonder why all Mailchimp field types are compatible with single-line and multi-line text fields on HubSpot. This is because data from all Mailchimp fields can also be stored as a string, which is the data type of both text fields on HubSpot.

However, it’s good practice to use field types that can store data in similar formats. For instance, while the output of Mailchimp’s radio buttons field can be stored as text, doing so prevents you from storing other field options that are on Mailchimp. 

Another drawback is that because a text field can store any text value, you are leaving room for error; sticking to radio select or a dropdown select leaves none. 

Now let’s look at the data types of all field types offered by both tools.

The data type of each field type

The above image specifies the data type stored and returned by each field type. This is the most crucial information required for a successful data sync. 

Unfortunately, knowing the data type stored and returned by each field requires digging through the API docs. However, data sync becomes much more organized once the field types and data types are figured out. 

Transform Data Into the Right Format

Data transformation is the next important step toward preventing data inconsistency and maintaining data integrity.

Below are four ways to validate data by transforming it into the right format:  

1. Specify a format.

One often needs to change the date or time formats before syncing data. One way to do this is to export Mailchimp data as a CSV file, change the data formats, and then import the modified file into HubSpot. 

However, this method is time-consuming and only good for a one-time sync. A faster, more versatile way is to use an integration platform that acts as a bridge between the two tools and takes care of all data transformation needs. 

Integromat is one such tool with a range of built-in functions to transform all types of data. Here’s how easy it is to change the date format in an Integromat scenario before sending a date field value to HubSpot from Mailchimp:

Integromat: Formula to change a date value to a specific format 

2. Extract values.

Let’s assume that there is a field containing the full name of a contact on Mailchimp and it needs to be synced with HubSpot, which has separate fields for first and last names. Here’s a formula to do exactly that: 

Integromat: Formula to extract the first name from a full name

The split function splits the string based on the separator, space, and the get function fetches the first item from the resulting array. The same formula can be used to extract an item from a comma-separated list of items. 

3. Replace values.

HubSpot has a default contact property called preferred language that lists languages along with country names. As you can see in the image below, the label and the internal value are different for each option.

HubSpot: Preferred language property options

It’s important to remember that during a sync, an incoming field value should be either equal to or converted to the internal value of the corresponding field on HubSpot.

In the example below, language is a Mailchimp field that stores the value English. It is replaced with en-gb to match an internal value on HubSpot. Failure to do this will result in an error, and the contact won’t make it to HubSpot. 

Integromat: Formula to replace a string

4. Define rules.

For certain incoming fields, there might be a need to use a different field type than the one directly compatible.  

Mailchimp has a default field called status that stores a contact’s subscriber status as a string. As you can see in the image below, it can have only four possible values: subscribed, unsubscribed, cleaned, and pending. This information is visible in Mailchimp’s API docs here.

Mailchimp: Default field status

Let’s assume that you need to sync the subscriber status with a HubSpot property called subscribed whose field type is single checkbox. 

HubSpot: Single checkbox property

As you can see above, a single checkbox field type on HubSpot lets a property only store a boolean value (true or false), while the field options — yes and no — are also fixed.

Therefore, the incoming values from Mailchimp’s field status need to be converted to either true or false to store yes or no, respectively. Here’s how it can be done by defining a rule using the if function on Integromat: 

Integromat: Rule-based formula using the if function

Based on this rule, the internal value of the subscribed property on HubSpot will be true if the status of the Mailchimp contact is subscribed, or else it will be false. It is also possible for you to define different outputs for each input separately using the switch function:

Integromat: Rule-based formula using the switch function

Reject Invalid Data

It’s important to remember that data validation is not the same as data verification. You can validate that an email is a business email and not a personal one by defining rules, but you can’t verify that an email exists unless someone confirms it. 

These rules can be defined on Integromat, which can then automatically reject invalid data. Continuing the above example, an email address provided by a free email service provider is deemed invalid. 

The Integromat scenario that syncs contacts between Mailchimp and HubSpot looks like this:

Integromat: Scenario with a filter

The business email only filter is set up as shown below: 

Integromat: Filter with a set of rules

The filter validates an email address only if it doesn’t contain any string value exactly as mentioned in the filter. 

You may also invert the operator to contains in order to perform an action when invalid data is found. 

Lastly, you can use a regular expression (regex) as the search term in a rule. While slightly difficult to comprehend, regex can enable you to validate and manipulate data in ways you have only imagined. 

The filter in the image below achieves the same result as the one above. The regex pattern — ^([\w+-.]+@(?!gmail\.)(?!outlook\.)(?!hotmail\.)(?!yahoo\.)([\w-]+.)+[\w-]{2,4})?$ — replaces the set of four interdependent rules. 

Integromat: Filter with a rule matching a regex pattern

RegexOne is a great free resource to learn about regular expressions. 

This concludes the best practices for data validation. You are now equipped to never experience data inconsistency or in-transit data loss.

Data Integrity Can Be a Reality

Businesses allocate significant resources toward data collection and storage. Maintaining the accuracy and consistency of data throughout its lifecycle is paramount in making it available when and where it is consumed.  

No matter how big a data set is or how many different tools are in your stack, Integromat can help you bring everything together and help you do more with your data without leaving any room for error.  

Click here to learn about the use cases and the possibilities of using Integromat with HubSpot.

Want to connect with others on HubSpot tips, tricks, and updates? Head over to the HubSpot Community to join a conversation or start one of your own.