The prompt
Score 10 of 10, Excellent
Use it when You want to see what share of customers who started in each month are still active or buying in the months after, from your own data.
- {{activity_data}}
- {{start_definition}}
- {{active_definition}}
- {{period}}
- {{tool}}
- {{audience}}
- {{min_cohort_size}}
Full prompt and why it works
System prompt
You help analysts build cohort retention tables. You define cohorts and activity exactly, show how each percentage is calculated, and never fill a cell the data does not support.
User message
<data> {{activity_data}} </data> <definitions> What counts as a customer's start: {{start_definition}} What counts as active in a later period: {{active_definition}} Period size: {{period}} Tool for the refresh formula or query: {{tool}} </definitions> Build a cohort retention table from the data above for {{audience}}, so they can see whether customers who joined more recently stay longer or leave sooner than earlier ones. Produce: 1. A count table: one row per cohort (the period customers started), with the columns Cohort, Customers at start, then Period 0, Period 1, Period 2 and onward, each holding the number of that cohort's customers active in that period. 2. A retention table of the same shape, each cell being the count divided by the cohort's customers at start, as a whole percentage. Under it, show the calculation for one cell. 3. The {{tool}} formula or query that builds the count table from the raw data, so I can refresh it next period. Skip this part if the tool is "none". 4. Findings: up to 5 bullets on what the table shows, such as a cohort that falls away faster than the others or the period in which most customers leave. <example> The form of a cell calculation: "March cohort, Period 2: 184 / 410 = 45%". </example> Use only the rows in the data. Leave a cell blank, not zero, when that period has not happened yet for the cohort, because a zero there would read as everyone leaving. Count each customer once per period, however many times they were active in it. A good table lets a reader compare any two cohorts at the same period without doing any arithmetic. If a cohort has fewer than {{min_cohort_size}} customers, keep it but mark it as too small to compare. Do not explain why retention changed unless the data shows it; describe the pattern and suggest what to check. If the data has no clear customer ID or the dates cannot be read, say so before building anything. Keep the findings under 200 words.
Variables
- {{activity_data}}
- Customer activity or orders: customer ID, date, and anything that defines active, such as order value.
- {{start_definition}}
- What starts a customer, for example "first paid order" or "signup date".
- {{active_definition}}
- What counts as active in a period, for example "placed at least one order" or "logged in".
- {{period}}
- Monthly, weekly or quarterly.
- {{tool}}
- Where you want the refresh version, for example "Google Sheets", "PostgreSQL" or "none".
- {{audience}}
- Who will read it, for example "the growth team and the head of customer success".
- {{min_cohort_size}}
- The smallest cohort worth comparing, for example 30.
Why it works
- What good looks like. Future periods stay blank, never zero, and each customer counts once per period, the two mistakes that most often distort a retention table.
- An example. One cell's calculation shows exactly how every percentage was made, so the table can be checked.
- If unsure. No clear customer ID or unreadable dates stop the work at the start, rather than producing a table built on a guess.