Carl Friedrich Gauss was a German mathematician born in 1777, and a child prodigy who made breakthrough mathematical discoveries while still a teenager. His contributions to history go far beyond statistics, but this post will focus specifically on the normal (or Gaussian) distribution, a continuous probability distribution we can apply to pipeline ratios to predict sales performance probability in a given period. This statistical technique also known as the “bell curve” can enable the alignment of marketing investments to achieve sales quotas.
In his article about required pipeline to achieve bookings targets, VC and startup metrics guru Tomasz Tunguz states that a 20% conversion rate of SQLs to customers implies a 5x pipeline to bookings ratio, and concludes “the required pipeline to enable each salesperson to achieve their quota is again just the inverse of their lead to close rate.”
But what if the CEO asks, “what is the probability we will meet or beat our bookings plan this quarter, so I don’t miss another quarter and have to explain that to the board?”
Gauss can help us answer this question confidently using statistics he dreamed up, provided we have captured the appropriate historical data.
Let’s first look at the historical pipeline data required to calculate pipeline ratios and predict bookings as a function of sales opportunity pipeline. If we are in week 6 of the current quarter, and want to know the expected end-of-quarter bookings from current quarter pipeline -- we need to know the pipeline in week 6 of previous quarters, and the bookings that resulted in those quarters. Thus, data snapshots of historical pipeline at specific times during past quarters are essential to truly understand the value of pipeline in the current quarter. A key point -- if you start capturing pipeline data now, it will be 6 months from now until you have 2 quarters of data to calculate an average pipeline ratio! Salesforce does not have a feature to gather historical pipeline values, so a business process is needed to structure and save pipeline data.
Pipeline snapshot data requirements and process to predict quarterly bookings:
Snapshot timeframes for each quarter (For example end of weeks 2, 6 and 10)
Ideally a sales process to scrub pipeline data prior to snapshots
Pipeline amount at each snapshot timeframe
Closed Won amount at each snapshot timeframe
End of quarter bookings
Geographic segmentation (optional)
By rep segmentation (optional)
A sample 4-quarter pipeline snapshot dataset might look like:
Pipeline Ratio in the table above is calculated as Pipeline ($M) / Incremental Bookings ($M). If pipeline is measured at any time other than the beginning of the quarter, business already booked at the time of snapshot (i.e. Closed Won) is subtracted from End of Quarter Bookings to calculate the Pipeline Ratio. Pipeline Ratio for each quarter tells us the precise historical pipeline multiple of bookings resulting from that pipeline. In the example above for Q1, Q2, Q3, and Q4 the actual pipeline ratios were 4.0, 3.0, 6.0 and 3.0, respectively. The average of these ratios is (4+3+6+3) / 4 = 4.0, but the ratio ranged from 3.0 to 6.0.
Using a simple average pipeline ratio of 4.0, we would expect $4M in pipeline at week 6 in the quarter to result in $1M of incremental bookings. Statistically, $1M in bookings is the average expectation for for $4M in pipeline … but the historical data also includes variance. Enter Gauss. The historical pipeline ratios have an average of 4.0, but also have a standard deviation. So, if your job in sales or marketing was on the line based on achieving company booking goals for the quarter, pipeline was 4.0 times remaining bookings needed to make the quarter in week 6, and the CEO asks: “what is the probability we will meet or exceed our bookings goal for the quarter?” What is a mathematically sound answer? Assuming the pipeline ratios are normally distributed, the data-driven answer to the CEO’s question is: “We have a 50% chance of meeting or exceeding bookings plan based on historical pipeline data.”
A critical concept in viewing pipeline value from Gauss’s perspective is that to have a high likelihood of achieving company bookings goals, the pipeline ratio for a given timeframe must be more than the average, and how much more can be calculated using Gaussian statistics.
The probability of achieving bookings plan as a function of pipeline ratio utilizes the standard deviation of historical ratios, in addition to the average or arithmetic mean. This can be visualized as how narrow or wide the “bell” is in the bell curve. The example of an average pipeline ratio of 4.0 from (4.0, 3.0, 6.0, 3.0) has a higher standard deviation and wider “bell” than 4.0 from (4.0, 3.9, 4.2, 3.9). In the former case, a pipeline ratio of 4.3 this quarter yields a 58% probability of meeting or beating bookings plan, where a ratio of 4.3 in the latter scenario has a 98% probability of achieving company plan.
The standard deviation of (4.0, 3.0, 6.0, 3.0) is 1.4, and the standard deviation of (4.0, 3.9, 4.2, 3.9) is 0.1. Some statistical guideposts for this technique are that a pipeline ratio 1 standard deviation above the mean implies a 84% chance of meeting or beating bookings plan, and pipeline ratio 1 standard deviation below the mean has a 16% chance of making plan.
The probabilities are calculated using the cumulative distribution function for the normal distribution, which is characterized in the following graphic with standard deviation on the x-axis, and probability on the y-axis:
Per Wikipedia, in probability theory and statistics, the cumulative distribution function (CDF) of a real-valued random variable X, or just distribution function of X, evaluated at x, is the probability that X will take a value less than or equal to x. In this application of Gaussian statistics to pipeline ratios, if the actual pipeline ratio for the current quarter (pipeline/bookings) when the quarter is completed is less than the pipeline ratio observed during the quarter, the company meets or beats booking plan. This is because a lower actual pipeline/bookings ratio means more bookings per dollar of pipeline due to an inverse mathematical relationship.
The concepts and mathematics here may seem complex and convoluted, but they could, for example, inform your business 6 weeks into the quarter that a 4.3 pipeline ratio translates to a 58% versus 98% chance of a successful quarter.
To calculate the probability of meeting or exceeding quarterly plan for a rep, region or company, the following data points are needed:
Quota for current quarter
Current quarter bookings at time of prediction (e.g. end of week 6)
Current quarter pipeline at time of prediction
A series of historical pipeline ratios measured at time of prediction (e.g. past 4 quarters)
With this handful of numbers and a few Excel formulas, we can calculate the probability of making the quarter. The Excel formulas needed are =AVERAGE(), =STDEV(), and =NORMDIST(), which respectively perform arithmetic average, standard deviation, and the cumulative distribution function.
Below is a simple Excel model for the case where 4 historical quarters of pipeline ratio are known:
More than 4 quarters of historical data is usually preferred, but 2 quarters is a good starting point. A rolling 12 quarters of historical data is my personal favorite, as quarterly seasonality can be investigated.
I had the opportunity to backtest this technique with 12 quarters of historical bookings and pipeline data for multiple product lines with thousands of data points. The predicted probability of achieving quota compared quite accurately to actual results. In particular, product lines with pipeline ratios more than 1 standard deviation above the mean exceeded the predicted 84% probability of making plan at +1 SD, and product lines with ratios less than 1 standard deviation below the mean made quota less than 16% of the time, which is the probability -1 SD below the average ratio.
Understanding the probabilistic implications of pipeline data can be used in many ways including resource allocation, decision making, and optimization. For example, a data-driven process with feedback loops could be implemented to choose marketing investments that will deliver sufficient sales pipeline to achieve company bookings goals with the probability desired by executive leadership.
Would you like to know your company’s likelihood of success this quarter? Gauss can help!