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

Exploratory Seminar #46 - RFM Analysis for Sale...

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

Kan Nishida

May 27, 2021
Tweet

More Decks by Kan Nishida

Other Decks in Technology

Transcript

  1. 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
  2. 3 Data Science is not just for Engineers and Statisticians.

    Exploratory makes it possible for Everyone to do Data Science. The Third Wave
  3. 5 Questions Communication (Dashboard, Note, Slides) Data Access Data Wrangling

    Visualization Analytics (Statistics / Machine Learning) ExploratoryɹModern & Simple UI
  4. 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
  5. How much have they spent in the past? 9 $1000

    $100 $5,000 $4000 $3000 Customers who have spent the most amount of money!
  6. 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!
  7. The way you approach to each customer group would be

    different. 12 Frequency $6000 $4000 $2000 Sales 15 1 45 30 60
  8. 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.
  9. 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.
  10. 16 We have positioned each customer based on their monetary

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

    amount and most often. Monetary $1,500 $1,000 $500 30 10 20 Frequency
  12. 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
  13. 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
  14. 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
  15. 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.
  16. We can use a Heatmap chart to visualize how many

    customers are in each segment. 22 Monetary Frequency
  17. 26 Once we have the data prepared, then we can

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

    like this. Each row represents each product order, not each customer.
  19. 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
  20. 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
  21. 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
  22. 38 Duration Last_Order_Date - First_Order_Date We need to calculate the

    duration first before calculating the Monetary and the Frequency.
  23. 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.
  24. 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.
  25. 43 Select the ‘Order Date’ with ‘Min’ to return the

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

    last purchasing date for each customer.
  27. 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.
  28. 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.
  29. 49 Select the ‘Order Date’ with ‘Unique’ to calculate the

    unique number of order dates for each customer.
  30. 55 There are customers who have purchased only once. We

    want to remove these customers for this analysis.
  31. 57

  32. 58

  33. 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
  34. 62 Duration Last_Order_Date - First_Order_Date We need to calculate the

    duration first before calculating the Monetary and the Frequency.
  35. 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.
  36. 66

  37. 67

  38. 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).
  39. 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
  40. 82 We can create 5 buckets for each of the

    metrics - R (Recency) F (Frequency) M (Monetary) - and visualize with Heatmap chart.
  41. 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
  42. 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.
  43. Click the cell with the most customers. These are who

    have purchased the most but haven’t come back for a long time.
  44. 104 If we bucket with the ‘Equal Frequency’ method then

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

  46. 106

  47. 107

  48. 108

  49. 109

  50. 110

  51. 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.
  52. 122 Once we have the data prepared, then we can

    assign the columns to Heatmap chart and visualize it.