Build a Cohort Retention Analysis
Structure and interpret cohort analysis to understand retention trends and identify where users are churning.
The Prompt
Help me set up and interpret a cohort retention analysis. Provide: 1. Data requirements: what events/tables are needed 2. SQL query to generate the cohort retention table 3. How to read the cohort table correctly 4. Red flags: what patterns indicate churn problems 5. Benchmark: what good retention looks like for my business type 6. Root cause investigation: how to dig into cohorts with poor retention 7. Interventions: what levers to pull for each pattern found Context: - Product type: [SaaS / MARKETPLACE / APP / E-COMMERCE] - Cohort definition: [MONTHLY SIGNUP COHORT / FIRST PURCHASE COHORT] - Retention metric: [DAU / WAU / PAID RENEWAL / ACTIVE USAGE] - Database: [POSTGRESQL / BIGQUERY / SNOWFLAKE] - Time range: [HOW MANY MONTHS OF DATA]
Example Output
SQL query using a self-join with DATE_TRUNC to create monthly cohorts, a retention table showing week-by-week retention for each signup cohort. Benchmarks: B2B SaaS should target 85%+ month-1 retention; below 70% at month-3 is a serious signal. Identified the 'activation gap' pattern — month-1 drop indicates users never reached the aha moment.
FAQ
Which AI model is best for Build a Cohort Retention Analysis?
Claude Sonnet 4 — excellent at cohort analysis methodology and SQL generation.
How do I use the Build a Cohort Retention Analysis prompt?
Copy the prompt, replace the [BRACKETED] placeholders with your specific information, and paste into your preferred AI assistant (ChatGPT, Claude, Gemini, etc.). SQL query using a self-join with DATE_TRUNC to create monthly cohorts, a retention table showing week-by-week retention for each signup cohort. Benchmarks: B2B SaaS should target 85%+ month-1 retention; below 70% at month-3 is a serious signal. Identified the 'activation gap' pattern — month-1 drop indicates users never reached the aha moment.
Model Recommendation
Claude Sonnet 4 — excellent at cohort analysis methodology and SQL generation.