topic Re: Location data clean up with multiple checkboxes in Tips, Tricks & Best Practices

Location data clean up with multiple checkboxes

automatedllama — Thu, 27 Jun 2024 16:12:03 GMT

I'm trying to clean up location data for easier external reporting. Our target customers can have businesses in different cities and states. For example, there's currently a multiple checkbox type for states. The unfortunate drawback to this is that once we pull the data out of Hubspot, if the customer has multiple states selected, these all get added to one row per customer separated by semicolons. (I'm aware of pivot options in Hubspot Reporting but this won't work for our needs.)

If it's possible, what would be the best approach to cleaning this up in Hubspot (possibly Operations Enterprise / Workflows?) before data flows out for external reporting in Snowflake / Tableau? My other step would be to tackle this in Snowflake but exploring all options.

Re: Location data clean up with multiple checkboxes

karstenkoehler — Thu, 27 Jun 2024 16:30:52 GMT

Hi @automatedllama,

What exactly would be your desired outcome?

Best regards

Re: Location data clean up with multiple checkboxes

automatedllama — Thu, 27 Jun 2024 17:49:55 GMT

Ideally, we would still have multiple states associated with each customer when that occurs, but this would output into separate rows associated with that customer when it's brought into Snowflake and eventually into reporting within Tableau. The current output is merging states into one row ("Colorado;Florida;Texas) instead of separate rows for each state for that one customer.

Re: Location data clean up with multiple checkboxes

karstenkoehler — Thu, 27 Jun 2024 18:52:11 GMT

@automatedllama there is no field type in HubSpot that would support such an output: https://knowledge.hubspot.com/properties/property-field-types-in-hubspot

If you want an actual one-to-many relationship (one company, multiple addresses), you would have to work with custom objects for the addresses: https://knowledge.hubspot.com/object-settings/create-custom-objects

... or find a way to transform the data before you report on it in Tableau.

Re: Location data clean up with multiple checkboxes

automatedllama — Sat, 29 Jun 2024 15:39:37 GMT

Hmm, I was hoping that Hubspot would have more options available, especially now that we have access to Operations Hub. It seems like this would be a common problem that comes up so was hoping that there might be a readymade solution that I missed somewere.

My impression in general is that I'd potentially be creating a convoluted workflow, custom code, or custom objects within Hubspot. Back to the original plan of working on this within SQL!