The Recency/Frequency Formula for Deciding Who Gets Your Best Email Offers
Most businesses still send the exact same discount to their entire list regardless of who's on it — which means your best, most engaged customers get the same generic code as someone who opened one email eight months ago. 💖 That's not just inefficient, it's actively burning margin on people who would have bought anyway, while barely moving the people who've genuinely gone quiet.
What most businesses get wrong
The default approach is "all subscribers" as the only segment, which treats a daily customer exactly the same as someone who signed up once and never opened another email. Most of the data needed to fix this already sits inside your email platform or store — last order date, number of orders, last open — it's just never been pulled together into a score. And when businesses do attempt segmentation, they often build it once and never touch it again, so a customer who was "VIP" a year ago is still being treated that way long after they've gone quiet.
Build this in a spreadsheet with one row per customer and these columns:
- Recency score (1–5): 0–30 days since last order/engagement = 5, 31–60 days = 4, 61–120 days = 3, 121–240 days = 2, 241+ days = 1.
- Frequency score (1–5): based on orders or meaningful engagements in the trailing 12 months — 0–1 = 1, 2–3 = 2, 4–6 = 3, 7–10 = 4, 11+ = 5.
- Combined score: add Recency + Frequency (out of 10).
A worked example with five sample customers:
| Customer | Last order | Orders (12mo) | R | F | Total | Tier |
|---|---|---|---|---|---|---|
| A | 12 days | 9 | 5 | 4 | 9 | VIP |
| B | 45 days | 3 | 4 | 2 | 6 | Active |
| C | 200 days | 8 | 2 | 4 | 6 | Active (lapsed regular) |
| D | 150 days | 1 | 2 | 1 | 3 | At-Risk |
| E | 300 days | 1 | 1 | 1 | 2 | Dormant |
Notice Customer C — high frequency historically, but low recency. A combined score alone would lump them in with Customer B, but they actually need a "we've missed you" message, not a standard offer.
The mechanics: building this without buying new software
Export your order or engagement history from Shopify or your email platform, add a column for days since last order and a column for order count in the trailing 12 months, then use simple nested IF formulas to assign the 1–5 scores on each axis. Combine them into a single number, then map ranges of that number into three or four named segments — VIP, Active, At-Risk, Dormant works for most businesses, but the exact labels matter less than having genuinely different messaging and offers for each. Once you've built the segments in a spreadsheet, tag or tier customers inside your actual email platform (custom fields or list tags) so your campaigns can target each tier with a different offer, subject line and cadence.
Mistakes to avoid
- Scoring the list once and never updating it as behaviour changes.
- Sending the VIP tier the same generic code as everyone else instead of something genuinely better or earlier.
- Only ever using this for discounting, when the At-Risk tier is often better served by a personal check-in than another offer.
- Combining Recency and Frequency into one number without checking which axis is actually driving a customer's score — a high-frequency, low-recency customer needs a different message than a brand-new one.
- Building an elaborate model before checking whether your data (order dates, engagement history) is even clean enough to trust yet.
Frequently asked questions
Is this kind of scoring reliable for a small customer list?
With a genuinely small list — a few hundred customers or fewer — the buckets can get noisy, since a handful of customers can swing a whole tier. Treat the segments as directional guidance you sense-check against your own judgement, not as a precise model, until your list has enough volume to smooth out the edges.
Which matters more, recency or frequency?
It depends on the business, but recency is generally the stronger predictor of whether someone will engage again soon. That said, the combination is what catches the customers either number alone would mislabel — like a lapsed regular who still scores well on frequency.
How often should I rebuild the scores?
Quarterly is a reasonable default for most businesses, or immediately after a major sales event when a lot of customer behaviour shifts at once.
Do I need Klaviyo or similar software to do this?
Not to start. Most popular email platforms have some version of RFM-style segmentation built in, but building it manually in a spreadsheet first helps you actually understand the logic behind your segments before you hand it over to automated tooling.
Keep reading 🤍
I help Gold Coast and Brisbane businesses grow with branding, websites and marketing that actually works.
Work with me ✦