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
- Per customer, calculate days since last order, number of orders and total spend.
- Score each of the three from 1 to 5 using quintiles.
- Combine scores into segments: Champions, Loyal, Potential loyalists, New, At risk, Cannot lose, Hibernating, Lost.
- Count customers and revenue in each segment.
- 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.






