Please note this information can also be found in Aircall's Developer page here: Format your contact numbers in HubSpot automatically using scheduled workflows
Overview
When you have leads coming into HubSpot from multiple sources, it is easy to end up with inconsistently formatted numbers. In this guide, we’ll show you how to set up a workflow in Hubspot that automatically reformats your numbers to E.164 format.
E.164 number format is an internationally recognized standard phone number format that helps ensure the deliverability of calls. When your phone numbers are saved in the E.164 format, our systems can accurately map calls to the correct contact or company in Hubspot. This ensures you get the correct information on insight cards and prevents the creation of duplicate contacts.
It’s important to note that while we aim to switch numbers to E.164 format, Hubspot will automatically reformat the number to the local standard formatting (depending on the region). Hubspot cannot disable this automatic local formatting. Despite completing the workflow, the contact phone number will look like it hasn’t changed when viewing it in the property field.
However, when the workflow is applied to a contact/lead, their phone number will be formatted in E.164 format in the backend. Aircall searches for the endpoint-calculated phone number when logging calls and pulling contact data. As long as the calculated phone number is in E.164 format then you will see the expected behavior in terms of call logging consistency.
Requirements
- An active Aircall <> Hubspot integration. More information on installing that integration: Installing Your Hubspot Integration
- Hubspot Operations Hub & Hubspot Workflows (available on Professional and Enterprise Plans in Hubspot)
- Previous experience with creating Hubspot Workflows
If you need an extra hand with implementing this workflow or would like to learn more about other automations that can improve your operations, check out our Professional Services page where you can book time with our Technical Consultants.
Checking Phone Number Field Usage
Before building the workflow, you must identify the predominant phone number fields in Hubspot. Later, we will use those specific fields to trigger a workflow automation. We recommend this step if you are using custom phone number fields to store Contact phone numbers. However, the standard Phone number and Mobile phone number fields will be the predominant fields in most cases.
- Navigate to the Reports drop-down menu in your HubSpot environment
- Select Data Management → Data Quality
- Under the Data Quality drop-down menu, select Property Insights
- Scroll down to Properties to review and enter “Phone number” in the search bar to bring up the relevant phone fields
The results contain a column to the right labeled Fill Rate ( % ). You will find a breakdown of the most used phone number fields in that column.
Now that you have identified the predominant phone number fields, we will use them to trigger our workflow automation in the next step. Please keep in mind that you will have to set up separate workflows for each phone number field you would like to update.
Creating the Number Reformatting Workflow
Moving forward, we will build our workflows around the predominant phone number fields. The workflow will include several actions but the most important actions are:
- Schedule Trigger: this action will allow us to set the frequency and the time rules for when we want the formatting updates to take place.
- Enrollment Trigger: this action allows you to enroll records in the workflow.
- Format Data: this action is the engine of the workflow and will allow us to set the rules for the number formatting.
Please note that Aircall only logs against numbers stored in the Phone number or Mobile phone number field and not custom fields. When it comes to copying the string value later in this guide, you will have to ensure it is copied into the Phone number or Mobile phone number fields native to HubSpot.
To get started, navigate to the Workflows in your HubSpot environment. Select Create a New Workflow → Contact Based → Schedule. You will then be prompted to set the Schedule Trigger, where you determine how frequently and when you would like the numbers to be automatically reformatted. This will automatically add the Schedule Trigger to the workflow. We recommend scheduling the workflow outside business hours to prevent any possible disruption to your daily operations.
Next, we will set up the Enrollment Trigger using the phone number field you determined to be the dominant one as the singular contact property. The trigger should be set up as follows:
- On the Contact enrollment trigger action, select Set up triggers
- In the panel on the right-hand side of the page, select Contact properties under Filter type
- Under Contact Information, search for the phone number field you would like to update for the enrolled Contacts. This will be one of the predominant fields you identified previously
- Finally, you'll want to trigger the workflow when the chosen phone number field is Known
Now that we have set up the Enrollment Trigger and the Schedule Trigger, we will use the Format Data action to set a custom formula that will trim unwanted spaces and cut characters from our phone number.
- trim - trim leading and trailing whitespace
- cut - cut a character or several characters
The characters commonly found in phone numbers are -, (, ) and spaces ( ). If additional characters are included in the phone numbers, make sure to account for those in your formula. When using a custom formula, it’s important to note that each character requires a corresponding function, and vice versa. For example, if you have 5 characters you would like to remove, you will need to add the ‘cut' function 5 times.
If you're familiar with setting up custom formulas in Hubspot, you can type out the completed formula as outlined in the GIF below. If you prefer step-by-step instructions, please continue reading.
Here are the steps for setting up the custom formula:
- Select Format Data as the next action in your workflow and toggle on Custom Mode. We highly recommend turning on Formula Guidance for visual assistance and error warnings while building the formula
- Click on Insert → Function and select trim
- Working off the assumption that the only characters we need to remove are the 4 mentioned above, we need to add 4 cut functions next. Click on Insert → Function and select cut. Repeat this 3 more times for a total of 4
- Once completed, your function should look like the images below
It's expected the formula guide will indicate issues at this point, as we have not completed the workflow.
Next, we have to add the source. In this case, the source is the phone number property from the first step in the workflow. Click Insert and Hubspot Property and select your phone field. Add a comma directly after it, which should leave your formula looking like this:
Now we add the characters that need to be removed. There are 3 simple rules to remember when entering the characters:
- Commas must be followed by spaces
- Characters must be in quotation marks
- Each character must be closed off with a parenthesis
In the formula field, start adding the characters you would like to cut while following the formatting rules above. Here's a reference:
- Dash: , "-")
- Open Parenthesis: , "(")
- Closed Parenthesis: , ")")
- Space: , " ")
Once you have added all the characters to the cut function in the formula, close the formula by adding an additional parentheses to the end. Your formula guide should be green, indicating "no issues", and your formula should look like the image below. Make sure to Save and return to the workflow.
We now have to take the cut and trimmed value and put it back into the Phone number or Mobile phone number field using the action Copy property value. When you select this action, you will be prompted to select the Property or value to copy from in a drop-down menu.
- In that menu, you'll need to select 1. Format data -> [String] value
- Next, under Target Object, select Contact
- Finally, under Property to Copy "[String]" value to, select either Phone number or Mobile phone number
- Click Save to finish setting up the Copy property value action
After reformatting the numbers and pasting the updated values into the Phone number or Main phone number fields, we need to ensure that each number has a + in front of it. We will use the If/Then branching action to handle cases where some numbers already have a + and some do not.
- Add a new If/then branch action in the workflow
- Next, set up two branches in the If/then branch action
- For the first branch, set the branch name to "Has +" and set the filter type to Contact properties → Phone number → starts with any +
- For the second branch, utilize the default HubSpot branch if none of the criteria are met and set the branch name to "No +", meaning any phone number without a + at the beginning will fall into this branch
For the Has + branch, this is where the workflow ends and no further action is taken. The characters and spaces have been removed, and it already has the + so it is in the E.164 format. However, we must configure the No + to have the + added to the front of the number. Under the No + branch, select Format Data and toggle on Custom Mode. We are revisiting the custom formula, but this time with a new function called Concat:
- Click on Insert → Function and select Concat
- Immediately after the bracket type
"+", - Click on Insert → HubSpot Property and select your previously used phone number field. Then close off the formula using )
To complete the workflow, we have to copy the [String] value again. Repeat the step above following the slight difference below:
- Select Copy property value
- Property or value to copy from → 4.
- Format Data → [String] value Target Object= Contact Property to copy [String] value to= Phone number or Mobile number
Congratulations! You have just created your number formatting workflow! 🎉
Testing the Workflow
To test this, we recommend creating a fake contact with a fake number containing the likes of (, ), - and then manually enroll the contact.
We are aiming to change the numbers into the E.164 format, however the default native behaviour of HubSpot is to automatically format the number to the local standard formatting. Each country has their own stylised version of number formatting, which we are hoping to override with the universally recognised E.164. Due to a limitation on HubSpot’s side, we cannot disable this automatic local formatting. Aesthetically, despite completing the workflow, the contact phone number is going to look like it hasn’t changed when viewing it in the property field (front end). However, if the contact has successfully enrolled and completed this workflow, in the backend it will be in the E.164 format which is the more important aspect. Aircall searches the endpoint calculated phone number when looking to log calls and pull contact data, so as long as this is in E.164 format then you will see the expected behaviour in terms of call logging consistency.
Notes
If the contact numbers do not have the country code in front of them, you can create a similar workflow to add the country code to the start like we did with the “+”. This only applies if your contact numbers are all in the same geographical zone e.g +44. If you have contacts from multiple different countries, it is possible to automate this with workflows if you have a country code field on each contact.