Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

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

19fc8f6113c5c3d86e6176362ff29479?s=128

Kan Nishida
PRO

May 12, 2021
Tweet

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.

  77. 77

  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.
  104. None
  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
  113. None
  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