User retention - Token Terminal Docs

One row per project per signup month in reports_projects.user_retention_monthly: the users first active in the project that month, and the share of them active in each of the twelve months from signup. A user is an address active in the project in a month, and the counts are HyperLogLog estimates at precision 15.

Columns

Column	Type	Description
`project_id`	STRING	Project the cohort belongs to, such as `aave`; joins `dimensions.projects`.
`period_start`	TIMESTAMP	First moment of the signup month. A project’s rows run every month from its first cohort to its latest month, and for an archived project they end at the month it was archived.
`period_end`	TIMESTAMP	Last moment of the signup month.
`period_is_complete`	BOOL	False while the signup month is still running. The table is rebuilt weekly, and a cohort’s later cells keep moving until their own month ends.
`cohort_users`	INT64	Distinct users first active in the project that month. 0 for a month with no new users.
`retention_month_0`	FLOAT64	Share of the cohort active in its signup month: 1 for a cohort with users, 0 for an empty one.
`retention_month_1`	FLOAT64	Share of the cohort active in the month after signup, as a fraction to three decimal places. Null until that month is reached, 0 when it is reached with no activity.
`retention_month_2`	FLOAT64	The same share, 2 months after signup.
`retention_month_3`	FLOAT64	The same share, 3 months after signup.
`retention_month_4`	FLOAT64	The same share, 4 months after signup.
`retention_month_5`	FLOAT64	The same share, 5 months after signup.
`retention_month_6`	FLOAT64	The same share, 6 months after signup.
`retention_month_7`	FLOAT64	The same share, 7 months after signup.
`retention_month_8`	FLOAT64	The same share, 8 months after signup.
`retention_month_9`	FLOAT64	The same share, 9 months after signup.
`retention_month_10`	FLOAT64	The same share, 10 months after signup.
`retention_month_11`	FLOAT64	The same share, 11 months after signup.

Sample queries

One project's cohort triangle
Month-3 retention across projects
Average retention curve

Each row is a signup month and each retention_month_k column a month since signup. Cells for months not reached yet are null, which gives the triangle its shape.

select
    period_start,
    cohort_users,
    retention_month_0,
    retention_month_1,
    retention_month_2,
    retention_month_3,
    retention_month_6,
    retention_month_11
from `reports_projects.user_retention_monthly`
where project_id = 'uniswap'
  and period_start >= timestamp('2025-01-01')
order by period_start

The window holds the twelve latest signup months whose third month after signup has ended. Weighting by cohort_users keeps a month with few new users from moving the average, and the floor keeps small projects out of the ranking.

with cohorts as (
    select
        project_id,
        cohort_users,
        retention_month_3
    from `reports_projects.user_retention_monthly`
    where period_start >= timestamp(date_sub(date_trunc(current_date(), month), interval 15 month))
      and period_start < timestamp(date_sub(date_trunc(current_date(), month), interval 3 month))
      and cohort_users > 0
)
select
    projects.name,
    sum(cohorts.cohort_users) as cohort_users,
    safe_divide(
        sum(cohorts.cohort_users * cohorts.retention_month_3),
        sum(cohorts.cohort_users)
    ) as retention_month_3
from cohorts
join `dimensions.projects` as projects
    using (project_id)
group by projects.name
having sum(cohorts.cohort_users) >= 10000
order by retention_month_3 desc
limit 25

unpivot turns the twelve retention columns into one row per month since signup. Null cells drop out, so each point averages only the cohorts that have reached it.

select
    months_since_signup,
    safe_divide(sum(cohort_users * retention), sum(cohort_users)) as retention
from (
    select
        cohort_users,
        retention_month_0 as m0, retention_month_1 as m1, retention_month_2 as m2,
        retention_month_3 as m3, retention_month_4 as m4, retention_month_5 as m5,
        retention_month_6 as m6, retention_month_7 as m7, retention_month_8 as m8,
        retention_month_9 as m9, retention_month_10 as m10, retention_month_11 as m11
    from `reports_projects.user_retention_monthly`
    where project_id = 'aave'
      and period_start >= timestamp('2025-01-01')
      and cohort_users > 0
)
unpivot (
    retention for months_since_signup in (
        m0 as 0, m1 as 1, m2 as 2, m3 as 3, m4 as 4, m5 as 5,
        m6 as 6, m7 as 7, m8 as 8, m9 as 9, m10 as 10, m11 as 11
    )
)
group by months_since_signup
order by months_since_signup