In part I of this three-part series I introduced the idea of an inverted funnel whereby marketing can derive a required demand generation budget using the sales target and historical conversion rates. In order to focus on the funnel itself, I made the simplifying assumption that the company’s new ARR target was constant each quarter.
In part II, I made things more realistic both by quarterizing the model (with increasing quarterly targets) and accounting for the phase lag between opportunity generation and closing that’s more commonly known as “the sales cycle.” We modeled that phase lag using the average sales cycle length. For example, if your average sales cycle is 90 days, then opportunities generated in 1Q19 will be modeled as closing in 2Q19 .
There are two things I dislike about this approach:
- Using the average sales cycle loses information contained in the underlying distribution. While deals on average may close in 90 days, some deals close in 30 while others may close in 180.
- Focusing only on the average often leads marketing to a sense of helplessness. I can’t count the number of times I have heard, “well, it’s week 2 and the pipeline’s light but with a 90-day sales cycle there is nothing we can do to help.” That’s wrong. Some deals close more quickly than others (e.g., upsell) so what can we do to find more of them, fast .
As a reminder, time-based close rates come from doing a cohort analysis where we take opportunities created in a given quarter and then track not only what percentage of them eventually close, but when they close, by quarter after their creation.
This allows us to calculate average close rates for opportunities in different periods (e.g., in-quarter, in 2 quarters, or cumulative within 3 quarters) as well an overall (in this case, six-quarter) close rate, i.e., the cumulative sum. In this example, you can see an overall close rate of 18.7% meaning that, on average, within 6 quarters we close 18.7% of the opportunities that sales accepts. This is well within what I consider the standard range of 15 to 22%.
Previously, I argued this technique can be quite useful for forecasting; it can also be quite useful in planning. At the risk of over-engineering, let’s use the concept of time-based close rates to build an inverted funnel for our 2020 marketing demand generation plan.
To walk through the model, we start with our sales targets and average sales price (ASP) assumptions in order to calculate how many closed opportunities we will need per quarter. We then drop to the opportunity sourcing section where we use historical opportunity generation and historical time-based close rates to estimate how many closed opportunities we can expect from the existing (and aging) pipeline that we have already generated. Then we can plug our opportunity generation targets from our demand generation plan into the model (i.e., the orange cells). The model then calculates a surplus or (gap) between the number of closed opportunities we need and those the model predicts.
I didn’t do it in the spreadsheet, but to turn that opportunity creation gap into ARR dollars just multiply by the ASP. For example, in 2Q20 this model says we are 1.1 opportunities short, and thus we’d forecast coming in $137.5K (1.1 * $125K) short of the new ARR plan number. This helps you figure out if you have the right opportunity generation plan, not just overall, but with respect to timing and historical close rates.
When you discover a gap there are lots of ways to fix it. For example, in the above model, while we are generating enough opportunities in the early part of the year to largely achieve those targets, we are not generating enough opportunities to support the big uptick in 4Q20. The model shows us coming in 10.8 opportunities short in 4Q20 – i.e., anticipating a new ARR shortfall of more than $1.3M. That’s not good enough. In order to achieve the 4Q20 target we are going to need to generate more opportunities earlier in the year.
I played with the drivers above to do just that, generating an extra 275 opportunities across the year generating surpluses in 1Q20 and 3Q20 that more than offset the small gaps in 2Q20 and 4Q20. If everything happened exactly according to the model we’d get ahead of plan and 1Q20 and 3Q20 and then fall back to it in 2Q20 and 4Q20 though, in reality, the company would likely backlog deals in some way  if it found itself ahead of plan nearing the end of one quarter with a slightly light pipeline the next.
In concluding this three-part series, I should be clear that while I often refer to “the funnel” as if it’s the only one in the company, most companies don’t have just one inverted funnel. The VP of Americas marketing will be building and managing one funnel that may look quite different from the VP of EMEA marketing. Within the Americas, the VP may need to break sales into two funnels: one for inside/corporate sales (with faster cycles and smaller ASPs) and one for field sales with slower sales cycles, higher ASPS, and often higher close rates. In large companies, General Managers of product lines (e.g., the Service Cloud GM at Salesforce) will need to manage their own product-specific inverted funnel that cuts across geographies and channels. There’s a funnel for every key sales target in a company and they need to manage them all.
You can download the spreadsheet used in this post, here.
 Most would argue there are two phase lags: the one from new lead to opportunity and the one from opportunity (SQL) creation to close. The latter is the sales cycle.
 As another example, inside sales deals tend to close faster than field sales deals.
 Doing this could range from taking (e.g., co-signing) the deal one day late to, if policy allows, refusing to accept the order to, if policy enables, taking payment terms that require pushing the deal one quarter back. The only thing you don’t want to is to have the customer fail to sign the contract because you never know if your sponsor quits (or gets fired) on the first day of the next quarter. If a deal is on the table, take it. Work with sales and finance management to figure out how to book it.