Build a basic cohort retention table
Intermediate Data AnalystCohort analysis is common in SaaS and other recurring-behavior analysis because it shows whether people who started in the same period kept returning, subscribing, purchasing, or staying active over time. Instead of relying on one blended retention rate, you can compare signup groups month by month and spot whether newer cohorts are holding up better or worse.
In this exercise, the source table lists subscription customers, the numbered month they signed up, and the last numbered month they were still active. Real reports often use calendar months or quarters, but numbered months keep the focus here on the cohort retention logic. Complete the cohort grid so each row shows the size of that signup cohort and the percentage of the original cohort that remained active in each later month.
This is a good fit for COUNTIF and COUNTIFS. For example, a basic COUNTIF can count how many records belong to one category:
=COUNTIF(A1:A10,1)
Your task:
Use the customer activity table in A1:C13 to complete the cohort retention grid in B16:E19. Fill in the cohort size for each signup month, then calculate only the follow-up month percentages that can be measured from the data shown. In the Month +1, Month +2, and Month +3 columns, enter the percentage of the original cohort that was still active at least that many months after signup. If a later month does not exist yet for that cohort, leave that cell blank.
Need some help?
Hint 1
Treat the last active month as an inclusive endpoint. If a customer was last active in month 4, they still count as retained for any earlier follow-up month in that cohort row.
Hint 2
Read each retention column as a target milestone. For example, Month +2 for the Month 2 cohort means the customer stayed active through month 4 or later, so count matching signups with a last active month of at least 4 and divide by the cohort size.
Related function(s)
Build a basic cohort retention table
Intermediate Data AnalystCohort analysis is common in SaaS and other recurring-behavior analysis because it shows whether people who started in the same period kept returning, subscribing, purchasing, or staying active over time. Instead of relying on one blended retention rate, you can compare signup groups month by month and spot whether newer cohorts are holding up better or worse.
In this exercise, the source table lists subscription customers, the numbered month they signed up, and the last numbered month they were still active. Real reports often use calendar months or quarters, but numbered months keep the focus here on the cohort retention logic. Complete the cohort grid so each row shows the size of that signup cohort and the percentage of the original cohort that remained active in each later month.
This is a good fit for COUNTIF and COUNTIFS. For example, a basic COUNTIF can count how many records belong to one category:
=COUNTIF(A1:A10,1)
Your task:
Use the customer activity table in A1:C13 to complete the cohort retention grid in B16:E19. Fill in the cohort size for each signup month, then calculate only the follow-up month percentages that can be measured from the data shown. In the Month +1, Month +2, and Month +3 columns, enter the percentage of the original cohort that was still active at least that many months after signup. If a later month does not exist yet for that cohort, leave that cell blank.
Need some help?
Hint 1
Treat the last active month as an inclusive endpoint. If a customer was last active in month 4, they still count as retained for any earlier follow-up month in that cohort row.
Hint 2
Read each retention column as a target milestone. For example, Month +2 for the Month 2 cohort means the customer stayed active through month 4 or later, so count matching signups with a last active month of at least 4 and divide by the cohort size.