Temporal RFM Segmentation with Year-over-Year Customer Loyalty Dynamics
Authors/Creators
Description
We present a SQL-based customer segmentation framework that computes Recency, Frequency, and Monetary metrics over a rolling three-year window and tracks segment transitions between consecutive years. Using window functions including NTILE for quantile-based ranking and LAG for year-over-year segment comparison, the system classifies customers into five loyalty tiers: VIP, Regular, Loyal, Potential, and Lost. The methodology is implemented in standard SQL with support for PostgreSQL, Oracle, and other databases supporting EXTRACT and window functions. The query handles over 10 million order records with linear scaling, producing actionable business intelligence on customer churn prediction and loyalty dynamics. Segment definitions are configurable through quantile thresholds, allowing adaptation to different business domains.
Files
Restricted
The record is publicly accessible, but files are restricted. Log in to check if you have access.
Additional details
Identifiers
Related works
- Cites
- Software: 10.5281/zenodo.15000000 (DOI)
Dates
- Available
-
2026-06-05
References
- 1. Hughes AM. *Strategic Database Marketing*. 3rd ed. McGraw-Hill; 2006. 2. Fader PS, Hardie BGS, Lee KL. RFM and CLV: Using iso-value curves for customer base analysis. *Journal of Marketing Research*. 2005;42(4):415-430. 3. McCarthy DM, Fader PS, Hardie BGS. Valuing subscription-based businesses using publicly disclosed customer data. *Journal of Marketing*. 2017;81(1):17-35. 4. Chen D, Sain SL, Guo K. Data mining for the online retail industry: A case study of RFM model-based customer segmentation using data mining techniques. *Journal of Database Marketing & Customer Strategy Management*. 2012;19(3):197-208. 5. Kahan R. Using database marketing techniques to enhance your one-to-one marketing initiatives. *Journal of Consumer Marketing*. 1998;15(5):491-493.