Seminar #44 - Cohort Analysis Part 3 - Calculating CLTV (Customer Life Time Value)

Slide 1

Slide 1 text

EXPLORATORY Online Seminar #44 Cohort Analysis Part 3 CLV (Customer Lifetime Value) with Survival Curve

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 #44 Cohort Analysis Part 3 CLV (Customer Lifetime Value) with Survival Curve

Slide 7

Slide 7 text

7 Agenda • What and Why CLV? • How to Calculate? • Calculating CLV for Each Cohort

Slide 8

Slide 8 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 9

Slide 9 text

For SaaS businesses, the initial payment from a customer tends to smaller but it accumulates over time.

Slide 10

Slide 10 text

10 Jan $100 You sell a product to one user and collect the money right a way. Non Subscription Model

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

12 $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. Jan Feb Mar … Dec Non Subscription Model

Slide 13

Slide 13 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 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

16 Jan Feb Mar Total … Dec $120 But, you’ll accumulate the revenue from the same customer over time. $10 $10 $10 $10

Slide 17

Slide 17 text

17 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 18

Slide 18 text

18 Jan Feb Mar Total … Dec $120 Expense $80 $10 $10 $10 $10 Customer Lifetime Value

Slide 19

Slide 19 text

19 It is important for any SaaS / Subscription type businesses to estimate the CLV as accurate as possible. How to calculate it?

Slide 20

Slide 20 text

20 Jan Feb Mar Total … Dec $120 Multiply the monthly revenue by the customer lifetime of 12 months? $10 $10 $10 $10

Slide 21

Slide 21 text

But, customers upgrade or downgrade… 21 Basic Plan Pro Plan $10 $10 $20 $20 Jan Feb Mar Apr

Slide 22

Slide 22 text

We can calculate the monthly average revenue and make it as the revenue we can expect from one customer. 22 $15 ($90/6ਓ) $10 $10 $10 $20 $20 $20 April

Slide 23

Slide 23 text

This is called ARPU (Average Revenue Per User). 23 ARPU = MRR / Number of Customers $15 ($90/6ਓ) April

Slide 24

Slide 24 text

Once we know the ARPU we can multiply it by the customer lifetime periods (e.g. 12 months). 24 CLV = customer lifetime periods * ARPU Example: 1,200 = 12 month * 100

Slide 25

Slide 25 text

25 But, there are some problems…

Slide 26

Slide 26 text

• Customer churn. Some customers retain longer, but some quickly churn. • We don’t know how long customers retain hence don’t know what should be the average period.

Slide 27

Slide 27 text

Apr Mar Feb Jan Jessica Tien Nancy Victor Jane

Slide 28

Slide 28 text

Jessica Tien Nancy Victor Jane 4th Month 3rd Month 2nd Month 1st Month Align all customers based on how many months they are in.

Slide 29

Slide 29 text

? ? ? ? 4th Month 3rd Month 2nd Month 1st Month Jessica Tien Nancy Victor Jane We don’t know if Jessica will retain or churn after the 4th month. ? ? ?

Slide 30

Slide 30 text

? ? ? ? 4th Month 3rd Month 2nd Month 1st Month Jessica Tien Nancy Victor Jane We don’t know if Nancy will retain or churn after the 3rd month. ? ? ?

Slide 31

Slide 31 text

? ? ? ? 4th Month 3rd Month 2nd Month 1st Month Jessica Tien Nancy Victor Jane ? ? ? We don’t know how long the customers who haven’t churn will retain, hence we don’t know what is the average customer period.

Slide 32

Slide 32 text

32 It is said that you can estimate the average lifetime period based the churn rate. Average Lifetime = 1 / Churn Rate If the churn rate is 10% (0.1) : 10 month = 1 / 0.1

Slide 33

Slide 33 text

33 But, the churn rate (or retention rate) changes over the customer’s lifetime period.

Slide 34

Slide 34 text

34 Here’s a chart showing the customers’ retention rate through each period. 35% 100% 40% 32% 60% 48% 1st Month 2nd Month 3rd Month 4th Month 5th Month 6th Month

Slide 35

Slide 35 text

35 Each retention rate on this chart indicates the rate of customers who would retain through a given period. 48% 48% of Customers would retain through 2nd month. 35% 100% 40% 32% 60% 1st Month 2nd Month 3rd Month 4th Month 5th Month 6th Month

Slide 36

Slide 36 text

36 48% Month 35% 100% 40% 32% 60% In the world of Data Science / Statistics, this curve is called ‘Survival Curve’ and each ‘retention rate’ is called ‘Survival Rate’.

Slide 37

Slide 37 text

Typically, customers tend to churn more at the early periods than at the later periods. 37 Early Later

Slide 38

Slide 38 text

38 40% 32% 60% 48% 40% 20% 17% 100% 35% It could be that the onboarding experience is not good or simply that customers couldn’t ﬁnd much values of the service…

Slide 39

Slide 39 text

39 But, once the customers passed the certain period of time they tend to retain longer because these are the ones who have found good values of the service. 40% 32% 60% 48% 35% 12% 9%

Slide 40

Slide 40 text

40 This creates Churn Rate Paradox.

Slide 41

Slide 41 text

41 Let’s consider this rapidly growing service with many newer customers.

Slide 42

Slide 42 text

42 The recent months observe higher churns but this is expected because newer customers tend to churn at higher rate. 40% 40%

Slide 43

Slide 43 text

This makes the average churn rate for the recent months higher than the one for the older months. 43

Slide 44

Slide 44 text

44 Now, let’s consider another business that is not growing. It has many retained customers but with fewer newer customers.

Slide 45

Slide 45 text

45 9% 9% The recent months observe lower churns but again, this is expected because older customers tend to churn at lower rate.

Slide 46

Slide 46 text

46 9% This makes the average churn rates for the recent months lower, hence the churn rate is getting better than the older months.

Slide 47

Slide 47 text

When the business grows rapidly the churn rate goes up. When the business grows slowly the churn rate goes down. 47 A lot of new customers could mean higher chance of churn. A lot of old customers could mean lower chance of churn.

Slide 48

Slide 48 text

48 Churn Rate Paradox The average Churn Rate greatly depends on whether your business is growing with many new customers or is stable with not many new customers.

Slide 49

Slide 49 text

49 48% Month 35% 100% 40% 32% 60% Here’s a good news. We can actually use the survival rate data. We can use a series of the survival rates through the entire period to calculate the CLV.

Slide 50

Slide 50 text

50 48% Month 35% 100% 40% 32% 60% Let’s assume our ARPU (Average Revenue Per User) is $100.

Slide 51

Slide 51 text

51 48% Month 35% 100% 40% 32% 60% Let’s say we had 100 customers to begin with. 100 Customers

Slide 52

Slide 52 text

52 48% Month 35% 100% 40% 32% 60% At the beginning, 100 customers (100%) make their initial payment. This means that the expected revenue from this period (0) is $10,000. Revenue: 10,000 = 100 * 100

Slide 53

Slide 53 text

53 48% Month 35% 100% 40% 32% 60% That means that the expected revenue per customer is $100. Expected Revenue per Person: 10,000 (total revenue) / 100 (customers) = $100 OR $100 * 1 (100%) = $100

Slide 54

Slide 54 text

54 48% Month 35% 100% 40% 32% 60% After the 1st month, 60 customers (60%) make another payment, and the expected revenue from this period (1) will become $6,000. Revenue: 6,000 = 100 * 60

Slide 55

Slide 55 text

55 48% Month 35% 100% 40% 32% 60% And, the expected revenue per customer for this period is $60. Expected Revenue per Person: 6,000 (total revenue) / 100 (customers) = $60 OR $100 * 0.6 (60%) = $60

Slide 56

Slide 56 text

56 48% Month 35% 100% 40% 32% 60% After the 2nd month, 48 customers (48%) make another payment, and the expected revenue from this period (2) will become $4,800. Revenue: 4,800 = 100 * 48

Slide 57

Slide 57 text

57 48% Month 35% 100% 40% 32% 60% Expected Revenue per Person: 4,800 (total revenue) / 100 (customers) = $48 OR $100 * 0.48 (48%) = $48 And, the expected revenue per customer for this period is $48.

Slide 58

Slide 58 text

58 Month We can calculate the Expected Revenue per Customer for each period. $48 $60 $40 $35 $32 $100 $28

Slide 59

Slide 59 text

59 Month Then, we can add them up. Total Expected Revenue per Customer: $343 $48 $60 $40 $35 $32 $100 $28

Slide 60

Slide 60 text

60 Month And, this is the CLV! CLV: $343 $48 $60 $40 $35 $32 $100 $28

Slide 61

Slide 61 text

61 CLV = sum(Survival Rate through Ti * ARPU) T is a given time and i is an index. e.g. 1st month, 2nd month, 3rd month… If we put that into a formula…

Slide 62

Slide 62 text

62 Once we have the Survival Curve, we can quickly calculate the CLV!

Slide 63

Slide 63 text

63 With Exploratory…

Slide 64

Slide 64 text

64 1. Create the Survival Curve under the Analytics view.

Slide 65

Slide 65 text

65 You can export the survival rate data under the ‘Data’ tab.

Slide 66

Slide 66 text

66 Calculate the ARPU for each period then add them up.

Slide 67

Slide 67 text

67 Let’s do it!

Slide 68

Slide 68 text

68 1. Prepare Data for Survival Curve 2. Run Survival Curve 3. Export Survival Rate Data 4. Calculate Expected Revenue for Each Period 5. Summarize the Revenue to Calculate CLV

Slide 69

Slide 69 text

69 We’ll use a sample data published at Exploratory Data Catalog.

Slide 70

Slide 70 text

70 Select ‘Data Catalog’ from the Data Frame menu.

Slide 71

Slide 71 text

Search with ‘Cohort’ and click the Import button for ‘Sample Data for Cohort Analysis - Survival Curve’.

Slide 72

Slide 72 text

The data is imported.

Slide 73

Slide 73 text

73 Data under the Table view. Event Status

Slide 74

Slide 74 text

• One row represents one observation. • Each observation needs to have the following information. • Start Time • End Time • Event Status: e.g. Cancelled, Quit, Died, etc. Data Requirement for Survival Curve

Slide 75

Slide 75 text

75 Expected Input Data for Survival Curve Unique Observation ID Event Status Survival Period

Slide 76

Slide 76 text

76 You can check ‘Survival Curve’ seminar for more details.

Slide 77

Slide 77 text

Slide 78

Slide 78 text

78 Now, let’s run the Survival Curve!

Slide 79

Slide 79 text

79 Select ‘Survival Curve’ under the Analytics view.

Slide 80

Slide 80 text

80 Assign the ﬁrst_date, the last_date, and the canceled columns as follows.

Slide 81

Slide 81 text

81 We’ve got our ﬁrst survival curve!

Slide 82

Slide 82 text

82 The default period is set automatically based on the data period.

Slide 83

Slide 83 text

83 We can change it to ‘Month’ to see the monthly survival rates.

Slide 84

Slide 84 text

84 We have about 30% of the customer who churned by the end of the ﬁrst month.

Slide 85

Slide 85 text

The ‘Data’ tab shows the raw data.

Slide 86

Slide 86 text

86 Click on the Export button and select ‘Save Table Data as New Data Frame.’

Slide 87

Slide 87 text

87 Save it as a new data frame.

Slide 88

Slide 88 text

Open the newly created data frame. 88

Slide 89

Slide 89 text

89 We can calculate the expected revenue as: Expected Revenue for Each Period = Survival Rate * ARPU

Slide 90

Slide 90 text

We’ll use the Survival Rate column to calculate the ARPU. 90

Slide 91

Slide 91 text

91 Select ‘Create Calculation’ from the column header menu.

Slide 92

Slide 92 text

This time, we’ll assume that the ARPU is $100. 92

Slide 93

Slide 93 text

93 Now we have the expected revenue calculated for each period.

Slide 94

Slide 94 text

We can use Summary Table to calculate the total. 94 The CLV is $608.74!

Slide 95

Slide 95 text

95 One more thing…

Slide 96

Slide 96 text

96 We can draw the survival curves by cohorts (groups). Here, I’ve created two cohorts of customers based on what device they use. PC Users Mobile Users

Slide 97

Slide 97 text

97 PC Users Mobile Users If the survival rates are different between the cohorts the CLVs should be different, too.

Slide 98

Slide 98 text

98 If we can calculate the CLV more accurate for each cohort (or segment) then we can estimate how much we can spend for acquiring customers even better!

Slide 99

Slide 99 text

99 Assign ‘Country’ column to Color By to create cohorts of countries.

Slide 100

Slide 100 text

100 There are too many countries, we can keep the top 3 most frequent countries and put everything else under the Other group.

Slide 101

Slide 101 text

101 Once you click the Run button, you’ll get the survival rates calculated for each period and for each country.

Slide 102

Slide 102 text

102 We can quickly check the survival curves for the cohorts.

Slide 103

Slide 103 text

Export the Survival Rate data and save it as a new data frame.

Slide 104

Slide 104 text

No content

Slide 105

Slide 105 text

Open the newly saved data frame.

Slide 106

Slide 106 text

106 Select ‘Create Calculation’ from the column header menu.

Slide 107

Slide 107 text

Again, we’ll assume that the ARPU is $100. 107

Slide 108

Slide 108 text

108 Now we have the expected revenue calculated for each period.

Slide 109

Slide 109 text

We can use the Summary Table to calculate the CLV, but this time we can assign ‘Cohort’ column to Group By to calculate the CLV for each country. 109

Slide 110

Slide 110 text

110 Conclusion 1. It's critical for any SaaS businesses to estimate the customer lifetime value (CLV) better. 2. By estimating the CLV better you can estimate how much you can spend for acquiring your customers. 3. You can use the Survival Curve to estimate the CLV. 4. And even better, you can create the survival curves for multiple cohorts so that you calculate the CLV for each cohort better.

Slide 111

Slide 111 text

Next Seminar

Slide 112

Slide 112 text

EXPLORATORY Online Seminar #45 5/19/2021 (Wed) 11AM PT Cohort Analysis Part 4 Finding What Makes Churn with Survival Model

Slide 113

Slide 113 text

No content

Slide 114

Slide 114 text

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