How to Conduct a Cohort Analysis for Lead Gen Optimization

By now you’ve been hard at work for quite some time generating high quality leads for your SaaS sales team, and are already implementing basic optimizations to ensure that you’re maximizing leads and pipeline value. You’re looking at lead sources and identifying the most valuable channels in which to invest your lead gen budget — or are you?

As a software-as-a-service company with a solution that requires careful decision and implementation time, your sales cycles can be long, often six months to a year in length and sometimes even longer. As a result, the true value of your investment in each lead generation channel can be difficult to understand or forecast based on your raw lead generation metrics.

Historical lead yield values, which we utilized in our channel budget model in my last blog, often fail to reflect the current value of your lead generation efforts due to marketing changes or environmental shifts (increased competition, customer expectations, etc.). This is where cohort analysis of pipeline and revenue development comes to the rescue.

What Is Cohort Analysis?

Cohort analysis, covered here in depth as applied to customer churn and revenue retention forecasting by Christopher Janz, can also be effectively applied to lead generation in order to measure the effect of campaign optimizations on pipeline / revenue generation. Simply stated, a cohort is group or segment of users sharing a specified measurement criteria. In this case we can use monthly campaign optimization cycles as cohorts (grouping users by the month in which they first reached your website via a lead gen channel) and then identify how campaign changes impact pipeline performance.

Qualified pipeline generation is usually a great leading indicator of channel lead quality and can greatly aid in optimization decision making in as close to real-time as possible. With this type of analysis you aren’t left waiting until your cohort qualifies and closes all of its potential pipeline or stuck using outdated values to inform your lead generation channel strategy.

Cohort Analysis in Action

For the sake of simplicity in building an example, we will work under the assumption that all lead generation channels will develop at an equal pace, moving through the sales funnel over the same average timeframe. Customer revenues are recurring, and since your product is extremely sticky and beloved by users, lifetime values are high; for the sake of this example, we’ll use four years as our average customer retention period regardless of acquisition.

You know from historical data that pipeline (potential annual deal revenue) develops from qualification to close over 6 months on average, with 50% of total cohort pipeline qualified in the first month, and the remaining 50% qualified evenly over the following five months. Based on your sales team’s past results, you can expect qualified pipeline to convert into annual revenue at a 33% rate. With these considerations in mind, you can utilize the following model to forecast channel revenue and spend efficiency:

Notice the Budget, Avg. CPL, and Leads column values? We’re borrowing these numbers from the model in my last blog. For Qualified Pipeline values you will need to supply your own channel pipeline data from the data in your CRM system. In Base you can find this data either by creating a smart list filtering on qualified deals (giving you a clear grid view of pipeline progression by deal name), or via the one-click pipeline development report.

To find your Total Forecasted Pipeline (cell G2), multiply your first month’s Qualified Pipeline by the quotient of one divided by the first month’s Pipeline Development percentage.

To find your Expected Lifetime Revenue value (cell H2) for each channel in this cohort, multiply the Total Forecasted Pipeline value for each channel by your expected close rate (in this scenario 33%), multiplied by the Average Customer Retention (in years).


Forecast Return on Ad Spend (cell I2) for each channel in this cohort by dividing Expected Lifetime Revenue by the Budget for each channel. We’ll use whole number values for this metric in this example for the sake of visual simplicity. Sum up these values at the bottom of the table. We will use this sum value in the next step of calculations to help balance the budget for the next month of advertising based on expected pipeline generation and revenue conversion.


Now solve for the optimal Adjusted Budget (to guide the next period’s budget planning) by multiplying the individual channel ROAS value by the quotient of the total cohort budget divided by the sum of the ROAS values.


Drag this formula down throughout the rest of the cells in your table to get your Adjusted Budget allocation based on the Expected Lifetime Revenue for each channel, which you can see in the completed table below.


With this type of model you can account for differences in pipeline generation across channels when optimizing your channel mix to maximize the effectiveness of your lead generation efforts. So get to work and start feeding your sales team the best possible leads!

Until Next Time

Later in this series we’ll discuss how testing new, unproven channels should fit into your lead gen strategy, as well as how to structure and measure a basic advertising test, and much more! In the meantime, here are a few additional cohort resources to check out:

How to Crush Your SaaS Marketing with Cohort Analysis – by Hubspot
SaaS Metrics 2.0: A Guide to Measuring and Improving What Matters – by Matrix Partners
Using the Cohort Analysis report in Google Analytics – by Mark @ Megalytic

Want to get the excel model we utilized in this blog to plug-and-play with your own data values? Click here to download the interactive excel worksheet

Related Articles

Digging Into Lead Generation Channel Strategy

Sales Performance

Digging Into Lead Generation Channel Strategy

Your company is growing, your product is awesome, customers love it and churn is low. You’ve raised some substantial funding and are ready to accelerate your growth. Awesome - let’s dig in.

Elliot Kolt

Elliot Kolt

October 26, 2016

Sales Lead Generation: The Who, What and Where

Sales Performance

Sales Lead Generation: The Who, What and Where

So you want to grow your SaaS product’s revenue, and you need to get more leads for your sales team in order to continue your rapid growth trajectory. Just one problem: the leads aren’t coming your way.

Elliot Kolt

Elliot Kolt

September 14, 2016

Subscribe to the Sell Blog

Be the first to hear about product updates, as well as sales, productivity and CRM strategies.