Questions? Let me know in the comments happy to discuss. 🚀 Also, if you want to learn how to systematically scale your startup without ending up as one of the 90% of startups that fail, have a look at this ⇒ www.ericandrewsstartups.com/financeforstartups
Oh man this was excellent. Clear and concise. Even helped me understand the level of granularity(daily, weekly, monthly) I should approach while calculating CLV over time. Signed up for the waiting list to your course too. Cheers!
Hey Eric, very helpful, thanks. Question for you: If my product offers only 6 month and 12 month memberships, I'm assuming looking at one year wouldn't really give you a great picture of retention since each customer doesn't have the opportunity to churn every month. That being said, 1) How many years would you recommend looking back to get a good picture of the company's retention 2) Would you break these tables out separately for 6 month memberships and 12 month memberships? Any other insights on longer term contracts and how to analyze them would be extremely valuable, thank you. Edit: one more question. Since sales commission is technically an expense related to acquiring customers, would you put that expense into CAC and exclude it from gross margin calculations? In other words if I give 10% sales commission on a $100 product, I wouldn't deduct $10 from the $100 to get gross margin but would instead add $10 to my CAC, correct?
Im not having understaing in this content. I am wonder why retain if is no gain or interst in youir product? Maybe you product is not qualitaive and so people is bye the product. Maybe you shall have make in another country your product so it doesnt spread such a negative vibes for audience.
Two different options. First would be to create a separate cohort analysis for each product / price point and split them apart. I've seen these built with a filter at the top to switch between them. Second is to just use the net revenue retention cohort table which makes the price point sort of irrelevant and just shows you how well your business does at actually retaining total dollars. Hope that helps
@@eric_andrews thanks Eric ! I just created two different pivots, one with MRR and the other with churn, then combined them! Love your vids awesome content !
I belive he shall use names. Like January February March is better to understand for us how lovely it is around here to see the meanaing of retaining .
Well, the issue is that monthly churn rates only apply to businesses that sell their products on a monthly subscription. And even those businesses usually have churn rates that vary a lot for a customer that is 1 month old vs. 12 months old. So using the same "churn" every month is highly inaccurate. In addition, most businesses are not subscription based, but still retain a lot of customers. For example a social network, or a marketplace, or a consulting business, or a restaurant, or an ecommerce store - churn doesn't apply to them. The cool thing about retention is that you can use it for all business models, including SaaS, and get really accurate models.
I was about to shit in pants, when I understood your parrotism churn rate. It is siefe on the cherry liquior. Depends what your parrotism converastion is about. It is always good to copy the wanted and not wanted.
Great! Questions: Is the month 1, month 2 buyers, refer to the users purchased in the month or in/after the month? A problem I met is: some buyers came in March but didn’t do any purchases on month 1 then they came back in month 2. So, sometime the month 2 buyers could be higher than month 1.
This is really helpful, thank you! This focuses on new customers and the balance of digital acquisition spend as it relates to a customer's time with you which is eye opening. Two questions, this is a rolling twelve month view point, is there any point in looking at a longer time period? And then, do you have any videos on the health of repeat customers? What is the right balance of new to existing, etc? Thanks again!
On your first question, use the longest time periods you have data for. If you have 5 years of data, use it. That will give you a lot more info to plan your marketing. On your second question, the actual mix of new vs existing is completely irrelevant (if you are growing faster you'll have more new vs. slower, youll have less, so you can misinterpret that data easily), what matters is your LTV:CAC ratio which tells you how much money you'll make on a customer after marketing. If it's high grow as much as you can. Subscription business often have LTV:CAC ratios that are 5-10+, eComm in the 2-3 range (average ones), and marketplaces 1-3 starting out, and then 5-10+ later on.
I did nt got it becuze me come from Pakistan so I can not able to write in a english way. But I guess it is interesting in your busniess surrounding the retain of your call or in customers. I am also mental handicappated and I like to retain custonres that are in good quality bus and I also show good capability of understandinfg month of the year 12 month -start with december is called 0. Bery good retention , bery attractive learning book.
Great video, thanks. One thing still not clear to me: The model here is based around 12 months - but if 26% are retained in month 12, then we can assume some % will continue into month 13 and beyond. So how would you think about Customer LTV beyond month 12. Would you project forward (starting with 26% and decreasing by X%/month) beyond the 12 months to get a full account of Customer LTV?
thank you so much for the valuable content. Just a quick question, let's say an investor ask what is the retention rate of the business? From this cohort table, which is the representative one? Is it the cohort having the largest samples? (which is 26% in the net revenue cohort table)
Yes important question. If they ask, you can literally send them this table, and then specify how many times a typical customer buys over their lifetime (and the gross profit from that lifetime i.e. LTV). So, as a made up example: our typical customer buys 7 times over a 3 year period, CAC is $50, lifetime revenue is $256 and LTV is $174 and here is the cohort retention table. That is my much more instructive than "50%" which basically tells you nothing and barely makes sense
Hi Eric, thank you for sharing such valuable content. On application basis how do evaluate if the customer purchased on Ecommerce marketplace instead of our own website and if it was 1st or repeat, since MP dont share Customer data.
Without a customer ID like email or a way to track them, there is no way to calculate customer retention. You need to know who your customers are to track them. If the platform itself doesn't give you a cohort report, then it is impossible because they hide the data.
Thanks. Very concise. One question, if a customer after a few months purchases again, would his purchase be set on his month of the first purchase or is it a start again (as a new customer)
Great video, thank you!! I've been wondering about Day 0/Month 0. If a customer joins later in the month they get less days to experience the platform. So should we instead use a rolling window from the time a customer joins? Like if they joined on Apr 21, their Month 0 will be till May 20. If so how will we still group them in Apr cohort?
Here's my thinking - yes, you could theoretically build the report. You could go even further to build rolling weekly cohorts, or even cohortize individual days. Need to draw the line somewhere. I think over longer periods of time monthly just summarizes the information into an easier-to-understand analysis. "The May 2021 cohort had great retention over the first 18 months" vs. "the rolling date cohort of 18 months ago with the start and end date constantly changing had great retention", that second analysis is a little harder to deal with.
Hi Eric, nice video. I have a question, what is the difference between calculating retention rate by cohort vs by formula ((E-N)/S)*100%? as many websites explain. I compare these two methods the results are quite high different. Thanks.
Hey what an amazing session &thanks. Basically i have 2 years experience in raw business development like lead generation,market research, team handling , sales, customer success or relationship so my question on which kind of analysis i should focua as you mentioned in video can you let me know such kind of techniques please i am in genuine need .
Eric, hi! Need your help, I'm new in marketing and get not easy tasks. I need to calculate average client lifetime (not value), and CAC. Data that I have (all per week, 44 weeks total): installs, active users, retention rate (in %), weekly revenue and revenue cohort. Which formulas do I need to calculate ACL and CAC?
Is this for an actual business? Or just a case study? I would calculate CAC by looking at marketing spend / installs. For customer lifetime I would take either your revenue cohort / month 0 users, or look at customer lifetime by taking 1 / (1-retention rate i.e. churn rate). Here are some other videos of mind that might help you: CAC calculation: ua-cam.com/video/8WChmQuTeN0/v-deo.html Customer lifetime value: ua-cam.com/video/eHi875QuVcA/v-deo.html User retention ratios: ua-cam.com/video/YxJFzfXk5DU/v-deo.html
The avg number of months to profitability per customer seems like its an important metric, does it have a name? (Ie 3 months in your example ar the very end of the video)
Hi Eric! I'm doing someting similar but also trying to figure out how to model this when given a conversion rate and retention rate for users that converted from free to paid users.
Same, just need to decide what is the conversion event that you start the cohort table, either conversion to free users, or conversion to paid. I personally might build the table with paid users and then just track the free => paid CVR separately
Hey Eric, I've two questions 1: How frequently should we calculate NRR and report to senior leadership? 2: How to calculate NRR for multi year contracts?
1 - ideally monthly, but at a bare minimum quarterly 2 - if you are looking at cohorts, reference the initial purchase month to see the NRR of your oldest cohorts. If you are tracking business-wide metrics, you can use YoY. Just be clear with definitiiitions when you are presenting metrics.
Erick, amazing video, definetly subscribing and learning from you in the future. I wanted to ask you what way do you calculate your recurring customers that are first-time buyers in the actual month? What is a way of tracking it that your expertise would recommend?
@@eric_andrews no, it is an allacarte business. I am managing to get the information, but it is hard to get only new customers and their recurring purchases on following months. Im working on it 🫡
I was about to keep asking but I found the solution! A tough one but its done. if youre interested I can share it with you. My business is allacarte, that is why its so difficult.Thank you for your response btw!@@eric_andrews
Hey Eric, This is a superb primer on customer cohort analysis. Wanted to understand this for the first time and your video was super helpful. Liked and Subscribed. Keep up the awesome content.
Eric, thanks for the video. Various SAAS companies have different subscription plan - monthly, quarterly etc. How do we look at the retention rate? Also customers shifting from monthly to quarterly plan?
Great video Eric! If you wanted to continue this model into a multi-year scenario, would it simply be a matter of extending the X/Y axes from 0-11 to, say, 0-23, or 0-35, etc? You should be able to extend any given cohort out forever, no? For example, would it be feasible/practical for a new customer that arrived in an Aug-2018 cohort to map out to Feb of 2023?
Yes just extend it. Being able to see a 5 year wide cohort would be extremely interesting and would give you much more confidence about customer lifetime dynamics.
Bro.. you are a gem I want to go more with you.. I want to grab good knowledge in business analyst with excel so it's a bit of a request to advise me from where I can learn more from you?
Thanks Nikhil!! If you're looking to get some broad background on finance / business / marketing, I'd recommend watching my 3 statement financial model, the finance case study, KPIs for digital marketing, and the startup metrics and KPIs video....once you watch those 4 I think you will understand a lot of different concepts and I think you can decide where you want to focus next (maybe more deep financial modeling or maybe more e-commerce strategy), just leave me another comment and I'll try to respond 👍
Hi Eric, first of all thanks for the video, great explanation! I wonder though if the profitability you explain around 16:10 is correct? Wouldn't break even occur during month 2 as the profits cummulate vs the one time cac? 33+65+95=193>115
65,95.....264 are cumulative profits and not absolute profits. In month 1 actual profit is 65-33 = 32 and in 2nd month it is 95-65=30 and so on. Thus, total profit made from a customer is 123 till month 3
Hi Eric! Thank you for this great tutorial! I'm sorry if I sound ignorant asking this, If I have a shop with products that aren't purchased on a monthly basis, like shoes or appliances does this approach work by quarters for instance? (excuse my English I hope you could understand the point I'm trying to get to)
Hey, absolutely this type of analysis works for your business! You can look at quarterly, the main idea is you want to understand how much a customer will buy after their first purchase. This analysis will help you understand how often they purchase the second, third time etc, and when they do it. Perfect English as well btw 👍
Hello Eric thank you for the excellent teaching! My question is: when calculating LTV, the direct cost 35% (Gross Margin is 65%), what's the relationship between CAC and 35% direct cost, will any overlap exist?
The direct costs and CAC don't have any relationship. The 65% profit is basically the profit that comes back to the company as gross margin when they sell the product. With that 65%, they need to do the marketing. So the idea is that the CAC should be at a minimum less than the 65% GM LTV so that you know you will be profitable on the customer lifetime AFTER marketing expenses (CAC). Does that make sense?
Eric - thank you, this was super helpful! I was wondering, how would you typically go about interpreting monthly/annual retention from such analyses? Would you just take the average retention of all cohorts every single month and then do another average of those figures to get to an average monthly retention for the year?
Yes, it's a great question. Yes you could take an average (it's not totally incorrect) but it is still a pretty crude way of measuring it...here's why. So these retention tables sometimes eliminate the idea of "monthly" retention in the way you are thinking about it. So if you have very stable retention over time and across customer lifetimes (ex: 5% of customers return per month 6 months into their lifetime, and 5% return 3 years into their lifetime), well then yes an average is probably fine. But the issue is that usually retention behavior generally declines in a non-linear way, so taking averages of people in month 3 vs. year 3 of their lifetime ends up not telling you anything very useful because it eliminates the nuance of your customer ages (ex: 10% of customers are returning 3 months into their lifetime vs. 1% 3 years in). Averaging those numbers basically tells you nothing. Once you have the cohortized data split out by acquisition month, the best way to look at "monthly" retention is to compare the most recent month of data (the last cell in each horizontal row) across all the cohorts by comparing it to the vertical column (so that would compare June 2023 retention in every single individual cohort across the month 5, month 6, month 7, etc) so you could see if you had above average or below average retention in each cohort & lifetime month. So just look at the entire cohort table without averaging or combining anything, it will tell you the story. In terms of your retention, you would more want to be tracking your customer LTV over time (ex: wow look our oldest customers are purchasing 5 times not 4) so that you can calibrate your CAC to profitable customer acquisition. You might see that LTV is higher than you had previously estimated in your oldest cohorts because in June you had strong retention. That would be something to dig into. By the way overall % repeat revenue and your forecast for it are super important and you can build that forecast accurately with your cohort table! Anyway, hope that makes sense!
Hi Eric, how do you summarize data in the first table if the period is for more than 1 year? Lets say you have customer purchase data for 3 years. Do you summarize all the first purchases in April 2020, April 2021 and April 2022 (in year 1, 2 and 3) as one?
@@eric_andrews thanks for clarifying Eric! One more question, how do you go about getting the aggregate number of cohorts per month if you only have customer ID ( would you just use simple count formula via pivot table?). Also, say I have customer sign up date and first date of purchase of each customer. how do I find the average time of first purchase? Thanks for helping out!
@@eric_andrewsdo you have a work through video of how you went from the raw data (showing each customer’s purchase date etc) to the cohort table you used in this video. Would appreciate if you don’t und explaining please. Thanks
Hi Eric, can you give some use cases how these cohort tables are used monthly mobile subscribers data? How different would the retention percentages be?
User retention is also cohortized, but very often is tracked using the DAU/MAU ratio or even better power user curves. I also have a video on that here: ua-cam.com/video/YxJFzfXk5DU/v-deo.htmlsi=o1u_YZ2qtfxlXwL0
Hi Eric, how would you use this analysis to determine the customer churn rate? I am unsure if this is by taking the average across all the cohorts or how this is done.
Great stuff, am learning something from each video you make. The whole LTV and retention calculation is quite complex for a marketplace business. Perhaps a topic for your next video? Its not as straight forward as subscription where you have fixed formulas. @@eric_andrews
Hi @eric_andrews, I am a fan of your work and follow your videos. I have one question: At 13:05, could you clarify why we are dividing cumulative revenue by the initial set of customers? I was thinking it might make more sense to divide cumulative revenue by the retained customers, as the customers counted in the 11th month should reflect those contributing to the cumulative revenue for that month.
It depends on analysis goal - Dividing by initial set of customers or cohort size helps us understand the broad perspective of customers acquired in that cohort and can help us assess the quality of that cohort. You can divide by retained customers too that will give you different insights
Yes I have lots - here are a few! Unit economics for hardware, software, and e-commerce: ua-cam.com/video/AMKgcBzK7cg/v-deo.html 5 ways to increase your LTV: CAC ratio: ua-cam.com/video/rTP39v2s8dI/v-deo.html SaaS startup unit economics journey: ua-cam.com/video/o9ufogwDrwc/v-deo.html
@@eric_andrews why aren't you taking retention while calculating LTV? Since generally the formula of LTV is: Customer Lifetime Value = (Customer Value* x Average Customer Lifespan) *Customer Value = (Average Purchase Value x Average Number of Purchases)
Hi Eric, This is very informative. Could you please tell me how this can be calculated for each segment and sub-segments of business? More of an excel question, than a business question I guess.
Hi Eric, thank you for this insightful video. I wanted to know if this same methodology is applicable to a telecom company's mobile subscribers data? Is this how companies would do?
@@eric_andrews how would we identify seasonality for monthly mobile subscribers then? Would it be like a sharp increase for a certain month every year?
Eric, if you were trying to find the average customer retention at say, 4 months. Would it be the straight average of retention percentages at 4 months or would you use a weighted average, taking into consideration the size of each cohort?
Hi Eric, thanks for the great video. I have one question: What do I do when my customers don't buy regularly? For example it's possible that a customer makes an order in January and February, doesn't purchase anything in March but then decides to order again in April. How can I analyse this kind of behaviour?
Just look at the purchase stream cumulatively and watch how the lifetime revenue of the customer accumulates over time and with what timing. That's perfect data to cohortize.
Hey Eric! Thank you for creating this super video that is really helpful in my work today. But maybe can you help to create a video with sales related analysis? Thanks
You create it with customer order data. Usually a data scientist / analyst will create these reports. Probably the sales team would not be tracking this
Hey Bro, thanks a lot.. anyway, just want to ask about net revenue retention by cohort, how you find the $ number? cause i try to calculate number of customer x $50, the result are little bit different from yours. Thanks in advance
Hey...yep so you can't just multiply by $50, you need to use the actual revenue not an estimate. The reason is that net revenue per customer / month can change over time depending on discounts, pricing, refunds, etc....so better to use the real revenue than the total customers for the NDR. The rev numbers are in the spreadsheet you can just download in the description.
@EricAndrews1 This was the simplest and most impactful content i have come across till date. You are doing a phenomenal job. I just had one question in this case (monthly calculation) LTV will change each month how often do you recommend one can do this analysis in context to the product lifecycle. For eg. Hypercasual games can have a short product lifecycle etc. If you can shed some light on this it will be really helpful
Hey I appreciate the comment! For your question, I'm not 100% sure what the perfect metric is to measure if you are succeeding (% progress, completion of game, number of days active, etc), but obviously you are measuring lifetime in days not months. Search "power user curves by Andrew Chen" and you will see a very powerful way of measuring this type of the type of user activity I think you're talking about. Cheers
Can retention tables have repeat customers who use the products multiple times during the day D0/D1? (Users playing mobile games) or should it be unique?
Ya that could work for sure. Could be unique or not unique, just would want to specify when presenting it. But the original D0 number of users should always of course be unique when you calculate the customer lifetime usage (or whatever metric you're measuring)
@@sofianghazali6203 so it just depends on what you're measuring. If you're measuring the lifetime of the customer then probably would be based on unique because you care about time not frequency. But if you're measuring the lifetime activity or purchases for a customer then you would want to include all of their individual actions. Do you see what I mean? If you're just looking at retention then probably unique would make the most sense.
Hi Eric, Awesome video! I found this after I was trying to solve a for similar problem statement. While my approach was quite similar, instead of entering data in what looks like right-angled triangles with the base on top in your case, I made triangles with the base at the bottom. Eg. When I want to say that 18 people purchased for the first time in Feb, instead of Cell D12 in your sheet, I write it in Cell E12 and so on and so forth. That way, to find the total number of people purchasing in Feb, I can simply add values in column E instead of going sideways. While the logic seems to be the same, is the basic practice I followed flawed or might fail for a different scenario?
That way works as well, it's mathematically the same. In your way, it's easier to total the months but harder to compare the cohorts. Mine is harder to total months but easier to compare cohorts. For me the cohort comparison is where I'm focusing, but however you want is fine, cheers 👍
A lot of businesses will create cohort dashboards with live data so you can see them in real time. If you are doing it manually, I would do it maybe once a quarter (or even monthly) to make sure you aren't seeing retention drop offs.
@@eric_andrews so the ultimate aim of cohort analysis is to understand retention rates? Basically, can you give some example solutions that companies gain from this and how they use the end result of the cohort analysis?
@@shyamss2338Yes. They use them for financial forecasting and to understand what they can spend on marketing. If you know a customer will spend say $1000 over their lifetime with your company over a set period of time, then you know what you can invest in marketing to acquire that customer.
No issue. They would just appear in the april cohort but in june, just like we showed in the video. That's why these cohort tables are great, they show inconsistent buying behavior really clearly
Hard to help with this amount of information but just make sure you are using the original purchase month as your cohort, and excluding anyone who purchased after.
Questions? Let me know in the comments happy to discuss.
🚀 Also, if you want to learn how to systematically scale your startup without ending up as one of the 90% of startups that fail, have a look at this ⇒ www.ericandrewsstartups.com/financeforstartups
Hi, can retention of the subsequent month be higher than the previous one?
@vladimirdemidov6163 yes that is called revenue expansion or 100%+ net dollar retention and is common in SaaS
@eric_andrews thank you for the answer!
@eric_andrews could you please tell me how we should calculate average life span of the user?
Oh man this was excellent. Clear and concise. Even helped me understand the level of granularity(daily, weekly, monthly) I should approach while calculating CLV over time. Signed up for the waiting list to your course too. Cheers!
Really glad to hear it Rahul. Awesome you are on the waiting list as well, cheers!
Hey Eric, very helpful, thanks. Question for you:
If my product offers only 6 month and 12 month memberships, I'm assuming looking at one year wouldn't really give you a great picture of retention since each customer doesn't have the opportunity to churn every month. That being said,
1) How many years would you recommend looking back to get a good picture of the company's retention
2) Would you break these tables out separately for 6 month memberships and 12 month memberships?
Any other insights on longer term contracts and how to analyze them would be extremely valuable, thank you.
Edit: one more question. Since sales commission is technically an expense related to acquiring customers, would you put that expense into CAC and exclude it from gross margin calculations? In other words if I give 10% sales commission on a $100 product, I wouldn't deduct $10 from the $100 to get gross margin but would instead add $10 to my CAC, correct?
man this video is such a saviour.
Dude, I am starting a new job next week and your content has been a huge help.
You got it!
Im not having understaing in this content. I am wonder why retain if is no gain or interst in youir product? Maybe you product is not qualitaive and so people is bye the product. Maybe you shall have make in another country your product so it doesnt spread such a negative vibes for audience.
Oh after watching the video, I have to say that you opened up my thought process! I subbed and did the notification thingy!
Really appreciate that Harish!!! 🙏🙏 Cheers
Very well explained...one of the best cohort explanation...thank you buddy...!!
my pleasure, cheers!
This is one of the best videos on the interpretation of customer cohorts.
Eric, this is extremely valuable to me. Thank you so much for sharing and explaining what Customer retention and Cohort Analysis is to me.
You are very welcome!
Im Brasilian. I love your videos! Congratulations, you are the best!
Muito obridado amigo! I'm so happy they are helpful for you 😁
This is the best video about cohort analysis I ever see. thank you very much for sharing.
You are very welcome!
Though there are soo many example of cohort analysis using Tableau, Python, no one explained how to read the cohort table. Thank you Eric.
Haha, yes I noticed that, that's why I made this video!! Interpreting is usually harder than calculating 😎
Thank you. I'm trying to start my career in Digital Marketing and this is helpful.
You are very welcome!
thank you so much. so clearly explained. your pace and tone of speaking was so apt
Really glad to hear it, thanks!
Awesome vid thanks Eric! How would one approach it if each customer bought in a different MRR?
Two different options. First would be to create a separate cohort analysis for each product / price point and split them apart. I've seen these built with a filter at the top to switch between them.
Second is to just use the net revenue retention cohort table which makes the price point sort of irrelevant and just shows you how well your business does at actually retaining total dollars.
Hope that helps
@@eric_andrews thanks Eric ! I just created two different pivots, one with MRR and the other with churn, then combined them! Love your vids awesome content !
@@eric_andrews Hello Eric! Could you help me in understanding the question please? I'd really like to understand a new scenario. Thank you!
Hey Eric, use an if function to conditionally apply zero's or blanks to the cells below the diagonal for which months or sales hasn't happened.
That's a good idea I'll see if I can work that into my future cohorts
I belive he shall use names. Like January February March is better to understand for us how lovely it is around here to see the meanaing of retaining .
I’m having trouble building the first table from the dataset (a step back from what you’ve shown)!! can’t find the right formula!
Excellent video! Here, shouldn't we consider the churn rate of each month?
Well, the issue is that monthly churn rates only apply to businesses that sell their products on a monthly subscription. And even those businesses usually have churn rates that vary a lot for a customer that is 1 month old vs. 12 months old. So using the same "churn" every month is highly inaccurate. In addition, most businesses are not subscription based, but still retain a lot of customers. For example a social network, or a marketplace, or a consulting business, or a restaurant, or an ecommerce store - churn doesn't apply to them. The cool thing about retention is that you can use it for all business models, including SaaS, and get really accurate models.
I was about to shit in pants, when I understood your parrotism churn rate. It is siefe on the cherry liquior. Depends what your parrotism converastion is about. It is always good to copy the wanted and not wanted.
Great! Questions: Is the month 1, month 2 buyers, refer to the users purchased in the month or in/after the month? A problem I met is: some buyers came in March but didn’t do any purchases on month 1 then they came back in month 2. So, sometime the month 2 buyers could be higher than month 1.
Very explicit, informative, and concise. Thank you, Eric a bunch!
Glad it was helpful!
Great video, thanks for sharing!
How do you manage this same information when you have a 30 day free trial?
This is really helpful, thank you! This focuses on new customers and the balance of digital acquisition spend as it relates to a customer's time with you which is eye opening. Two questions, this is a rolling twelve month view point, is there any point in looking at a longer time period? And then, do you have any videos on the health of repeat customers? What is the right balance of new to existing, etc? Thanks again!
On your first question, use the longest time periods you have data for. If you have 5 years of data, use it. That will give you a lot more info to plan your marketing.
On your second question, the actual mix of new vs existing is completely irrelevant (if you are growing faster you'll have more new vs. slower, youll have less, so you can misinterpret that data easily), what matters is your LTV:CAC ratio which tells you how much money you'll make on a customer after marketing. If it's high grow as much as you can. Subscription business often have LTV:CAC ratios that are 5-10+, eComm in the 2-3 range (average ones), and marketplaces 1-3 starting out, and then 5-10+ later on.
So well done and explained succinctly. A lot of information, explained in a simple manner and totally got it!
Cheers Roni!
I did nt got it becuze me come from Pakistan so I can not able to write in a english way. But I guess it is interesting in your busniess surrounding the retain of your call or in customers. I am also mental handicappated and I like to retain custonres that are in good quality bus and I also show good capability of understandinfg month of the year 12 month -start with december is called 0. Bery good retention , bery attractive learning book.
Great video, thanks. One thing still not clear to me:
The model here is based around 12 months - but if 26% are retained in month 12, then we can assume some % will continue into month 13 and beyond.
So how would you think about Customer LTV beyond month 12. Would you project forward (starting with 26% and decreasing by X%/month) beyond the 12 months to get a full account of Customer LTV?
I think this should be done yearly because the next year can help you out to compare the different rates of spends.
Amazing explanation. Excelent insights! Thank you so much. I will definitely come back here to review the content!
Incredible and really clear, thank you !
Eric you're a lifesaver TY for this video!!!
thank you so much for the valuable content. Just a quick question, let's say an investor ask what is the retention rate of the business? From this cohort table, which is the representative one? Is it the cohort having the largest samples? (which is 26% in the net revenue cohort table)
Yes important question. If they ask, you can literally send them this table, and then specify how many times a typical customer buys over their lifetime (and the gross profit from that lifetime i.e. LTV). So, as a made up example: our typical customer buys 7 times over a 3 year period, CAC is $50, lifetime revenue is $256 and LTV is $174 and here is the cohort retention table. That is my much more instructive than "50%" which basically tells you nothing and barely makes sense
@@eric_andrews great! thanks again Eric.
@@baotoannguyen-n8s happy to help
Hi Eric, thank you for sharing such valuable content. On application basis how do evaluate if the customer purchased on Ecommerce marketplace instead of our own website and if it was 1st or repeat, since MP dont share Customer data.
Without a customer ID like email or a way to track them, there is no way to calculate customer retention. You need to know who your customers are to track them. If the platform itself doesn't give you a cohort report, then it is impossible because they hide the data.
Thanks. Very concise. One question, if a customer after a few months purchases again, would his purchase be set on his month of the first purchase or is it a start again (as a new customer)
It would appear again in the original cohort (not a new one), as that is a returning customer!
@@eric_andrews ok. thanks for the quick reply
@@Fertep 👍
Great video, thank you!!
I've been wondering about Day 0/Month 0. If a customer joins later in the month they get less days to experience the platform. So should we instead use a rolling window from the time a customer joins? Like if they joined on Apr 21, their Month 0 will be till May 20. If so how will we still group them in Apr cohort?
Here's my thinking - yes, you could theoretically build the report. You could go even further to build rolling weekly cohorts, or even cohortize individual days. Need to draw the line somewhere.
I think over longer periods of time monthly just summarizes the information into an easier-to-understand analysis. "The May 2021 cohort had great retention over the first 18 months" vs. "the rolling date cohort of 18 months ago with the start and end date constantly changing had great retention", that second analysis is a little harder to deal with.
Hi Eric, nice video. I have a question, what is the difference between calculating retention rate by cohort vs by formula ((E-N)/S)*100%? as many websites explain. I compare these two methods the results are quite high different. Thanks.
Excellent explanation
Hey what an amazing session &thanks.
Basically i have 2 years experience in raw business development like lead generation,market research, team handling , sales, customer success or relationship so my question on which kind of analysis i should focua as you mentioned in video can you let me know such kind of techniques please i am in genuine need .
This is super helpful. Thanks Eric!
THANK YOU! Clear and concise.
Glad it was helpful!
Thank you
This is super helpful. It gave me full understanding of the most practical way to calculate the retention matrix
glad to hear it
Very nice analysis and very nicely explained! Thank you. Keep up
Thanks, will do!
loved it, watched the whole video, stopped, started to follow along several times. so helpful thanks!
Awesome to hear
Very instructive Eric, see you at the next step
Eric, hi! Need your help, I'm new in marketing and get not easy tasks. I need to calculate average client lifetime (not value), and CAC. Data that I have (all per week, 44 weeks total): installs, active users, retention rate (in %), weekly revenue and revenue cohort. Which formulas do I need to calculate ACL and CAC?
Is this for an actual business? Or just a case study?
I would calculate CAC by looking at marketing spend / installs.
For customer lifetime I would take either your revenue cohort / month 0 users, or look at customer lifetime by taking 1 / (1-retention rate i.e. churn rate).
Here are some other videos of mind that might help you:
CAC calculation: ua-cam.com/video/8WChmQuTeN0/v-deo.html
Customer lifetime value: ua-cam.com/video/eHi875QuVcA/v-deo.html
User retention ratios: ua-cam.com/video/YxJFzfXk5DU/v-deo.html
The avg number of months to profitability per customer seems like its an important metric, does it have a name? (Ie 3 months in your example ar the very end of the video)
I thnk it is fairly normal to refer to it as "Payback time for CAC"
Hi Eric! I'm doing someting similar but also trying to figure out how to model this when given a conversion rate and retention rate for users that converted from free to paid users.
Same, just need to decide what is the conversion event that you start the cohort table, either conversion to free users, or conversion to paid. I personally might build the table with paid users and then just track the free => paid CVR separately
Hey Eric, I've two questions
1: How frequently should we calculate NRR and report to senior leadership?
2: How to calculate NRR for multi year contracts?
1 - ideally monthly, but at a bare minimum quarterly
2 - if you are looking at cohorts, reference the initial purchase month to see the NRR of your oldest cohorts. If you are tracking business-wide metrics, you can use YoY. Just be clear with definitiiitions when you are presenting metrics.
Erick, amazing video, definetly subscribing and learning from you in the future. I wanted to ask you what way do you calculate your recurring customers that are first-time buyers in the actual month? What is a way of tracking it that your expertise would recommend?
How do you know they are recurring? Are they subscription?
@@eric_andrews no, it is an allacarte business. I am managing to get the information, but it is hard to get only new customers and their recurring purchases on following months. Im working on it 🫡
I was about to keep asking but I found the solution! A tough one but its done. if youre interested I can share it with you. My business is allacarte, that is why its so difficult.Thank you for your response btw!@@eric_andrews
Hey Eric,
This is a superb primer on customer cohort analysis. Wanted to understand this for the first time and your video was super helpful. Liked and Subscribed. Keep up the awesome content.
Appreciate that prem, really glad to hear it
hi Eric, thank you for the video. and I've a question.
what's the different Customer Retention and Customer Stickiness?
Same thing
Eric, thanks for the video. Various SAAS companies have different subscription plan - monthly, quarterly etc. How do we look at the retention rate? Also customers shifting from monthly to quarterly plan?
Monthly to quarterly plan: might need a separate report, but still connected with the net dollar retention table
The explanation was spot on! Thank you so much!
Glad it was helpful!
Gran contenido. Me ha encantado y lo recomendarè.
me alegra mucho!!
Hey. Nicely explained. Can you suggest that the same CLV is applicable for those companies who businesses through dealers.
I would say yes I think it applies to any business that makes money and has customers that have the potential to pay them more than one time.
Great video Eric! If you wanted to continue this model into a multi-year scenario, would it simply be a matter of extending the X/Y axes from 0-11 to, say, 0-23, or 0-35, etc? You should be able to extend any given cohort out forever, no? For example, would it be feasible/practical for a new customer that arrived in an Aug-2018 cohort to map out to Feb of 2023?
Yes just extend it. Being able to see a 5 year wide cohort would be extremely interesting and would give you much more confidence about customer lifetime dynamics.
Bro.. you are a gem I want to go more with you.. I want to grab good knowledge in business analyst with excel so it's a bit of a request to advise me from where I can learn more from you?
Thanks Nikhil!! If you're looking to get some broad background on finance / business / marketing, I'd recommend watching my 3 statement financial model, the finance case study, KPIs for digital marketing, and the startup metrics and KPIs video....once you watch those 4 I think you will understand a lot of different concepts and I think you can decide where you want to focus next (maybe more deep financial modeling or maybe more e-commerce strategy), just leave me another comment and I'll try to respond 👍
Hi Eric, first of all thanks for the video, great explanation! I wonder though if the profitability you explain around 16:10 is correct? Wouldn't break even occur during month 2 as the profits cummulate vs the one time cac? 33+65+95=193>115
65,95.....264 are cumulative profits and not absolute profits. In month 1 actual profit is 65-33 = 32 and in 2nd month it is 95-65=30 and so on. Thus, total profit made from a customer is 123 till month 3
Hi Eric! Thank you for this great tutorial! I'm sorry if I sound ignorant asking this, If I have a shop with products that aren't purchased on a monthly basis, like shoes or appliances does this approach work by quarters for instance? (excuse my English I hope you could understand the point I'm trying to get to)
Hey, absolutely this type of analysis works for your business! You can look at quarterly, the main idea is you want to understand how much a customer will buy after their first purchase. This analysis will help you understand how often they purchase the second, third time etc, and when they do it. Perfect English as well btw 👍
@@eric_andrews Thank you Eric I appreciate!
Thank you for this amazing and beautiful beneficial information ❤❤
You are very welcome!!
Hello Eric thank you for the excellent teaching! My question is: when calculating LTV, the direct cost 35% (Gross Margin is 65%), what's the relationship between CAC and 35% direct cost, will any overlap exist?
The direct costs and CAC don't have any relationship. The 65% profit is basically the profit that comes back to the company as gross margin when they sell the product. With that 65%, they need to do the marketing. So the idea is that the CAC should be at a minimum less than the 65% GM LTV so that you know you will be profitable on the customer lifetime AFTER marketing expenses (CAC). Does that make sense?
Eric - thank you, this was super helpful! I was wondering, how would you typically go about interpreting monthly/annual retention from such analyses? Would you just take the average retention of all cohorts every single month and then do another average of those figures to get to an average monthly retention for the year?
Yes, it's a great question. Yes you could take an average (it's not totally incorrect) but it is still a pretty crude way of measuring it...here's why.
So these retention tables sometimes eliminate the idea of "monthly" retention in the way you are thinking about it. So if you have very stable retention over time and across customer lifetimes (ex: 5% of customers return per month 6 months into their lifetime, and 5% return 3 years into their lifetime), well then yes an average is probably fine.
But the issue is that usually retention behavior generally declines in a non-linear way, so taking averages of people in month 3 vs. year 3 of their lifetime ends up not telling you anything very useful because it eliminates the nuance of your customer ages (ex: 10% of customers are returning 3 months into their lifetime vs. 1% 3 years in). Averaging those numbers basically tells you nothing.
Once you have the cohortized data split out by acquisition month, the best way to look at "monthly" retention is to compare the most recent month of data (the last cell in each horizontal row) across all the cohorts by comparing it to the vertical column (so that would compare June 2023 retention in every single individual cohort across the month 5, month 6, month 7, etc) so you could see if you had above average or below average retention in each cohort & lifetime month. So just look at the entire cohort table without averaging or combining anything, it will tell you the story.
In terms of your retention, you would more want to be tracking your customer LTV over time (ex: wow look our oldest customers are purchasing 5 times not 4) so that you can calibrate your CAC to profitable customer acquisition. You might see that LTV is higher than you had previously estimated in your oldest cohorts because in June you had strong retention. That would be something to dig into.
By the way overall % repeat revenue and your forecast for it are super important and you can build that forecast accurately with your cohort table!
Anyway, hope that makes sense!
Thank you for this interesting video, very helpful in my marketing courses
you are very welcome!
Very interesting indeed. So good to retain according to the Marketing Parametrs,.
Brilliantly explained...
Fantastic video, thanks Eric!
Hi Eric, how do you summarize data in the first table if the period is for more than 1 year? Lets say you have customer purchase data for 3 years. Do you summarize all the first purchases in April 2020, April 2021 and April 2022 (in year 1, 2 and 3) as one?
No, you should just extend the table out wider and keep breaking every cohort apart my month.
@@eric_andrews thanks for clarifying Eric!
One more question, how do you go about getting the aggregate number of cohorts per month if you only have customer ID ( would you just use simple count formula via pivot table?).
Also, say I have customer sign up date and first date of purchase of each customer. how do I find the average time of first purchase? Thanks for helping out!
@@eric_andrewsdo you have a work through video of how you went from the raw data (showing each customer’s purchase date etc) to the cohort table you used in this video. Would appreciate if you don’t und explaining please. Thanks
Hi Eric, can you give some use cases how these cohort tables are used monthly mobile subscribers data? How different would the retention percentages be?
User retention is also cohortized, but very often is tracked using the DAU/MAU ratio or even better power user curves. I also have a video on that here: ua-cam.com/video/YxJFzfXk5DU/v-deo.htmlsi=o1u_YZ2qtfxlXwL0
Hi Eric, how would you use this analysis to determine the customer churn rate? I am unsure if this is by taking the average across all the cohorts or how this is done.
Different data, just released a video on that here: ua-cam.com/video/fC_gLwyAvMo/v-deo.htmlsi=xv7FMMkBzS45Sm4E
Great stuff, am learning something from each video you make. The whole LTV and retention calculation is quite complex for a marketplace business. Perhaps a topic for your next video? Its not as straight forward as subscription where you have fixed formulas. @@eric_andrews
This is GOLD, thanks for this!
Thank you so much Eric , great explanation !
Hi @eric_andrews,
I am a fan of your work and follow your videos. I have one question:
At 13:05, could you clarify why we are dividing cumulative revenue by the initial set of customers? I was thinking it might make more sense to divide cumulative revenue by the retained customers, as the customers counted in the 11th month should reflect those contributing to the cumulative revenue for that month.
It depends on analysis goal - Dividing by initial set of customers or cohort size helps us understand the broad perspective of customers acquired in that cohort and can help us assess the quality of that cohort. You can divide by retained customers too that will give you different insights
Hi Eric, nice video. What about we take LTV to CAC ratio as well? Can you make a separate video on it?
Yes I have lots - here are a few!
Unit economics for hardware, software, and e-commerce: ua-cam.com/video/AMKgcBzK7cg/v-deo.html
5 ways to increase your LTV: CAC ratio: ua-cam.com/video/rTP39v2s8dI/v-deo.html
SaaS startup unit economics journey: ua-cam.com/video/o9ufogwDrwc/v-deo.html
@@eric_andrews why aren't you taking retention while calculating LTV?
Since generally the formula of LTV is:
Customer Lifetime Value = (Customer Value* x Average Customer Lifespan)
*Customer Value = (Average Purchase Value x Average Number of Purchases)
@@muhammadmuneebkhanafridi154 these cohorts show the same information you are summarizing but with more detail
Hi Eric, This is very informative. Could you please tell me how this can be calculated for each segment and sub-segments of business? More of an excel question, than a business question I guess.
Data should be aggregated based on the customer ID and the month of the first time they purchased.
Hi Eric, thank you for this insightful video. I wanted to know if this same methodology is applicable to a telecom company's mobile subscribers data? Is this how companies would do?
It is applicable absolutely
@@eric_andrews how would we identify seasonality for monthly mobile subscribers then? Would it be like a sharp increase for a certain month every year?
Great content, thanks for sharing
It is clever because he knows book. He know to apply the book, in all the circumstances and he also see the future for the retain customers.
I really don't get the idea of this cohort and retention analysis. please can you break it and show your dataset. thanks
If you spell proper nouns with a capital letter , than maybe you could have understood from the first second.
Eric, if you were trying to find the average customer retention at say, 4 months. Would it be the straight average of retention percentages at 4 months or would you use a weighted average, taking into consideration the size of each cohort?
Yeah I mean I think a waited average would probably make the most sense if it's not too hard to do
Hi Eric,
thanks for the great video.
I have one question: What do I do when my customers don't buy regularly?
For example it's possible that a customer makes an order in January and February, doesn't purchase anything in March but then decides to order again in April. How can I analyse this kind of behaviour?
Just look at the purchase stream cumulatively and watch how the lifetime revenue of the customer accumulates over time and with what timing. That's perfect data to cohortize.
@@eric_andrews thanks for the quick answer!
This was really helpful! Thanks Eric :)
Glad to hear it!
Hey Eric! Thank you for creating this super video that is really helpful in my work today. But maybe can you help to create a video with sales related analysis? Thanks
My pleasure! What exactly do you mean by sales analysis?
Can you do the same for non-recurring revenue?
@@greymatterdecay yes
hey, Eric! Is there any chance we can make a private lesson regarding cohort analysis and my task?
How could I contact you?
Great stuff- thanks for sharing
Thank you, really useful and informative info
Glad to hear it Tony 👍👍
great explanation !
Hi Eric, If I am working in a company, what is the source of these data normally? from sales team? thank you
You create it with customer order data. Usually a data scientist / analyst will create these reports. Probably the sales team would not be tracking this
A lot of companies don't track this data so you may find that the report doesn't exist yet. In that case, you want to ask someone to create it
Hey Bro, thanks a lot.. anyway, just want to ask about net revenue retention by cohort, how you find the $ number? cause i try to calculate number of customer x $50, the result are little bit different from yours. Thanks in advance
Hey...yep so you can't just multiply by $50, you need to use the actual revenue not an estimate. The reason is that net revenue per customer / month can change over time depending on discounts, pricing, refunds, etc....so better to use the real revenue than the total customers for the NDR. The rev numbers are in the spreadsheet you can just download in the description.
@@eric_andrews understood, thank you very much 😀
@EricAndrews1 This was the simplest and most impactful content i have come across till date. You are doing a phenomenal job. I just had one question in this case (monthly calculation) LTV will change each month how often do you recommend one can do this analysis in context to the product lifecycle. For eg. Hypercasual games can have a short product lifecycle etc. If you can shed some light on this it will be really helpful
Hey I appreciate the comment! For your question, I'm not 100% sure what the perfect metric is to measure if you are succeeding (% progress, completion of game, number of days active, etc), but obviously you are measuring lifetime in days not months. Search "power user curves by Andrew Chen" and you will see a very powerful way of measuring this type of the type of user activity I think you're talking about. Cheers
@@eric_andrews Supremely delighted will surely check this out. Again you are doing an amazing job🙌
@@kapilbonde3090 awesome thanks 👍
Can retention tables have repeat customers who use the products multiple times during the day D0/D1? (Users playing mobile games) or should it be unique?
Ya that could work for sure. Could be unique or not unique, just would want to specify when presenting it. But the original D0 number of users should always of course be unique when you calculate the customer lifetime usage (or whatever metric you're measuring)
@@eric_andrews sounds good! I’m currently measuring the retention rate, so unique or not it doesn’t matter right?
@@sofianghazali6203 so it just depends on what you're measuring. If you're measuring the lifetime of the customer then probably would be based on unique because you care about time not frequency. But if you're measuring the lifetime activity or purchases for a customer then you would want to include all of their individual actions. Do you see what I mean? If you're just looking at retention then probably unique would make the most sense.
This was helpful , but need to know how to get to that table ❗
Can this help in finding out the loyal customer for a service or a store ?
Yes, would work for any business.
Mind=Blown!!
Hi Eric, Awesome video! I found this after I was trying to solve a for similar problem statement. While my approach was quite similar, instead of entering data in what looks like right-angled triangles with the base on top in your case, I made triangles with the base at the bottom. Eg. When I want to say that 18 people purchased for the first time in Feb, instead of Cell D12 in your sheet, I write it in Cell E12 and so on and so forth. That way, to find the total number of people purchasing in Feb, I can simply add values in column E instead of going sideways. While the logic seems to be the same, is the basic practice I followed flawed or might fail for a different scenario?
That way works as well, it's mathematically the same. In your way, it's easier to total the months but harder to compare the cohorts. Mine is harder to total months but easier to compare cohorts. For me the cohort comparison is where I'm focusing, but however you want is fine, cheers 👍
Hi Eric, how often would companies do cohort analysis (especially Telecom companies)?
A lot of businesses will create cohort dashboards with live data so you can see them in real time. If you are doing it manually, I would do it maybe once a quarter (or even monthly) to make sure you aren't seeing retention drop offs.
@@eric_andrews so the ultimate aim of cohort analysis is to understand retention rates?
Basically, can you give some example solutions that companies gain from this and how they use the end result of the cohort analysis?
@@shyamss2338Yes. They use them for financial forecasting and to understand what they can spend on marketing. If you know a customer will spend say $1000 over their lifetime with your company over a set period of time, then you know what you can invest in marketing to acquire that customer.
Hi Eric, How do I sort my data to get the cohort table?
Thanks, your videos are excellent!
Hey Leon - good to see you in the comments again! Thanks for the support and glad it was helpful
Hi Eric, I am unable to download your excel template using the given link. Where can I find the right link? Thanks in advance.
Bro,how to create this table,can you please explain me
What if a customer of apr cohort didn’t buy in may but then come back subscribe again in June?.. how do calculate retention
No issue. They would just appear in the april cohort but in june, just like we showed in the video. That's why these cohort tables are great, they show inconsistent buying behavior really clearly
Great video!! 👍
Thanks Hans, cheers!
Thanks, ou explain it so to the point, very helpful
Hi,
Is It suitable for OTT platform businesses like disney+, prime m, Netflix?
Absolutely. Subscription businesses in particular are obsessed with retention cohorts!!
@@eric_andrews thank youuuu !
@@deeptisharma4272 😁
Thanks, where can I download excel file?
Hi I’m struggling to get the customer cohort data from sql, my numbers are constant for all the months, any help would be really appreciated. Thanks
Hard to help with this amount of information but just make sure you are using the original purchase month as your cohort, and excluding anyone who purchased after.
@@eric_andrews Hey! Thanks for your response, I actually managed to figure it out finally! Was doing it the wrong way!
@@aakanksharai4559 awesome!!!