How to Standardize the State/Region Property in HubSpot

Does your HubSpot Instance have a messy State/Region property that is riddled with state abbreviations? 🤔🙅

It's time you get that fixed with this effective data governance workflow.

What is a data governance workflow, you ask? The primary purpose of these workflows is to fix inconsistencies in data standardization. Primarily, this is done by mapping data points across objects when they are populated on one object and not the other. (Example: Industry is known on the company object but not the contact object so we copy that value over to the contact.) However, these workflows can become more complex when building for particular data hygiene use cases that extend beyond simply copying values.

Data governance can have a positive downstream effect that helps streamline reporting, lead routing, segmentation, and more. Not to mention, clean data is an integral part of a powerful CRM instance.

TRANSCRIPT

Hey LinkedIn, it is Liam Redding, Senior HubSpot Strategist for Remotish agency. And today I have a tip around how you can standardize your company, or contact state/region property. And it's one of the ones that we run into where most often has some data inconsistencies in terms of some contacts or companies have, you know, an abbreviation, some have the full state written out. And it's one of the things, it's really actually pretty simple to get fixed with a workflow.

So I created this workflow for a client. It's part of a Data Data Governance Project, I do a lot of the data governance workflows. And essentially what the purpose of this workflow is, is to stand a standardize that state/region property, one of the things you're going to notice is that I had to create a state/region property for the data governance workflow itself. And the reason why I had to do that was if you go into the state region, you can see this is a drop down select, that is populated by HubSpot. So this is a default HubSpot property out of the box. And it has all the different options here. For all the states, as you can see, there's no abbreviations best practice not to use an abbreviation when you are specifying the state that the contact or company comes from. So one of the issues that we're having, or the reason why I had to create that custom property is because this particular instance, is syncing with Salesforce. And that Salesforce state region property has options that are outside of the options included here. So for example, if we go look, this is a report that breaks down what that looks like. We can see, for example, there is CA for California–870 contacts, 1.5% of the database has that. And then you also have, you know, 5,000 or 9.75% of the contacts who have actually, properly labeled. And what you'll notice is that because of that integration, there's inconsistent values. Because of that, you can't just like it would be really easy if I could come in here, and I could just say, state region is any of and then just grab all of those states that are abbreviated. But because those options aren't included here, we have to actually build that logic into our workflow, which makes it a little bit more complicated than it typically is.

So, the first thing we're going to do is we're going to check whether or not the state has already standardized properly, right? If it’s standardized properly, then we have no need for that contact or company to go down this workflow, we can just if/then out to the end of the workflow, and those contacts, that segment would end there. The contacts who do not meet that criteria, who are not standardized correctly, would then go down the remainder of the workflow. The first thing that we're going to do is we're going to copy over that state region value to this new data governance property. And the data governance property has all the different, you know, abbreviations included in it. So it will actually copy over that state abbreviation value into that new property. So it'll essentially do what we wanted it to do for the enrollment logic up here. But because it's a default, out of the box, during the HubSpot property, we don't have that option.

So once it copies that value over, we're then going to if/then branch, and this is really simple, it's just we're looking for all of the different variations that could exist, we're going to check for all of them on that new custom property that we've just created. And you will notice that you have to have multiple if/then branches, because if then branch can only have up to 20 branches; there’s more than 20 states. Therefore, we need more than one if/then branch. So the first one will check all these different values. And then you know, it will correct that. And it will type out what that state is, you know, populate that back into the state region property that default HubSpot property. I don't care about that property that we created just for the standardization sake, that can remain the abbreviation. And then when you get down to the final 20th branch, we then have to go down to none met. And then off that none met, you're going to build your second if none branch, which is then going to continue to check in and correct those states when there is an abbreviation.

And a final for your final. I guess that would be 20, 40. Yeah, this is the final 10. So yeah, there's your final 10. It's going to check all of them, it's going to correct it. When you turn this workflow on, that report, I'll give it about 30 minutes or so. But once the workflow has a chance to run, the database has a chance to update and then that will then be live in the report and then you'll be able to see which percentage of your database actually belongs to which state without having to see it spliced up in a million different directions. So, hope this is helpful. Leave all your thoughts and comments in the comments below and I appreciate your time. Thanks.