> ## 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><code>project\_id</code></td>
      <td>STRING</td>
      <td>Project the cohort belongs to, such as <code>aave</code>; joins <code>dimensions.projects</code>.</td>
    </tr>

    <tr>
      <td><code>period\_start</code></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><code>period\_end</code></td>
      <td>TIMESTAMP</td>
      <td>Last moment of the signup month.</td>
    </tr>

    <tr>
      <td><code>period\_is\_complete</code></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><code>cohort\_users</code></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><code>retention\_month\_0</code></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><code>retention\_month\_1</code></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><code>retention\_month\_2</code></td>
      <td>FLOAT64</td>
      <td>The same share, 2 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_3</code></td>
      <td>FLOAT64</td>
      <td>The same share, 3 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_4</code></td>
      <td>FLOAT64</td>
      <td>The same share, 4 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_5</code></td>
      <td>FLOAT64</td>
      <td>The same share, 5 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_6</code></td>
      <td>FLOAT64</td>
      <td>The same share, 6 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_7</code></td>
      <td>FLOAT64</td>
      <td>The same share, 7 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_8</code></td>
      <td>FLOAT64</td>
      <td>The same share, 8 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_9</code></td>
      <td>FLOAT64</td>
      <td>The same share, 9 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_10</code></td>
      <td>FLOAT64</td>
      <td>The same share, 10 months after signup.</td>
    </tr>

    <tr>
      <td><code>retention\_month\_11</code></td>
      <td>FLOAT64</td>
      <td>The same share, 11 months after signup.</td>
    </tr>
  </tbody>
</table>

## Sample queries

<Tabs>
  <Tab title="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
    ```
  </Tab>

  <Tab title="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
    ```
  </Tab>

  <Tab title="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
    ```
  </Tab>
</Tabs>


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