An RFM report gives every customer a score and often a name, such as Champions or At risk. The names sound like findings. When most customers have placed one order, though, the score says least about the group in question: first-time buyers who might buy again. RFM works better as a way to decide what to check next. A small hypothetical shop shows where the scores stop helping and which questions they can still raise.
Define the score before reading any segment
RFM summarises each customer with three values: recency (time since the last purchase), frequency (number of past purchases) and monetary value. Fader, Hardie and Lee define monetary value as the average amount per transaction. Some reports use total spend instead. Total spend equals frequency times average order value, so it counts frequency a second time. This example uses the average.
Scoring turns those values into ranks. Shopify's RFM report, for example, gives each customer three digits from 1 to 5, based only on that store's data. A 5 means the top 20% of the dimension.
Three decisions come first: the snapshot date, which orders count, and what identifies a customer. Use the shop's order data, because GA4 may not recognise a returning buyer. Check for duplicates too. One person with two email addresses becomes two one-time buyers.
The query below builds one row per customer. It was not run. The table, columns and status value are placeholders for a BigQuery table with one row per order.
WITH clean AS (
SELECT
customer_id,
order_date,
net_revenue,
ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY order_date, order_id
) AS order_no
FROM `your-project.shop.orders`
WHERE status = 'completed'
AND order_date <= DATE '2026-10-31'
)
SELECT
customer_id,
DATE_DIFF(DATE '2026-10-31', MAX(order_date), DAY) AS days_since_last,
COUNT(*) AS orders,
SUM(net_revenue) / COUNT(*) AS avg_order_value,
DATE_DIFF(
MIN(IF(order_no = 2, order_date, NULL)),
MIN(IF(order_no = 1, order_date, NULL)),
DAY
) AS days_to_second_order
FROM clean
GROUP BY customer_id;