Cohort Analysis Plan

A complete cohort analysis plan with query skeleton, cohort definitions, and an interpretation guide ready for a data analyst to execute.

Best For

•A growth analyst testing whether referral signups retain better than organic signups at 90 days before recommending budget reallocation
•A data scientist at a subscription product designing a weekly cohort retention study ahead of a board deck
•An analytics engineer writing a reusable BigQuery cohort query skeleton so future retention questions don't start from scratch

Prompt Template

Design a cohort analysis plan to answer this business question: [BUSINESS_QUESTION, e.g., "Are users who sign up via our referral program more retained at 90 days than organic signups?"]. Product or service being analyzed: [PRODUCT_NAME]. Available data: [AVAILABLE_DATA_DESCRIPTION]. The plan should specify: (1) Cohort definition, how will cohorts be grouped? (by acquisition date, acquisition channel, first product action, plan tier, etc.) and the exact cohort window (e.g., weekly cohorts from [START_DATE] to [END_DATE]), (2) Cohort size minimum, the minimum cohort size required for statistical reliability and how to handle small cohorts, (3) Key metrics per cohort, retention rate at Days [DAY_INTERVALS], and any secondary metrics: [SECONDARY_METRICS], (4) Analysis dimensions, which dimensions to slice by: [DIMENSIONS, e.g., acquisition channel, geography, plan type], (5) Expected output format, table structure and visualization type (heatmap vs. line chart), (6) SQL query skeleton, provide a skeleton query structure using [DATA_WAREHOUSE, e.g., BigQuery, Snowflake] syntax that a data analyst can populate, (7) Interpretation guide, how to read the results and what patterns indicate problems vs. health.

Pro Tip

Set the minimum cohort size threshold before running the analysis, not after, so small acquisition-channel cohorts don't get misread as retention wins or losses due to noise.

Example Output

A sample of what this prompt produces once you fill in the placeholders.

Weekly cohorts from Jan 6 to Mar 31, grouped by acquisition channel. Referral cohorts show 42% Day-90 retention versus 31% for organic, based on cohorts averaging 340 users each. Heatmap output flags the March referral cohort as an outlier due to a promo-driven signup spike with lower engagement.