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

SOLVE
New Contributor

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...

Reply
0 Upvotes
2 Accepted solutions

Accepted Solutions
Community Superstar

>>"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)

Hope that helps.

 

Best,
Frank

 

MFrankJohnson-dot-com-HubSpot-Community-banner-gif-v20190817

Regular Contributor | Gold Partner

@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!

8 Replies 8
Highlighted
Community Manager

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


Did my post help answer your query? Help the Community by marking it as a solution
Reply
0 Upvotes
New Contributor

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!

Reply
0 Upvotes
New Contributor

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!  

Reply
0 Upvotes
Community Manager

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 Smiley Happy

 

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

 

Than


Did my post help answer your query? Help the Community by marking it as a solution
Reply
0 Upvotes
New Contributor

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

Reply
0 Upvotes
Community Superstar

>>"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)

Hope that helps.

 

Best,
Frank

 

MFrankJohnson-dot-com-HubSpot-Community-banner-gif-v20190817

Regular Contributor | Gold Partner

@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!

New Contributor

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!  

Reply
0 Upvotes