Report that counts number of companies per count of deals closed

[SRomankiw]

Hi, 

I've been asked for a report that shows the count of companies per count of deals closed, i.e. how many companies with 2 deals closed this year, how many with 3 closed deals etc etc. Is there a way to create a report that features a count of a count like this?

Thanks, 

Sally

[karstenkoehler · Accepted solution]

Hi @SRomankiw,

This is unfortunately not so straight forward and unintuitively complicated. It mainly has to do with the fact that you cannot specify buckets in bar charts – hence it's immediately workaround territory.

You might actually be better off to create a report that's a raw table of companies and deals, then export this report table and perform the analysis outside of HubSpot, e.g. in Excel or Sheets.

If you want to do it in HubSpot, here's what you'd have to do:

1. Create a custom property "Close date (year)" with dropdown options for each year you want to report on
2. Create a deal-based workflow that enrolls won deals, then branches by date (one branch per year), then sets the corresponding value in "Close date (year)"
3. Create a calculation property "Number of associated won deals" on the company object that counts the number of associated deals per company with a condition that corresponds to the property from step one (Close date (year) is any of 2024)
4. Create a custom company property "Number of closed deals (buckets)" with dropdown options 1, 2, 3, 4, 5 etc
5. Create a company-based workflow that enrolls companies based on "Number of associated won deals" (enable reenrollment), then branches by the number of associated won deals property (one branch per step: 1, 2, 3, 4, 5 etc), then assigns the corresponding value in "Number of closed deals (buckets)"
6. Create a report that uses that visualizes "Count of companies" by "Number of closed deals (buckets)"

Step 2 and 5 and also be done with calculation properties if that's what you prefer.

Best regards