Quick Tip: Associating Contacts, Companies, and Deals - A Case Study in Lemonade

[#1]

Thanks for the post - very helpful for this longtime Salesforce user.  

Quick question in this scenario - if I wanted to build a Deal Filter of number of deals per "neighborhood", the attributes of the neighborhood reside with the company but I am building the filter on the deal.  

In my case, I want to see how many deals I have that are international.  We have a "Country Code" field on the Company.  However, I have not been able to figure out how to build a query that combines the relationship of "Get all Deals for current quarter whose Company Country Code <> US or United States".  Is it possible to build a query like this in Hubspot?  Thanks in advance...

[#2]

Hi @adearing, 

For the deals within the current quarter, would you be looking for deals with a close date in the current quarter, that were created in the current quarter, or something else? Knowing what criteria we want to filter the deals off of, in addition the country code, will help us build this filter.

Thank you,
Jenny

[#3]

Jenny - thanks for the reply!  I would be looking to create a report with a close date in the  current quarter with the associated company outside the US.  Appreciate the help!

[#4]

Hi Jenny - checking up on this thread, do you have any insights into this query to  be able to pull all deals outside of the US for a current quarter?  Thanks in advance!  

[#5]

Hi @adearing,

What have you built so far? If you have a country code for the US you can use the filter country code is not equal to US and use the close date filter as well . 

@MFrankJohnson, @joshua-paul, @ConnorSlivensky do you have any additional suggestions for creating this sort of report?

Than

[#6]

I am trying to build a filter on the Deals to show all non-US deals; however, the country field only exists on the Company associated with the Deal.  I am trying to traverse the Deal back to the Company; however, I am not able to build that query (or not sure how).  

The alternative is to duplicate the country code from the company on EACH deal and then perform the filter; however, I am trying to avoid data duplicatoin between Deal and Company.

Thanks,

Andy

[#7 · Accepted solution]

>>"The alternative is to duplicate the country code from the company on EACH deal and then perform the filter; however, I am trying to avoid data duplicatoin between Deal and Company."

In some HubSpot use cases, data duplication is unavoidable. This is one such case.

You'll want to build a workflow to copy Company.country to a custom Deal.country property, then filter based on your custom deal property. Consider, Company.country isn't always populated by HubSpot Insights data (referred to earlier in this thread), so ... there will be gaps in data based on this solution.

Thanks for the ping @jennysowyrda ...

-ps
It's also important that your custom Deal.country property EXACTLY match Company.country in both type and internal values. (important)

[#8 · Accepted solution]

@adearing Frank's approach is the right one. I'll add a few things.

Duplicate data on different CRM objects is not a problem if you set up your workflows to run behind the scenes and not be exposed to your sales team. The automation will simply keep everything in sync. If, for whatever reason, the country of the company changes, you'll want that pushed to that company's deals without anyone noticing. The data will just be there and be accurate.

In the event that the company's country data is not known in the system, you'll also want to make "deal country" a required property when creating a deal (to cover deals being created manually) and on several deal stages (for deals that are created through automation). This forces the sales rep to select a country if it is blank. It takes 10 seconds for them to do and will maintain the accuracy of your filtered data.

Good luck!

[#9]

Thanks @joshua-paul - appreciate your feedback and recommendation of the workflow.  To your point, making it easier on the Sales reps on data entry and automating the data entry (in cases where duplication is unavoidable) is a good solution.  I will give that try.  

Appreciate everyone's help!