AI Prompt to Build an RFM Customer Segmentation Model From Purchase Data
This prompt guides ChatGPT, Claude, or Gemini through building an RFM (Recency, Frequency, Monetary) customer segmentation model directly from a raw purchase or transaction export, without requiring the user to write SQL or Python first. It's built for ecommerce analysts, growth marketers, and small business owners who have a list of orders with customer IDs, dates, and amounts, and need to turn that into actionable customer segments like "Champions," "At Risk," or "Lost" customers.
The prompt walks the model through calculating recency (days since last purchase), frequency (number of orders in the period), and monetary value (total or average spend), then scoring each metric on a 1-5 scale and combining the three scores into named segments. It also asks the model to flag any data issues — missing dates, duplicate orders, refunds mixed into totals — before it starts scoring, so the segmentation isn't built on dirty inputs.
For teams whose transaction history lives in a large export with far more columns than RFM needs, pulling only the customer ID, order date, and order total columns first makes the segmentation faster and less error-prone; Context Extractor is built for isolating exactly those relevant rows and columns out of a bigger CSV before this prompt ever sees the data.
Prompt template
Want it sharper? Optimize this prompt with Prompt Optimizer, check it with the Prompt Debugger or shorten it with the Token Optimizer.
Example input
When to use it
- You have a spreadsheet or CSV export of customer orders and want to group customers by buying behavior
- You're planning a win-back or loyalty campaign and need to know who counts as "at risk" versus "loyal"
- You want a repeatable scoring method instead of eyeballing which customers matter most
- You need to explain customer segments to a non-technical stakeholder in plain business terms
Best practices
- Give the model the actual column names from your file so it maps recency, frequency, and monetary fields correctly instead of guessing
- Specify the analysis period (e.g. "last 12 months") so recency and frequency scores aren't skewed by very old orders
- Ask for the segment definitions and score cutoffs up front so the output is reproducible next quarter, not a one-off
- Request the output as a table with customer ID, each RFM score, and the final segment name so it can be pasted straight into a spreadsheet
Common mistakes
- Feeding in a transaction log that still includes refunds or cancelled orders, which inflates monetary and frequency scores
- Not specifying how many segments you want, so the model picks an arbitrary number that doesn't match your campaign plans
- Treating RFM scores as exact math instead of a relative ranking within your own customer base
- Skipping the data-quality check step and segmenting on a file with duplicate customer IDs or missing dates
FAQs
What is RFM analysis in customer segmentation?
RFM stands for Recency, Frequency, and Monetary value — three metrics used to score and group customers based on how recently they bought, how often they buy, and how much they spend, so marketing effort can be targeted at the segments that matter most.
Can ChatGPT actually calculate RFM scores from a spreadsheet?
Yes, if you paste in or describe the actual order data with customer IDs, dates, and amounts, ChatGPT, Claude, or Gemini can calculate recency, frequency, and monetary values and score them, though for very large datasets a spreadsheet or BI tool is more reliable for the raw math.
How many RFM segments should I use?
Most teams use 5 to 8 named segments (such as Champions, Loyal, At Risk, and Lost); fewer segments are easier to act on, while more segments give finer targeting at the cost of simplicity.
Which Cuelara tool can help me clean up my data before running this RFM prompt?
Context Extractor — pulls only the relevant customer, date, and order columns out of a large or messy transaction export so the RFM prompt scores clean data instead of a noisy full file.