Automating phone number formatting in HubSpot: not a simple formality

Why bother formatting phone numbers?

Some RevOps managers clean phone numbers just to keep clean data. However, there are instances when it's important to do so in order for integrations, like Aircall for example, to work properly.  Aircall and HubSpot work together to help businesses make calls directly from a CRM, then tracks them and allows reporting on them, keeping the CRM clean and your employees happy by not having to log or tidy one more thing.  

They synchronize quite seamlessly if everything is formatted properly. However, if anyone has explored their own CRM, there are often challenges with data cleanliness, especially when it comes to telephone numbers as I would find out. 

First Look: I thought it would be simple and easy

Aircall is able to recognize a large amount of different phone formats so I though this would be a simple task. Especially since I knew HubSpot Operations Hub workflow functions were quite powerful, such as the "format data" option with many functions. I thought I would be able to use the "replace" function to remove all the periods, hyphens, brackets or other things that might cause problems and be done quickly. Essentially I thought my workflow would be the image below: 

However, I started to explore phone number and mobile number fields through filters under Contacts and even better, through active lists that allowed more nuance and flexibility. I noticed that just by clicking there was thousands of contacts with phone numbers but with #NA instead. This misleads any reporting or integrations that think there is a valid number for contact as only blank fields are recognized as "unknown". I added this on to deal with these separately to remove the #NA from unknown numbers. 

📌 Tips: Clean up your HubSpot CRM in a few steps

Extensions discovery

Then I discovered a major problem: phone extensions written into the phone number field (and even the mobile phone field). Extensions are especially a problem because HubSpot recommends phone numbers are imported with the format "+18779290687 ext123". This poses a problem when you try to dial this number from Aircall for example (or other applications that don't accept extensions in the primary phone number field) since it excludes the extension and employees are left with trying to get past a receptionist or worse: they are left with a bot receptionist who only excepts valid extension numbers. 

In addition, many of the CRM users will discover correct or new extensions for contacts and if there is no clear formatting rule for extensions, they will enter all sorts of text that will need to be cleaned. For example, we have seen "x 123", "/ 123", "*123", "-123" etc, all of which do not work in Aircall and other applications. 

So for extensions, a rather large If/then branch got started with all the various letters and ways people made note that there was an extension. A large number of "replace" functions were used to achieve this as originally thought. Then I had to ensure that both the phone and mobile phones were being considered equally since some people put phone numbers with extensions under mobile phones. This doubled the number of branches.

This way we formatted all numbers as "+18779290687 ext123" and we then used a custom code action to separate the extension in another custom contact property named "Phone extension" so the phone number and mobile phone number can be formatted as "+18779290687" and be recognized and match by the integrated phone system (i.e, Aircall).

exports.main = async (event, callback) => {

    const phone = event.inputFields['phone'];
    const mobilePhone = event.inputFields['mobilephone'];

    if (phone != undefined && phone.includes("ext", 1)) {
        const extensionNumber = phone.split('ext').pop();
        const extension = `Extension: ${extensionNumber}`
        const newPhone = phone.split('ext', 1)[0];
        const newMobilePhone = "null"

        callback({
            outputFields: {
                newMobilePhone: newMobilePhone,
                newPhone: newPhone,
                extension: extension
            }
        });
        console.log("Phone extension has been found");
    } else if (mobilePhone != undefined && mobilePhone.includes("ext", 1)) {
        const mobileExtNumber = mobilePhone.split('ext').pop();
        const mobileExt = `Extension: ${mobileExtNumber}`
        const newMobilePhone = mobilePhone.split('ext', 1)[0];
        const newPhone = "null"

        callback({
            outputFields: {
                newMobilePhone: newMobilePhone,
                newPhone: newPhone,
                extension: mobileExt
            }
        });
        console.log("Mobile phone extension has been found");
    } else {
        console.log("No extensions found");
    }
}

Country Code Addition

Country codes are also important. If you're fortunate, they're all the same, otherwise, it becomes quite complicated. Since the business we were helping was largely Canada-USA based we formatted all the numbers to +1 if not already have a different country code. But once again, the challenge was that some numbers had +1 already, 1 only, or just the number and so this became a fairly large if/then section for phone and mobile or both for all of the above 3 options. 

Final result

In the end, we had a very large workflow that we were very proud of that worked seamlessly for the phone number and mobile numbers in the CRM to be formatted into one unified format. It was much larger and far more complicated than originally thought, using the "format data" and "custom code" workflow functionalities that were all available through HubSpot Operations Hub software. It's too big to easily publish here, but this gives you the magnitude of the size:

Need help with issues like this?

If you need help solving problems in your company that appear simple but become much too complicated, reach out to us here, we take great pride in helping companies get their flywheel of revenue spinning at a high velocity with as little friction as possible.