Magento Business Intelligence Help Center

Recency, frequency, monetary (RFM) analysis

In this article, we demonstrate how to set up a dashboard that will allow you to segment your customers by their recency, frequency, and monetary rankings. RFM analysis is a marketing technique that takes customer behaviors into account to help you determine segmentation for outreach. It takes three aspects into account: Recency in how recently a customer purchased from your store, Frequency in how often they purchase from you and Monetary in how much the customer spends.

This analysis contains advanced calculated columns that can only be created by a Magneto BI analyst, so file a support request with the subject [RFM ANALYSIS], before getting started with this analysis.

Columns to create

    • customer_entity table
      • Customer's last order date
        • Select a definition: Max
        • Select table: sales_flat_order
        • Select column: created_at
        • Path: sales_flat_order.customer_id = customer_entity.entity_id
        • Filter:
          • Orders we count
      • Seconds since customer's last order date
        • Select a definition: Age
        • Select column: Customer's last order date
      • [INPUT] Reference for RFM will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Number of customers will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • [INPUT] Ranking by customer lifetime revenue will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Ranking by customer lifetime revenue will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Customer's monetary score (by percentiles) will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • [INPUT] Ranking by customer lifetime number of orders will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Ranking by customer lifetime number of orders will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Customer's frequency score (by percentiles) will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Ranking by days since customer's last order will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Customer's recency score (by percentiles) will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Customer's RFM score (by percentiles) will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Number of customers (RFM > 0) will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Customer's RFM score (R+F+M) will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • [INPUT] Ranking by customer's overall RFM score will be created by an analyst as part of your [RFM ANALYSIS] ticket
    • Count reference File Upload table (to be created by an analyst)
      • Number of customers will be created by an analyst as part of your [RFM ANALYSIS] ticket
      • Number of customers (RFM > 0) will be created by an analyst as part of your [RFM ANALYSIS] ticket

NOTE: the percentiles used are even splits of customers (I.e. 20% buckets to return 1-5). If you have a custom way you would like to weight these, please let the analyst know when you submit the ticket.

No new metrics!

Note: Make sure to add all new columns as dimensions to metrics before building new reports.

  • Customers by RFM grouping
    • Metric A: New customers
      • Metric: New customers
      • Filter:
        • Customer's RFM score (by percentiles) Not Equal to 000
    • Time period: All time
    • Interval: None
    • Hide chart
    • Group by: Customer's RFM group
    • Group by: Email
    • Chart type: Table
  • Customers with 5 recency score
    • Metric A: New customers
      • Metric: New customers
      • Filter:
        • Customer's recency score (by percentiles) Equal to 5
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
    • Hide chart
    • Group by: Email
    • Group by: Customer's RFM score (R+F+M)
    • Chart type: Table
  • Customers with 1 recency score
    • Metric A: New customers
      • Metric: New customers
      • Filter:
        • Customer's recency score (by percentiles) Equal to 1
    • Time period: All time
    • Interval: None
    • Chart Type: Scalar
    • Hide chart
    • Group by: Email
    • Group by: Customer's RFM score (R+F+M)
    • Chart type: Table

After compiling all the reports, you can organize them on the dashboard as you desire. The end result may look like the above sample dashboard, but the three generates tables are just examples of the types of customer segmentation you can perform.

If you run into any questions while building this analysis, or simply want to engage our professional services team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk