Build a Comprehensive Cohort Analysis to Track User Retention and Behavior

Act as a senior product analyst experienced in cohort analysis and user lifecycle analytics. I need you to help me design, build, and interpret a cohort analysis for the following product scenario. **Product & Data Context:** - Product type: [PRODUCT_TYPE, e.g., SaaS platform, mobile app, e-commerce store] - Cohort definition: users grouped by [COHORT_CRITERIA, e.g., signup month, first purchase date, acquisition channel] - Key action to measure: [KEY_ACTION, e.g., login, purchase, feature usage] - Analysis time window: [TIME_WINDOW, e.g., 12 weeks, 6 months] - Available data fields: [DATA_FIELDS, e.g., user_id, signup_date, event_date, event_type, revenue, acquisition_source] **Please provide the following deliverables:** 1. **SQL Query for Cohort Table:** Write a production-ready SQL query (compatible with [DATABASE_TYPE, e.g., PostgreSQL, BigQuery, Snowflake]) that generates a cohort retention table. The query should: - Assign each user to their cohort based on [COHORT_CRITERIA] - Calculate the number and percentage of users who performed [KEY_ACTION] in each subsequent period - Handle edge cases like users with multiple events on the same day 2. **Retention Matrix:** Show me how to structure and interpret a retention matrix (cohort period × time period) with sample output formatting. Explain what a healthy retention curve looks like for a [PRODUCT_TYPE]. 3. **Cohort Comparison Analysis:** Outline how to compare cohorts to identify: - Which cohorts retain best and worst, and hypothesize why - Whether retention is improving or degrading over time - The critical drop-off period where most users churn 4. **Advanced Segmentation:** Suggest how to layer in additional dimensions such as [SEGMENTATION_OPTIONS, e.g., pricing plan, geography, acquisition channel, user persona] and provide a modified SQL query for at least one segmented cohort view. 5. **Visualization Recommendations:** Recommend the best chart types (heatmap, line chart, etc.) and tools for presenting cohort data to [AUDIENCE, e.g., executives, product team, investors]. Include specific color-coding and formatting tips. 6. **Actionable Insights Framework:** Provide a framework of 5-7 specific questions I should answer from the cohort data and what actions each answer should trigger (e.g., if Week 1 retention drops below X%, then do Y). Format the SQL with clear comments, use markdown tables for sample outputs, and include interpretation guidance alongside every data point.