Exploratory Seminar #42 - Cohort Analysis Part 1 - Layer Cake Chart

The Layer Cake chart is often used to understand how the recurring revenue (MRR or ARR) is accumulated over time but also to see how efficient and sustainable SaaS / Subscription businesses are growing. By segmenting your customers based on when they converted, you can see if the revenue retention is improving or not.

But creating this useful chart is not straightforward unless you know a few tricks of Data Wrangling.

Kan presents how to prepare the data and create the Layer Cake chart quickly with Exploratory.

Subscribe ↓
https://www.youtube.com/channel/UCOVfLaSQBvMRwZCyiccq4Iw

Twitter ↓
https://twitter.com/ExploratoryData

UI Tool: Exploratory(https://exploratory.io/)
Exploratory Online Seminar: https://exploratory.io/online-seminar

Transcript

1. EXPLORATORY Online Seminar #42 Cohort Analysis Part 1 Layer Cake Chart
2. Kan Nishida CEO/co-founder Exploratory Summary In Spring 2016, launched Exploratory, Inc. to democratize Data Science. Prior to Exploratory, Kan was a director of product development at Oracle leading teams to build various Data Science products in areas including Machine Learning, BI, Data Visualization, Mobile Analytics, Big Data, etc. While at Oracle, Kan also provided training and consulting services to help organizations transform with data. @KanAugust Speaker
3. 3 Data Science is not just for Engineers and Statisticians. Exploratory makes it possible for Everyone to do Data Science. The Third Wave
4. 4 Questions Communication Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis Data Science Workflow
5. 5 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis ExploratoryɹModern & Simple UI
6. EXPLORATORY Online Seminar #42 Cohort Analysis Part 1 Layer Cake Chart
7. SaaS - Software as a Service A business model where you charge a software license fee as subscription basis for a value you provided through the software.
8. What are the differences from other type of businesses including the traditional software business, retail businesses, etc.?
9. 9 Jan $100 You sell a product to one user and collect the money right a way. Typical Sales Model
10. 10 This means, you can spend $80 to acquire this customer but still can make a profit. Jan $100 Expense Revenue Profit
11. 11 $100 If the product lasts for a long time you don’t see the same customer coming back to buy the same product again for a while. Typical Sales Model Jan Feb Mar … Dec
12. If your business is SaaS / Subscription model your consumers subscribe a plan to use your service and pay monthly or yearly as long as they need it.
13. 13 Jan $10 You collect only the monthly subscription amount at the first month in case of the monthly subscription.
14. 14 If you spend $80 to acquire the customer you will lose money. Jan $10 Subscription Expense $80
15. 15 Jan Feb Mar Total … Dec $120 But the good news is, you’ll accumulate the revenue from the same customer over time. $10 $10 $10 $10
16. 16 Jan Feb Mar Total … Dec $120 So if the customer retain for a long enough, you’ll be able to pay off the initial cost of acquisition. Expense $80 $10 $10 $10 $10
17. 17 Jan Feb Mar … Dec This repeated revenue is often called ‘recurring’ revenue. $10 $10 $10 $10
18. 18 $10 $30 $40 $60 And of course, you’ll have more than one customer so you will combine all the revenue from all the customers for each month as your monthly revenue. Jan Feb Mar Apr
19. 19 And this is what is called MRR (Monthly Recurring Revenue) or ARR (Annual Recurring Revenue). $10 $30 $40 $60 Jan Feb Mar Apr
20. 20 Good MRR vs. Bad MRR
21. 21 Let’s say our business’s MRR is growing as below.
22. 22 And we found that much of the MRR is coming from new customers who just converted in each month. Existing New
23. 23 Existing New Existing customers are the customers from the previous months. has just converted in the previous month and the customers who had been customers even before.
24. 24 Existing New Existing customers are the customers from the previous months. Some had just converted in the previous month (Blue) and the customers who had been customers even before (Green). Some retain, some churn. Only the retained customers are the existing customers.
25. 25 Existing New If only a few customers retain you’ll need to acquire a lot of new customers to make u for it in order to grow the MRR as total.
26. 26 Acquiring New Customers Retaining Existing Customers > • Marketing / Advertisement • Sales • Customer Success / Support Typically, acquiring new customers is much more expensive than retaining existing customers. Cost
27. 27 Existing New If your produce (or service) provide enough values your customers will retain longer, which means the ratio of MRR from the existing customers is high. You can keep the customer acquisition cost lower. This is much more efficient and sustainable growth!
28. 28 Separating New MRR and Existing MRR can help you understand how sustainable and efficient your business is growing. However, the subscription business model is more than that. Not just retaining customers, but also how long customers retain is very critical for the subscription businesses to grow rapidly.
29. 29 Jan Feb Mar Total Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec The longer your customer retain you’ll make more money. Customer Life Time Value - CLTV
30. 30 Jan Feb Mar Total Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec This means not only you can spend more money on acquiring customers but also can invest more in your products and people. Profit
31. 31 Jan Feb Mar Apr It’s important to divide the MRR into cohorts of customers life time. 4 mon. 4 mon. 4 mon. 4 mon. 3 mon. 3 mon. 3 mon. 3 mon. 2 mon. 1 mon. 1 mon. 3 mon. 2 mon. 3 mon. This customer has paid 4 months of subscription by this time.
32. Let’s say our business’s MRRs are divided into the cohorts as below. 32 9 Month Ago 8 Month Ago 7 Month Ago 6 Month Ago 5 Month Ago 4 Month Ago 3 Month Ago 2 Month Ago 1 Month Ago Customers churn, so each cohort’s MRR tends to go down. Converted at
33. 33 Here’s MRR for the last month. 9 Month Ago 8 Month Ago 7 Month Ago 6 Month Ago 5 Month Ago 4 Month Ago 3 Month Ago 2 Month Ago 1 Month Ago
34. 34 A majority of the ratio of MRR is coming from the relatively newer customers. MRR from longer life time customers MRR from shorter life time customers 9 Month Ago 8 Month Ago 7 Month Ago 6 Month Ago 5 Month Ago 4 Month Ago 3 Month Ago 2 Month Ago 1 Month Ago
35. 35 If customers don’t churn then the ratio of longer customers in MRR is bigger. if the older cohorts retain longer you can accelerate your growth as you acquire new customer. If many customers churn then the ratio of shorter customers in MRR is bigger. 9 Month Ago 8 Month Ago 7 Month Ago 6 Month Ago 5 Month Ago 4 Month Ago 3 Month Ago 2 Month Ago 1 Month Ago
36. 36 By dividing the MRR by the cohort of when the users converted, you can see how efficiently your business is growing and can grow further. 9 Month Ago 8 Month Ago 7 Month Ago 6 Month Ago 5 Month Ago 4 Month Ago 3 Month Ago 2 Month Ago 1 Month Ago
37. In general, Area chart is better than bar chart to show the continuous growth trend of each cohort. 37
38. 38 And this chart is called Layer Cake chart.
39. The revenue from each cohort tend to go down as the time goes by because customers churn. 39
40. 40 Recent Cohort One of the most important tasks for SaaS businesses is to reduce the churns by improving the product and the customer support including customer onboarding.
41. If there is no churn each new cohort will be added as a growth of your business. 41
42. But, this is actually not the best shape of Layer Cake. There is even something better. 42
43. 43 This 🍰 !!!!
44. 44 Ok, just kidding… but I’m serious, there is something better!
45. MRR decreases when customers churn. $100 $100 $100 $100 $100 $100 $100 Jan Feb Mar Apr
46. Source: S-1 filing
47. Source: S-1 filing
48. Customers upgrade!
49. Customers upgrade the subscription plan to get more or better services. Or, they increase the number of users under the same account. Expansion
50. When customers upgrade the plan, the revenue from them will increase. $100 $100 $200 $200 Jan Feb Mar Apr
51. Customers downgrade the subscription plan or decrease the number of users under the same account. Contraction
52. Expansion Churn + Contraction >
53. 53 When you have the expansion that is bigger than the churn and the contraction combined then the MRR as whole can increase even without new customers. This is called Negative Churn. $400 $500 $200 $200 $100 $100 $100 $100 $100 Jan Feb Mar Apr
54. When you have the negative churn the MRR will increase over time. 54
55. 55 Layer Cake chart helps you understand how efficient your MRR is growing taking into account for Expansion, Contraction, and Churn by visualizing the MRR by cohort of the conversion month.
56. 56 Let’s make Layer Cake!
57. 57 We can get a sample data from Exploratory Data Catalog.
58. 58 Payment Data
59. 59 Each row represents each payment from a given customer.
60. Enter ‘layer cake’ to find the data for this exercise. 60
61. Import 61
63. 63 Each row represents each payment from a given customer.
64. 64 In order to create the Layer Cake chart we need the ‘Conversion Date’ column.
65. The original data doesn’t have the conversion date column that indicates the conversion date (the first date of payment) for each user. 65 ʁ
66. If we know the first date of the payment for each user, then we can use that as the ‘conversion date’. 66 Payment Date User ID 2019-01-17 A 2019-02-01 B 2019-02-17 A 2019-02-22 C 2019-03-01 B 2019-03-17 A 2019-03-22 C 2019-04-01 B 2019-04-17 A
67. First, we group the data by user ID. 67 Payment Date User ID 2019-01-17 A 2019-02-01 B 2019-02-17 A 2019-02-22 C 2019-03-01 B 2019-03-17 A 2019-03-22 C 2019-04-01 B 2019-04-17 A Payment Date User ID 2019-01-17 A 2019-02-17 A 2019-03-17 A 2019-04-17 A 2019-02-01 B 2019-03-01 B 2019-04-01 B 2019-02-22 C 2019-03-22 C Group By
68. Then, we can use the ‘min’ function to get the first date of the payment for each group (user). 68 Payment Date User ID 2019-01-17 A 2019-02-17 A 2019-03-17 A 2019-04-17 A 2019-02-01 B 2019-03-01 B 2019-04-01 B 2019-02-22 C 2019-03-22 C Payment Date User ID Converted Date 2019-01-17 A 2019-01-17 2019-02-17 A 2019-01-17 2019-03-17 A 2019-01-17 2019-04-17 A 2019-01-17 2019-02-01 B 2019-02-01 2019-03-01 B 2019-02-01 2019-04-01 B 2019-02-01 2019-02-22 C 2019-02-22 2019-03-22 C 2019-02-22 min(Payment Date)
69. 1. Select ‘Group By’ from the ‘userid’ columns’ menu. 69
70. 70 Each group is colored differently.
71. 2. Create a Calculation to return the first payment date. 71
72. 72 Use the ‘min’ function, which returns the minimal value of the payment column for each group.
73. 73 Now we have the conversion date.
74. 74 3. Since we no longer need the data being ‘grouped’, we can remove the group setting.
75. 75 This is the data we wanted!
76. 76 Now, it’s time to create the chart!
77. 77 Select ‘Area’ chart type, then assign the ‘payment_date’ column to X-Axis. Change the aggregation level to ‘Month’.
78. 78 Assign the ‘price’ column to Y-Axis. We can keep the aggregation function as the default ‘SUM’.
79. 79 Assign the ‘Conversion Date’ column to Color and select ‘Month’ as the aggregation level.
80. 80 MRR is divided into each cohort based on the conversion month. But there is one problem.
81. 81 The older cohort is piled up on top of the newer period cohorts, but it’s much easier to see it in the opposite way. Older Cohort with Long Period Newer Cohort
82. 82 Having the newer cohorts piled up on top the older cohorts makes it more intuitive to understand how the MRR is growing. A cohort of the longest period A cohort of the shortest period
83. 83 We can change the order from the Color’s Order Setting.
84. 84 Select ‘From bottom to top’.
85. Now we have it! 85
86. 86 Summary: 1. Acquiring new customers is much more expensive than retaining existing customers in general. Therefore, we want to keep the ratio of MRR from existing customers higher to make our business grown more efficiently. 2. But, separating into the existing and the new is not enough. LTV grows as customers retain longer. It’s important to understand how the MRR for each cohort of customers based on the converted month (or year) is growing over time. 3. Layer Cake chart visualizes the growth of MRR by the cohort of converted month and help you understand how efficient and sustainable your business is growing.
87. 87 But…
88. Each cohort grows or shrink in different ways. For example, the September cohort seems to decrease the MRR much more than the May cohort. 88 September May
89. 89 The September cohort seems to have big churn rate in the second month. We want to compare the September cohort with others. September
90. 90 However, we want to compare orange to orange. Now, how the September cohort is worse than the other cohorts for the 2nd month, 3rd month, and so on?
91. 91 We want to know how the customer retention rate of each cohort changes over time and compare among different cohorts.
92. We can use ‘Survival Analysis’ algorithm to calculate the retention rates over time for each cohort. 92
93. 93 By comparing the retention rate trends among the cohorts based on the conversion month, we can answer the following questions. • Have product improvements helping retaining customers? • Are we acquiring the ‘right’ customers for the value our service provides? • Is onboarding experience getting improved?
94. EXPLORATORY Online Seminar #43 5/5/2021 (Wed) 11AM PT Cohort Analysis Part 2 Survival Curve
96. Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar
97. Q & A 97
98. EXPLORATORY 98