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

Slide 1

Slide 1 text

EXPLORATORY Online Seminar #42 Cohort Analysis Part 1 Layer Cake Chart

Slide 2

Slide 2 text

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

Slide 3

Slide 3 text

3 Data Science is not just for Engineers and Statisticians. Exploratory makes it possible for Everyone to do Data Science. The Third Wave

Slide 4

Slide 4 text

4 Questions Communication Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis Data Science Workﬂow

Slide 5

Slide 5 text

5 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling Visualization Analytics (Statistics / Machine Learning) Data Analysis ExploratoryɹModern & Simple UI

Slide 6

Slide 6 text

EXPLORATORY Online Seminar #42 Cohort Analysis Part 1 Layer Cake Chart

Slide 7

Slide 7 text

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.

Slide 8

Slide 8 text

What are the differences from other type of businesses including the traditional software business, retail businesses, etc.?

Slide 9

Slide 9 text

9 Jan $100 You sell a product to one user and collect the money right a way. Typical Sales Model

Slide 10

Slide 10 text

10 This means, you can spend $80 to acquire this customer but still can make a proﬁt. Jan $100 Expense Revenue Proﬁt

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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.

Slide 13

Slide 13 text

13 Jan $10 You collect only the monthly subscription amount at the ﬁrst month in case of the monthly subscription.

Slide 14

Slide 14 text

14 If you spend $80 to acquire the customer you will lose money. Jan $10 Subscription Expense $80

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

17 Jan Feb Mar … Dec This repeated revenue is often called ‘recurring’ revenue. $10 $10 $10 $10

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

19 And this is what is called MRR (Monthly Recurring Revenue) or ARR (Annual Recurring Revenue). $10 $30 $40 $60 Jan Feb Mar Apr

Slide 20

Slide 20 text

20 Good MRR vs. Bad MRR

Slide 21

Slide 21 text

21 Let’s say our business’s MRR is growing as below.

Slide 22

Slide 22 text

22 And we found that much of the MRR is coming from new customers who just converted in each month. Existing New

Slide 23

Slide 23 text

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.

Slide 24

Slide 24 text

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.

Slide 25

Slide 25 text

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.

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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 efﬁcient and sustainable growth!

Slide 28

Slide 28 text

28 Separating New MRR and Existing MRR can help you understand how sustainable and efﬁcient 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.

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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. Proﬁt

Slide 31

Slide 31 text

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.

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

36 By dividing the MRR by the cohort of when the users converted, you can see how efﬁciently 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

Slide 37

Slide 37 text

In general, Area chart is better than bar chart to show the continuous growth trend of each cohort. 37

Slide 38

Slide 38 text

38 And this chart is called Layer Cake chart.

Slide 39

Slide 39 text

The revenue from each cohort tend to go down as the time goes by because customers churn. 39

Slide 40

Slide 40 text

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.

Slide 41

Slide 41 text

If there is no churn each new cohort will be added as a growth of your business. 41

Slide 42

Slide 42 text

But, this is actually not the best shape of Layer Cake. There is even something better. 42

Slide 43

Slide 43 text

43 This 🍰 !!!!

Slide 44

Slide 44 text

44 Ok, just kidding… but I’m serious, there is something better!

Slide 45

Slide 45 text

MRR decreases when customers churn. $100 $100 $100 $100 $100 $100 $100 Jan Feb Mar Apr

Slide 46

Slide 46 text

Source: S-1 ﬁling

Slide 47

Slide 47 text

Source: S-1 ﬁling

Slide 48

Slide 48 text

Customers upgrade!

Slide 49

Slide 49 text

Customers upgrade the subscription plan to get more or better services. Or, they increase the number of users under the same account. Expansion

Slide 50

Slide 50 text

When customers upgrade the plan, the revenue from them will increase. $100 $100 $200 $200 Jan Feb Mar Apr

Slide 51

Slide 51 text

Customers downgrade the subscription plan or decrease the number of users under the same account. Contraction

Slide 52

Slide 52 text

Expansion Churn + Contraction >

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

When you have the negative churn the MRR will increase over time. 54

Slide 55

Slide 55 text

55 Layer Cake chart helps you understand how efﬁcient your MRR is growing taking into account for Expansion, Contraction, and Churn by visualizing the MRR by cohort of the conversion month.

Slide 56

Slide 56 text

56 Let’s make Layer Cake!

Slide 57

Slide 57 text

57 We can get a sample data from Exploratory Data Catalog.

Slide 58

Slide 58 text

58 Payment Data

Slide 59

Slide 59 text

59 Each row represents each payment from a given customer.

Slide 60

Slide 60 text

Enter ‘layer cake’ to ﬁnd the data for this exercise. 60

Slide 61

Slide 61 text

Import 61

Slide 62

Slide 62 text

Slide 63

Slide 63 text

63 Each row represents each payment from a given customer.

Slide 64

Slide 64 text

64 In order to create the Layer Cake chart we need the ‘Conversion Date’ column.

Slide 65

Slide 65 text

The original data doesn’t have the conversion date column that indicates the conversion date (the ﬁrst date of payment) for each user. 65 ʁ

Slide 66

Slide 66 text

If we know the ﬁrst 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

Slide 67

Slide 67 text

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

Slide 68

Slide 68 text

Then, we can use the ‘min’ function to get the ﬁrst 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)

Slide 69

Slide 69 text

1. Select ‘Group By’ from the ‘userid’ columns’ menu. 69

Slide 70

Slide 70 text

70 Each group is colored differently.

Slide 71

Slide 71 text

2. Create a Calculation to return the ﬁrst payment date. 71

Slide 72

Slide 72 text

72 Use the ‘min’ function, which returns the minimal value of the payment column for each group.

Slide 73

Slide 73 text

73 Now we have the conversion date.

Slide 74

Slide 74 text

74 3. Since we no longer need the data being ‘grouped’, we can remove the group setting.

Slide 75

Slide 75 text

75 This is the data we wanted!

Slide 76

Slide 76 text

76 Now, it’s time to create the chart!

Slide 77

Slide 77 text

77 Select ‘Area’ chart type, then assign the ‘payment_date’ column to X-Axis. Change the aggregation level to ‘Month’.

Slide 78

Slide 78 text

78 Assign the ‘price’ column to Y-Axis. We can keep the aggregation function as the default ‘SUM’.

Slide 79

Slide 79 text

79 Assign the ‘Conversion Date’ column to Color and select ‘Month’ as the aggregation level.

Slide 80

Slide 80 text

80 MRR is divided into each cohort based on the conversion month. But there is one problem.

Slide 81

Slide 81 text

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

Slide 82

Slide 82 text

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

Slide 83

Slide 83 text

83 We can change the order from the Color’s Order Setting.

Slide 84

Slide 84 text

84 Select ‘From bottom to top’.

Slide 85

Slide 85 text

Now we have it! 85

Slide 86

Slide 86 text

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 efﬁciently. 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 efﬁcient and sustainable your business is growing.

Slide 87

Slide 87 text

87 But…

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

89 The September cohort seems to have big churn rate in the second month. We want to compare the September cohort with others. September

Slide 90

Slide 90 text

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?

Slide 91

Slide 91 text

91 We want to know how the customer retention rate of each cohort changes over time and compare among different cohorts.

Slide 92

Slide 92 text

We can use ‘Survival Analysis’ algorithm to calculate the retention rates over time for each cohort. 92

Slide 93

Slide 93 text

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?

Slide 94

Slide 94 text

EXPLORATORY Online Seminar #43 5/5/2021 (Wed) 11AM PT Cohort Analysis Part 2 Survival Curve

Slide 95

Slide 95 text

No content

Slide 96

Slide 96 text

Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar