wowprompt

RFM customer segmentation from order history

Scores customers on recency, frequency and monetary value from an order export and groups them into named segments with an action for each. Use it before a sale, a win-back campaign or a loyalty launch to target the right customers.

An RFM scoring method, a segment table with customer counts, revenue share and recommended action, and spreadsheet formulas to repeat it
The output this prompt produced, unedited.

The prompt

Goal

Group customers by how recently, how often and how much they buy, so each group can get a message that suits it.

Inputs

  • Business type: [e.g. online fashion store]
  • Analysis date: [date]
  • Order data: [paste or describe columns: customer ID, order date, order value]
  • Tool you will use: [Excel, Google Sheets, SQL, Python]
  • Upcoming campaign: [e.g. Diwali sale, year-end clearance, none]

Steps

  1. Per customer, calculate days since last order, number of orders and total spend.
  2. Score each of the three from 1 to 5 using quintiles.
  3. Combine scores into segments: Champions, Loyal, Potential loyalists, New, At risk, Cannot lose, Hibernating, Lost.
  4. Count customers and revenue in each segment.
  5. Recommend an action and message angle per segment.

Output format

  • Scoring rules table: | Score | Recency days | Orders | Spend |
  • Segment rules: which score combinations go in each segment.
  • | Segment | Customers | Share of revenue | What they need | Action | Message angle |
  • Formulas or code for the chosen tool to repeat this monthly.

Check before answering

  • Quintile cut-offs are calculated from the data, not assumed.
  • Returns and cancelled orders are excluded or flagged.
  • Customers with a single order are not labelled loyal.
  • Discount suggestions go to at-risk segments first, not to Champions.
  • If only a sample is pasted, label all counts as sample figures.
rfm analysiscustomer segmentationrfm modelcustomer lifetime valueecommerce analyticswin back campaign

How to run this

Copy the prompt and paste it into Gemini, then set the aspect ratio to 3:4. No reference photo is needed for this one.

The prompt is written in Markdown with each part under its own heading, which is what the copy action hands over. Change one section at a time, keeping the rest intact, so you can tell which change did the work.

Related prompts

All productivity