Tracking average deal length from mid-funnel stage to close.
resolver
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!
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!
Karsten Köhler HubSpot Freelancer | RevOps & CRM Consultant | Community Hall of Famer
Tracking average deal length from mid-funnel stage to close.
resolver
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.
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!
Karsten Köhler HubSpot Freelancer | RevOps & CRM Consultant | Community Hall of Famer
Tracking average deal length from mid-funnel stage to close.
resolver
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?
Tracking average deal length from mid-funnel stage to close.
resolver
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?
Tracking average deal length from mid-funnel stage to close.
resolver
@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.
Tracking average deal length from mid-funnel stage to close.
resolver
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.
Tracking average deal length from mid-funnel stage to close.
resolver
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...