Save 37% off PRO during our Black Friday Sale! »

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

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

19fc8f6113c5c3d86e6176362ff29479?s=128

Kan Nishida
PRO

April 28, 2021
Tweet

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

  62. 62

  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
  95. None
  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