ORIGINAL TECHNICAL PRACTICE
Define a retention cohort before writing a query
A dashboard reports30-day retention. What definitions would you clarify before accepting that number?
Written for independent practice. The model answer presents one approach; clarify constraints and explain alternatives.
A REASONED APPROACH
Model answer.
Retention depends on the cohort, qualifying activity, time window and eligibility to be observed. Without these definitions, two correct calculations can answer different questions.
Work through it
- Define cohort entry, such as first completed signup, and the user identifier. Specify whether retention means activity on day30, during days30–36 or at any time within30days.
- Determine the timezone and whether days are calendar dates or elapsed24-hour periods. Exclude or separately mark cohorts whose observation window has not matured.
- Count each eligible user once in the denominator and once at most in the retained numerator. Verify the calculation on a small hand-worked set of events.
Tradeoffs to discuss
- A wider return window can reduce noise but answers a broader question. Excluding immature cohorts improves comparability while reducing the amount of recent data shown.
Common pitfalls
- Counting events rather than distinct users inflates retention. Including users who have not yet reached day30 makes recent cohorts look artificially weak.