RFM Segmentation with LLM-Assisted Analysis
Jul 2026 · Data EngineeringRFM 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.
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 hereMap 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 leverageFor 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 multiplierTurn 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
ActivationDraft 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 |
|---|---|---|---|
| Champions | R 5, F 4–5, M 4–5 | 8% | 41% |
| Loyal | R 3–4, F 4–5, M 3–5 | 14% | 27% |
| At-risk | R 1–2, F 3–5, M 3–5 | 11% | 18% |
| Lapsed | R 1–2, F 1–2, M 1–2 | 32% | 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.
Who Owns What
| Task | Deterministic (SQL) | LLM-assisted |
|---|---|---|
| Recency / frequency / monetary values | Yes | Never |
| Quintile scoring | Yes | Never |
| Segment counts & revenue share | Yes | Never |
| Naming & defining segments | Rules possible | Better fit |
| Per-segment action playbook | No | Yes (human-approved) |
| Plain-language interrogation | No | Yes |
| Draft campaign copy | No | Yes (human-edited) |
Guardrails and Dead Ends
- Don't let the model do math. One wrong number and people stop trusting the whole analysis. If a figure feeds a decision, compute it in SQL and pass it in.
- Don't send raw customer rows. Segment totals are more accurate, use fewer tokens, and keep personal data out of the prompt. The model only needs the shape of each segment, not who is in it.
- Don't make too many segments. You don't need 25 campaigns just because you have 25 score triples. Group them into a handful of segments people can act on. The model is good at suggesting those groups, but keep the number small.
- The model's output is a draft. Labels, playbooks, and copy all need a person to sign off. The warehouse table stays the source of truth.
- Check the numbers. Before anything reaches a stakeholder, compare every number the model repeats with the source table. It's the same habit I use for multi-model verification for enterprise reports: if a model says something about the data, check it against the data.
Frequently Asked Questions
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
You're in. Check your inbox for a welcome note.