Speaker 1: Hey everyone, Eric here. In this video, I'm going to teach you the real way we look at customer retention and customer lifetime value in the startup ecosystem, which is using cohort analysis. This analysis is one of the first things a venture capitalist would ask for when thinking about investing in a business. Okay, let's get started. So I'm going to give you a little background on customer cohorts first to understand what they are. And the first thing I want to say is that the only way to understand customer retention is using cohorts. Do not waste your time doing some other type of analysis, because this is how we actually do it. So the great thing about cohorts is that they show customer attention for both subscription and non-subscription businesses. And non-subscription businesses, a lot of people really struggle when trying to analyze the retention of their customers. So this works for you. So how do cohort analysis tables work? So a cohort analysis table organizes the data by the initial purchase month of customers, and then looks at the stream of subsequent purchases through time based on that initial purchase month. So let me show you here. So this is a customer cohort table. And so the cohort is January. This cohort is March. This cohort is June. And so how it works is that, let's go to April. The cohort is April because the initial purchase month was April. So all the customers that purchased for the very first time in April, we look at in this row. So 21 people purchased, and we call this month zero, because this is really day zero of our relationship with them. It's the first time they purchased. So one month later, those 21 people purchased again. Then two months later, those initial 21 purchased people, 20 of them purchased again. So one person decided not to purchase. Three months later, 18 of the initial 21 purchased again. Four months later, but we're always referencing back the initial 21 people in the April cohort. So it's looking at the stream of subsequent purchases through time based on the initial purchase month. Also, I want to just help you understand conceptually something about how this table works. How many purchases did we have total in April? 21? No. We have the people who purchased for the first time in April, but then we have the March people. They purchased in the second month of their relationship with our customer, of our company, is April, March, April. The February people, the third month, is April, February, March, April. January, fourth month, so the real total number of customers we had that purchased from us in April was 85, you can see right here. So to understand one month in time, you actually have to go diagonally through the table, but we're more focused on the cohort, which is the initial purchase group, and how much did they purchase afterwards. So with this organization of the data, we can see retention and customer lifetime value, and we're going to analyze all of that right now. So our setup is that we're a SaaS business with a $50 monthly subscription, and people are billed month to month, so each month, any number of people can cancel that no longer want to continue buying our product. So now that I explained to you the initial customer cohort table, and by the way, this type of table, it's possible to do it in Excel. Typically with startups, you have a data scientist who can write SQL or pull the information out of your database, and they can organize it in these tables. So this is pretty much industry standard, and your data needs to get organized like this. Sometimes there's like plugins and applications. Google Analytics even has some customer cohorts. If you look around a bunch, there's an area of Google Analytics that has it. So you can figure out how to get your data into this format. So let's look at the actual customer retention. So we're going to write a formula. We're going to take that initial cohort, and we're going to divide it by the same month, but we're going to lock the column. So dollar sign in front of the D. I tapped F4 to do that. So what is this going to do? Let's just multiply this over. So first off, that formula allows us to just continue to look and reference the initial purchase month, and take the current month and divide it by the initial cohort, because we're talking about the January cohort. So what this basically shows us is that 100% of customers made it to month two with us, and then only 86% of the initial 35 customers got to month two with the business. And so over time, you see that the customers, some percentage of them starts to drop off, but 12 months into their relationship with the company, you still have 26% of your customers purchasing still. So that gives you a sense of what is the retention of that January cohort. But the reason why we break it down each month individually is because the quality of the customers that you acquire is probably going to vary over time. So let me just clean this up. So all of these areas with zeros, it's zeros because this is some time period in the future, so there's no information. So if you think about this, let me just clean this up, and then I'll explain to you. So let's say December is the most current month we have data for. So what about January? Well, January hasn't happened yet, so we can't calculate that. So December for the December cohort is here, but December for the November cohort is here. And so all these different cells represent December for all these different cohorts. But we don't have January yet, so these tables always are going to look diagonal if you have the data that is from the most recent month. So this data shows us how each individual cohort has sort of different retention metrics. So in general, it seems like they're pretty similar, but like this July cohort, we still had 91% of people purchasing, whereas this January cohort, we had 74% of people purchasing. And it could be that maybe in July, we did some sort of event or we did different marketing and we acquired a different type of profile or a different type of person that's stickier. So you want to be able to see that to try to understand your data. So this is a customer retention table. This is what a lot of the VCs are after, and people running businesses are monitoring closely. But what about financial data? And first off, before I move on, I just want to say that if you're finding this content valuable, please like this video and subscribe to my channel right now so that you don't miss any of my new videos. So this is a table called, I'm calling it Net Revenue by Cohort, and it's the exact same information as this, 35 customers, but this just represents the value of the purchases that they made. So these 35 customers purchased, generated $17.50 in revenue in month zero. In month one, $17.50, month two, et cetera, et cetera, et cetera. So you see that you have your initial cohort, and then you have your stream of subsequent purchases over time. But now we're actually looking at the revenue, not just the individual people. Because the revenue is more relevant to our business than individual people. Because people don't always purchase the same amount of something. A lot of times they purchase less, a lot of times they purchase more. So let's do the exact same, and we can actually just copy this formula, because it's just going to reference this table, and it'll give us the same recalculation of this data. So you can see here that it's doing the same thing. And so this is called Net Dollar Retention, or Net Revenue Retention. And this is an extremely, extremely important metric. A lot of times they call it Net Dollar Retention, NDR. And you'll see this referenced a lot in startups. So first thing I want to highlight is you'll see that the NDR here actually goes over 100%. So how could it be that March, where you had 22 people, them in their first and second month of their relationship with the company, you have the same amount of people purchasing, but the revenue is more. So the revenue is actually more than the initial month. Well, it could be that we offered those 21 or 22 people some sort of deal. So we said, hey, if you buy this additional product, we'll give you a discount, whatever. And we actually expanded the amount of money that they're spending with the company. And that's called Expansion Revenue, and most SaaS companies are trying to get Expansion Revenue. And a lot of SaaS companies sometimes see their Net Dollar Retention exceeding 150% because they're able to grow their relationship with the customers. Because the customers are having such a good experience, they want to buy more of the product. So if you see this, it's not an error. It's actually a really positive thing. But other than this, this is a Net Revenue Retention table, and it shows you something similar to the Customer Retention table. But now what I really want to try to do is figure out what is the Cumulative Lifetime Revenue for each cohort, for each customer, and then what is the Customer Lifetime Value? So let's walk through this. So Cumulative Lifetime Revenue is pretty easy if you have the revenue like this. So what we're going to do is we're going to take that first month, and then we're going to take the initial month, and we're going to add the second month. So those initial 35 customers, if you look at their first two months of purchasing, cumulatively, they've generated $3,500. And by month 11, $14,000. Even though in their first month they only purchased $17.50, they've purchased $14,000 by month 11. So again, let's just copy this down, and then delete the months which don't make sense, which are months in the future, which don't exist yet. And then we can just delete some of this. I bet someone's going to tell me in the comments some faster way to do this. Please do. Okay, so what can we see? Now we can see that cumulatively, these original 36 people actually spent $14,000 with us, and we can see this by each cohort. This is our Cumulative Lifetime Revenue by cohort. And when you can start to understand this, you can understand the stickiness of a customer, and you can understand the dynamics of your occurring revenue, so that when you acquire customers today, you know how much revenue you can expect from them a year from now. And that gives you a lot of visibility into your profitability. So this is important, but now let's look at this on a per-customer basis. That is a little more helpful. So we're going to take our Cumulative Lifetime Revenue and divide it by your January cohort, which is 35 customers, and lock the column. So lock $ in front of the D. I did that by tapping F4. So what does this tell us? Okay, so again, clean this up so we can delete this. Delete this stuff here. Delete some of this extra stuff. Okay, so this tells us that of those original 35 customers, this shows us the total cumulative revenue divided by those 35 customers. So this gives us an average. It says on average, for each customer that we acquired, we made $406. Now obviously some of those 35 customers dropped off, and some of them stuck on. So some portion of those customers spent a lot more than $406, and some portion of those customers spent a lot less, but the average is $406. That is really, really interesting and really important for us to know, because then we know when we acquire a new customer today in, let's say, January, we know that we might only make $50 from them in the first month, but within a year, on average, we're going to make $400 from each customer. But revenue is important, but what we really need is the profit. How much profit are we going to make on the relationship with each customer? The total lifetime gross profit that you make from one customer is your customer lifetime value. That's what we're going to calculate right here. In this case, all we have to do is take your customer lifetime revenue and multiply it by your gross margin. Your gross margin is how much profit you make at the product level. So it excludes your marketing. It excludes your overhead costs, but it's just your revenue minus the direct cost of your product. So let's lock the gross margin, and I put in an assumption here saying our gross margin is 65%. So we can see that of those $50, 33 are gross margin. So it means $33 come back to the company as profit. So same as always, let's just copy this formula, and then I will explain to you why this is one of the most important, probably the most important metric for any business, which is the customer lifetime value. And I'll explain sort of through an example exactly what we do with this metric. So let's say right now our customer acquisition cost is $115. And by the way, your customer acquisition cost is the total advertising spend that you spend to get one new customer to buy for the very first time. So you're spending $115 to get one customer to buy for their first purchase. So after you spend that $115, that customer comes on average and spends $50 with the company, and only 33 of those $50 are profit. So we spent $115 and we made $33. So should we be spending $115 on marketing? Can we afford that? Well, if you just look at their first purchase, it's like, no way, we only made $33. But if you can see that over time, okay, but by their second purchase, we've actually made $65 on average off a customer. By their third, $95. By their fourth, $123. And $123 in profit is more than the marketing cost. So at this point, we actually become profitable on our relationship with each customer. And so in this month, we're actually making a profit, more profit, more profit, more profit. So by their 12th month, we only spent $115 to get them as a customer, but we've made $264 of profit. So we've made back our money more than 2x in the first year of our relationship with the customer. So $115 CAC is no problem. And this is really what businesses are trying to understand, what's the maximum we can spend on marketing and still be profitable eventually on our relationship with the customer, which is your customer lifetime value. So the CAC LTV ratio is absolutely critical, and you're obviously trying to make a multiple of your CAC in the customer lifetime value. So I hope you now have a really good understanding of how to calculate customer retention, net revenue retention, and customer lifetime value using cohort analysis tables. By the way, in the description below, you can download this Excel file if you want to use it for yourself. I've also linked down below to some of my other financial modeling videos. Check those out. And if you found this content valuable, please subscribe and click the little bell icon, like this video and leave me a comment below. Also check out my online programs if you want to do more finance training with me. That's all for today. Thanks for watching, and I'll see you in the next video.