Looking for the Excel win rate calculator? Here you go.
Looking for the Funnelcast demonstration application (on a fake dataset)? Here you go.
Looking for the free Funnelcast Essentials application (on your data)? Here you go.
How not to Estimate Win Rates: "Won/(Won+Lost)"
What if I asked you:
Among all deals that closed last quarter and were closed won, what percentage were won?
It's not a trick question, just a silly one. The answer is obviously 100%. It's an extreme example of a basic statistical error called selection bias. Now consider:
Among all deals that closed last quarter and were closed won or closed lost, what percentage were won?
That includes lost opportunities but still suffers from selection bias because lots of opportunities from the past might not be closed. The problem is that the set "all deals that closed last quarter" can be different from "all deals in the past" or "all deals from last quarter." The effective difference in estimated win rate might be small or large depending on your business and time interval involved.
Despite potential statistical problems, "win rates" are often computed like this. Here are some screenshots from a few B2B applications we found on the web that use this approach:
These examples require a bit of explanation. They are filtered to only include deals that were marked closed won or lost in the last year (or other period), and to exclude deals that didn't begin in the first sales stage. The plots show counts of deals lost in each stage, deals "converted" from each stage, and a final count of all deals won over the last year.
"Converted" is in quotes because, as you know, deals may proceed through sales stages non-linearly—they might skip stages, go backwards, etc. The "conversion" counts and percentages above assume an idealized sales process in which each opportunity proceeds step-by-step through each stage. Your actual sales process does not look like that.
The examples also assume good data hygiene that makes sure deals are systematically marked closed lost. If that is not the case the estimates will be wrong. For instance, your team may periodically clean up old opportunities. That will make this win-rate estimate volatile.
If you satisfy the assumptions in the examples then the ratio (won) / (won + lost) deal counts is one way to estimate win rate. It is an estimate of the chance that a deal will ever be won (with no bound on time limit)—an estimate of a long-term win rate. Because it's long-term, this kind of estimate is only reasonable when measured over a long time interval relative to sales cycle length. Over shorter intervals, this will likely not be a good estimate of win rate.
Another common way to miscalculate win rates is to consider closed-won deals (in a period) as a proportion of the total open pipeline. While this method avoids the selection bias issue, it is flawed because it mixes new opportunity generation with win rate. Here's a blog on that.
How to Estimate Win Rates: Time to Event
Instead of estimating the chance that a deal will ever be won, we usually want to know something more specific—like the chance a deal will be won in the next 90 days. Or the next year. Or month.
Once you realize that, it's the distribution of times til won—or more generally, times to event—that's really what's interesting in your data. For each won deal, the time to event is simply the time from when the deal was created to when it was won. For deals not known to be won, the time to (non) event is the time from deal creation til now. Time to event can be computed for any cohort of deals.
The distribution of times-to-event, along with the counts of deals involved, provides remarkable insight into the sales process. Using that information, you can estimate the chance that a deal will close in the next day, week, month, quarter, year, or any time interval. That estimate can be computed for any business cohort (sales team, product line, group). Use it to compare win rates across groups, and more importantly, to make inferences about differences between groups (confidence intervals, figure 3). You can make win-rate estimates by sales stage (figure 4).
The time-to-event approach is incredibly simple and (unlike the won/(won+lost) examples above) it makes almost no assumptions on your data. All it needs to know are when a deal was created and when it was won. It doesn't care about lost deals and is much less affected by bad data hygiene. Estimates can be tuned to be much more responsive to short-term win-rate trends than the examples in the last section. The time-to-event approach is also largely insensitive to changes (even dramatic changes) in deal generation rate.
It sounds like magic, but it's just statistics. Time-to-event models are part of an important field called survival analysis. Engineers use survival analysis to predict when a part will fail. Epidemiologists use survival analysis to evaluate the efficacy of medical treatments. Funnelcast uses it to help you sell more.
Our win rate calculator is an example Microsoft Excel workbook that computes win rates using time-to-event for your data.
Download the workbook (contains no macros), follow the instructions, paste your data in, and you will see your win rate chart. Compare win rates of various cohorts (e.g., new logos vs. expansion, or different geographies) by pasting their data separately. For those wanting more details, the workbook includes a recipe to construct your own calculator.
Much better yet, use the free Funnelcast Essentials service. This easy-to-use software extends these ideas in very powerful ways:
Forecasts, current quarter to next year
Actionable insights about when and where to engage to sell more
How much lead flow do you need to meet a plan
And much more…
We also have a free demo that lets you experiment on fake data. Check it out!
References
https://help.salesloft.com/s/article/Deals-Analytics-Conversion-Rate-Chart
https://www.clari.com/blog/new-from-clari-next-level-analytics-for-revenue-leaders/
https://www.aviso.com/blog/product-summary-updates-2021
https://www.insightsquared.com/blog/the-basics-of-sales-funnel-conversion-analysis