Score the List: Building an RFM Segmentation Table from Raw Transactions

Marketing Academy · Field Work●Email & Lifecycle

CoreBuild the Asset· 50 minutes

Chewy

Objective: Given a supplied customer transaction table (customer_id, order_date, order_value), calculate Recency, Frequency, and Monetary scores per customer and map each to a named segment.

You're the retention marketing analyst at Chewy. You've been handed a 12-month transaction export for the Autoship customer base and asked to build the first RFM segmentation pass before flows can be built.

Calculate the three derived fields per customer, score each on quintiles, and map the resulting profile to one of the lesson's named segments.

Before you start

What you'll need

Free path (everything below is enough to finish)

Google Sheets

FreePivot the transaction export and calculate quintile scores

Handles pivoting, ranking, and lookup formulas needed for RFM without any paid platform

Paid upgrades (optional, faster/deeper)

Klaviyo(optional)

FreemiumAutomate RFM scoring and segment membership on a recurring monthly refresh

Built-in RFM analysis report removes the need to manually re-pivot every month

The process

2 steps

Step 01 of 02

Deriving Recency, Frequency, and Monetary from a raw transaction table

Recency, Frequency, and Monetary are each calculated per customer from a rolling 12-to-24-month window of order_date and order_value rows, capped at 24 months.

The export has one row per order, not one row per customer. Before any scoring can happen, what three fields need to exist per customer_id?

Google Sheets— Pivot the raw transaction export by customer_id.

Procedure

Build a pivot table grouped by customer_id
Add days since most recent order_date as Recency
Add count of orders as Frequency, and sum of order_value as Monetary

Sample output

customer_id  Recency(days)  Frequency  Monetary
C-1042       6              11         $1,340
C-2231       178            2          $95
C-3087       21             6          $612

Healthy

Every customer_id has exactly one row with all three fields populated, no duplicate rows and no blank Monetary values.

Unhealthy

Multiple rows per customer_id because the pivot wasn't grouped correctly, which will silently corrupt every quintile calculated on top of it.

What this means

RFM scoring is only as reliable as this derivation step. A pivoting mistake here produces plausible-looking but wrong scores downstream, with no error to flag it.

So what do I do about it?

Symptom	Action	Effort
Row count after pivoting doesn't match the count of unique customer_ids	Re-check the pivot table's grouping field before moving to scoring	5 min

YouYou can do this yourself, no engineering access required.

Step 02 of 02

Quintile-based scoring per dimension

Each dimension is scored independently on quintiles, 1 to 5, with lower Recency-days mapping to a higher score and higher Frequency or Monetary mapping to a higher score.

C-1042 has Recency 6 days, Frequency 11, Monetary $1,340. Against this dataset's quintile boundaries, what score and segment does that customer land in?

Google Sheets— Add a scoring column per dimension using the sheet's quintile function.

Procedure

Use RANK or a quintile formula to score each dimension 1-5
Concatenate the three scores into an RFM profile per customer
Map each profile against the lesson's segment table

Sample output

C-1042: R5 F5 M5  ->  Champions
C-2231: R1 F1 M1  ->  Lapsed
C-3087: R4 F3 M3  ->  Potential Loyalist

Healthy

Roughly 20% of the list lands in each quintile per dimension, confirming the scoring is relative to this dataset, not a fixed cutoff.

Unhealthy

Using a fixed cutoff (e.g. 'Frequency 10+ = score 5') copied from a different dataset instead of recalculating quintiles on this list.

What this means

Quintile boundaries are dataset-specific and must be recalculated on each new export, they are not portable numbers.

So what do I do about it?

Symptom	Action	Effort
A segment distribution wildly skewed to one end (e.g. 60% scored as 5)	Recompute quintile boundaries on the current dataset instead of reusing a prior month's cutoffs	5 min

YouYou can do this yourself, no engineering access required.

Final deliverable

A scored customer table with Recency, Frequency, Monetary values, quintile scores, RFM profile, and mapped segment name for every customer in the export.

See a reference example

Sample output

Mailchimp, Autoship RFM table (excerpt)

customer_id  R  F  M  Profile  Segment
M-501        5  5  4  5-5-4    Champions
M-502        2  4  4  2-4-4    At Risk
M-503        5  1  2  5-1-2    New Customer

Success criteria

You're done when you can:

Every customer has a complete R, F, M profile with no blank fields
Quintile boundaries are recalculated on this dataset, not copied from elsewhere
Every profile is correctly mapped to a named segment per the lesson's table