I recently wrote a piece called Cohort Analysis for Dummies that covered the basics of what’s involved in a cohort analysis and what type of information you can extract from it.

In this follow-up, I’ll cover more sophisticated ways of extracting insight from different types of cohort analyses in order to report more information, and to optimize campaigns and funnels even further.

In our example, we took a look at a Lead-to-Conversion model web business (freemium) that had the following data set:

Example One: Lead-to-Conversion Cohorts

In our basic cohort analysis we assumed that P1 was April, and that a Conversion was a purchase or some other desired result on the original Lead. So, in the above example, there were 1,876 new Leads in April, of which 134 converted in April, 47 in May, and so on. After 7 months, we’re left with a Conversion Rate of 12.1%.

Now let’s try to dig deeper into understanding the quality of each cohort (months in this example) from a marketer’s perspective by adding more information.

Example Two: Customer Acquisition Costs

In this example we’ve now layered-in Spend data, which in turn allows us to calculate CPL (cost-per-lead) and CAC (customer acquisition cost). CPL and CAC are two important, yet very different metrics in this example. Higher up in your funnel comes CPL, which serves as an important early benchmark to determine how effectively you’re driving Leads. However, that may prove ineffective if you’re just driving Leads without converting them consistently. Here are some key takeaways:

• CPL increases quite a bit from P1 to P2 ($77.82 to $111.31). What gives? Possible reasons include:

         - Diminishing returns on Spend as it went from $145K to $243 in just one
         month. The new Spend has not been as effective.

         - There could be a “lag” effect whereby many Leads from that
         P2 Spend will come in the future – in fact we see CPL drop in June to
         $56.08.

• CAC in October is $2,288.89 – what the heck?!? As was pointed out in Cohort Analysis for Dummies and in Example One above, the October (P7) cohort needs more time to convert those Leads.

This last takeaway presents a problem. How can we project what CAC will be in the future if there is a long customer acquisition cycle? The truth is there isn’t one right answer, but a confident guestimate always arms a marketer with some data points.

For P7, we know that the Conversion % right now is 3.2%, but will certainly continue to rise with time. One assumption we could make would be to take the average conversion rates and assume that it will hold true in future months. That works for some businesses. However in most businesses, there will be drastic fluctuations in Conversion due to things like seasonality, competitive landscape, poor Leads funnel etc. In our example, P1 had a Month 1 conversion of 7.1% (134 out of the total 227 Conversions) while it fell drastically to 3.2% in P2. It’s all over the place so a marketer should err on the side of conservatism when making any sort of assumptions in this example.

An alternative method would be to choose a past cohort that looks similar, and use its conversion rate. You could choose the same period one year ago, or choose a cohort with similar attributes. Let’s do the latter.

P7 had a first month Conversion rate of 3.2%. If we look back, P2 also had a first month Conversion rate of 3.2%. Ultimately that P2 cohort is now at 9.1%. Let’s see what our CAC for P7 looks like if we assume that P7 Conversion rate will match P2.

Example Three: Projecting CAC For Leads That Have Not Yet Converted

Wow! P7 CAC in our projected view drops to $807.44. That’s much more in line with the ROI we were seeing in previous months. We also know that this is a conservative estimate, and hopefully the Conversion rate will be even higher than 9.1% with proper lead nurturing campaigns.

Finally, we now know how many Leads we’re driving, we know how effectively we’re converting them, and we know the ROI on our marketing efforts. What if we could measure how quickly we’ll payback our Spend? Your accounting guy will love you and your CEO will change her perception of marketing if you can figure that out! If you’ve gotten this far, it’s simple at this point. Let’s take our P7 projection, and assume that our business had a monthly recurring revenue (MRR) value of $99.99. We’d have the following:

Example Four: Payback on Marketing Spend

There you have it – you will have paid off your marketing Spend with real revenue in 8.1 months! Is that good or bad? It depends on many things such as cash position, competitive landscape, margins, growth targets etc. Most of these are outside a marketer’s scope.

However, one variable that is not outside of a marketer’s scope is CHURN. Losing customers = churn. If we never lose customers, that $99.99/month will theoretically keep coming in perpetuity. Your lifetime revenue value will be very, very, very, high. However, like most businesses, you will probably eventually lose customers for a variety of reasons. Without getting into too much detail, churn is a relatively easy measure (customers lost / total customers) and plays a crucial role in understanding customer lifetime revenue (CLTV).

In our final example, let’s determine CLTV by assuming Churn is 3% monthly and gross profit margin is 50% (ask your accounting guy).

It’s debatable whether this is a good or bad CLTV in our example above. Some argue that a CLTV > CAC x 3 is a good benchmark. It’ll depend on your business though.

And there you have it. We started with defining what a cohort is and looked at some examples of comparing performance between multiple cohorts. We’ve now evolved that simple data set to include marketing data such as payback, CAC, and CLTV for individual cohorts. Remember, a cohort can represent almost anything. In our examples we took the most common look by comparing periods of time against each other. You can take this methodology much further to compare almost anything.

Have some ideas? Please post them below or email me at neil@uberflip.com.