Cohort and retention analysis plan with code

Set up a proper cohort retention analysis, from defining cohorts and the activity event to producing the table and reading it correctly.

Change note: Initial version

Prompt

Help me do a cohort retention analysis.

Product or business: {{business}}
Cohort is defined by: {{cohort_basis}}
Retention means the user did this: {{retention_event}}
Time granularity: {{granularity}}
Data tables and columns available: {{data}}
Tool: {{tool}}

Provide:
1. Definitions: restate the cohort, the retention event, and the time bucket exactly. Point out any ambiguity (for example, is the first period "period 0" or "period 1"? is retention "active in period N" or "active in N or later"?). Recommend which to use.
2. Step by step build: the logic for assigning each user to a cohort, computing periods since cohort start, counting active users per cohort-period, and dividing by cohort size. Then give working {{tool}} code.
3. Incomplete periods: show how to avoid counting partially elapsed periods as drop-offs.
4. How to read the retention table: what the diagonal pattern, a flat tail, and a steepening drop each mean. Describe the retention curve shape for a healthy product of this type and what would be a warning sign.
5. Pitfalls: small cohorts, changing definitions of the activity event, seasonality, reactivated users, bots or internal users.
6. Three follow-up cuts that could explain differences between cohorts (for example acquisition channel, plan, first-week behavior).
7. A heatmap spec: colour scale, labeling, and how to show cohort sizes.

State your assumptions, and ask about any detail that would change the code.

Variables

• {{business}}Businesstext

  Default: Mobile fitness app with a free tier and a paid subscription

• {{cohort_basis}}Cohort definitiontext

  Default: Signup week

• {{retention_event}}Retention eventtext

  Default: Completes at least one workout in the week

• {{granularity}}Granularityselect

  Options: Daily, Weekly, Monthly

  Default: Weekly

• {{data}}Data availablelong

  Default: users(user_id, signup_at, plan, channel); workouts(workout_id, user_id, completed_at)

• {{tool}}Toolselect

  Options: SQL (PostgreSQL), Python (pandas), R, Excel, Google Sheets

  Default: SQL (PostgreSQL)

Example output

**1. Definitions**
- Cohort: users grouped by the week of signup_at (week starts Monday).
- Retained in week N: completed 1+ workout during the week that is N weeks after the signup week. Week 0 = signup week (always 100% if signup counts as activity, so I recommend excluding week 0 from the headline and showing week 1 onward, or defining week 0 by workouts too).
- Ambiguity: "active in week N" (point-in-time) rather than "active in week N or later" (rolling). Use point-in-time; it is stricter and more common for apps.

**2. Build (PostgreSQL)**
WITH cohorts AS (
  SELECT user_id, date_trunc('week', signup_at)::date AS cohort_week FROM users),
activity AS (
  SELECT DISTINCT user_id, date_trunc('week', completed_at)::date AS active_week FROM workouts)
SELECT c.cohort_week,
       (a.active_week - c.cohort_week) / 7 AS week_n,
       COUNT(DISTINCT a.user_id) AS active_users
FROM cohorts c JOIN activity a USING (user_id)
WHERE a.active_week >= c.cohort_week
GROUP BY 1, 2 ORDER BY 1, 2;
Divide by cohort size (COUNT per cohort_week from cohorts).

**3. Incomplete periods**: Exclude cells where cohort_week + N weeks > the latest complete week.

**4. Reading it**: Fitness apps often lose 60-70% in week 1; a flattening curve by week 6 is healthy; a continuing steep slope means no habit forms.

**6. Cuts**: acquisition channel, plan, workouts completed in the first 7 days.

**7. Heatmap**: rows = cohort week, columns = week N, shade by retention %, label cohort size at the row start.

Notes

Defining the retention event and the period numbering up front prevents the off-by-one confusion that makes retention tables untrustworthy. Ask for rolling retention variants if your product has infrequent use.