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

It's critical for any SaaS business to understand what the customer lifetime value (CLV) is. Without accurately estimating the customer lifetime value you don't know how much you can spend for acquiring customers via Sales or Marketing. However, calculating the CLV has been somewhat mysterious.

In this seminar, Kan is going to introduce a way to estimate the CLV by using the Survival Curve data.

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 #44 Cohort Analysis Part 3 CLV (Customer Lifetime Value) with Survival Curve
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 #44 Cohort Analysis Part 3 CLV (Customer Lifetime Value) with Survival Curve
7. 7 Agenda • What and Why CLV? • How to Calculate? • Calculating CLV for Each Cohort
8. 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.
9. For SaaS businesses, the initial payment from a customer tends to smaller but it accumulates over time.
10. 10 Jan $100 You sell a product to one user and collect the money right a way. Non Subscription Model
11. 11 This means, you can spend $80 to acquire this customer but still can make a profit. Jan $100 Expense Revenue Profit
12. 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
13. 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.
14. 14 Jan $10 You collect only the monthly subscription amount at the first month in case of the monthly subscription.
15. 15 If you spend $80 to acquire the customer you will lose money. Jan $10 Subscription Expense $80
16. 16 Jan Feb Mar Total … Dec $120 But, you’ll accumulate the revenue from the same customer over time. $10 $10 $10 $10
17. 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
18. 18 Jan Feb Mar Total … Dec $120 Expense $80 $10 $10 $10 $10 Customer Lifetime Value
19. 19 It is important for any SaaS / Subscription type businesses to estimate the CLV as accurate as possible. How to calculate it?
20. 20 Jan Feb Mar Total … Dec $120 Multiply the monthly revenue by the customer lifetime of 12 months? $10 $10 $10 $10
21. But, customers upgrade or downgrade… 21 Basic Plan Pro Plan $10 $10 $20 $20 Jan Feb Mar Apr
22. 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
23. This is called ARPU (Average Revenue Per User). 23 ARPU = MRR / Number of Customers $15 ($90/6ਓ) April
24. 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
25. 25 But, there are some problems…
26. • 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.
27. Apr Mar Feb Jan Jessica Tien Nancy Victor Jane
28. Jessica Tien Nancy Victor Jane 4th Month 3rd Month 2nd Month 1st Month Align all customers based on how many months they are in.
29. ? ? ? ? 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. ? ? ?
30. ? ? ? ? 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. ? ? ?
31. ? ? ? ? 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.
32. 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
33. 33 But, the churn rate (or retention rate) changes over the customer’s lifetime period.
34. 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
35. 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
36. 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’.
37. Typically, customers tend to churn more at the early periods than at the later periods. 37 Early Later
38. 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 find much values of the service…
39. 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%
40. 40 This creates Churn Rate Paradox.
41. 41 Let’s consider this rapidly growing service with many newer customers.
42. 42 The recent months observe higher churns but this is expected because newer customers tend to churn at higher rate. 40% 40%
43. This makes the average churn rate for the recent months higher than the one for the older months. 43
44. 44 Now, let’s consider another business that is not growing. It has many retained customers but with fewer newer customers.
45. 45 9% 9% The recent months observe lower churns but again, this is expected because older customers tend to churn at lower rate.
46. 46 9% This makes the average churn rates for the recent months lower, hence the churn rate is getting better than the older months.
47. 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.
48. 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.
49. 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.
50. 50 48% Month 35% 100% 40% 32% 60% Let’s assume our ARPU (Average Revenue Per User) is $100.
51. 51 48% Month 35% 100% 40% 32% 60% Let’s say we had 100 customers to begin with. 100 Customers
52. 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
53. 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
54. 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
55. 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
56. 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
57. 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.
58. 58 Month We can calculate the Expected Revenue per Customer for each period. $48 $60 $40 $35 $32 $100 $28
59. 59 Month Then, we can add them up. Total Expected Revenue per Customer: $343 $48 $60 $40 $35 $32 $100 $28
60. 60 Month And, this is the CLV! CLV: $343 $48 $60 $40 $35 $32 $100 $28
61. 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…
62. 62 Once we have the Survival Curve, we can quickly calculate the CLV!
63. 63 With Exploratory…
64. 64 1. Create the Survival Curve under the Analytics view.
65. 65 You can export the survival rate data under the ‘Data’ tab.
66. 66 Calculate the ARPU for each period then add them up.
67. 67 Let’s do it!
68. 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
69. 69 We’ll use a sample data published at Exploratory Data Catalog.
70. 70 Select ‘Data Catalog’ from the Data Frame menu.
71. Search with ‘Cohort’ and click the Import button for ‘Sample Data for Cohort Analysis - Survival Curve’.
72. The data is imported.
73. 73 Data under the Table view. Event Status
74. • 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
75. 75 Expected Input Data for Survival Curve Unique Observation ID Event Status Survival Period
76. 76 You can check ‘Survival Curve’ seminar for more details.
78. 78 Now, let’s run the Survival Curve!
79. 79 Select ‘Survival Curve’ under the Analytics view.
80. 80 Assign the first_date, the last_date, and the canceled columns as follows.
81. 81 We’ve got our first survival curve!
82. 82 The default period is set automatically based on the data period.
83. 83 We can change it to ‘Month’ to see the monthly survival rates.
84. 84 We have about 30% of the customer who churned by the end of the first month.
85. The ‘Data’ tab shows the raw data.
86. 86 Click on the Export button and select ‘Save Table Data as New Data Frame.’
87. 87 Save it as a new data frame.
88. Open the newly created data frame. 88
89. 89 We can calculate the expected revenue as: Expected Revenue for Each Period = Survival Rate * ARPU
90. We’ll use the Survival Rate column to calculate the ARPU. 90
91. 91 Select ‘Create Calculation’ from the column header menu.
92. This time, we’ll assume that the ARPU is $100. 92
93. 93 Now we have the expected revenue calculated for each period.
94. We can use Summary Table to calculate the total. 94 The CLV is $608.74!
95. 95 One more thing…
96. 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
97. 97 PC Users Mobile Users If the survival rates are different between the cohorts the CLVs should be different, too.
98. 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!
99. 99 Assign ‘Country’ column to Color By to create cohorts of countries.
100. 100 There are too many countries, we can keep the top 3 most frequent countries and put everything else under the Other group.
101. 101 Once you click the Run button, you’ll get the survival rates calculated for each period and for each country.
102. 102 We can quickly check the survival curves for the cohorts.
103. Export the Survival Rate data and save it as a new data frame.
105. Open the newly saved data frame.
106. 106 Select ‘Create Calculation’ from the column header menu.
107. Again, we’ll assume that the ARPU is $100. 107
108. 108 Now we have the expected revenue calculated for each period.
109. 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
110. 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.
111. Next Seminar
112. EXPLORATORY Online Seminar #45 5/19/2021 (Wed) 11AM PT Cohort Analysis Part 4 Finding What Makes Churn with Survival Model
114. Information Email kan@exploratory.io Website https://exploratory.io Twitter @ExploratoryData Seminar https://exploratory.io/online-seminar
115. Q & A 115
116. EXPLORATORY 116