Analytics & BI · Customer segmentation
An automotive service network spent the same on every customer and only noticed churn once it had already happened. I built a dynamic RFM engine that scores every customer on Recency, Frequency and Monetary value, assigns a loyalty tier and an activity status — so marketing acts on each person, not on averages.
The problem
Campaigns were one-size-fits-all: the same offer and the same budget reached a brand-new customer and a ten-year loyal one. There was no way to see who's valuable, who's slipping, and who's already gone.
What RFM means
How recently did they last come in? Days since their last service visit — the fresher, the more engaged.
How often do they come back? Total service visits over their whole lifetime with the network.
How much do they spend? Total spend on works and parts, converted to a stable USD base.
What I built
From the network's service history I compute Recency, Frequency and Monetary value for every customer, for every year of their lifetime. Each customer is ranked against their true peers, the ranks are normalized into one comparable 0–1 loyalty score, and from that score they get a loyalty tier and an activity status.
Because it runs per customer, per year, the output isn't a one-off snapshot — it's a trajectory. You see exactly when someone starts slipping.
Why my approach works
Most RFM is a one-time snapshot with hand-picked thresholds. Mine ranks each customer against their real peers and normalizes those ranks into one fair, comparable score — recomputed every year. That's what makes it sensitive enough to catch a loyal customer the moment they start cooling off.
The result
Marketing opens one dashboard and sees the whole base by loyalty tier and activity status — and can drill to any single customer. (Distribution shown is illustrative.)
Down to one customer
Drill into any single customer and see their entire trajectory — every year their tier, their status, and the exact moment their rating starts to slip. That early warning is what marketing acts on, months before a churn report would notice.
| Year | Visits | Total $ | Loyalty tier | Activity | Rating | Rating Δ |
|---|---|---|---|---|---|---|
| 2017 | 4 | $688 | Silver | New | 0.65 | 0.0% |
| 2018 | 9 | $1,969 | Gold | Active | 0.90 | 0.0% |
| 2019 | 8 | $3,398 | Gold | Active | 0.95 | 0.0% |
| 2020 | 11 | $4,837 | Gold | Active | 0.95 | 0.0% |
| 2021 | 6 | $5,878 | Platinum | Active | 1.00 | 0.0% |
| 2022 | 4 | $7,616 | Platinum | Declining | 0.95 | −3.5% |
| 2023 | 8 | $8,925 | Platinum | Active | 0.95 | −1.3% |
| 2024 | 6 | $10,696 | Platinum | Active | 0.95 | −1.4% |
| 2025 | 5 | $12,759 | Platinum | Active | 1.00 | −0.3% |
| 2026 | 1 | $13,333 | Platinum | Declining | 0.95 | −1.8% |
This customer climbed from Silver to Platinum in four years, dipped in 2022 (−3.5%), recovered, and is cooling again now. Every turn was flagged the year it happened — months before a churn report would notice.
The amber points are the moment to act: the customer is Declining but still recoverable. Ignore them and the same person slides to Churned — when winning them back costs far more. The whole value is reacting in the amber zone, not the red one.
The point
Once every customer has a tier and a status, marketing knows the exact play for each group. Promos go only where they move the needle — not blanket-sprayed across the whole base.
| Group | What it means | Marketing play | Ad spend |
|---|---|---|---|
| Platinum · Active | Your best, fully engaged customers | VIP perks & loyalty care | ≈ none needed |
| Gold / Silver · Declining | Valuable customers starting to slip | Targeted win-back offer — the high-ROI move | focus here |
| Bronze · New | Recent customers worth growing | Onboarding nudge, next-service reminder | low |
| Dormant / Churned | Already gone or never engaged | One low-cost reactivation — then stop | minimal |
Built to be trusted
The outcome
Quantified business results (ad-spend reduction, retention lift) — add real numbers.
Stack & role
Sole data engineer — metric design, scoring logic, data-quality and deployment.
I'll score your whole base, flag who's slipping, and hand marketing a playbook per group — so the budget goes where it works.
Book a strategy call →