Metrics and analytics · Analytics

The retention grid

Build the cohort-by-week retention grid from two event exports, with weeks that have not happened yet left blank rather than counted as zero.

codehardExercise · 4 min · 25 of 59

On this page

The retention query returns one long column; the grid a tenant can read needs the ragged right edge handled, and the spreadsheet that did it last quarter counted every blank as zero.

The situation

A tenant's success manager asks for week-N retention by sign-up week for the 600 agents on the drafting assistant. The analyst's spreadsheet pivoted the retention query and reported the newest cohort at 0% for week 2, because week 2 had not happened yet and the pivot filled the cell with 0. The tenant read it as a collapse. The data is two exports: signups, one row per agent with the day they signed up, and events, one row per activity event with the day it happened; any event counts as active. The rules the grid must follow, from the cohorts lesson: a cohort is a sign-up week starting on Monday; week N for an agent is days 7N to 7N+6 after their own sign-up day, so a Friday signer's week 1 starts on the next Friday; an agent is active in a week once, however many events they logged; and a week is reported for a cohort only when it has closed for every member, measured against the latest date anywhere in the data. Not yet closed is None, never 0.

The question

Write the function so that the grid has one row per cohort, every row the same length, and a blank cell wherever the week has not happened.

def solve(signups, events):
    """Week-N retention per sign-up cohort.

    signups: [(user_id, 'YYYY-MM-DD')] — one row per user, the day they signed up.
    events:  [(user_id, 'YYYY-MM-DD')] — one row per activity event; any event counts.
    Returns [(cohort, size, [pct, ...]), ...] in cohort order: cohort is the ISO date of
    the Monday of the sign-up week, size the users in it, pct[N] the percentage of the
    cohort active in days 7N..7N+6 after their own sign-up day, one decimal. A week is
    reported only once it has closed for every member, else None; all rows share a length.
    """

signups = [('a', '2026-08-03'), ('b', '2026-08-05'), ('c', '2026-08-07'), ('d', '2026-08-09'),
           ('e', '2026-08-10'), ('f', '2026-08-12'), ('g', '2026-08-19')]
events  = a on 08-03, 08-11, 08-20, 09-01 · b on 08-05, 08-13 · c on 08-07 · d on 08-09, 08-24
          e on 08-10, 08-18 · f on 08-12, 08-13, 08-20, 08-27 · g on 08-19, 08-30

solve(signups, events)  ->  [('2026-08-03', 4, [100.0, 50.0, 50.0]),
                             ('2026-08-10', 2, [100.0, 100.0, 50.0]),
                             ('2026-08-17', 1, [100.0, 100.0, None])]

A strong answer · Rubric · Where answers go wrong · Solution

Write your answer first, then open

A strong answer

Three passes. The first reads signups into a map from user to sign-up date and groups users by the Monday of their week, date − weekday. The second reads events and, for each row whose user has a sign-up date and whose day is on or after it, adds (day − signup) // 7 to that user's set of active weeks; the set counts forty events in one week once. The latest date seen in either export becomes today.

The third pass walks the cohorts in order of their Monday, which is the row order, and decides for each how many weeks have closed: take the cohort's latest sign-up day, measure days to today, and week N is closed when 7N + 6 is at most that, so the count is (days − 6) // 7 + 1, or 0 if fewer than six days have passed. The row's cells are the closed weeks, each 100 × active ÷ size to one decimal, padded with None to the longest row.

In the example, cohort 3 August's last member signed up on the 9th and the latest date is 1 September, 23 days later, so weeks 0 to 2 have closed; the 17 August cohort's member signed up on the 19th, 13 days before, so only weeks 0 and 1 have, and week 2 is None. Agent f was active on 27 August, their week 2, and it shows: 50% in the 10 August cohort's third cell.

It never counts from the cohort's Monday, counts a user not in signups, or returns 0 for an unclosed week. A narrower definition of active than "any event" is a filter the caller applies to events.

Rubric

Keys cohorts by the Monday of the sign-up week and orders rows by it
Measures week N from each user's own sign-up day, not from the cohort's Monday
Counts a user once per week however many events they logged, and ignores events before sign-up or from unknown users
Reports a week only when it has closed for the cohort's latest member, measured against the latest date in the data
Pads short rows with None, so every row has the same length and a blank is distinguishable from 0.0
Produces the example grid exactly, including the None in the youngest cohort

Where answers go wrong

"Fill unreached weeks with 0" — the analyst's spreadsheet, and the cause of the tenant's alarm. A cohort that has not reached week 2 has no week-2 figure; reporting 0 makes a healthy product look like it collapsed.

"Week N starts at the cohort's Monday plus 7N" — a Sunday signer's week 0 is then one day long, and the grid's week-0 column drops below 100% for reasons that have nothing to do with retention. The window is relative to each user's own day.

"len(events) per week divided by size" — counts events, not users, so one agent with 40 drafts shows a cohort of 4 at 1,000% retention. Active is a set of users, and the numerator is its size.

Solution

Python 3 · standard library59 lines · 7 of 7 tests passing

"""The retention grid — week-N retention per weekly sign-up cohort, with the ragged right edge made explicit."""
from datetime import date, timedelta


def monday_of(d):
    """The Monday that starts d's week; cohorts are keyed by it."""
    return d - timedelta(days=d.weekday())


def solve(signups, events):
    """Week-N retention per sign-up cohort.

    signups: [(user_id, 'YYYY-MM-DD')] — one row per user, the day they signed up.
    events:  [(user_id, 'YYYY-MM-DD')] — one row per activity event; any event counts as active.
    Returns [(cohort, size, [pct, ...]), ...] in cohort order, where cohort is the ISO date of
    the Monday of the sign-up week, size is the users in it, and pct[N] is the percentage of the
    cohort with at least one event in days 7N..7N+6 after their own sign-up day, rounded to one
    decimal. A week is reported only once it has closed for every member — the window's last
    day is on or before the latest date anywhere in the data — and is None otherwise. Every row
    has the same length: through the last week any cohort has closed.
    """
    signed = {}
    for user, day in signups:
        d = date.fromisoformat(day)
        if user not in signed or d < signed[user]:   # a duplicate row keeps the earliest day
            signed[user] = d
    if not signed:
        return []

    today = max(signed.values())
    weeks_active = {user: set() for user in signed}
    for user, day in events:
        d = date.fromisoformat(day)
        today = max(today, d)                         # every row dates the export, known user or not
        if user not in signed:                        # an event with no sign-up row is noise
            continue
        offset = (d - signed[user]).days
        if offset >= 0:                               # before sign-up is not retention
            weeks_active[user].add(offset // 7)       # a set: many events in one week count once

    cohorts = {}
    for user, d in signed.items():
        cohorts.setdefault(monday_of(d), []).append(user)

    # A cohort has closed week N when its latest member's window, days 7N..7N+6, has ended.
    def closed_weeks(members):
        last = max(signed[u] for u in members)
        days = (today - last).days
        return 0 if days < 6 else (days - 6) // 7 + 1

    columns = max(closed_weeks(m) for m in cohorts.values())
    rows = []
    for start in sorted(cohorts):
        members = cohorts[start]
        n = closed_weeks(members)
        pcts = [round(100.0 * sum(week in weeks_active[u] for u in members) / len(members), 1) for week in range(n)]
        pcts += [None] * (columns - n)                # not yet reached is None, never 0
        rows.append((start.isoformat(), len(members), pcts))
    return rows

The cases that ran

TESTS = [
    # Three cohorts; the newest has not reached week 2, so its cell is None rather than 0.
    ((
        [('a', '2026-08-03'), ('b', '2026-08-05'), ('c', '2026-08-07'), ('d', '2026-08-09'),
         ('e', '2026-08-10'), ('f', '2026-08-12'), ('g', '2026-08-19')],
        [('a', '2026-08-03'), ('a', '2026-08-11'), ('a', '2026-08-20'), ('a', '2026-09-01'),
         ('b', '2026-08-05'), ('b', '2026-08-13'),
         ('c', '2026-08-07'),
         ('d', '2026-08-09'), ('d', '2026-08-24'),
         ('e', '2026-08-10'), ('e', '2026-08-18'),
         ('f', '2026-08-12'), ('f', '2026-08-13'), ('f', '2026-08-20'), ('f', '2026-08-27'),
         ('g', '2026-08-19'), ('g', '2026-08-30')],
    ), [('2026-08-03', 4, [100.0, 50.0, 50.0]), ('2026-08-10', 2, [100.0, 100.0, 50.0]), ('2026-08-17', 1, [100.0, 100.0, None])]),
    # Duplicates count once, an event before sign-up is ignored, an unknown user is ignored,
    # and a user with no events pulls week 0 below 100.
    ((
        [('p', '2026-01-05'), ('q', '2026-01-06'), ('r', '2026-01-07')],
        [('p', '2026-01-05'), ('p', '2026-01-05'), ('p', '2026-01-12'), ('p', '2026-01-19'), ('p', '2026-01-21'),
         ('q', '2026-01-04'), ('q', '2026-01-14'),
         ('z', '2026-01-10')],
    ), [('2026-01-05', 3, [33.3, 66.7])]),
    # No sign-ups: nothing to report.
    (([], [('a', '2026-01-01')]), []),
    # A cohort whose first week has not closed yet: a row with no cells.
    (([('a', '2026-03-02')], [('a', '2026-03-05')]), [('2026-03-02', 1, [])]),
    # The latest date anywhere in the data closes the week, even when it belongs to an unknown user.
    (([('a', '2026-05-04')], [('a', '2026-05-04'), ('z', '2026-05-11')]), [('2026-05-04', 1, [100.0])]),
    # An old cohort with five closed weeks beside a young one: 0.0 is a closed week with nobody
    # active; None is a week that has not happened.
    ((
        [('a', '2026-02-02'), ('b', '2026-02-23')],
        [('a', '2026-02-02'), ('a', '2026-02-09'), ('a', '2026-02-16'), ('a', '2026-02-23'), ('a', '2026-03-02'), ('a', '2026-03-09'),
         ('b', '2026-02-23'), ('b', '2026-03-01'), ('b', '2026-03-09')],
    ), [('2026-02-02', 1, [100.0, 100.0, 100.0, 100.0, 100.0]), ('2026-02-23', 1, [100.0, 0.0, None, None, None])]),
    # A Sunday sign-up belongs to the week that started the Monday before; week N is counted
    # from the user's own sign-up day, not from the cohort's Monday.
    ((
        [('s', '2026-04-05'), ('t', '2026-04-06')],
        [('s', '2026-04-05'), ('t', '2026-04-20')],
    ), [('2026-03-30', 1, [100.0, 0.0]), ('2026-04-06', 1, [0.0, 0.0])]),
]