What is a Cohort Analysis?
See the link below from Kissmetrics for further reading on Cohort Analyses.
Learn more from the full course
SQL for NEWBS: Weekender Crash Course
Data Analysis for Data Scientists, Marketers, & Business/Product folks. Using MySQL but appropriate for all SQL.
03:15:47 of on-demand video • Updated September 2022
Analyze user behavior
Find actionable customer/business insights
Make data-driven decisions
Measure and track marketing efforts
Discover sexy marketing stats (e.g. 1 in 4 people love toast!)
All right all right welcome back. So in this lecture if you go over what a cohort analysis is the next lecture will be actually writing the query and showing you how to do one but you wanted to prep it with this lecture and kind of go over it for we jump into it David's going to take you through like a sneak peek of what a cohort analysis chart will look like. Boom there it is there is a chart right there. And so it might look a little overwhelming at first but really if they're going to break the sound we're basically doing two things here. First thing on the left hand column we're grouping users by their sign up date. So it is August 2010 that people who joined in August 2010 and then September 2010 that's another group of people. The second thing we're doing is that for each group that cohort the August cohort we're seeing their activity month to month. So we see the first comment as one up top. We're seeing there how many people came to the site in month one hundred people came back to the site months 2 3 4 and so on and so forth. So we're following them following that cohort through their life. This chart has like two really big main benefits so one we can compare different cohorts or groups of users at the same stage in their life cycle so that would be looking down from top to bottom on the chart so like we can see for all the codewords what percentage are coming back to the site three months after they signed up and we can compare how we're doing with our improvement in product and user experience so hopefully that's increasing in our products becoming better and more enjoyable. The second big benefit is we can see the long term relationship that we have with a given user group for each cohort about 25 percent are coming back after 1 month 6 percent coming after two months and we can see how long people from a given cohort are coming back to the site. We can see how strong that cohort is and how valuable they are. Yes well just like a product overview of what I cover and also in the benefits of it but if I was confusing stick around we're going go deeper right now. First off what is the cohort. Yeah. So for our purposes a cohort is a group of people who have become a customer. Around the same time. So people for example using example analogy at the high school people join the high school they become freshmen in high school in 2002. That's one cohort. The next year another professional arrived there they joined in 2003 that's another cohort. So we're grouping people by their joint. So after we've got them will follow each cohort and monitor their behavior every month or every year so on and so forth. Yeah and so that's gone so why are we grouping these people by the joint they are why we want to follow them over time so that we're going to get a really good life example similar to our Siculus database we're going to be talking about. Let's say we're Netflix subscription business where people come in and pay $15 a month to watch our videos our TV shows and we want to grow our avenues and so the first thing we want to do is we want to say we want to get as many new users as we can each month. So that's like people have never used Netflix before and get them to sign up make an account pay us out first months yet. So if you want to grow revenue that's one way to do it right you get new users and what I call Pete that's acquisition that is acquisition time but it'll be up so that once we acquire all these users and that's a higher 5000 last month we acquired another 7000 this month they were like wow we're killing it Caroline edition off the chain. But we're going to be like Missy in a couple big areas. If we just focus on not and pat ourselves on the back and there's two big reasons. There were things that were going to be missing. Yeah. If we see this chart going up instead right new users like dude are companies going like crazy. But you would be misjudging your business for one reason is because what if that group that you acquired last month that 5000 people they pay for a subscription for $15 for that month for the next month only 1 percent of those people continued their subscription. That would be that. And so the those people who come back we call that retention and that's like acquisition and retention those are two key parts of the business. You want to have both really good. But when we look at just the acquisition that doesn't show us our attention but the benefit of a cohort analysis is that we can follow that group across their lifetime so we can see how many people are coming back each month. So cocksure analysis help us in that. Let us know how many people are coming back and what our retention rate is. Yeah and then the second thing is that it's like comparing one group month over month the other one will be comparing all of our cohorts at a similar period in time. So for example the example if we take a look at this chart again here we see that in August month three of the August 2010 cohort we're getting about 5 percent of people that come back to the site. But then we go into March of March 2011 cohort. We're getting 7.2 percent and so that's like a really good start and so it's like we've changed our product over time so that this cohort of March 2011 is a better experience are coming back to the site more. And that's if you can trend really well going forward so it's a really good thing to see improvements going from top down to the bottom in any given month. Yeah basically that chart kind of that Excel kind of spreadsheet like looking chart can turn into this line chart that shows cohorts kind of like retention over time. And so you see here that that line's going to decrease and that's because we don't have the data for the newer cohorts we only have a few months of data for the new records but that light blue light at the Wave bottom that's like our office cohort they've been around for 12 months and we see where they're trending right. That's how much attention were having. But as we see the tension go higher. Hopefully that slope will become less steep. And that's a good sign for us because that means the lifetime value of that cohort or that those customers are increasing and that's huge for us. This is like so let the church show you here is like really good so each cohort is better than the one before him. The idea is OK here this is where our latest cohort is but on that trend they're going to end up with something around here where we're you know 12 months later we're retaining 80 percent of our users vs. 55 percent in that older cohorts are showing like hey we're improving the product we're getting more valuable users to join. And yet this is an awesome way to see the strength of the business and like how well everything's you know so that's pretty much it now you know how important cohort analyses are the only thing left is to actually make one create one using sequel. Yeah. So we'll be showing you that in the next lecture so go on to the next lecture if you want to read some more about cohort analysis and how you can apply to your cohort analysis how you can apply that we provided some links and a lecture description of this lecture. So go check that out if you're interested in that. All right see you next time.