Weekly cohorts of new users, and the share of each cohort that did anything in the app in the weeks after signing up. About 46% come back in week one and 24% are still active in week five. Sample data from a fictional app, written by a coding agent.
Back within a week
46%Still active in week four
28%New users in these cohorts
5,379retention() comes from DuckDB's behavioral extension. For each user it takes one condition per week, "active in the signup week", "active one week later", and so on, and returns a true/false list. Averaging those lists per cohort gives the share of people who came back. The plain-SQL version of this query needs a self-join and a separate count of cohort sizes.
| Week | All users | Free | Pro | Users counted |
|---|---|---|---|---|
| 0 | 100 | 100 | 100 | 5379 |
| 1 | 46 | 45 | 53 | 5379 |
| 2 | 44 | 42 | 51 | 4574 |
| 3 | 35 | 34 | 44 | 3813 |
| 4 | 28 | 27 | 35 | 3103 |
| 5 | 24 | 24 | 27 | 2464 |
| 6 | 22 | 22 | 25 | 1863 |
| 7 | 20 | 19 | 23 | 1324 |
| 8 | 16 | 15 | 23 | 819 |
Finding: Pro users come back more. 53% of them are active the week after signing up against 45% of free users, and the gap widens to 35% against 27% by week four. Later weeks include fewer cohorts, so the last rows rest on fewer users (see the last column). Next: look at what pro users do in their first week that free users do not.