If you are running a subscription business model, whether 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. It’s because these data and insights 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 advocacy. 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. It’s 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. So you can deploy your marketing and sales strategy based on the analysis.
- What Is Cohort Analysis
- Subscription Plan & Performance By Plan
- Necessary Google Sheets Formula and Data Tool
- Avg. retention rate, churn rate, CAC, and CLV
What Is Cohort Analysis?
Cohort analysis is a kind of behavioral analytics. It takes the data from given eCommerce orders, website subscribers, or online game users rather than looking at all users as one group. And 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 segments 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 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, any business can offer multi-options and break them down into different pricing tiers. And the plan can be a monthly or yearly plan, etc. It 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 broken down by new, renewal, cancel, or other cases. They might especially occur in your business model. So you can keep track of how many closing users balance by a selected lookback window. And you can understand the total amount of users is increasing, performing flat, or unfortunately going down.
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. This includes the sign-up/refund/cancel data, membership unique ID, etc.
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 months and years for the purpose to calculate easily.
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
Vlookup () or Index/Match () Function for calculating new signup month_no
We need to calculate a new signup ID lifetime value over a time period. So we need to understand the lifespan of new signups month_no for each unique ID. Before that, first, you need to use, 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.
If you are interested in learning index/match, please check out this article.
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
Lifetime Value – if () and or () function
New signups 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)
When the raw data sheet is ready, you need to use the pivot table in Google sheets to visually present the lump sum numbers by lifespan and newsignup_monthno
Total members, renew members and unsubscribers are all here. So we can calculate the retention rate, which is renewal members/total members by month.
For easily spotting the change between months, you can use Google Sheets color scaler. And you can spot which month’s 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, and CAC data are in place. For more details, please check out this article.
We can reverse to calculate the CAC and gross profit per user. It’s because 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 and donate to the channel through PayPal (paypal.me/Easy2digital)
- 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
- You sign up for our weekly newsletter to receive Easy2Digital latest articles, videos, and discount code
- Subscribe to our monthly membership through Patreon to enjoy exclusive benefits (www.patreon.com/louisludigital)