CRM

adearing
Participant

Associating Deals with Companies

SOLVE

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

0 Upvotes
2 Accepted solutions
MFrankJohnson
Solution
Thought Leader

Associating Deals with Companies

SOLVE

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

 

Note: Please search for recent posts as HubSpot evolves to be the #1 CRM platform of choice world-wide.

 

Hope that helps.

 

Be well,
Frank


www.mfrankjohnson.com

View solution in original post

joshua-paul
Solution
Top Contributor | Platinum Partner
Top Contributor | Platinum Partner

Associating Deals with Companies

SOLVE

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

View solution in original post

8 Replies 8
jennysowyrda
Community Manager
Community Manager

Associating Deals with Companies

SOLVE

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

0 Upvotes
adearing
Participant

Associating Deals with Companies

SOLVE

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!  

0 Upvotes
jennysowyrda
Community Manager
Community Manager

Associating Deals with Companies

SOLVE

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

0 Upvotes
adearing
Participant

Associating Deals with Companies

SOLVE

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

0 Upvotes
joshua-paul
Solution
Top Contributor | Platinum Partner
Top Contributor | Platinum Partner

Associating Deals with Companies

SOLVE

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

adearing
Participant

Associating Deals with Companies

SOLVE

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!  

0 Upvotes
MFrankJohnson
Solution
Thought Leader

Associating Deals with Companies

SOLVE

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

 

Note: Please search for recent posts as HubSpot evolves to be the #1 CRM platform of choice world-wide.

 

Hope that helps.

 

Be well,
Frank


www.mfrankjohnson.com
adearing
Participant

Associating Deals with Companies

SOLVE

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!

0 Upvotes