RFM Analysis Copy link

Performance: Read br-analytics-standards at the start of every run (read_skill("br-analytics-standards")). All pipeline steps must follow those rules. Target runtime: under 7 minutes for single-module, under 20 minutes for orchestrations.

Take a transactional Blitz Report (AR invoices, sales orders, shipments) and turn it into a ranked, named customer segmentation a commercial team can act on the same day. Pure aggregation + quintile scoring — computed in the workbook with formulas (and light JS where convenient), so it stays transparent and refreshable. No ML black box: every score is explainable.

When to use / not use

• Use for: customer/vendor value segmentation from dated, valued transactions; finding Champions, At-Risk, Lapsed accounts; prioritising outreach.
• Don't use for: clustering on arbitrary behavioural features (br-clustering), or data without a per-entity transaction history (no dates/amounts to score).

Shared conventions (the br_ analytics house style)

1. Source = an existing Blitz Report sheet; row 1 is headers. Never modify source columns.
2. Scratch work on br_calc_; delete when done.
3. Outputs (all four): a results sheet br_rfm_results (Customer | Recency | Frequency | Monetary | R | F | M | RFM | Segment); a chart; a dashboard (reuse br-data-dashboard for KPI/slicer layout; slicer on Segment); a plain-English chat summary with per-segment actions.
4. Determinism: quintile scoring is deterministic; state the as-of date and the scoring scheme.
5. Privacy: operate only on the provided sheet.

Workflow — follow in order

Step 1 — Identify columns and the as-of date

Read headers + sample rows. Identify the entity key (Customer/Account), the transaction date, the monetary amount, and optionally a transaction/invoice id for frequency. Set the as-of date:

• default to the latest date in the data (so recency is measured against the extract, not today — important for historical extracts),
• offer "today" if the data is live. State which you used.

Steps 2-5 — Aggregate, score, segment + write results sheet (single execute_office_js call)

In one call: read the entity key + date + amount (+ optional id) columns, then aggregate per entity (Step 2), score R/F/M by quintile (Step 3), map to named segments (Step 4), and write the br_rfm_results sheet (Step 5) — all in the same JS block. Trust the values it returns; do not insert a verification read between aggregating, scoring, segmenting, and writing. The per-step detail (formulas, scoring scheme, segment matrix) follows.

Step 2 — Aggregate per entity

For each customer compute:

• Recency = as-of date − most recent transaction date, in days (lower = better).
• Frequency = count of transactions (or distinct invoice/order ids).
• Monetary = sum of amount. Decide gross vs net of returns/credits and say so; negatives need a rule (exclude credits, or net them). Formulas (MAXIFS, COUNTIFS, SUMIFS) in references/rfm.md.

Step 3 — Score R, F, M on 1-5 by quintile

Bin each measure into 1-5 by quintiles. Recency is reverse-scored (most recent → 5). Frequency and Monetary higher → 5. If a measure has fewer than 5 distinct values (e.g. many one-purchase customers), use fewer bins and note it rather than forcing 5. Quintile formulas in references/rfm.md.

Step 4 — Map to named segments

Combine R with a FM score (average or max of F and M) and map to the standard segment names — Champions, Loyal, Potential Loyalist, New/Recent, Promising, Need Attention, About To Sleep, At Risk, Can't Lose Them, Hibernating, Lost — using the matrix in references/rfm.md. Also keep the raw 3-digit RFM cell (e.g. "545") for power users.

Step 5 — Results sheet (written in the Steps 2-5 call)

br_rfm_results: Customer | Recency | Frequency | Monetary | R | F | M | RFM | Segment, sorted by Monetary or by segment priority. This sheet is created and populated inside the combined Steps 2-5 execute_office_js call — not as a separate write.

Step 6 — Charts (single execute_office_js call — the permitted separate charts call)

• a segment-size bar (customers per segment),
• a Recency-vs-Frequency bubble chart sized by Monetary (shows where the valuable, active customers sit) — or a segment-by-revenue bar if a bubble is awkward.

Step 7 — Dashboard

Dashboard from br_rfm_results with KPI cards: total customers, total monetary, % of revenue in top segments (Champions + Loyal), count At-Risk/Can't-Lose. Slicer on Segment so the team can pull one segment's customer list.

Step 8 — Chat summary

Report: the segment distribution, where the value concentrates (e.g. "Champions are 9% of customers but 38% of revenue"), the at-risk value exposure, and specific actions per priority segment — retain/reward Champions, win back Can't-Lose-Them and At-Risk before they lapse, nurture Potential Loyalists, re-engage Hibernating with a low-cost offer. End with where the sheet and dashboard are.

Gotchas

• As-of date drives recency — for a historical extract use the data's max date, not today, or every customer looks lapsed.
• Returns/credits create negative or inflated monetary; pick a rule (exclude vs net) and state it.
• One-transaction customers cluster at Frequency = 1 and can break quintiles — bin on distinct values and note reduced granularity.
• Quintile ties: customers on a boundary may land in adjacent bins; that's expected, don't over-tune.
• Tiny datasets don't segment meaningfully — say so rather than forcing 11 segments onto 30 customers.