RFM — Events Table Requirements
The RFM (Recency, Frequency, Monetary) model segments users by historical purchase behaviour:
- Recency — time since the last purchase.
- Frequency — number of purchases in a defined period.
- Monetary — total spend in that period.
It helps identify loyal, at-risk, and high-value customers for tailored marketing.
Data structure overview
RFM analysis requires:
- A transaction event table — one row per purchase.
- At least one user identifier.
- A monetary value per transaction.
Field names are examples, not mandatory. Identity resolution ensures cross-table consistency. Event tables follow the standard: 1 row = 1 transaction, with one timestamp and one user ID.
1. Transaction event table (required)
| Column name | Type | Description |
|---|---|---|
event_timestamp | DATETIME | When the transaction occurred (UTC, mandatory) |
user_id | STRING | A user identifier (mandatory) |
monetary | FLOAT | Value of the transaction (mandatory) |
transaction_id | STRING | Not required for modelling, but strongly recommended for traceability and value reconciliation |
All timestamps must be in UTC.
2. Optional enrichment fields (recommended)
| Column name | Type | Description |
|---|---|---|
country | STRING | Country where the transaction was made |
client_type | STRING | e.g. B2B, reseller, end-customer — useful for filtering segments |
transaction_state | STRING | e.g. completed, refunded — needed only if the table mixes statuses |
These fields allow finer segmentation by customer type, geography, or transaction status.
Best practices
Choose the right time window
- Fast purchase cycle (fashion, food) → 3–6 month window.
- Slow purchase cycle (electronics, luxury) → 12–24 month window.
- Unknown cycle → default to 12 months.
Consider seasonality
If your business is seasonal (Christmas peaks, summer sales), choose a window that captures at least one full seasonal cycle.
Segment by customer type
If you serve different profiles (private individuals and resellers), use client_type to build separate RFM models — don't mix behaviours that differ drastically in frequency or value.
Summary
| Area | Must-have |
|---|---|
| Event table | One row per transaction, with user ID and timestamp |
| Monetary field | Required for monetary scoring |
transaction_id | Recommended for traceability |
| Optional enrichments | country, client_type, transaction_state |
| Recommended time window | 6–24 months depending on purchase cycle and seasonality |
| Multi-customer handling | Use client_type to separate B2B vs B2C or reseller behaviour |