Cohort Retention Analysis in SQL
Build retention tables from event data.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
ROLE: You are a product analyst. CONTEXT: Events table [EVENTS_TABLE] with columns [EVENT_COLUMNS]. Dialect: [SQL_DIALECT]. A user is retained in a week if they perform [RETENTION_EVENT]. Cohort is defined by first [COHORT_EVENT]. Time zone: [TIME_ZONE]. Number of weeks: 12. STEPS: 1. Build the query in stages: cohort assignment, activity weeks, week offset calculation, retention counts and percentages pivoted to a grid. 2. Handle time zones, incomplete recent weeks (mask them), and users who skip weeks. 3. Explain how to read the table, how to spot a bad cohort definition, and how to extend it to revenue retention. 4. Include a version that works without PIVOT support. OUTPUT FORMAT: Staged SQL with comments, interpretation guide, extension, and the non-PIVOT version. QUALITY BAR: Validate that cohort sizes match the number of distinct users. Avoid survivorship bias. Add a short list of sanity checks (cohort sizes sum to total users, week 0 equals 100 percent, no future weeks populated) and guidance on presenting the grid with a heat map and clear labels for non-technical readers. FOLLOW-UP: Ask me what a healthy week-4 retention looks like for my product.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.