cohort analysis

If you are running a subscription business model, whatever there is a similar model like yours or there is no model like it on the internet. Understanding your customer feedback about your product and service is critical because these data and insight highly reflect the trend of granular revenue and profit forecasting in this business. And with the domino effect, directly impacts your decision of branding and marketing options from paid to social and email drive sales and customer advocate. In the coming years, I believe if all the positive P&L results from actual performance and forecasts are coming up, all are thanks to this good beginning.

So the question is how to analyze the subscription business model performance. In this article, I would walk you through how to leverage cohort analysis that might be just in the Google Sheets, for the purpose to understand the subscription’s actual retention and churn rate performance. By the end of this piece, you can learn the beauty of cohort analysis for your subscription business, and deploy your marketing and sales strategy based on the analysis.

What Is Cohort Analysis?

Cohort analysis is a kind of behavioral analytics that takes the data from given eCommerce orders, website subscribers, or online game users rather than looking at all users as one group, it breaks them into related customer segmentation for analysis. So in a subscription business model, you can sell different memberships with a free trial and paid options. So you can break it down into different segmentation and see how the membership performs over a time period.

Cohort analysis is a tool to measure user repurchase, renewal, and son re-engagement activity over time. It helps to know whether user engagement is actually getting better or worse over time so that you can understand the user lifetime value from the current content, products, or services. You can forecast CAC in the coming sale and marketing campaign activities, based on the current customer lifetime value.

Subscription Plan & Performance By Plan

Any business can only provide one option for customers, or based on customer segmentation needs, offer multi-options and break down into different pricing tiers. And the plan can be a monthly or yearly plan, etc, which can depend on your customer purchase behavior and user patterns. Whatever subscription product approach your business adopts, basically each plan must have a performance tracker is broken down by new, renewal, cancel, or other cases that especially occur in your business model. So you can keep track of how many closing users balance by a selected lookback window, and understand the total amount of users are increasing, performing flat, or unfortunately going down.


cohort analysis

Necessary Google Sheets Formulas and Tool

For organizing the raw data on the cohort analysis sheet, basically, you must have some member baseline data at least that includes the sign up/refund/cancel data, membership unique ID, etc.

cohort analysis

If you don’t have these 3 column data in your database, it is not feasible to analyze an accurate customer retention rate, churn rate, CAC, and CLV.

  • Month () and Year () Function in Google Sheets.

For calculating the customer lifetime value, data points are critical such as start date, end date, etc. If the date format shows like this 1/1/2017, you need to split it into month and year for the purpose to calculate easily.

cohort analysis

  • Number of Months

This data mainly tells us how many months a customer uses the membership, which starts from a month and year, renews in the process, and cancels at the end.

Number of months = (joined year – 2017)*12 + joined month

cohort analysis

  • Vlookup () or Index/Match () Function for calculating newsignup month_no

As we need to calculate a newsignup ID lifetime value over a time period, so we need to understand the lifespan newSignup month_no for each unique ID. Before that, first, you need to use vlookup (), or match/index () to identify the same customer ID and feed in the month_no when she or he joined the membership. Here I copy and paste all newb2c data into a new sheet, and vlookup the newb2c only back to the original raw datasheet.

cohort analysis

If you are interested in learning index/match, please check out this article.

  • Lifespan

Now we know from month_no when a unique ID is created and unsubscribed, so using the unsubscribed month_no minus the unique ID creation date is equal to the membership lifespan

Lifespan = month_no – newsignup_monthno

cohort analysis

  • Lifetime Value – if () and or () function

Newsignup and renewal members are the data points to calculate the customer lifetime value, so if your plan is priced at US$99, you can create a column and use if() and or() to separate paid and unsubscribed or refund.

Lifetime value = IF(or(D2=”newb2c”, D2=”RENEWB2C”),99,0)

lifetime value

  • Pivot Table

When the raw data sheet is ready, you need to use pivot table in Google sheets to visually present the lump sum numbers by lifespan and newsignup_monthno

pivot table

  • Color Scaler

Total members, renew members and unsubscribers are all here. So we can calculate the retention rate, which is renewal members/total members by month.

color scaler

For easily spotting the change between months, you can use Google Sheets color scaler and you can spot which month campaigns are better and worse.

Avg. retention rate, churn rate, CAC, and CLV

Previously we shared a formula for how to calculate a membership customer lifetime value if lifespan, retention rate, CAC data are in place. For more details, please check out this article.

eCommerce One to One Marketing Strategies for 6 Customer Segmentation

We can reverse to calculate the CAC and gross profit per user after we know a membership plan avg. retention rate and users by month over a lifespan period from this cohort analysis table.


We assume a monthly membership fee is a flat number, so we can calculate monthly revenue. We can say this membership is representing a group of customers, so the customer lifetime value is equal to the membership lifetime value. Thus, we can finally calculate the average revenue per user (ARPU).

From here, if we aim to break even or earn profit regarding new user acquisition, CAC should be equal to or less than the average revenue per user. And if we aim to increase the CAC for the purpose of reaching out to the higher funnel customers, retention rate incremental is a key.

So easy, right? I hope you enjoy reading Cohort Analysis for Retention Rate, Churn Rate, CAC, CLV In A Subscription Business Model. If you did, please support us by doing one of the things listed below, because it always helps out our channel.

  • Support my channel through PayPal (
  • Subscribe to my channel and turn on the notification bell Easy2Digital Youtube channel.
  • Follow and like my page Easy2Digital Facebook page
  • Share the article to your social network with the hashtag #easy2digital
  • Buy products with Easy2Digital 10% OFF Discount code (Easy2DigitalNewBuyers2021)
  • You sign up for our weekly newsletter to receive Easy2Digital latest articles, videos, and discount code on Buyfromlo products and digital software
  • Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (

By Louis Lu

Growth Hacker & Digital Marketer, with a proven record of over 11 years experience in 20+ Asian markets, and 25,000+ connections in Linkedin

Leave a Reply

Your email address will not be published. Required fields are marked *