In this exercise, you will work with a Google Sheet template designed for analysing retention and churn. The template consists of six tabs that will guide you through various aspects of retention analysis, including creating retention cohorts, visualising churn and retention rates, running segment analysis, and exploring different retention scenarios.
Gain a better understanding of retention analysis techniques and be able to apply them to your own data.
Instructions & Resources
- Open the provided Retention and Churn Analysis Google Sheet template.
- Familiarise yourself with the goal of the template, which is to help you understand retention cohorts, visualise churn and retention rates, and run segment analysis on user event data.
- Begin with the first tab, "Dataset." a. Review the example dataset provided, which will be used for the analysis in the remainder of the sheet.
- Move on to the second tab, "Formatted dataset." a. Format the dataset according to the instructions provided in the tab to prepare it for retention cohort pivot tables and stacked area charts.
- Proceed to the third tab, "Retention analysis." a. Create a pivot cohort table using the formatted dataset. b. Calculate the retention rate by month since signup using the pivot table.
- Navigate to the fourth tab, "Cohort analysis." a. Use the formatted dataset to create a stacked cohort chart that identifies which cohorts underperform or perform well. b. Analyse the cohort chart to gain insights into retention and churn patterns.
- Move to the fifth tab, "Segment analysis." a. Run a segment analysis on the "company region" dimension to examine retention rates by that specific dimension. b. Use the provided instructions to interpret the segment analysis results and draw meaningful insights.
- Finally, explore the sixth tab, "Scenarios." a. Review the three different retention scenarios showcased in visual format. b. Analyze each scenario to understand what constitutes good and bad retention outcomes. c. Use the insights gained to inform your retention strategy and decision-making process.
Note: As you work through the tabs, make sure to refer to the instructions, formulas, and charts provided in each tab. Feel free to customise the dataset and apply the analysis techniques to your own data to gain insights specific to your business or project.
By completing this exercise, you will develop a solid understanding of retention and churn analysis using pivot tables, cohort charts, segment analysis, and scenario visualisation. This knowledge will enable you to effectively track and analyse retention rates, identify areas for improvement, and make data-driven decisions to enhance customer retention in your business.