topic Re: Report showing time a deal has spent in each stage as a line graph in Dashboards & Reporting

Report showing time a deal has spent in each stage as a line graph

BMiller9 — Mon, 06 Mar 2023 12:36:37 GMT

I'm looking to create a report to show how long a deal has spent in each stage, plus where it is in the pipeline.

I want the X-axis to be the deal stages, Y-axis to be time. Then each line will mark a new individual deal. Therefore it would show the fastest moving deals at the bottom of the chart and the slowest moving deals at the top.

Any ideas on how to do this? I'm struggling to even get each deal as its own line, and not just on the axis.

Thanks

Re: Report showing time a deal has spent in each stage as a line graph

karstenkoehler — Mon, 06 Mar 2023 12:41:16 GMT

Hi @BMiller9,

If you want to build this report from scratch and not use a report from the sales analytics, this is going to be complicated and it's not going to be possible to visualize durations for all stages in one report. You would have to create on report per stage.

The approach would be to set a date stamp when a deal enters a stage. You can then create calculation properties for each stage, calculating the time between 1 and 2, 2 and 3, 3 and 4, and so on.

You could then create a report visualizing this duration as per your requirements. The complicated part stems from populating this information historically. You would have to export the deal stage property history and re-import it into the date properties.

(I'm not aware of an easier way or any way that would work retroactively with the requirements that you shared, at least not natively within HubSpot.)

Best regards

Re: Report showing time a deal has spent in each stage as a line graph

BMiller9 — Mon, 06 Mar 2023 12:48:16 GMT

Thank you for the speedy reply.

So I actually have time spent in each deal stage as a property for every deal already, so it's now just working out how to use this in the report.

Re: Report showing time a deal has spent in each stage as a line graph

karstenkoehler — Mon, 06 Mar 2023 12:52:17 GMT

Hi @BMiller9,

In that case you would navigate to Menu > Reports > Reports > Create report > Single object > Deals, under Filters set whatever date range makes sense. In the Visualization tab, display the Deal name and your duration property.

If that doesn't work, can you please specify what exactly isn't working?

Best regards

Re: Report showing time a deal has spent in each stage as a line graph

BMiller9 — Mon, 06 Mar 2023 14:53:08 GMT

So I would ideally have the x-axis as "deal stages" and the y-axis as "time in stage", then each deal as an individual line on the graph. Then you'd be able to see deals that have moved quickly as lines at the bottom of the graph and slow moving deals at the top.
I'm struggling to get individual deals as the lines on the graph, by doing what I think you suggest just gets the deals as the x axis and then by switching deal name for deal stage you get the lines get grouped by deal stage.

Hopefully the images I attach help with this explanation, not sure if what I want exactly is possible through HS. Thanks for your time.

Re: Report showing time a deal has spent in each stage as a line graph

karstenkoehler — Mon, 06 Mar 2023 14:56:27 GMT

Hi @BMiller9,

In your screenshot, I can't see which information you currently have on the x-axis?

Best regards

Re: Report showing time a deal has spent in each stage as a line graph

BMiller9 — Mon, 06 Mar 2023 14:58:43 GMT

It's just deal names, would prefer to not disclose

Re: Report showing time a deal has spent in each stage as a line graph

BMiller9 — Mon, 06 Mar 2023 15:05:03 GMT

Then changing the left to Deal stage gives the deal stage names

Re: Report showing time a deal has spent in each stage as a line graph

karstenkoehler — Mon, 06 Mar 2023 15:03:37 GMT

Hi @BMiller9,

Understood, thank you. Unfortunately, I don't think that's possible. You would have to add a breakdown by deal name into the chart which HubSpot doesn't accept because you are already breaking down by multiple time in stage properties.

HubSpot's report editor is not that powerful.

Best regards