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).
Works with: ChatGPT, Claude, Gemini, Any LLM