RFM Analysis: How to Segment Customers by Purchase Behavior

What RFM is and why it helps

RFM splits your customer base by actual behavior rather than demographics. Each customer gets three scores:

• Recency (R): days since the last purchase. Fewer days means a higher score.
• Frequency (F): how many purchases they made in the chosen period.
• Monetary (M): how much they spent in that same period.

Each metric is usually mapped to a 1 to 5 scale. A customer coded 555 bought recently, buys often and spends a lot. A 155 customer used to be among your best but has gone quiet, and is worth winning back.

The point is that one message does not fit everyone. A discount for a loyal customer eats margin, and a "thanks for your loyalty" email to a newcomer sounds odd.

What data you need

An orders table with three fields is enough: customer ID, order date, order amount. Export it from your CRM, accounting system or store admin.

Before calculating:

• Merge duplicate customers. One person with several phone numbers or emails distorts frequency.
• Remove cancelled and refunded orders.
• Pick an analysis window that matches your purchase cycle: shorter for everyday goods, longer for expensive or infrequent purchases.
• Fix the calculation date. Recency is measured from it.

How to calculate RFM in a spreadsheet

1. Build a per-customer summary: last order date (MAXIFS), order count (COUNTIFS), total amount (SUMIFS).
2. Recency: =calc_date - last_order_date.
3. Assign scores. Two approaches:
   • quantiles: split customers into five equal groups per metric (PERCENTRANK or QUARTILE);
   • fixed thresholds: for frequency, for example "1 order", "2 orders", "3 to 4", "5 or more".
4. Concatenate the three scores into a segment code, for example =R&F&M.

Thresholds usually work better than quantiles for frequency: many customers have a single purchase, and quantiles push identical values into different groups.

How to calculate RFM in SQL

A PostgreSQL example:

WITH stats AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date)::date AS recency_days,
    COUNT(*)                             AS frequency,
    SUM(amount)                          AS monetary
  FROM orders
  WHERE status = 'paid'
    AND order_date >= CURRENT_DATE - INTERVAL '24 months'
  GROUP BY customer_id
)
SELECT
  customer_id,
  NTILE(5) OVER (ORDER BY recency_days DESC) AS r,
  CASE WHEN frequency >= 5 THEN 5
       WHEN frequency >= 3 THEN 4
       WHEN frequency = 2  THEN 3
       ELSE 1 END                            AS f,
  NTILE(5) OVER (ORDER BY monetary)          AS m
FROM stats;

Recency is sorted descending, so the longest-absent customers get 1 and the most recent get 5. Tune the frequency thresholds to your own data.

Typical segments and what to do with them

Treat the table as a starting point, not a standard. Adapt segment names and boundaries to your business.

Common mistakes

• One window for every product. If you sell both consumables and equipment, calculate RFM per category.
• Discounts for everyone. Active customers often do not need a discount; they would have bought anyway.
• A one-off calculation. RFM pays off when recalculated regularly so you can see customers moving between segments.
• No control group. To know whether a campaign worked, hold out part of the segment and compare.