mx_signups (user_id, signed_up_at) and mx_events (event_id, user_id, event_at) are UTC timestamps.
Users are grouped by signup week; weeks run Monday 00:00 to Sunday 23:59, and cohort_week is the Monday of the week the user signed up in. An event's week_number is how many calendar weeks after the cohort week it falls: 0 for the signup week itself, 1 for the next Monday–Sunday, and so on. This counts week boundaries, not 7-day periods since the signup.
Build the retention matrix for weeks 0 to 4, one row per cohort and week number. Every cohort gets all five rows, with 0 when nobody from it was active that week; activity after week 4 is ignored.
- •
cohort_size — every user who signed up that week, active or not - •
active_users — cohort users with at least one event in that week - •
retention_pct — active_users as a percentage of cohort_size, rounded to 1 decimal place
Columns: cohort_week (a DATE), week_number, cohort_size, active_users, retention_pct. Sort by cohort_week, then week_number.