Need help with company report showing deals and activities in a period

SOLVE
JNewman
Contributor

I am trying to build a report that shows a count of deals closed and a count of activities undertaken against a set list of companies for a given period.  For example, in the last month Company A had three sales, no activities while Company B had no sales, three activities and Company C had no sales or activities.

 

The filters are messing with my head and I can't get the report to work - either the report shows only those companies with sales and activities in the period or it starts counting things outside the period.

 

Can anyone help me work this out?  I'm currently running exports and doing a manual pivot and I thought the new reporting tool might put a stop to that.

0 Upvotes
1 Accepted solution

Accepted Solutions
karstenkoehler
Solution
Hall of Famer

Hi @JNewman,

 

Got it, yes, that might indeed be a limitation of the report builder. In that case the solution might lie in creating two reports instead of one. One report would show deals created/closed/etc in a certain timeframe with activities from the same timeframe. The second report would show deals from the same timeframe but without any activity. Technically, these should be mutually exclusive and easy to read, even if they're side by side on a dashboard. Would that work for you?

 

I know, it's not ideal and it would be great to have this information in one report only. But sometimes with HubSpot, you need to choose your battles (time invested) wisely and this seems like a problem that is best solved by not solving it – but taking a different approach.

 

Best regards!

Karsten Köhler
Digital Marketer | HubSpot Freelancer | CRM Consultant

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

View solution in original post

8 Replies 8
karstenkoehler
Hall of Famer

Hi @JNewman,

 

This can be achieved with the new Custom Report Builder (Menu > Reports > Reports > Create custom report). In the Select data sources step, choose Companies for Primary data source, under CRM select Deals and under Sales select Sales activities.

 

Now, set up your Configure section like this, including deal Name, Count of deals and Distinct count (Activity type).

 

karstenkoehler_1-1622087331637.png

 

Under Filter, set filters by "Deal stage is any of Closed Won". Additionally, you could filter either by deal Create date or deal Close date. Alternatively, if you're not interested in the deal dates but in when sales activities for closed deals have happend, you could filter by Activity date.

 

Also, filter by company Name to only show deals from your list of defined contacts. If that list is longer, this tip might help.

 

The result would something like this:

 

karstenkoehler_0-1622087256526.png

 

For another visualization, choose these settings.

 

karstenkoehler_3-1622087614014.png

 

They should return a report like this:

 

karstenkoehler_2-1622087598218.png

 

Hope this helps!

Karsten Köhler
Digital Marketer | HubSpot Freelancer | CRM Consultant

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

JNewman
Contributor

I'm ok with all of that for overall deals and activities but when I want to see a given period, that's when the count seems to go wrong.  I want to see deals and activities in a month - but it only shows me companies where there were deals and activities, not when there was one or the other.  

0 Upvotes
karstenkoehler
Hall of Famer

Hi @JNewman,

 

I drafted these reports based on your initial description, "count of deals closed". You can of course remove the report filter for closed/won deals, you should then see activities for companies without deals but with activities.

 

You can see in my screenshots already that companies with deals are displaying, even if there isn't an activity.

 

Could you confirm that you set up the reports as outlined in my earlier post, removing the filter for "Deal stage is any of"? You will then see companies by the number of associated deals and number of activities.

 

Best regards!

Karsten Köhler
Digital Marketer | HubSpot Freelancer | CRM Consultant

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

0 Upvotes
JNewman
Contributor

 I do want to see the closed deals, correct.  For Company A, I want to see all deals closed in January and all activities that happened in January.   I started off a while back basically as you outlined but that gives me all deals and all activities.  It's the filtering it down to a time period that is the issue.

 

You mentioned "Under Filter, set filters by "Deal stage is any of Closed Won". Additionally, you could filter either by deal Create date or deal Close date. Alternatively, if you're not interested in the deal dates but in when sales activities for closed deals have happend, you could filter by Activity date."  I have tried that but when I set deals closed to January it will then count all activities (Jan, Feb, last year, whenever).   If I set a filter for the Activity date, I get all deals regardless of when closed.  If I filter both dates then I only get companies with activities and deals in January.

 

Does that make sense?

0 Upvotes
karstenkoehler
Hall of Famer

Hi @JNewman,

 

I'm not sure I'm following. If you set both a filter for a deal date property and an activity date property, you should see exactly what you're looking for: "all deals closed in January and all activities that happened in January". Could you explain what you're missing here? Which data points would be "outside" of those filters although you want to see them?

 

Best regards!

Karsten Köhler
Digital Marketer | HubSpot Freelancer | CRM Consultant

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

JNewman
Contributor

The problem I am having is that if I set a filter for all deals closed in January and another filter for activity dates in January the report only shows those companies where there was both a deal and an activity......so if there was a deal but no activity, that company falls off the report.

 

If I use OR in the filter then I get activities and or deals outside the January timeframe.  

 

I can't work out how to get the filters to work as I need them to work  😞

0 Upvotes
karstenkoehler
Solution
Hall of Famer

Hi @JNewman,

 

Got it, yes, that might indeed be a limitation of the report builder. In that case the solution might lie in creating two reports instead of one. One report would show deals created/closed/etc in a certain timeframe with activities from the same timeframe. The second report would show deals from the same timeframe but without any activity. Technically, these should be mutually exclusive and easy to read, even if they're side by side on a dashboard. Would that work for you?

 

I know, it's not ideal and it would be great to have this information in one report only. But sometimes with HubSpot, you need to choose your battles (time invested) wisely and this seems like a problem that is best solved by not solving it – but taking a different approach.

 

Best regards!

Karsten Köhler
Digital Marketer | HubSpot Freelancer | CRM Consultant

Beratungstermin mit Karsten vereinbaren


Did my post help answer your query? Help the community by marking it as a solution.

View solution in original post

JNewman
Contributor

I do have the two reports but it's hard to work with them because they have different companies listed depending on whether they had deals or not.  This seems like such an obvious report to be able to produce so I'm amazed that I can't.

 

I guess I'll keep doing my exports for now. 

0 Upvotes