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

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.
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.
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.
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.
69. Let’s calculate the Monetary.
71. We’ve got Monetary!
72. Now let’s calculate the Frequency.
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.
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’.
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.
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?
111. One more thing…
112. We have a ‘Market’ column.
113. Can we show this heatmap for each market?
115. We can change the number of the columns to layout the charts nicely.
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
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