Compute classic, rolling and cumulative retention
A copy-ready SQL query, spreadsheet formulas and four checks that catch the wrong definition.
All three come from one table of user and active date. Classic counts users active where day_n = N, rolling where day_n >= N, cumulative where day_n is between 1 and N. Divide by installs, include only cohorts that have reached Day N, and record the as-of date for rolling.
How do you calculate retention in SQL?
Every definition starts from the same raw data: which days each user opened the app after installing. Here a return is any app open; to use a different return event, such as a purchase, filter the activity table to that event first.
-- installs(user_id, install_date, channel)
-- activity(user_id, active_date) one row per user per day they opened the app
WITH returns AS (
SELECT DISTINCT a.user_id, (a.active_date - i.install_date) AS day_n
FROM activity a
JOIN installs i ON i.user_id = a.user_id
WHERE a.active_date > i.install_date -- Day 0 never counts
)
SELECT
i.install_date,
COUNT(DISTINCT i.user_id) AS installs,
COUNT(DISTINCT CASE WHEN r.day_n = 7 THEN r.user_id END) AS classic_d7,
COUNT(DISTINCT CASE WHEN r.day_n >= 7 THEN r.user_id END) AS rolling_d7,
COUNT(DISTINCT CASE WHEN r.day_n BETWEEN 1 AND 7 THEN r.user_id END) AS cumulative_d7
FROM installs i
LEFT JOIN returns r ON r.user_id = i.user_id
WHERE i.install_date + 7 <= (SELECT MAX(active_date) FROM activity) -- only cohorts that reached Day 7
GROUP BY i.install_date
ORDER BY i.install_date;Change the 7 to any Day N. For a full cohort table, compute day_n once and pivot, or repeat the three columns for each day you need. Add i.channel to the SELECT and GROUP BY to split by channel.
In a spreadsheet
Layout: one row per user. Column A = install date, B = channel,
C to Q = Day 0 to Day 14 (1 if the user opened the app that day, else 0).
Day 1 is column D, Day 7 is column J, Day 14 is column Q.
Classic Day 7 =J2
Rolling Day 7 =IF(COUNTIF(J2:Q2,1)>0,1,0) Day 7 to the last day you have
Cumulative Day 7 =IF(COUNTIF(D2:J2,1)>0,1,0) Day 1 to Day 7
Retention for a cohort = AVERAGE of a helper column over that cohort's rows
(for example with AVERAGEIFS on the install date).The ten-users view is exactly this layout with fourteen days.
Four checks that catch a wrong definition
- Classic is the floor: no rolling or cumulative cell may be below the classic cell for the same cohort and day.
- Cumulative never falls across a row.
- Rolling never rises across a row, for one as-of date.
- Identities: on a cohort's last observed day, rolling equals classic; and rolling Day 1 equals cumulative on the last observed day. In the sample app, the 1 Sep cohort's rolling Day 1 and cumulative Day 14 are both 108 of 148 users.
If a tool's columns break one of these, it is using a different definition or a different Day 0 than its label suggests.
Questions
How do I calculate classic retention in SQL?
Count distinct users whose activity falls on exactly install date plus N days, divide by the installs in the cohort, and only include cohorts that have reached Day N. The query on this page does that for Day 7.
Should Day 0 activity count as a return?
No. Day 0 is the install day, and almost every user is active on it, so counting it would inflate Day 1 cumulative and rolling numbers. Filter it out before counting.
Calendar days or 24-hour windows?
Either works if you are consistent and say which. Calendar days in one fixed time zone are easier to reconcile with dashboards; 24-hour windows treat late-night installs more fairly.
What is the difference between classic, rolling and cumulative retention?
Classic counts users active on exactly Day N. Rolling counts users active on Day N or any later day, and cumulative counts users active at least once from Day 1 to Day N. They use the same raw activity but answer different questions.
Why is my rolling retention higher than classic?
Rolling also counts users who skipped Day N but came back later, so it can never be lower than classic. The gap grows the longer a cohort has been observed, because late returns keep adding to it.
What is cumulative retention?
Cumulative retention is the share of an install cohort that opened the app at least once between Day 1 and Day N. It never falls as N grows and it is fixed once Day N has passed, which makes it a good activation measure.