Customer segmentation: RFM, behavioral and practical methods

Treating every customer the same is expensive. You send a 20% discount to someone who would have paid full price, you notice a lapsed regular six months too late, and your support team spends an hour on a $20 account while a $2,000 account waits.

Segmentation groups customers so each group can get a different treatment. This guide starts with RFM, which needs nothing more than an order table, and then adds behavioral and value-based methods.

Method 1: RFM analysis

RFM stands for Recency, Frequency and Monetary value:

• Recency: days since the customer's last purchase. Lower is better.
• Frequency: number of purchases in the analysis window, usually the last 12 months.
• Monetary: total spend in the same window.

It is popular because it uses data every business has and produces segments you can act on the same week.

Step 1: get the data

You need three columns: customer ID, order date and order amount. Export them from your store, your billing system or your database. Exclude refunds and test orders before you start.

Step 2: calculate the raw values

This PostgreSQL query produces one row per customer:

WITH rfm_raw AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date)::date AS recency_days,
    COUNT(DISTINCT order_id)             AS frequency,
    SUM(order_total)                     AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY customer_id
)
SELECT
  customer_id,
  recency_days,
  frequency,
  monetary,
  NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
  NTILE(5) OVER (ORDER BY frequency ASC)     AS f_score,
  NTILE(5) OVER (ORDER BY monetary ASC)      AS m_score
FROM rfm_raw;

Step 3: score each dimension

NTILE(5) splits customers into five equal groups per dimension and gives the best group a 5. For recency the most recent buyers score 5, which is why that column is ordered descending by days.

One catch: in many small businesses most customers have bought once. NTILE will still split them into five groups, so two customers with identical frequency can get different F scores. If more than a fifth of your customers share the same value, use fixed thresholds instead, for example F = 1 for one order, 2 for two, 3 for three or four, 4 for five to nine, 5 for ten or more.

Step 4: group the scores into segments

Three scores from 1 to 5 give 125 combinations. Nobody can run 125 campaigns, so group them into 5 to 8 segments:

A small worked example

Say you run an online shop and look at three customers on 1 September:

• Ana: last order 6 days ago, 11 orders, $1,100. Scores R 5, F 5, M 5: a Champion.
• Ben: last order 140 days ago, 7 orders, $820. Scores R 2, F 4, M 4: At risk. He used to buy every six weeks.
• Cara: last order 3 days ago, 1 order, $45. Scores R 5, F 1, M 1: New.

Ben is the one worth a personal email this week. Ana does not need a discount. Cara needs a reason to buy a second time.

Method 2: behavioral segmentation

RFM only sees purchases. Behavioral segmentation adds what customers do between purchases: logins, feature use, email engagement, support tickets.

Rule-based segments are the place to start, because anyone can see why a customer is in a group. For a software product:

• Power users: log in on 5 or more days a week and use 3 or more core features.
• Casual users: log in weekly and use one feature.
• Struggling: fewer than 3 logins in 30 days and 2 or more support tickets.
• Dormant: no login in 30 days.

Clustering (k-means is the usual choice) finds groups you did not think of. Standardize each feature first so that a column measured in thousands does not dominate one measured in single digits, try 3 to 8 clusters, compare them with the elbow method or the silhouette score, and then name each cluster by its averages. Clusters are harder to explain and act on than rules, so check them with the people who will use them before building campaigns around them.

Method 3: value-based segmentation

Here you group customers by what they are worth, which is useful for deciding where service and marketing time goes.

Simple customer lifetime value:

CLV = average order value × purchases per year × expected years as a customer × gross margin

With round numbers: $80 × 4 orders a year × 3 years × 40% margin = $384 of gross profit.

For subscriptions:

CLV = monthly revenue per customer × gross margin ÷ monthly churn rate

For example, $50 a month × 80% margin ÷ 4% churn = $1,000. Our LTV calculator does this arithmetic for you.

Once you have a CLV per customer, a common split is top 5%, next 15%, next 30% and the remaining 50%. The top group often brings in a large share of profit, so it deserves more attention than its size suggests.

Method 4: firmographic segmentation (B2B)

For business customers, add company size, industry, region and growth stage. On their own these are targeting data for sales. Combined with behavior they become more useful: a 200-person company that uses your reporting features every day is a very different account from a same-sized company that logs in once a month.

Putting segments to work

• Marketing: a different email flow per segment. Champions get referral invitations, At risk gets a win-back sequence, New gets onboarding.
• Sales and success: route high-value, low-recency accounts to a person, not an automated email.
• Product: find out which features your Champions use most, and why a large segment never touches a feature.
• Tracking: watch segment sizes month by month. A growing At risk group is an early warning. Cohort analysis is the natural next step.

Common mistakes

• Too many segments. If you cannot name a different action for a segment, merge it.
• Scoring once. Customers move. Re-score monthly or quarterly.
• Ignoring margin. A high spender who only buys on deep discount may be worth less than a steady full-price buyer.
• No owner. A segment nobody acts on is a spreadsheet, not a strategy.

Doing this in clariBI

clariBI does not assign RFM scores for you. What it does well is the groundwork and the follow-up:

1. Connect the source your orders live in: Stripe for payments and subscriptions, a PostgreSQL or MySQL database, or a CSV export from your store.
2. Ask the raw questions in plain English: "Top 20 customers by total spend in the last 12 months", "How many customers placed 1, 2, 3 or more orders this year?", "Show revenue per customer for the last 12 months as a table".
3. Run the SQL above in your database, or in a spreadsheet, and upload the scored file as a source (CSV, Excel and JSON all work; see file uploads). clariBI builds a dashboard for it, and you can ask "Revenue by RFM segment" or "How many customers are in each segment?".
4. Re-upload next month and compare.

If SQL is new to you, our SQL guide for business analysts covers GROUP BY and window functions like NTILE. Then score last year's customers, pick the At risk segment, and send one personal email to each of them.