RFM Customer Segmentation (Recency, Frequency, Monetary)

About the RFM Customer Segmentation (Recency, Frequency, Monetary)

Open an orders export — Shopify, WooCommerce, a POS, Tally or any sheet with a customer, a date and an amount — and every customer is scored on Recency (days since their last order), Frequency (orders) and Monetary value (total spend), each from 1 to 5, and put in a segment: Champions, Loyal, Potential loyalists, New, At risk, Can’t lose, Hibernating or Lost — names and rules you can change.

You see the customers and revenue in each segment, the R × F grid, where customers moved between two dates, and a cohort view of repeat revenue by the month of the first order. Refunded and zero-value orders are left out or netted, guest checkouts can join a customer by e-mail or phone, and customers with one order are scored F1 instead of being ranked against each other.

Without a pass the segments are a free preview: every segment’s size and the first rows of each table show; the customer list of each segment (with e-mails hashed if you choose), the ZIP of every segment and the workbook unlock with a Pro pass. Nothing is sent from here: the lists are for your own e-mail, SMS or ads tool.

Common uses

• An online shop building its win-back list (At risk, Can’t lose) and its VIP list (Champions).
• A café, salon or gym with a POS export, sending offers only to the customers likely to respond.
• A wholesaler seeing which accounts buy less often than they used to.
• Checking whether last quarter’s campaign moved customers from Hibernating back to Loyal.

How the scores are worked out

• Recency = days from the last order to the date recency counts to (the day after the last order in the file, today, or a date you choose). Frequency = orders. Monetary = total spend.
• Quintiles: the 20th, 40th, 60th and 80th percentiles of your customers cut R and M into five groups; a customer exactly on a cut-off gets the better score.
• Frequency is different, because most customers of most shops have one order: one order is always F1 (ranking one-order customers against each other would invent differences), and repeat customers are F2 to F5 by the quartiles of their own order counts.
• Your own cut-offs: R5 within so many days, F2 from so many orders, M2 from so much spend, and so on.
• The segment is the first rule whose R, F and M ranges the scores fall in. The eight starting rules (Champions: R 4–5 and F 4–5; Can’t lose: R 1–2, F 4–5, M 4–5; At risk: R 1–2, F 3–5; Loyal: R 3–5, F 3–5; New: R 4–5, F 1; Potential loyalists: R 3–5, F 1–2; Hibernating: R 2, F 1–2; Lost: R 1, F 1–2) give every score a segment.

Orders, refunds and guests

• Lines with the same order ID are one order: Shopify writes the order’s total on its first line only, and the other lines add nothing. If your export repeats the order total on every line, choose “The order’s total (counted once)”.
• Leave them out: refunded, cancelled or voided orders (by their status) and orders of zero or less are not counted. Net the refunds: a refunded amount is taken off its order, and an order left at zero or less lowers the spend without counting as an order.
• A customer is their customer ID where the row has one, else their e-mail, phone or name. Joining by e-mail or phone puts a guest’s orders with the customer who has the same e-mail or phone; phone numbers are compared in one international form.
• Hashed lists: the e-mail in lower case with its spaces removed, and the phone in international form with its plus sign, each as a SHA-256 hash — the form Google Ads Customer Match asks for in its formatting guidelines.

Movement and cohorts

• Movement: each customer’s segment on the earlier date (scored on their orders before it, with that date’s own quintiles) against their segment now. New customers since then are in “Not yet a customer”.
• Cohorts: customers by the month of their first order, how many came back, and their later revenue by months after that first month.

RFM analysis is set out in Arthur Middleton Hughes, Strategic Database Marketing (McGraw-Hill).

Limitations

• Quintiles are relative: a Champion is a top customer of your own shop, not by any outside measure.
• Customers with no ID, e-mail, phone or name cannot be told apart; every such row counts as its own customer.
• Joining by phone needs the numbers to be real phone numbers; numbers without a country code are read as your country’s.
• Files of up to 40 MB each (and up to 300,000 rows in a workbook sheet), read on your device.

Privacy

On this device; its files download once

Your orders and your customers’ details stay in this browser on this device; nothing you open is uploaded and nothing is sent from here. Your settings and segment rules are kept in this browser.