← Back to all posts RFM Segmentation + LLM-assisted analysis R F M SQL quintiles LLM layer labels · playbooks plain-language Q&A Champions At-risk Win-back

RFM Segmentation with LLM-Assisted Analysis

Jul 2026 · Data Engineering

RFM segmentation is one of the oldest methods in customer analytics. It's still useful because it's cheap to compute and points straight to an action. Score every customer on Recency, Frequency, and Monetary value, and you can tell champions from lapsed accounts with nothing but transaction history. What's new is what you can add at the end. A language model can turn those three scores into named segments, playbooks for each segment, and plain-language answers. You just have to be strict about where the numbers come from.

This is how I do RFM with an LLM on top without letting the model make anything up. The scoring stays in SQL. The model only sees totals that SQL already computed, and its job is to describe them. If you keep that split, you get faster and more consistent segment analysis. If you don't, a stakeholder ends up looking at a wrong number that the model stated with full confidence.

Quick answer: Compute R, F, and M and their quintile scores in SQL against your source of truth. Don't let the model compute them. Then give the finished segment summary table to an LLM (self-hosted when the data belongs to a client, so nothing is uploaded to a hosted service) and use it for four jobs: labeling score triples as named segments, drafting an action playbook for each segment, turning plain-language questions into the number that answers them, and writing first-draft campaign copy. SQL produces every number, and the model writes the words about them.

The Deterministic Half: Scoring RFM

The scoring is standard, and it belongs in the warehouse. Start from a table of purchase events tied to a resolved customer. Compute the three raw measures, then rank each one into quintiles with NTILE(5).

WITH rfm_raw AS (
  SELECT
    customer_key,
    DATE_DIFF(CURRENT_DATE(), DATE(MAX(purchase_ts)), DAY) AS recency_days,
    COUNT(*)                                               AS frequency,
    SUM(purchase_value)                                    AS monetary
  FROM purchases
  GROUP BY customer_key
)
SELECT
  customer_key,
  recency_days, frequency, monetary,
  -- fewer days since last order = higher recency score, so invert
  6 - NTILE(5) OVER (ORDER BY recency_days)  AS r_score,
  NTILE(5)     OVER (ORDER BY frequency)     AS f_score,
  NTILE(5)     OVER (ORDER BY monetary)      AS m_score
FROM rfm_raw

That gives every customer a score triple like 5-4-5 or 2-1-1. Watch out for one thing. Recency is flipped. A small recency_days is good, so the most recent buyers have to land in the top quintile. Everything after this depends on that table being correct and giving the same result every time, so keep the language model out of this step.

If you are building this on top of the GA4 export, the recency/frequency/monetary derivation from raw purchase events is covered end to end in GA4 + BigQuery: Customer-Intelligence Pipeline Patterns. This article starts where that mart ends.

The Model Never Does the Math

The most important rule in LLM-assisted analytics is that the model never computes a metric that feeds a decision. Language models write well, but they're bad at math over many rows. A model will average a column wrong and present it with total confidence. So I split the work like this.

SQL / warehouse R, F, M values quintile scores segment counts & revenue numbers | words LLM layer segment labels action playbooks plain-language answers
Everything that is a number is computed left of the line; everything that is language or judgment lives right of it. The LLM only ever reads the aggregates SQL already produced.

In practice, the model gets a small summary table (counts per segment, average recency, revenue share) and has to reason only from it. It never gets raw customer rows, for accuracy and for privacy. When the data belongs to a client, the model runs self-hosted on hardware I control, so nothing is uploaded to a hosted AI service. Every number in its output has to match a cell you gave it.

Four Jobs Where an LLM Helps

Once SQL owns the numbers, the model is good at four jobs.

1. Labeling segments

Start here

Map score triples to named segments like champions, loyal, at-risk, and lapsed, and keep the definitions the same and written down from run to run. This is the boring part of RFM, and it's easy to let it drift.

2. Action playbooks

Highest leverage

For each segment, draft the recommended move: nurture, upsell, win-back, or suppress. The model is good at proposing the play; a human approves it.

3. Plain-language Q&A

Analyst multiplier

Turn a question like "which good customers stopped buying?" into the number that answers it, then explain the result in a sentence a stakeholder can read without knowing SQL.

4. Campaign copy

Activation

Draft the first version of email or ad copy for each segment. At-risk customers get reassurance, win-back gets an incentive, and champions get a thank-you.

A Worked Example

Say the SQL step produced this segment summary. I made these figures up to show the shape of what the model receives.

Segment Score pattern Customers Revenue share
ChampionsR 5, F 4–5, M 4–58%41%
LoyalR 3–4, F 4–5, M 3–514%27%
At-riskR 1–2, F 3–5, M 3–511%18%
LapsedR 1–2, F 1–2, M 1–232%4%

Give a model that table, tell it to reason only from it, and it returns something like this: "At-risk customers are 11% of the base but hold 18% of revenue and their recency has dropped to the bottom two quintiles. Prioritize this segment, because these are proven high-value buyers who have stopped buying, not low-value churn. Lapsed accounts are a third of the base but only 4% of revenue; suppress rather than spend on them." Every number in that answer came from the table. The useful part is the call (prioritize at-risk, suppress lapsed). A person should review it, and the model gets you there fast.

Most of the work is in the prompt. Give the model only the summary table. Tell it to reason only from that table, to say so when the data doesn't support a claim, and to never use a figure that isn't in the input. Then check every number it repeats against the source before it goes anywhere.

Who Owns What

Task Deterministic (SQL) LLM-assisted
Recency / frequency / monetary valuesYesNever
Quintile scoringYesNever
Segment counts & revenue shareYesNever
Naming & defining segmentsRules possibleBetter fit
Per-segment action playbookNoYes (human-approved)
Plain-language interrogationNoYes
Draft campaign copyNoYes (human-edited)

Guardrails and Dead Ends

Frequently Asked Questions

What is RFM segmentation?
RFM segmentation scores each customer on Recency (how recently they bought), Frequency (how often they buy), and Monetary value (how much they spend). Each one is usually scored in quintiles from 1 to 5, and the three scores together sort customers into groups you can act on, such as champions, loyal, at-risk, and lapsed. It's one of the oldest customer-analytics methods, and it's still useful because it only needs transaction history and points straight to a marketing action.
What are the standard RFM segments?
Common segments are champions (bought recently, buy often, spend the most), loyal customers (buy often, high value, a little less recent), potential loyalists (recent, buying more often), new customers (very recent, low frequency), at-risk (used to buy often or spend a lot, but haven't bought lately), and lapsed or hibernating (low on all three). Where you draw the lines is your choice. A language model helps keep those definitions the same from run to run and written down.
Is LLM-assisted RFM analysis safe for sensitive customer data?
It can be, if you keep personal data out of the prompt. Send segment totals (counts, average recency, revenue share per segment) instead of individual customer records. Decide up front what gets kept and who can query it. For client or regulated data, use a private or self-hosted model. That's what I do, and nothing gets uploaded to a hosted AI service. The model only needs the shape of each segment, not who is in it.

Building customer analytics with AI in the loop?

I run a free community for people building real data and AI pipelines: warehouse modeling, segmentation, and analysis with AI in the loop. It's free, and the members are people building real projects.

Join the free AI community →

Related reading: GA4 + BigQuery: Customer-Intelligence Pipeline Patterns covers the pipeline that produces the RFM mart used here. Multi-Model Verification: Catching LLM Hallucinations in Enterprise Reports covers how I check AI analysis for made-up numbers. AI in Enterprise Data Work — What Actually Ships covers where AI helps in data work and where it stalls. For the hardware side of running models locally, see Blendlogic Tech.

Get the build notes

My AI experiments: what worked, what didn’t, and how I set them up. Sent to your inbox.

No spam, unsubscribe anytime · Privacy

More in AI & Building →