Create retention curves

Retention asks whether a user or account returns after starting. A cohort curve groups entities by their start week and plots the percentage active in each later week.

Define “start” and “active”#

This guide uses:

• start: account_created
• active: product_used
• entity: account_id
• calendar: weeks in America/Los_Angeles

Choose definitions that reflect durable product value. A page view may inflate activity; completing a core workflow is often more meaningful.

Log durable identities#

{
  "event": "account_created",
  "account_id": "acct_7"
}

{
  "event": "product_used",
  "account_id": "acct_7",
  "feature": "report_builder"
}

The same account ID must appear on both events.

Query weekly retention#

WITH signups AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    min(
      toStartOfWeek(
        toDateTime(timestamp, 'America/Los_Angeles')
      )
    ) AS cohort_week
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'account_created'
  GROUP BY account_id
),
activity AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    toStartOfWeek(
      toDateTime(timestamp, 'America/Los_Angeles')
    ) AS activity_week
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'product_used'
  GROUP BY account_id, activity_week
),
cohort_sizes AS (
  SELECT
    cohort_week,
    count() AS cohort_size
  FROM signups
  GROUP BY cohort_week
)
SELECT
  toUnixTimestamp(activity.activity_week) AS activity_timestamp,
  toString(signups.cohort_week) AS cohort,
  dateDiff('week', signups.cohort_week, activity.activity_week) AS week_number,
  uniqExact(activity.account_id) / any(cohort_sizes.cohort_size) AS retention
FROM activity
INNER JOIN signups USING account_id
INNER JOIN cohort_sizes USING cohort_week
WHERE activity.activity_week >= signups.cohort_week
GROUP BY
  signups.cohort_week,
  activity.activity_week
ORDER BY
  signups.cohort_week,
  activity.activity_week

The result has one point per cohort and activity week.

Visualize the curves#

Open Visualization and choose Multi Line:

• x-axis: activity_timestamp
• y-axis: retention
• split: cohort

Format retention as a percentage if needed.

An alternative cohort table uses week_number as the column and cohort as the row. Export the query data if you want to render a traditional triangular retention matrix in your own UI.

Validate the result#

Pick a small cohort and manually verify:

1. the number of created accounts
2. the accounts active in week 0
3. the accounts active in week 1
4. the resulting ratios

Week 0 can be below 100% when account_created does not itself count as active and some accounts never emit product_used.

Common mistakes#

Using first observed activity as signup#

If your dataset begins after the product launched, the first activity you observe may not be the true start date. Log an explicit creation event or import the source-of-truth creation timestamp.

Counting events instead of entities#

Retention counts whether an account was active in a period, not how many events it generated. Group by account_id and activity week before calculating the ratio.

Mixing time zones#

Use the same IANA time zone in cohort assignment and activity bucketing. Otherwise an event near midnight may fall into different weeks.

Including incomplete cohorts#

The newest cohort has not had time to reach later weeks. Do not compare its missing future periods with mature cohorts.

Redefining activity silently#

A chart becomes incomparable when its activity event changes. Version the definition or annotate the dashboard when the product’s core action changes.