You’ve just engaged with a hot prospect. You're confident, the conversation is flowing, and everything seems aligned. You can almost taste the victory. But then, weeks turn into months, and the deal is still hanging.
And all of a sudden, the prospect stops replying (or you get an email bounce as they changed job) and you’re back at square zero.
What went wrong?
Engaging with prospects quickly generally leads to a higher close rate. The same principle applies to opportunities. The faster you move a deal to close, the higher the win rate.
But there’s more to this story.
You might debate the correlation versus causation here. Are slow deals just due to reps not closing their old ones?
But in this post, I’ll ignore that question and instead focus on helping you understand the current reality regarding your business's close rate per cohort.
This post aims to help you answer the question: How does deal age impact your close rate?
We’re only looking at deals in a closed stage, i.e., won or closed deals.
In this example, there is a steep increase in deals lost the longer they stay open. This is often the case, but here, the increase is very steep and a reason for concern.
Either our data is highly corrupted by poor CRM hygiene, or we might be dealing with a broken sales process.
If I had the historical data (in this case, I don’t), I’d probably also look at different periods to understand whether this is new or “business as usual”.
What conclusions can we draw from the above chart?
While one report is rarely enough to draw definite conclusions, you might be facing one of the following situations.
Your sales process is not aligned with how your customers buy
Your sales process drags on for too long. Inefficiencies abound, from a lack of multi-threading to not having a joint business case with the customer. Add in some general skill gaps, and you’ve got a recipe for misaligned priorities and getting ghosted by the prospect.
You have poor CRM and sales process hygiene
Another common issue is that sales reps leave deals open for too long. This makes your sales data unreliable. Keeping the pipeline up to date is often the easiest task.
If this isn't done, you can be sure other issues are present.
Poor deal hygiene also creates a poor customer experience. This includes a lack of follow-ups, forgetting to re-engage with old customers, and leaving a big black box if a sales rep decides to leave the company.
Your forecasting is probably highly unreliable
If the above points are true, I’ll bet you also have an issue with forecasting.
The good news is that cohort analysis can help coach your team. It can also help you assess whether the forecast is likely to happen so you can avoid awkward conversations with the leadership team about why the forecasts are always way off.
For example, if most of the commits this month come from deals 300-400 days old, you might have a case of happy ears or lazy and inaccurate forecasting.
So, how do you address this? As there isn’t an out-of-the-box way to do this report in HubSpot (to my knowledge), we need to get creative.
Step 1: Decide on and create cohorts
Start by looking at your average and median time to close (based on closed deals only) to know how to distribute your cohorts.
In our case, the average was around 230 days, and the median was 210 days. I added six cohorts here, focusing on the shorter time frames. You can increase or reduce the number of cohorts as needed.
Step 2: Create the calculation for the cohorts
As all deals are already closed, we can use the “Days to close” default property to group the deals.
Note: If you want to do some cohort analysis on open deals, create a custom property. This is because it’s based on the close date, not the actual age of the deal.
For example, if I create a deal today and set the close date to three months into the future, the deal's age will be three months.
Here’s a screenshot of the property and the formula so you can do a quick copy-pasta 🍝 instead of typing manually. Then, edit the time frames based on your average sales cycle.
Formula:
if(([properties.days_to_close] < 25), 25, if(([properties.days_to_close] < 50), 50, if(([properties.days_to_close] < 100), 100, if(([properties.days_to_close] < 200), 200, if(([properties.days_to_close] < 300), 300, if(([properties.days_to_close] > 300), 400, 0))))))
Step 3: Create the report
When you have the calculated property, it’s straightforward to set up the report.
Put the calculation on the X-axis and the count of deals on the Y-axis.
Then go to Chart Settings and select Percentage under the Stacked dropdown.
Use filters to include only closed deals and a reasonable time frame. In this case, we chose to exclude deals closed within a day, but this should be judged on a case-by-case basis.
