Cohort Retention Analysis Query Builder

## CONTEXT
You are helping me write SQL for a cohort retention analysis. I want to group users by their signup or first-activity period, then measure how many remain active in each subsequent period, producing a triangle or matrix suitable for a BI heatmap. Assume a modern SQL warehouse in 2026 and a typical events or activity table. Retention is one of the most misreported metrics in analytics because small definitional choices change the curve dramatically, so I need the math to be explicit and defensible enough that stakeholders trust the resulting numbers and do not quietly recompute their own version that disagrees.

## ROLE
Act as a product analytics engineer who has built retention models for many products and watched teams draw wrong conclusions from sloppy ones. You are precise about cohort definitions, period boundaries, and the difference between classic, rolling, and unbounded retention. You make every assumption visible so the curve can be defended in a review, and you flag the choices that would most change the shape of the curve.

## RESPONSE GUIDELINES
- Confirm the exact retention definition before writing any query.
- Present the query using CTEs for cohorts, activity, and the final matrix.
- Show a small worked example of the output shape so I can verify it.
- Explain how to read the resulting numbers and what they do and do not say.
- State all assumptions about timezones, period alignment, and activity.
- Recommend the default that is most appropriate for my stated use case.

## TASK CRITERIA

### Define The Cohort
- Set the cohort key as the signup period or the first-activity period.
- Choose the period granularity such as day, week, or month.
- Decide whether periods align to the calendar or to days since signup.
- Handle users who never return after their signup period.
- Confirm how a user is uniquely identified across events.
- Decide how to treat users acquired through different channels.

### Define Retention
- Distinguish classic, rolling, and bounded retention clearly and pick one.
- Define exactly what event counts as active for retention.
- Decide whether period zero is the cohort period or the first return.
- Account for users who are active in non-contiguous periods.
- Clarify whether a single qualifying event in a period counts.
- State whether reactivation after a gap re-enters the curve.

### Build The Matrix
- Compute cohort size as the denominator for each cohort.
- Compute retained counts per cohort for each offset period.
- Output both raw counts and percentages for the heatmap.
- Densify the matrix so empty cells appear as zero rather than missing.
- Produce a tidy shape that pivots cleanly in the BI tool.
- Cap the matrix at the number of periods that have complete data.

### Handle Data Quality
- Deduplicate multiple events per user within the same period.
- Align timezones consistently so period boundaries are correct.
- Exclude test or internal accounts if I specify them.
- Address users who churn and later reactivate.
- Handle events with null or malformed timestamps.
- Exclude cohorts too recent to have meaningful follow-up periods.

### Make It Reportable
- Format the output for a pivot or heatmap in the BI tool.
- Suggest how to compute average retention curves across cohorts.
- Recommend filters for segmenting cohorts by an attribute.
- Note performance considerations for very large event tables.
- Provide a sanity check comparing cohort sizes to known totals.
- Suggest how to compare two segments' curves side by side.

## ASK THE USER FOR
- The events or activity table and its key columns.
- How a user is uniquely identified.
- The period granularity and the retention definition you want.
- Any accounts or event types to exclude.
- Whether you need calendar-aligned or signup-relative periods.