RFM Customer Segmentation in Excel
Score customers by recency, frequency and monetary value.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Walk me through RFM segmentation in Excel. Transaction table (customer ID, date, amount): [TRANSACTION_TABLE]. Analysis date: [ANALYSIS_DATE]. Number of score levels I want: [LEVELS]. Provide the steps and formulas to calculate recency, frequency, and monetary value for each customer, convert each to a 1 to [LEVELS] score using percentiles, combine them into a segment label with a lookup table I can edit (for example champions, at risk, new, lost), and summarise customers and revenue by segment. Include checks for customers with refunds and duplicates, a small invented example with eight customers, and an interpretation guide. Suggest one action for each segment, framed as a test, and warn that segments reflect past behaviour and may not predict future behaviour. Add a section on using the segments. For each segment, suggest one message idea and one test to run, such as a reminder email for customers who have not bought in 90 days, and the measure that would show whether the test worked. Provide a rule for keeping a control group, and an explanation of why segments should be recalculated regularly. Add a privacy note: use customer IDs rather than names in the analysis sheet, and check marketing consent rules for [JURISDICTION] [VERIFY] before contacting anyone. Include a short check on whether the segment sizes make sense for my business. Finally, add a quality check: compare the share of revenue from the top segment with the share of customers, explain what a large gap means, and list two reasons the segmentation could be misleading, such as a short history or seasonal buying patterns. Also add a section on building the workbook in a robust way: store the transaction data in a table, use structured references so formulas expand with new rows, calculate the analysis date with a single cell that can be changed, and protect the lookup table of segments. Provide a short test plan: check three customers by hand against the formulas, confirm the number of customers equals the count of unique IDs, and confirm the scores are evenly distributed when percentiles are used.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.