Site icon EASY2DIGITAL

Subscription Business Model Cohort Analysis for Retention Rate, ARPU, CAC, CLV

Louis Lu

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?

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.

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.

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

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

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.

eCommerce One to One Marketing Strategies for 6 Customer Segmentation

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.

FAQ:

Q1: What is cohort analysis?

A: Cohort analysis is a data analysis technique that groups users or customers into segments based on a common characteristic or behavior. It helps businesses understand how different groups of customers behave over time.

Q2: Why is cohort analysis important for eCommerce?

A: Cohort analysis is important for eCommerce because it allows businesses to track and analyze customer behavior over time. It helps identify patterns, trends, and insights that can be used to improve marketing strategies, customer retention, and overall business performance.

Q3: How can cohort analysis help optimize marketing campaigns?

A: Cohort analysis can help optimize marketing campaigns by providing insights into the effectiveness of different marketing channels, campaigns, and messaging. It allows businesses to identify which cohorts are most responsive to specific marketing efforts, enabling them to allocate resources more efficiently.

Q4: What metrics can be analyzed using cohort analysis?

A: Various metrics can be analyzed using cohort analysis, such as customer retention rate, customer lifetime value, average order value, conversion rate, and repeat purchase rate. These metrics provide valuable insights into customer behavior and can help identify areas for improvement.

Q5: What are some common use cases for cohort analysis in eCommerce?

A: Some common use cases for cohort analysis in eCommerce include analyzing customer retention, identifying high-value customer segments, measuring the impact of marketing campaigns, understanding customer churn, and predicting future revenue.

Q6: How can cohort analysis help improve customer retention?

A: Cohort analysis can help improve customer retention by identifying factors that contribute to customer churn. By analyzing cohorts, businesses can understand which groups of customers are more likely to churn and take proactive measures to retain them, such as targeted marketing campaigns or personalized offers.

Q7: What are the challenges of conducting cohort analysis?

A: Some challenges of conducting cohort analysis include data accuracy and consistency, selecting appropriate cohort periods, ensuring a large enough sample size for meaningful analysis, and interpreting the results accurately. It requires careful data collection and analysis to derive actionable insights.

Q8: How often should cohort analysis be performed?

A: The frequency of performing cohort analysis depends on the specific business needs and objectives. It can be performed monthly, quarterly, or annually, depending on the availability of data and the desired level of analysis. Regular analysis allows businesses to track customer behavior and make informed decisions.

Q9: What tools or software can be used for cohort analysis?

A: There are various tools and software available for conducting cohort analysis, such as Google Analytics, Mixpanel, Amplitude, and Kissmetrics. These tools offer features specifically designed for cohort analysis and provide visualizations and insights to facilitate data interpretation.

Q10: How can businesses use cohort analysis to drive growth?

A: Businesses can use cohort analysis to drive growth by identifying high-value customer segments, optimizing marketing campaigns, improving customer retention, and identifying opportunities for upselling or cross-selling. It helps businesses understand their customers better and make data-driven decisions to fuel growth.
Exit mobile version