[Feature] Include property labels alongside property internal values

[codingcyclist]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Any property in HubSpot has an internal value and a label. Especially for properties provided by HubSpot (e.g. Persona), the label is much easier to interpret than the internal value (see screenshot). Hence, In many cases, it's not the internal value but the property label that you'd want to surface in your reports and dashboards.

As the current dbt-hubspot plugin is not passing through the property labels from the property_option table, I'd suggest adding a use_property_label option to the pass-through column configuration, e.g.:

hubspot__contact_pass_through_columns:
    - name: property_hs_persona
      alias: persona
      use_property_label: true

Describe alternatives you've considered

The only alternative that seems reasonable is to build custom dbt models that join the property labels from the property_option table and the hubspot_{contacts,companies,deals} tables together, based on property_option.value

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance and will schedule time during your office hours for guidance.
• No.

Anything else?

No response

[fivetran-jamie]

hi there @codingcyclist thanks for taking the time to open this feature request! I agree that this seems very valuable to incorporate into the package.

I'd like to confirm that I'm understanding correctly:

So in your example, you are currently passing through property_hs_persona as persona, but the values are coming through as persona_1, persona_2, etc . However, it would be much more useful to have the package map these onto their longform labels of Reviewer, Economic buyer, etc

Are there cases where you would want to have both the internal value and the label in your end models?

[codingcyclist]

Hi @fivetran-jamie thanks a lot for your kind and timely response, I really appreciate that!

Your understanding is exactly right: We're passing through property_hs_persona as persona, but instead of the values coming through as persona_1, persona_2, we'd like them to come through as Reviewer, Economic buyer etc.

I can totally see cases where one would want to have both the internal value and the label in the end model. For instance, when the end model is not only used for reports but also for reverse ETL. In that case, one would like to show the labels in the reports and use the internal value of the property for the reverse ETL use case.

Do you think it's feasible to surface both the label and the internal value in the end model, maybe with a suffix?

[fivetran-jamie]

Yes I think that's feasible! I was thinking we'd add a suffix to the label version of each field (and not to the internal value version), so as to not to potentially break people's current transformations

In your case, would you want both the label and internal value automatically for all property_* passthrough fields, or would you want to pick and choose which fields you bring the labels in for?

[codingcyclist]

Amazing! We'd definitely prefer to be able to configure it per passthrough field whether we bring in the labels or not. However, if that makes the required code changes a lot more complex, it would also suffice to just always get both the internal values and labels. Do you already have a sense of how long it would take to implement the required changes?

[fivetran-jamie]

Cool! I will plan to include this in our next sprint (starting in two weeks), so i would say you could expect this in 4-6 weeks.

However, I do see you are interested in opening a PR! If you are still interested, we would happily welcome your PR and our team will prioritize its review once it comes through. Let me know if you are still interested or if you have any questions around opening a PR.

[codingcyclist]

Hi @fivetran-jamie - that's fantastic 🙌 . With my limited context on the package's internal workings, I think my turnaround on this issue would be a lot longer than 4-6 weeks. Hence, I'm very glad the team can pick this up as part of the next sprint, Thanks a lot!

[fivetran-catfritz]

@codingcyclist To update you, I have picked up this task and aim to have something for you to try in the next week!

[codingcyclist]

Wonderful @fivetran-catfritz
I really appreciate it and look forward to trying it out

[fivetran-catfritz]

Hi @codingcyclist. I have a test branch you can try out! You can install it by using the below snippet in your packages.yml in place of your normal hubspot lines.

- git: https://github.com/fivetran/dbt_hubspot.git
  revision: release/v0.15.0
  warn-unpinned: false

You would enable the columns by setting them in your vars exactly the way you imagined it in your proposal. You will also need sales enabled.

vars:
  hubspot_sales_enabled: true
  hubspot__contact_pass_through_columns:
    - name: property_hs_persona
      alias: persona
      use_property_label: true

should work perfectly. Alternatively, if you want to pass through several columns and don't want to have to flag use_property_label each time, you can also set hubspot__enable_all_property_labels: true. like the following

vars:
  hubspot_sales_enabled: true
  hubspot__enable_all_property_labels: true
  hubspot__contact_pass_through_columns:
    - name: property_hs_persona
      alias: persona
    - name: property_hs_#2
    - name: property_hs_#3

I'd love to know your feedback if you are able to try it out!

[fivetran-catfritz]

This update has been released in the latest version, so closing this issue out!