Tracking average deal length from mid-funnel stage to close.

[amangels]

I'm looking to build a report that tracks the average deal length from a mid-funnel stage to close.

For context, our pipeline is built a differently for where we are at as a company, with SQL as one of our stages mid-funnel. I'd like to track our deal age starting at the SQL stage.

Is there a way to build this into a report, either with a custom calculation or other workarounds? Thanks!

[karstenkoehler · Accepted solution]

Hello @amangels,

Out of the box, there isn't any default report for this, unfortunately.

Going forward, you could create a custom date property, for the mid-funnel stage. Using a deal-based workflow, you would use "Set property value" and set the date property to the date of the step when the deal enters this stage. You can then create a calculation property to calculate the duration between the new custom property and the Close date. Lastly, it would be easy to create reports on this calculation/duration property, e.g. under Menu > Reports > Reports > Single object > Deals and pick the KPI visualization to visualize the average of this calculation/duration property. This would only work going forward as the workflow cannot backdate.

For a solution that works historically, you could go into Menu > Reports > Reports > Single object > Deals. As Data fields, you should find "Time in" property for each stage. If you pick a KPI visualization, you can choose to show the average time spent in each stage. You would have to do the math and add up the durations from the mid-funnel stage to the close date.

Let me know if you have any follow-up questions!

[karstenkoehler · Accepted solution]

Hello @amangels,

Out of the box, there isn't any default report for this, unfortunately.

Going forward, you could create a custom date property, for the mid-funnel stage. Using a deal-based workflow, you would use "Set property value" and set the date property to the date of the step when the deal enters this stage. You can then create a calculation property to calculate the duration between the new custom property and the Close date. Lastly, it would be easy to create reports on this calculation/duration property, e.g. under Menu > Reports > Reports > Single object > Deals and pick the KPI visualization to visualize the average of this calculation/duration property. This would only work going forward as the workflow cannot backdate.

For a solution that works historically, you could go into Menu > Reports > Reports > Single object > Deals. As Data fields, you should find "Time in" property for each stage. If you pick a KPI visualization, you can choose to show the average time spent in each stage. You would have to do the math and add up the durations from the mid-funnel stage to the close date.

Let me know if you have any follow-up questions!

[amangels]

Hi @karstenkoehler , thanks for the response. I'll give it a try. One other question in the meantime: if we have multiple pipelines for the different verticals we sell into, would I need to create a new workflow within each pipeline? Or can I select all SQL stages from the different pipelines?

[karstenkoehler]

Hi @amangels,

One date property across pipelines is sufficient. You can later break down the average by pipeline.

[amangels]

Thanks @karstenkoehler this is super helpful and unlocking a lot for me.

Is there a way to track the age of a deal from the SQL timestamp to current day, if the deal has not closed yet? Would I need to create a filter or some if/then logic to then differentiate between the two once the deal does close?

[karstenkoehler]

Hi @amangels,

If the end date isn't known, HubSpot cannot calculate a duration, unfortunately. You would have to exclude deals without close date from your report.

[amangels]

@karstenkoehler great, thank you for that. Unfortunately the running duration is a big part of why we want to create this report, so we may need to re-think our pipeline and/or track this in a more manual way.

I appreciate all the help!

[JakeKnight · Accepted solution]

Our solution to this is that we have a couple of workflows to work togther that run daily to simply increment a "Time in XYZ Stage" and then copies that value to a "Time in Current Stage." So every day at 5 am, all of our deal aging goes up by one. Our interest is more in the time spent Not in closed won. 

I recently saw someone had a plugin for HS that would populate at property with "Today's Date." You could run that daily and then the calculation property that Karsten mentioned would still work with today's date at the relative date until it was closed won. 

Hope that helps. 

[amangels]

Thanks, that ended up working for me.

[JakeKnight]

The nice thing about this is that you have a Time in XYZ Stage value stored historically for every stage. You can run a lot of averaging reports that breakdown how long deals are sitting in various stages per rep, team, etc. 

The overall time in current stage thing is powerful too. We use the Tags system to mark deals at +7 Days, + 14 Days, etc...