lesson 53 Advanced product analysis · advanced

Cohorts & exact-day retention

Measure whether users return on a specific day after joining.

the idea

A cohort groups users by when they started; retention asks what share came back later. Exact-day retention checks activity on one specific day (signup date + 7 for D7). Only include cohorts that are mature: their measurement day must fall before the data ends.

Dataset: Product / SaaS Analytics

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Size each signup month. Run this. cohort_month groups users by when they joined—the denominator before you measure who came back.the pattern

   example.sql Run

   sql›

   SELECT substr(created_at, 1, 7) AS cohort_month, COUNT(*) AS signups
   FROM users
   GROUP BY cohort_month;

2. 2

   Check one user on day 7. Run this. d7 is signup plus seven days; events_on_d7 counts activity on that calendar day only.

   step-2.sql Run

   sql›

   SELECT
     u.user_id,
     date(u.created_at) AS signup_day,
     date(u.created_at, '+7 days') AS d7,
     COUNT(e.delivery_id) AS events_on_d7
   FROM users u
   LEFT JOIN events e
     ON e.user_id = u.user_id
     AND date(e.occurred_at) = date(u.created_at, '+7 days')
   WHERE u.user_id BETWEEN 9000 AND 9010
   GROUP BY u.user_id;

3. 3

   See who is D7-measurable. Run this. d7_observable counts signups whose seventh day already passed in the data—recent signups stay out until you can see that day.

   step-3.sql Run

   sql›

   SELECT
     substr(created_at, 1, 7) AS cohort_month,
     COUNT(*) AS signups,
     SUM(date(created_at, '+7 days') <= '2025-06-30') AS d7_observable
   FROM users
   WHERE created_at >= '2025-05-01'
   GROUP BY cohort_month;

common mistake

Users who signed up on 2025-06-28 cannot have D7 activity in data that ends on 2025-06-30. Including them makes the newest cohort look worse.