RFM Segmentation with LLM-Assisted Analysis

RFM segmentation is one of the oldest tricks in customer analytics, and it still earns its keep because it is cheap to compute and maps directly to action. Score every customer on Recency, Frequency, and Monetary value, and you can tell champions from lapsed accounts using nothing but transaction history. What has changed recently is what you can bolt onto the end of it: a language model that turns those score triples into named segments, per-segment playbooks, and plain-language answers — provided you draw a hard line about which side owns the numbers.

This is a practitioner's take on doing RFM with an LLM-assisted analysis layer without letting the model invent anything. The scoring stays deterministic in SQL; the model only ever sees aggregates it is asked to describe, not compute. That division of labor is the whole game. Get it right and you get faster, more consistent segment analysis; get it wrong and you get a confident, wrong number in front of a stakeholder.

The Deterministic Half: Scoring RFM

The scoring is standard and belongs in the warehouse. From a table of purchase events keyed to a resolved customer, compute the three raw measures, then rank each 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. The one subtlety worth flagging: recency is inverted — a small recency_days is good, so the freshest buyers must land in the top quintile. Everything downstream depends on that table being correct and reproducible, which is exactly why a language model has no business anywhere near 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 picks up where that mart leaves off.

The Line You Do Not Cross

The single most important rule of LLM-assisted analytics: the model never computes a metric that reaches a decision. Language models are fluent and unreliable at arithmetic over many rows — they will happily average a column wrong and present it with total confidence. So the boundary is drawn deliberately:

Concretely, that means the model is handed a small, already-aggregated table — segment-level counts, average recency, revenue share — and asked to reason strictly from it. It is never handed raw customer rows, both for accuracy and for privacy. If a number appears in its output, that number must trace back to a cell you gave it.

Where the LLM Actually Earns Its Place

With the boundary set, there are four jobs the model does genuinely well.

A Worked Example

Say the SQL step produced this segment summary. These figures are illustrative — the point is the shape of what the model receives, not the values:

Handed that table with an instruction to reason only from it, a well-prompted model returns something like: "At-risk customers are 11% of the base but hold 18% of revenue and their recency has dropped to the bottom two quintiles — this is the segment to prioritize, because these are proven high-value buyers going quiet, 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 sentence came from the table. The judgment — prioritize at-risk, suppress lapsed — is the part worth a person's attention, and it is the part the model is genuinely useful for surfacing quickly.

Who Owns What

Guardrails and Dead Ends

• Letting the model do math. The failure mode that ruins trust. If a figure influences a decision, compute it in SQL and pass it in — do not ask the model to derive it.
• Sending raw customer rows. Work at the segment-aggregate level. It is more accurate, cheaper in tokens, and keeps personally identifying data out of the prompt entirely — the interpretation layer only needs the shape of the segments, not the identities in them.
• Over-segmentation. Twenty-five score triples is not twenty-five strategies. Collapse them into a handful of segments people can actually act on; the model is good at proposing that collapse, but keep it deliberately coarse.
• Treating output as a system of record. The model produces drafts — labels, playbooks, copy — that a person signs off on. The warehouse table remains the source of truth; the LLM output is decoration on top of it.
• Skipping verification. Spot-check every number the model echoes against the source table before it reaches a stakeholder. This is the same discipline as multi-model verification for enterprise reports — trust nothing a model asserts about data without checking it against the data.

Frequently Asked Questions

Related reading: GA4 + BigQuery: Customer-Intelligence Pipeline Patterns — the pipeline that produces the RFM mart this article analyzes. Multi-Model Verification: Catching LLM Hallucinations in Enterprise Reports — the verification discipline that keeps an AI analysis layer honest. AI in Enterprise Data Work — What Actually Ships — where AI adds value in data work and where it stalls. For the hardware side of running models locally, see Blendlogic Tech.