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.

When the same population must power retention, funnels, dashboards, and workflows, define it with Reusable behavioral cohorts and segments.