Retention curves: distinguish acquisition and calendar views

A retention curve follows a defined starting population through time. A calendar activity chart counts activity during particular dates. Both can be useful, but a growing calendar count does not establish that a larger proportion of each acquisition cohort returns.

New customer acquisition can hide weak return behavior. Conversely, a smaller acquisition month can reduce total activity while retention within each cohort improves. Keep those questions separate before interpreting a chart.

Specify the starting event and the return event

Use the original synthetic product analytics dataset. A cohort contains external nonbot users who signed up on the same UTC date. A return means at least one valid report creation, publication, view or share.

Here, day seven means the UTC calendar date seven dates after signup. It does not mean the first seven elapsed days after the signup timestamp. The observation cutoff is January 16, 2026 at 00:00 UTC, exclusive.

Only display a cohort-age cell after its entire calendar day has elapsed. A future cell is unknown, not zero.

Build an eligible cohort-age grid

WITH ages(age) AS (VALUES(0),(1),(2),(7)),
eligible AS (
 SELECT user_id,date(signup_at) AS cohort
 FROM users WHERE user_kind='external' AND is_bot=0
), cells AS (
 SELECT u.user_id,u.cohort,a.age,
        date(u.cohort,'+'||a.age||' days') AS return_date
 FROM eligible u CROSS JOIN ages a
 WHERE date(u.cohort,'+'||(a.age+1)||' days')<='2026-01-16'
)
SELECT cohort,age,COUNT(*) AS cohort_users,
 SUM(EXISTS(
   SELECT 1 FROM events e WHERE e.user_id=c.user_id AND e.valid=1
   AND e.event_name IN
       ('report_created','report_published','report_viewed','report_shared')
   AND date(e.event_time)=c.return_date
 )) AS returning_users
FROM cells c GROUP BY cohort,age ORDER BY cohort,age;

The January 1 cohort contains U1 and U2. Its day-zero rate is 1/2; day one is 2/2; day two is 1/2; day seven is 1/2. Day zero is not automatically 100% because signup itself is not the qualifying return event.

For January 5, U5 and U6 form the cohort. Day-seven retention is 1/2 because U6 publishes on January 12. That same event falls exactly at the exclusive boundary of U6's seven-elapsed-day activation window. Different contracts can legitimately classify one event differently.

Verify mature and immature cells

Run this from the lab directory:

from datetime import date, timedelta
from build_and_verify import database, active_ids

db = database()
cohorts = {}
for user, signup in db.execute("SELECT user_id,signup_at FROM users WHERE user_kind='external' AND is_bot=0"):
    cohorts.setdefault(date.fromisoformat(signup[:10]), set()).add(user)
cells = {}
cutoff = date(2026, 1, 16)
for cohort, members in cohorts.items():
    for age in (0, 1, 2, 7):
        start = cohort + timedelta(days=age)
        end = start + timedelta(days=1)
        if end <= cutoff:
            active = set(active_ids(db, str(start)+'T00:00:00Z', str(end)+'T00:00:00Z'))
            cells[(str(cohort), age)] = (len(active & members), len(members))
db.close()
assert cells[('2026-01-01', 1)] == (2, 2)
assert cells[('2026-01-05', 7)] == (1, 2)
assert cells[('2026-01-14', 1)] == (0, 1)
assert ('2026-01-14', 7) not in cells
print(cells)

U9's day-one cell is observed and zero because its January 15 event is only a page view. Its day-seven cell is not yet observable. Rendering both as zero would erase a consequential distinction.

Choose the curve that answers the decision

Exact-day retention asks whether someone returned in a particular age bin. Return-on-or-after retention asks whether they returned at that age or later within the available horizon. Consecutive retention asks about uninterrupted activity. These measures have different denominators and observation requirements; do not relabel one as another.

Amplitude's retention interpretation documentation describes several time and return definitions. When reproducing a vendor report, match its settings explicitly rather than assuming this teaching query is identical.

For cohort comparisons, show sizes beside percentages and compare compatible ages. An aggregate age-seven rate should weight eligible cohorts by users, not average their percentages equally. Changing cohort composition can still affect that aggregate.

Exercise: move the cutoff to January 15 at midnight. Identify which cells become unknown and verify that no newly incomplete day remains in the denominator.

NeuraPath's Data Analytics with Generative AI course provides a route from event-level SQL to interpretable product reporting. Retention analysis becomes useful when a reviewer can reconstruct both the numerator and the time at risk.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Activation metrics: connect first value to observable behaviour.
• Continue with Customer lifetime value with transparent assumptions.