Cohort & Retention SQL — Marketing Analytics

Variables to fill in: {{question}}{{schema}}{{dialect}}

You are a staff analytics engineer who writes SQL that is correct first, fast second, and readable always. I need your help in the context of campaign, channel, and attribution data across the marketing funnel.

TASK: Build cohort retention analysis in SQL.

DELIVERABLE: Produce a cohort query: cohort assignment logic, period-over-period retention with the grain explicit, the pivot-ready output shape, and notes on the choices that change the numbers (calendar vs. relative periods).

PROCESS:
1. Review the inputs below. If anything critical is missing or ambiguous, ask me up to three clarifying questions before producing the deliverable.
2. Restate what the question means in terms of the actual tables and grain from the inputs before writing SQL.
3. Build incrementally with CTEs named after business concepts; each step should be checkable alone.
4. State the assumptions the query makes (dedup rules, timezone, null handling) right in the comments.
5. Produce the deliverable in a clean, skimmable format I can use directly.

QUALITY BAR:
- Handles the classic traps: fan-out joins, nulls in aggregates, timezone drift, late-arriving data.
- Readable by the next analyst without a walkthrough.
- Includes the validation query that proves the result is sane.

INPUTS:
- The business question the query must answer: {{question}}
- Relevant tables with their columns, grain, and quirks you know about: {{schema}}
- SQL dialect/warehouse: BigQuery, Snowflake, Postgres, etc.: {{dialect}}

Run this prompt on a real model without leaving the page. Every run is saved to your history for this prompt.

How to use

1. 1Fill in the {{question}}, {{schema}}, {{dialect}} variables with your real details — specifics in, specifics out.
2. 2Paste the prompt into ChatGPT, Claude, Gemini, or any capable model.
3. 3Answer the clarifying questions it asks; that step is what makes the output fit your situation.
4. 4Iterate on the deliverable: ask for alternatives, tighter versions, or a different angle on any section.

Pro tips

• If the output feels generic, add more concrete detail to the inputs — names, numbers, and constraints sharpen everything.
• Works well in a thread: keep the conversation going to refine a cohort query.