Cohort Analysis - Layer Cake Chart

Cohort analysis may be one of the most effective ways to gather information about customer behavior.

Cohort analysis is a subset of behavioral analytics that looks at groups (cohorts) of people (users) who have taken a common action during a select period of time.

Layer Cake chart helps you understand how efficient your MRR is growing by visualizing the net revenue retention for each cohort of customers based on when they converted.

Objective

To understand subscription based Recurring Revenue, how MRR (Monthly Recurring Revenue) is growing
To understand customer churn/retention and businese growth

Get Data

Dataset

https://exploratory.io/data/kanaugust/Sample-Data-for-Cohort-Analysis-Layer-Cake-DKF8fox5sk

Each row represents each payment from a given customer.

Clean and Transform Data

Remove Duplicate Records

    distinct(.keep_all = TRUE)

Create Column 'Conversion Date' (the first date of payment) for each user

group_by(userid)
mutate(`Conversion Date` = min(payment_date))
ungroup()

Visualize Data and EDA (insights)

Layercake

Having the newer cohorts piled up on top the older cohorts makes it more intuitive to understand how the MRR is growing.
Each layer is a chort by Conversion Month.
The MRR shrinked much more after 1 month from 2019-06 to 2019-09.

Layercake｜segment

There is a noticeable expansion at Dec 2019 for cusumer customers who coverted on 2019-11.

EDA-payment month by price

The expansion comes from increasing payment to price $300.
Loading...

EDA-numbers of expansions

For the 134 customers who converted on 2019-11, there are 63 (47%) of them upgraded to the pricing plan of $300 in the next month (2019-12), and 55 customers churn.
There are 136 customers who converted on 2019-12, and 54 (40%) of them upgraded to pricing plan of $300 in the next month (2020-01), and 58 customers churn.
Loading...

Reference

Online Seminar #42 - Cohort Analysis Part 1 - Layer Cake Chart https://exploratory.io/note/kanaugust/Online-Seminar-42-Cohort-Analysis-Part-1-Layer-Cake-Chart-zmK9Hlv5DL