Logical
RFM (Recency, Frequency, Monetary) customer scoring & CRM segment formula
Score customers 1–5 on Recency (lower days = higher score!), Frequency, and Monetary spend, build 3-digit RFM codes ("555", "155"), and assign lifecycle segments.
Remember: lower Recency days earns a HIGHER 5 score—while Frequency & Monetary go up!
This example uses chosen business thresholds for days since last purchase, order count and cumulative spend. Smaller recency earns a higher score; larger order count and spend earn higher scores. Define a common as-of date, observation window, currency, refund policy and order inclusion rules before comparing customers.
The resulting three-digit text code and segment labels are illustrative. They do not establish highest return on a campaign or automatically authorize contacting customers. This worksheet covers customers with at least one whole-number order and nonnegative spend and whole days; prospects with no orders need a separate segment.
| Customer | Days Ago (A) | Orders (B) | Spend (C) | R-F-M Code | Lifecycle Action Segment |
|---|---|---|---|
| Maya (Active VIP) | 8 days | 14 orders | $2,450 | "555" | "Champion" (R>=4, F>=4, M>=4) |
| Omar (Dormant Whale) | 210 days | 15 orders | $3,100 | "155" | "At-Risk VIP" (R<=2, F>=4, M>=4) |
| Zoe (Brand New) | 5 days | 1 order | $95 | "511" | "New Buyer" (R>=4, F=1) |
Build the 3-digit RFM score code ("555", "155", "511")
Scores Recency inversely (<=14d -> 5 down to >180d -> 1) and Frequency/Monetary directly.
=IF(COUNT(A2:C2)<>3,"Invalid inputs",IF(OR(COUNT(A2:C2)<>3,A2<0,A2<>INT(A2),B2<1,B2<>INT(B2),C2<0),"Invalid inputs",LET(recencyScore, IFS(A2 <= 14, 5, A2 <= 30, 4, A2 <= 90, 3, A2 <= 180, 2, TRUE, 1), frequencyScore, IFS(B2 >= 12, 5, B2 >= 6, 4, B2 >= 3, 3, B2 >= 2, 2, TRUE, 1), monetaryScore, IFS(C2 >= 2000, 5, C2 >= 1000, 4, C2 >= 400, 3, C2 >= 150, 2, TRUE, 1), recencyScore & frequencyScore & monetaryScore)))Assign automated retention campaign segment from R, F, M scores
Distinguishes Champions, At-Risk VIPs, New Buyers, and Standard cohorts.
=IF(COUNT(A2:C2)<>3,"Invalid inputs",IF(OR(COUNT(A2:C2)<>3,A2<0,A2<>INT(A2),B2<1,B2<>INT(B2),C2<0),"Invalid inputs",LET(recencyScore, IFS(A2 <= 14, 5, A2 <= 30, 4, A2 <= 90, 3, A2 <= 180, 2, TRUE, 1), frequencyScore, IFS(B2 >= 12, 5, B2 >= 6, 4, B2 >= 3, 3, B2 >= 2, 2, TRUE, 1), monetaryScore, IFS(C2 >= 2000, 5, C2 >= 1000, 4, C2 >= 400, 3, C2 >= 150, 2, TRUE, 1), IFS((recencyScore >= 4) * (frequencyScore >= 4) * (monetaryScore >= 4) = 1, "Champion", (recencyScore <= 2) * (frequencyScore >= 4) * (monetaryScore >= 4) = 1, "At-Risk VIP", (recencyScore >= 4) * (frequencyScore = 1) = 1, "New Buyer", TRUE, "Core"))))Try the example
Use the original values or change them to test your understanding.