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

Exploratory Seminar #46 - RFM Analysis for Sales Data

Exploratory Seminar #46 - RFM Analysis for Sales Data

RFM Analysis helps you understand your customers better based on the following three metrics.

- Recency - how recent they have purchased
- Frequency - how often they have purchased
- Monetary - how much they have spent

You can use these metrics to score the customers and segment them so that you can take more optimized actions to improve your business.

In this seminar, Kan presents a way to prepare and visualize data for RFM Analysis.

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 27, 2021
Tweet

Transcript

  1. EXPLORATORY Online Seminar #46 RFM Analysis for Sales Data

  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 Science Workflow
  5. 5 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling

    Visualization Analytics (Statistics / Machine Learning) ExploratoryɹModern & Simple UI
  6. EXPLORATORY Online Seminar #46 RFM Analysis for Sales Data

  7. 7 RFM RFM Analysis helps you understand your customers better

    based on the following three metrics. • Recency - how recent they have purchased • Frequency - how often they have purchased • Monetary - how much they have spent
  8. 8 Who are the most loyal customers?

  9. How much have they spent in the past? 9 $1000

    $100 $5,000 $4000 $3000 Customers who have spent the most amount of money!
  10. 10 But…

  11. How often have they purchased in the last 12 months?

    11 15 1 60 45 30 Some customers might not have spent a lot, but come and shop very often!
  12. The way you approach to each customer group would be

    different. 12 Frequency $6000 $4000 $2000 Sales 15 1 45 30 60
  13. 13 But, are they still purchasing?

  14. 14 Oct Aug Apr Mar Feb Sep Nov Dec Jan

    2020 2021 Last Time Purchase How recent have they purchased? (When was their last purchases?) We should also take it into account on how recent they have purchased.
  15. 15 RFM Analysis helps you understand your customers better based

    on the following three metrics. • Recency - how recent they have purchased • Frequency - how often they have purchased • Monetary - how much they have spent You can use these metrics to score the customers and segment them so that you can take more optimized actions to improve your business.
  16. 16 We have positioned each customer based on their monetary

    and the frequency scores. Monetary $1,500 $1,000 $500 30 10 20 Frequency
  17. 17 These are the ideal customers who spend the most

    amount and most often. Monetary $1,500 $1,000 $500 30 10 20 Frequency
  18. 18 These are the least ideal customers who spend the

    least amount and rarely come back. Monetary $1,500 $1,000 $500 30 10 20 Frequency
  19. 19 These are the customers who spend the most amount

    but rarely come back. Maybe we can send promotional emails to bring them back. Monetary $1,500 $1,000 $500 30 10 20 Frequency
  20. 20 These are the customers who spend the least amount

    but purchases the most often. Maybe we can introduce other products that matches with their purchasing items. Monetary $1,500 $1,000 $500 10 20 Frequency 30
  21. 21 Monetary $1,500 $1,000 $500 10 20 Frequency 30 We

    can create multiple groups based on the metrics (e.g. Monetary, Frequency) and give different colors based on the number of customers.
  22. We can use a Heatmap chart to visualize how many

    customers are in each segment. 22 Monetary Frequency
  23. 23 Data for RFM Analysis

  24. 24 Each customer is presented in each row.

  25. 25 There are the three metrics - Monetary, Frequency, and

    Recency.
  26. 26 Once we have the data prepared, then we can

    assign the columns to Heatmap chart and visualize it.
  27. 27 Typically, you get your sales data in a format

    like this. Each row represents each product order, not each customer.
  28. 28 Each customer has multiple rows for all the purchases

    he/she has made.
  29. 29 Data Wrangling Time! Sales Data RFM Data

  30. 30 Let’s do it!

  31. We’ll use a sample data from the Data Catalog.

  32. 32 Click the Import button for ‘Sample Data for RFM

    Analysis’.
  33. Once the data is imported it is automatically visualized under

    the Summary view.
  34. Each row represents each product order by a given customer.

  35. 1. Recency - How many days have passed since the

    last purchase? 2. Frequency - Total number of purchases per day 3. Monetary - Total amount of purchases per day 35 Metrics Definition
  36. 36 Sales / Duration The duration varies among the customers.

    The longer the duration is the sales tend to be more. In order to compare among the customers, we can divide the sales amount by the duration for each customer, and we’ll use this as the Monetary. Monetary - Total Sales Amount per Day
  37. 37 Number_of_Orders / Duration The same thing for the Frequency.

    The longer the duration is the number of orders tend to be more. So, we can divide the number of orders by the duration for each customer, and we’ll use this as the Frequency. Frequency - Number of Orders per Day
  38. 38 Duration Last_Order_Date - First_Order_Date We need to calculate the

    duration first before calculating the Monetary and the Frequency.
  39. 1. First Date of Purchase 2. Last Date of Purchase

    3. Total Sales 4. Number of Purchases 39 We need to calculate the following items for each customer.
  40. 40 Summarize

  41. 41 Select ‘Summarize’ from the column header menu.

  42. 42 Select the ‘Market’ and the ‘Customer ID’ columns for

    Group By so that we can summarize the data by the customer in each market.
  43. 43 Select the ‘Order Date’ with ‘Min’ to return the

    first purchasing date for each customer.
  44. 44 Select the ‘Order Date’ with ‘Max’ to return the

    last purchasing date for each customer.
  45. 45 Select the ‘Sales’ with ‘SUM’ to calculate the total

    sales amount for each customer.
  46. 46 There is a bit of challenge for calculating the

    number of purchases…
  47. 47 Each row is not necessarily an each order. If

    a customer ordered 4 products then we have 4 rows each of which is for a particular product order. It’s kind of like a line item in the invoice.
  48. 48 Instead of counting the number of rows, we can

    calculate the unique number of the Order Date values to have the number of orders.
  49. 49 Select the ‘Order Date’ with ‘Unique’ to calculate the

    unique number of order dates for each customer.
  50. 50 Click the Run button to execute.

  51. 51 Let’s fix the column names.

  52. 52 Select ‘Rename’ from the column header menu.

  53. None
  54. 54 Filter

  55. 55 There are customers who have purchased only once. We

    want to remove these customers for this analysis.
  56. 56 Select ‘Filter’ and ‘Greater than’ from the column header

    menu.
  57. 57

  58. 58

  59. We can see that the minimal number of orders is

    now 2.
  60. 60 Create Calculations

  61. 61 Now, it’s time to calculate Monetary, Frequency, and Recency.

    • Monetary - Sales / Duration • Frequency - Number of Orders / Duration • Recency - Last Date of All - Last Date of a given Customer
  62. 62 Duration Last_Order_Date - First_Order_Date We need to calculate the

    duration first before calculating the Monetary and the Frequency.
  63. None
  64. None
  65. 65 A new ‘Duration’ column is created but the data

    type is ‘difftime’. We want to convert it to ‘numeric’ so that we can use it for further calculations.
  66. 66

  67. 67

  68. None
  69. Let’s calculate the Monetary.

  70. None
  71. We’ve got Monetary!

  72. Now let’s calculate the Frequency.

  73. None
  74. We’ve got Frequency!

  75. 75 Recency max(Last_Order_Date) - Last_Order_Date We can calculate the recency

    as the difference between the last date of order for each customer and the last date of the data (everybody).
  76. 76 today() - Last_Order_Date If you have the data up

    to today you can use the ‘today’ or ‘now’ function. now() - Last_Order_Date Data Type: Date Data Type: POSIXct
  77. We’ll create the Recency.

  78. None
  79. None
  80. We’ve got Recency!

  81. We’ve got all three of RFM metrics!

  82. 82 We can create 5 buckets for each of the

    metrics - R (Recency) F (Frequency) M (Monetary) - and visualize with Heatmap chart.
  83. How can we bucket (or bin) the numerical values?

  84. 84 Equal Width method 100 200 300 400 500 600

    700 800 900 1000 Recency
  85. 85 Each group will have the same number of rows

    (customers). 100 200 300 400 500 600 700 800 900 1000 Recency Equal Frequency method
  86. We’ll use the ‘Equal Frequency’ method this time.

  87. Assign the Recency column to X-Axis.

  88. Assign the Monetary column to X-Axis.

  89. Change the ‘Category’ creation method to ‘Equal Frequency’.

  90. None
  91. 91 Very New, New, Neutral, Old, Very Old Group names

    for Recency
  92. We can update the X-Axis label names.

  93. 93 Group names for Monetary Very Low, Low, Neutral, High,

    Very High
  94. We can update the Y-Axis label names.

  95. 95 By the way, there is something weird about the

    X-Axis…
  96. 96 The recency values are time sensitive information. And we,

    human being, are so used to think the time goes from the left to the right.
  97. 97 Let’s change the order so that we can intuitively

    understand it.
  98. None
  99. Now, the Recency values are more in the natural order.

  100. Click the cell with the most customers. These are who

    have purchased the most but haven’t come back for a long time.
  101. Click on the Show Detail button.

  102. to see a list of the customers.

  103. Appendix

  104. 104 If we bucket with the ‘Equal Frequency’ method then

    shouldn’t the each box of the heatmap have the same number of customers?
  105. 105

  106. 106

  107. 107

  108. 108

  109. 109

  110. 110

  111. One more thing…

  112. We have a ‘Market’ column.

  113. Can we show this heatmap for each market?

  114. None
  115. We can change the number of the columns to layout

    the charts nicely.
  116. None
  117. None
  118. None
  119. Summary

  120. 120 RFM Analysis helps you understand your customers better based

    on the following three metrics. • Recency - how recent they have purchased • Frequency - how often they have purchased • Monetary - how much they have spent You can use these metrics to score the customers and segment them so that you can take more optimized actions to improve your business.
  121. 121 Data Wrangling! Sales Data RFM Data

  122. 122 Once we have the data prepared, then we can

    assign the columns to Heatmap chart and visualize it.
  123. That’s it for today!

  124. Next Seminar

  125. EXPLORATORY Online Seminar #47 6/2/2021 (Wed) 11AM PT Survey Data

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

  128. Q & A 128

  129. EXPLORATORY 129