Cohort retention analysis plan with SQL or pandas

You are a product analyst. Help me plan and build a cohort retention analysis.

Product and business model: [e.g. "subscription meal-kit app", "B2B SaaS with monthly plans", "free mobile game"]
Question I want to answer: [e.g. "did users who signed up after the new onboarding retain better?"]
Data available (tables and key columns):
"""
[e.g. users(user_id, signup_at, country, plan), events(user_id, event_name, event_at)]
"""
Tool: [SQL DIALECT, OR "Python pandas"]
Date range: [START AND END]

Steps:
1. Propose the cohort definition (what event and date puts a user in a cohort) and explain alternatives.
2. Propose the "retained" definition: which activity counts, and whether retention is classic (active in period N) or rolling (active in period N or later). Recommend one for my question.
3. Choose the time grain (day, week, month) and justify it from how often a healthy user should return.
4. Explain how to handle the most recent cohorts that have not had time to reach later periods, time zones, and users with no activity.
5. Write the query or code that outputs a cohort table: cohort, cohort size, and retention % for periods 0..N.
6. Explain how to read the table, what patterns to look for, and which comparisons would be misleading.
7. List checks to validate the result (e.g. cohort sizes match total signups).

Example (excerpt):
Cohort: week of signup_at (UTC). Retained: at least one "order_placed" event in week N (classic retention), because the question is about habitual ordering.
Grain: weekly - healthy customers order roughly once a week.
Incomplete periods: cohorts from the last 3 weeks will show blanks for later weeks; leave them empty, not 0%.
| cohort_week | users | wk0 | wk1 | wk2 | wk4 |
| 2026-09-07 | 1,240 | 100% | 41% | 33% | 27% |
Reading tip: compare the same column (e.g. wk4) across cohorts, not along a row.

When to use it

Use this prompt when someone asks “are users sticking around?” and you want an answer that survives scrutiny. It fits subscription products, apps, marketplaces and online shops, and it is especially useful for comparing cohorts before and after a product change.

It is less useful when you have only a few weeks of data or very few users per cohort: with a cohort of 40 people, a single user changes retention by 2.5 points, and noise will look like a trend. In that case, ask the model to suggest wider cohorts (monthly instead of weekly) or a simpler metric until more data arrives.

How to customize it

• [PRODUCT] determines what healthy usage looks like and therefore the right time grain.
• [QUESTION] shapes every definition. Measuring onboarding needs early periods; measuring loyalty needs many months.
• [DATA AVAILABLE] should list real table and column names so the generated query runs with few edits.
• [TOOL] picks the SQL dialect or pandas; date functions differ a lot between databases.
• [DATE RANGE] sets how many periods the oldest cohorts can show.

Why it works

Most retention mistakes happen before any code is written: an activity event that does not reflect real value, a grain that hides or exaggerates churn, or recent cohorts shown as 0% because they have not had time to return. The prompt forces those decisions into the open and asks for a justification of each one.

Requiring validation checks and a reading guide makes the output usable by colleagues who will see only the table. The warning about misleading comparisons, such as reading along a row instead of down a column, addresses the most common interpretation error.

Tips and variations

• Run the query, paste a few rows of output back, and ask for a short written summary of the trends.
• Add a segment column (country, plan, acquisition channel) to compare retention curves.
• If you are testing a change, also read our A/B test interpretation prompt; cohort differences are not proof of causation.
• For ad hoc analysis in a notebook, combine it with the pandas analysis plan.