Classic RFM segmentation: score every customer on Recency (R), Frequency (F), and Monetary (M), then assign a segment label.
| Column | Type |
|---|---|
| CustomerId | INTEGER (PK) |
| FirstName | TEXT NOT NULL |
| LastName | TEXT NOT NULL |
| Company | TEXT |
| Country | TEXT |
| TEXT NOT NULL | |
| SupportRepId | INTEGER (FK → Employee) |
| Column | Type |
|---|---|
| InvoiceId | INTEGER (PK) |
| CustomerId | INTEGER (FK) |
| InvoiceDate | TIMESTAMP NOT NULL |
| BillingCountry | TEXT |
| Total | NUMERIC(10,2) NOT NULL |
'2014-01-01'::dateRecency_Days (smaller is better), Frequency (invoice count), Monetary (total spend)NTILE(4) for each metric so the best quartile is 4 (smallest recency, highest frequency, highest monetary)RFM_Score = concatenation of three digits as a string (e.g., '4-4-4')Segment: 'Champion' if R=4 AND F=4 AND M=4; 'Loyal' if F=4 AND M>=3; 'At Risk' if R=1 AND M>=3; else 'Other'FullName, Recency_Days, Frequency, Monetary, RFM_Score, SegmentReady to take your skills to the next level? Enroll in our comprehensive Data Analyst Career Track to master SQL, Python, Excel, and Power BI.
Run your code to see the result here.