RFM Analysis for Shop Owners: Understand Your Customers Without a Data Degree

You already own everything you need to know your customers far better than you do today. It is sitting in your PrestaShop database right now: every order, every date, every total. The problem is that the back office shows it to you as one long undifferentiated list, so a customer who has spent €4,000 across thirty orders looks, at a glance, exactly like one who bought a single €19 item two years ago and never came back. RFM analysis is the cheapest, fastest way to tell those two people apart — and once you can, you stop spending the same marketing effort on both. No data degree, no expensive platform, no machine learning. Three columns of data you already have.

This guide is specifically about RFM: what it measures, how to compute it directly from PrestaShop's own tables, and how to turn the resulting scores into segments you can actually market to. RFM is a method for ranking customers. Two neighbouring topics build on it: the broader practice of treating different groups differently is covered in customer segmentation, and the single number that should sit behind your whole marketing budget is in customer lifetime value. This post stays on the mechanics of RFM itself.

What RFM actually measures

RFM scores every customer on three independent dimensions, each answerable from their order history alone:

• Recency — how long since their last order. A customer who bought last week is warm; one who bought eleven months ago is drifting away whether you have noticed or not. Recency is the single strongest predictor of whether someone will buy again, which is why it comes first.
• Frequency — how many orders they have placed. One order is a transaction; ten orders is a habit. Frequency separates people who happened to buy from people who chose you as a default.
• Monetary — how much they have spent. A customer with a €200 average order deserves a different level of investment than one with a €15 average. Monetary tells you how much marketing budget each relationship can justify.

So what? Any one of these alone is misleading. A high spender who has gone quiet for a year is a problem, not a champion. A frequent buyer with a tiny basket is loyal but low-margin. Scoring all three together is what turns a flat customer list into a map of who needs rewarding, who needs rescuing, and who is not worth chasing.

Where the data lives in PrestaShop

You do not need an export tool or an add-on to start. Everything RFM needs is in two core tables:

• ps_orders holds id_customer, date_add (the order timestamp), total_paid_real (what was actually collected) and valid, where valid = 1 marks orders PrestaShop treats as valid for stats; confirm your order-state configuration.
• ps_customer holds the name and email you will eventually need to act on a segment.

One decision matters before you run anything: count only valid orders. If you total total_paid_real across every row in ps_orders, you fold in non-valid created orders such as cancelled, error or awaiting-payment orders, and your monetary scores become fiction. Filtering on valid = 1 (or restricting to the order states you treat as "real money in") is the difference between a useful analysis and a misleading one.

If you would rather not touch SQL at all, the back office gives you the raw ingredients under Customers → Customers, and the per-customer detail page (and the Stats → Best customers view) shows order count and total spend. That is enough to eyeball your top tier, but it will not scale past a few dozen customers and it will not compute recency for you — which is exactly why the query approach below is worth the five minutes.

Compute the raw numbers with one query

Run this against your database (phpMyAdmin, Adminer, or your host's SQL console). It produces one row per customer with the three raw RFM inputs — days since last order, number of valid orders, and total spend — ready to score. Replace the ps_ prefix if yours differs (it is set by _DB_PREFIX_ — in PrestaShop 1.6 that lives in config/settings.inc.php, and in 1.7/8/9 in app/config/parameters.php):

A working version, grouped by customer and filtered to valid orders:

SELECT id_customer, DATEDIFF(NOW(), MAX(date_add)) AS recency_days, COUNT(id_order) AS frequency, SUM(total_paid_real) AS monetary FROM ps_orders WHERE valid = 1 GROUP BY id_customer ORDER BY monetary DESC;

Export that result to a spreadsheet and you have your entire customer base on three axes. No data science — one GROUP BY and a download button.

Turn raw numbers into 1–5 scores

Raw values are not comparable across the three dimensions — "412 days" and "€1,800" and "7 orders" mean nothing next to each other. The fix is to rank each dimension into quintiles (five equal-sized buckets) and assign a score of 1 to 5:

• Recency: the most recent 20% of customers score 5, the most stale 20% score 1. (Note the inversion — low days-since means a high recency score.)
• Frequency and Monetary: the top 20% by order count and by spend score 5, the bottom 20% score 1.

In a spreadsheet this is one formula per column. With your recency_days in column B, =MAX(1,MIN(5,6-CEILING(MAX(PERCENTRANK($B$2:$B$10000,B2),0.000001)*5,1))) turns each value into a 1–5 recency score (the 6- handles the inversion, and the MAX/MIN clamp keeps every result inside 1–5); drop the 6- for frequency and monetary, keeping the same MAX(1,MIN(5,…)) clamp so those scores stay in the 1–5 range too. The result is a three-digit code per customer — a 555 is your best customer alive, a 111 is effectively gone.

Quintiles matter because they are relative to your own store. There is no universal "good" recency; a champion for a coffee subscription buys monthly, a champion for office furniture buys every few years. Scoring against your own distribution means the method self-calibrates to whatever you sell.

The segments that actually matter

5×5×5 gives 125 possible codes. You do not need 125 marketing plans — that is the classic trap that turns RFM into a project that never ships. Collapse them into a handful of segments you can write a real campaign for:

Notice the priority: the At risk segment is where RFM earns its keep. These are people who already proved they will spend with you, caught at the moment they are drifting. Recovering one of them costs a fraction of acquiring a stranger — which is the whole argument behind a deliberate retention strategy.

Turning a segment into action inside PrestaShop

A score is worthless until it changes what you send. Once you have a list of id_customer values for a segment, you have three practical routes:

• Customer groups. Create a group under Customers → Groups — say "Champions" — and assign your top-scoring customers to it. Groups are first-class in PrestaShop: you can attach catalogue price rules (Catalog → Discounts → Catalog Price Rules), restrict a voucher to a group, or show group-specific pricing. That is how you give Champions early access or Loyal customers a standing discount without manual work each time.
• Cart rules / vouchers. Under Catalog → Discounts → Cart Rules you can limit a voucher to a specific customer or group, set a usage cap, and date-box it — the natural mechanism for an At-risk win-back code or a Champions-only perk.
• Email and ads. Export the segment's emails and load them as a list or a custom audience. Send the At-risk group a win-back, the Recent group a nurture sequence, the Champions an exclusive preview — and build a lookalike audience from your Champions to find more people like your best ones. Per-segment messaging consistently outperforms one blast to the whole list.

What you send after the order is part of the same machine — a strong post-purchase experience is what nudges a Recent customer into the Loyal column in the first place.

How often to recompute — and why it is not "set once"

RFM is a snapshot, and customers move between segments constantly. A Champion who goes quiet for six months becomes At-risk; a Recent buyer who orders again becomes Loyal. The scores are only useful if they track those moves:

• Monthly for live marketing decisions — who gets this month's win-back, who gets the VIP preview.
• Quarterly for strategy — is the Champions segment growing or shrinking?
• Annually for year-over-year comparison.

The most useful thing RFM gives you over time is not the segments themselves but their movement. If your At-risk count keeps climbing while Champions shrink, your retention is leaking no matter what your headline revenue says — a signal that hides completely in an aggregate sales chart. Watching segment sizes shift is a far earlier warning than waiting for the revenue line to bend.

The mistakes that waste the effort

• Counting invalid orders. The number-one data error — if you do not filter on valid = 1, refunds, cancellations and failed payments inflate frequency and monetary and quietly corrupt every segment. Fix this first.
• Over-segmenting. 125 codes is not 125 strategies. Five to eight actionable segments is the ceiling for a store run by humans. Start with two — Champions and At-risk — ship campaigns for those, then expand.
• Ignoring your purchase cycle. A store selling annual subscriptions or big-ticket durables has naturally low frequency; that does not make those customers weak. Score against your own quintiles (which the method already does) and read frequency in the context of how often your product is meant to be re-bought.
• Analysis with no action. The spreadsheet is not the deliverable; the changed email and the new customer group are. RFM that does not alter what you send is a hobby, not marketing.

RFM is the rare exercise that costs almost nothing and pays back immediately: the data is already in ps_orders, the maths is a quintile, and the payoff is finally spending your time and budget on the customers who justify it instead of an undifferentiated list. If you are still early and your order count is small, focus first on getting the volume that makes segmentation meaningful — that is the work in your first 100 orders. Once the orders are flowing, RFM is how you make sure the right ones keep coming back — and how you read the segment data alongside your real profit margins so the effort you pour into each tier is genuinely worth it.