> ## Documentation Index
> Fetch the complete documentation index at: https://tokenterminal.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

> ## Agent Instructions
> To query the Token Terminal data catalog, read https://tokenterminal.com/docs/catalog/agents-manual.md first. It is the whole catalog as one page: table naming grammar, key columns, partition and cluster rules, units, additivity, and the tables that are documented but not served yet.
> Never query a catalog table on a time bound alone. Also filter its cluster key, which you read from INFORMATION_SCHEMA.COLUMNS; an empty result means the object is a view, whose pruning contract is on its page. Compute is billed to the caller's own Google Cloud project.

# User retention

> A protocol's monthly user cohorts, and the share of each still active in the months after signup.

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

<table>
  <thead>
    <tr>
      <th width="230">Column</th>
      <th width="130">Type</th>
      <th>Description</th>
    </tr>
  </thead>

  <tbody>
    <tr>
      <td>`project_id`</td>
      <td>STRING</td>
      <td>Project the cohort belongs to, such as `aave`; joins `dimensions.projects`.</td>
    </tr>

    <tr>
      <td>`period_start`</td>
      <td>TIMESTAMP</td>
      <td>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.</td>
    </tr>

    <tr>
      <td>`period_end`</td>
      <td>TIMESTAMP</td>
      <td>Last moment of the signup month.</td>
    </tr>

    <tr>
      <td>`period_is_complete`</td>
      <td>BOOL</td>
      <td>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.</td>
    </tr>

    <tr>
      <td>`cohort_users`</td>
      <td>INT64</td>
      <td>Distinct users first active in the project that month. 0 for a month with no new users.</td>
    </tr>

    <tr>
      <td>`retention_month_0`</td>
      <td>FLOAT64</td>
      <td>Share of the cohort active in its signup month: 1 for a cohort with users, 0 for an empty one.</td>
    </tr>

    <tr>
      <td>`retention_month_1`</td>
      <td>FLOAT64</td>
      <td>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.</td>
    </tr>

    <tr>
      <td>`retention_month_2`</td>
      <td>FLOAT64</td>
      <td>The same share, 2 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_3`</td>
      <td>FLOAT64</td>
      <td>The same share, 3 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_4`</td>
      <td>FLOAT64</td>
      <td>The same share, 4 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_5`</td>
      <td>FLOAT64</td>
      <td>The same share, 5 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_6`</td>
      <td>FLOAT64</td>
      <td>The same share, 6 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_7`</td>
      <td>FLOAT64</td>
      <td>The same share, 7 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_8`</td>
      <td>FLOAT64</td>
      <td>The same share, 8 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_9`</td>
      <td>FLOAT64</td>
      <td>The same share, 9 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_10`</td>
      <td>FLOAT64</td>
      <td>The same share, 10 months after signup.</td>
    </tr>

    <tr>
      <td>`retention_month_11`</td>
      <td>FLOAT64</td>
      <td>The same share, 11 months after signup.</td>
    </tr>
  </tbody>
</table>

## Sample queries

**One project's cohort triangle**

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.

```sql theme={null}
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
```

**Month-3 retention across projects**

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.

```sql theme={null}
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
```

**Average retention curve**

`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.

```sql theme={null}
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
```


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.