The prompt
Score 10 of 10, Excellent
Use it when You have order history and want to group customers by how recently, how often and how much they buy, so you can treat each group differently.
- {{order_data}}
- {{business_context}}
- {{as_of_date}}
- {{use_of_segments}}
- {{tool}}
- {{min_customers}}
- {{audience}}
Full prompt and why it works
System prompt
You help analysts segment customers from their order history using recency, frequency and monetary value. You calculate from the data given, explain each step, and keep segment rules simple enough to rerun.
User message
<orders> {{order_data}} </orders> <context> Business and its typical purchase cycle: {{business_context}} Date to measure recency from: {{as_of_date}} How the segments will be used: {{use_of_segments}} Tool for the formulas, if the data is too large to list: {{tool}} </context> <rules> - Recency is the days from a customer's last order to the as-of date, frequency is their number of distinct orders, and monetary value is their total spend, all from the order data only. - Leave out refunded or canceled orders if the data marks them, and say how many rows were left out and why. - Score each measure from 1 to 5 by quintile. If there are fewer than {{min_customers}} customers, use three groups instead and say so. - Do not add customer traits the data does not contain, such as age, location or intent, and do not guess why a segment behaves as it does. - If one customer appears under two IDs or spellings, list them rather than merging them yourself. </rules> <task> Using the orders above, segment the customers for {{audience}}, so they can decide how to treat each group. A good segmentation has a handful of groups, each large enough to act on and defined by a rule anyone can rerun. Return: 1. Scoring rules: the cut-off values for each score of recency, frequency and monetary value, taken from the data. 2. Segments: up to 6 named groups built from the scores, for example "Recent and frequent: recency 4 to 5, frequency 4 to 5". This example shows the form only. For each group give the rule, the customer count, the share of customers and the share of total spend, with the sums shown. 3. A customer table with the columns Customer ID, Recency days, Orders, Spend, R, F, M, Segment, or the {{tool}} formulas that produce it if the data is too large to list. 4. Notes: up to 5 bullets on what stands out and which groups need a closer look before anyone acts. Keep the notes under 250 words. </task>
Variables
- {{order_data}}
- Order history: customer ID, order date, order value, and refund or cancel status if you have it.
- {{business_context}}
- What you sell and how often a typical customer buys, for example "coffee subscriptions; most reorder monthly".
- {{as_of_date}}
- The date to count recency from, usually the day after the data ends.
- {{use_of_segments}}
- What the segments are for, for example "choosing who gets the winback email".
- {{tool}}
- Your spreadsheet or database, for example "Excel" or "BigQuery".
- {{min_customers}}
- The fewest customers for five-level scoring to be meaningful, for example 100.
- {{audience}}
- Who will use the segments, for example "the marketing manager".
Why it works
- What good looks like. Each segment is defined by a score rule anyone can rerun next month, with counts and spend shares that add up.
- What to avoid. No traits or motives the data does not contain, so the segments describe buying behavior and nothing invented about the people.
- Layout. Tagged sections with the order data first and the task last, the layout Google recommends for Gemini.