RFM(T) segmentation: the complete guide

Everything I know about customer segmentation in one place: the strategy, the SQL, the profit-weighting upgrade most people skip, the results it drove at a fintech, and a free template you can copy or download. Written from running it in production, not from a textbook.

By Gökhan Ağarer · Growth Marketing Manager, Berlin · Updated 23 June 2026

Why a single average hides everything

When I joined the growth side at PayTR, the dashboards told a comforting story: average revenue per merchant, average transactions, overall active count. The problem with an average is that it blends a merchant who processes payments every day with one who signed up and never came back. They get treated the same in reporting, and worse, the same in marketing.

In a two-sided fintech, that blending is expensive. Acquiring a new merchant costs real money, but a merchant one bad week from churning costs you future revenue silently. You can't act on either if you only see the average. You need to split the base into groups that behave differently and deserve different treatment. That's what RFM does.

What RFM(T) measures

RFM scores every customer on three simple, behavioral questions, and I add a fourth for fintech:

• Recency. How recently did they transact? Active yesterday is a very different risk from last seen 90 days ago.
• Frequency. How often do they transact? Frequency separates a habit from a one-off.
• Monetary. How much value do they move? Not every active customer is equally valuable.
• Tenure (the T). How long have they been a customer? A brand-new high-spender and a loyal three-year merchant need different messages even when R, F, and M look identical.

How to score it (the part people overcomplicate)

You don't need a data-science team. Rank customers into five buckets per dimension using quintiles, so each ends up with a score like R5 F4 M5 T3. In SQL that's NTILE(5) over each metric. Two rules save you pain: reverse recency so 5 always means "best," and use quintiles rather than hardcoded cutoffs, so the scoring rebalances itself when your base shifts (a seasonal spike, a new market) instead of rotting.

The upgrade that mattered most: weight it by profit

Textbook RFM has a blind spot, and in a payments business it's a costly one. Classic RFM treats a merchant who transacts constantly on razor-thin margin exactly like one who transacts just as often but is genuinely profitable. By volume they look identical. By what they're worth to the business, they're not even close.

So the version that actually worked wasn't pure RFM, it was profit-weighted. Instead of scoring monetary value on payment volume alone, I blended a profitability signal into the score, so margin, not just throughput, decided where a merchant landed. I also added an override so a genuinely high-profit merchant is never buried in a low tier just because their raw frequency is average. In a business where different merchants carry very different economics, this is the difference between a segmentation that looks tidy and one that actually points budget at the right accounts.

From scores to named tiers you can act on

A code like "R5 F4 M5" is useless to a marketing team. The value is mapping it to a handful of plain-language tiers, each with one obvious action. Name them in business language, not score codes: an account manager should open the dashboard and see "strategic" or "at-risk," and know what to do. This is the map I worked from:

The free SQL template

Here's the whole thing as runnable SQL, generic table names, profit-weighting and the named tiers built in. Copy it below or download the file. No email wall.

WITH params AS (
  SELECT CURRENT_DATE AS as_of_date,
         DATE_SUB(CURRENT_DATE, INTERVAL 12 MONTH) AS window_start
),
-- 1) One clean row per customer
customer_base AS (
  SELECT t.customer_id,
    MAX(t.transaction_date)                 AS last_txn_date,
    COUNT(*)                                AS frequency,
    SUM(t.amount)                           AS monetary,
    SUM(COALESCE(t.margin, t.amount * 0.0)) AS total_profit,
    MIN(t.signup_date)                      AS signup_date
  FROM source_transactions t                 -- <-- YOUR table
  CROSS JOIN params p
  WHERE t.transaction_date >= p.window_start
  GROUP BY t.customer_id
),
-- 2) Recency / Tenure in days
customer_metrics AS (
  SELECT cb.customer_id,
    DATE_DIFF(p.as_of_date, cb.last_txn_date, DAY) AS recency_days,
    cb.frequency, cb.monetary, cb.total_profit,
    DATE_DIFF(p.as_of_date, cb.signup_date, DAY)   AS tenure_days
  FROM customer_base cb CROSS JOIN params p
),
-- 3) Quintile scores (recency reversed so 5 = best)
scored AS (
  SELECT customer_id, recency_days, frequency, monetary, total_profit, tenure_days,
    6 - NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency ASC)        AS f_score,
    NTILE(5) OVER (ORDER BY monetary ASC)         AS m_score,
    NTILE(5) OVER (ORDER BY total_profit ASC)     AS p_score,
    NTILE(5) OVER (ORDER BY tenure_days ASC)      AS t_score
  FROM customer_metrics
),
-- 4) Profit-weighted value score (60% profit / 40% revenue)
weighted AS (
  SELECT *, CAST(ROUND(0.6 * p_score + 0.4 * m_score) AS INT) AS value_score
  FROM scored
)
-- 5) Map to named tiers (high-profit override on the first line)
SELECT customer_id,
  CASE
    WHEN p_score = 5 AND r_score >= 3                       THEN 'Strategic'
    WHEN r_score >= 4 AND f_score >= 4 AND value_score >= 4 THEN 'Strategic'
    WHEN f_score >= 4 AND value_score >= 3                  THEN 'High-Value'
    WHEN r_score >= 4 AND f_score <= 2 AND t_score <= 2     THEN 'Promising / New'
    WHEN r_score <= 2 AND f_score >= 3                      THEN 'At-Risk'
    WHEN r_score <= 2 AND f_score <= 2                      THEN 'Dormant'
    ELSE 'Developing'
  END AS tier,
  r_score, f_score, m_score, p_score, t_score, value_score
FROM weighted
ORDER BY value_score DESC, r_score DESC;

Adapt it in three lines: point source_transactions at your table, map the columns, set the window. No profit column? Set the profit weight to 0 and it collapses cleanly to classic RFM. The downloadable file has fuller comments and the full segment map.

Make it live, not a one-off

A one-off RFM analysis in a notebook is dead the day you close the laptop. The version that drove results was a production model: the scoring logic lived in the semantic layer of our BI model (built in DAX), refreshed automatically, and surfaced the tiers directly in the dashboards leadership and the commercial team already used every day. Wired to live data through a composite connection rather than a static extract, the tiers rebalanced as the business moved, so a merchant sliding from "high-value" toward "at-risk" showed up on its own, in time to act, instead of in a quarterly review after they'd already gone quiet.

The results it drove

Two things moved once we ran the base through this. First, budget reallocation: we'd been spending heavily to acquire new merchants while letting at-risk merchants, who had already proven their value, slip away. Shifting a slice of budget into targeted win-back and onboarding was far cheaper per retained dollar than net-new acquisition.

Second, message fit: new merchants got an aggressive onboarding sequence (tenure data showed the first 30 days were make-or-break), and high-value tiers got recognition instead of discounts. Over the period I owned this, the active merchant base grew 65%+ and acquisition cost dropped 30%+, because we stopped paying to re-acquire people we already had and stopped discounting people who didn't need it.

Where teams get it wrong

• Scoring once and forgetting it. RFM is a snapshot; customers move between tiers constantly. Re-score on a schedule or the segments go stale within weeks.
• Too many tiers. If your team can't recite the action for each tier from memory, you have too many. Five to seven is plenty.
• No feedback loop. Tie each tier's campaign back to revenue, so you know the win-back actually won anyone back. Without that, you're just sorting customers prettily.
• Skipping profit. Pure revenue scoring rewards your thinnest-margin volume. Weight by profit or you'll back the wrong accounts.

The takeaway

RFM(T) isn't a clever model; it's a refusal to treat all your customers the same. The math takes an afternoon. The value is in the discipline around it: weight by profit so you back the right customers, name the tiers so people act, and run it live so it never goes stale. Do that, and the growth hiding inside your existing base becomes the cheapest growth you have.

Gökhan Ağarer

Growth Marketing Manager · Berlin

I'm a growth marketer who builds the analytics underneath the campaigns. I ran a €1.5M+ paid-media budget at PayTR and built the commercial-intelligence and segmentation models behind it. This guide is generalised from that production work, with no proprietary data.