> ## Documentation Index
> Fetch the complete documentation index at: https://seilabs-docs-evm-cookbook.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Dune Analytics

> Complete guide to working with Dune Analytics on Sei Network

## What is Dune Analytics?

Dune Analytics is a blockchain analytics platform. You can use it to query, visualize, and share blockchain data with SQL. Game developers can use it for:

* **Player analytics**: Track user acquisition, retention, and engagement
* **Transaction analysis**: Monitor the game economy and player behavior
* **Performance metrics**: Measure daily and weekly active users, and transaction volumes
* **Cohort analysis**: Understand the player lifecycle and retention patterns
* **Custom dashboards**: Create visual reports for stakeholders

### Key features:

* SQL-based querying interface
* Pre-indexed blockchain data from multiple networks
* Visualization tools for charts and dashboards
* Real-time data updates

## Prerequisites

Before you begin, make sure you have:

1. **Dune account**: Sign up at [dune.com](https://dune.com)
2. **Basic SQL knowledge**: Understanding of the SELECT, JOIN, WHERE, and GROUP BY clauses
3. **Contract addresses**: Know your game's smart contract addresses
4. **Understanding of your game logic**: Know what each transaction represents in your game

### SQL knowledge requirements:

* Basic `SELECT` statements
* JOINs (`INNER`, `LEFT`)
* Aggregate functions (`COUNT`, `SUM`, `AVG`)
* Date functions (`DATE_TRUNC`, `DATE_DIFF`)
* Common table expressions (`WITH` clauses)

## Getting started

### Step 1: Access the template dashboard

Open the [Sei Games Query Templates](https://dune.com/sei/sei-games-query-templates) dashboard.

### Step 2: Understanding the dashboard structure

The template dashboard contains these metrics:

* Total unique users
* Cohort retention analysis
* User acquisition trends
* Transaction volume analysis
* Daily and weekly active users

This guide gives the SQL queries behind these metrics.

## How to fork and use query templates

### Forking a query

1. To open a query, click any visualization in the dashboard.
2. To open the query editor, click **Edit Query** or the query title.
3. To fork the query, click **Fork** in the top right.
4. Give your fork a descriptive name, such as "MyGame - Daily Active Users".
5. Replace the placeholder values with your own contract addresses.

### Making queries private/public

* **Private queries**: Visible only to you
* **Public queries**: Visible to all Dune users
* **Unlisted**: Not searchable, but accessible through a direct link

## Query templates for game analytics

### 1. Total unique users

**Purpose**: Get the total number of unique players who have ever interacted with your game.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
)

SELECT
  COUNT(DISTINCT t."from") AS total_unique_users
FROM sei.transactions AS t
JOIN my_game_contracts AS c
  ON t."to" = c.address
WHERE
  t.success = TRUE
```

**How to use**:

* Replace `0xYOUR_CONTRACT_ADDRESS_1` with your own contract addresses
* Add or remove addresses as needed

***

### 2. Cohort retention analysis

**Purpose**: Analyze how well you retain players over time by tracking weekly cohorts.

```sql theme={null}
WITH my_game_contracts AS (
    SELECT array[
        0xYOUR_CONTRACT_ADDRESS_1,
        0xYOUR_CONTRACT_ADDRESS_2
    ] AS addresses
),

-- Find the first time every user was ever seen (Define the Cohort)
user_cohorts AS (
    SELECT
        t."from" AS user_address,
        MIN(DATE_TRUNC('week', t.block_date)) AS cohort_week
    FROM sei.transactions t
    CROSS JOIN UNNEST(
        (SELECT addresses FROM my_game_contracts)
    ) AS c (address)
    WHERE t."to" = c.address
        AND t.success = true
    GROUP BY 1
),

-- Find all weeks where users were active (Activity Log)
user_activity AS (
    SELECT DISTINCT
        t."from" AS user_address,
        DATE_TRUNC('week', t.block_date) AS activity_week
    FROM sei.transactions t
    CROSS JOIN UNNEST(
        (SELECT addresses FROM my_game_contracts)
    ) AS c (address)
    WHERE t."to" = c.address
        AND t.success = true
),

-- Calculate Cohort Size
cohort_size AS (
    SELECT cohort_week, COUNT(user_address) AS total_users
    FROM user_cohorts
    GROUP BY 1
),

-- Calculate the time difference (offset) and retained users
retention_data AS (
    SELECT
        c.cohort_week,
        DATE_DIFF('week', c.cohort_week, a.activity_week) AS week_offset,
        COUNT(DISTINCT c.user_address) AS retained_users
    FROM user_cohorts c
    JOIN user_activity a ON c.user_address = a.user_address
    WHERE a.activity_week >= c.cohort_week
    GROUP BY 1, 2
)

-- Final Output: Dynamic List Format for Heatmap Visualization
SELECT
    d.cohort_week,
    s.total_users AS cohort_size,
    d.week_offset,
    ROUND(d.retained_users * 100.0 / s.total_users, 2) AS retention_percentage
FROM retention_data d
JOIN cohort_size s ON d.cohort_week = s.cohort_week
ORDER BY 1 ASC, 3 ASC;
```

**Key metrics**:

* `cohort_week`: When users first joined
* `week_offset`: Weeks since the first interaction (0 is the first week, 1 is the second week, and so on)
* `retention_percentage`: Percentage of the cohort still active

***

### 3. Weekly user acquisition

**Purpose**: Track how many new users you get each week.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
),

user_first_seen AS (
  SELECT
    t."from" AS user_address,
    MIN(t.block_date) AS first_interaction_date
  FROM sei.transactions AS t
  JOIN my_game_contracts AS c
    ON t."to" = c.address
  WHERE
    t.success = TRUE
  GROUP BY 1
)

SELECT
  DATE_TRUNC('week', first_interaction_date) AS acquisition_week,
  COUNT(user_address) AS new_users
FROM user_first_seen
GROUP BY 1
ORDER BY 1 DESC
```

***

### 4. Daily transaction volume

**Purpose**: Monitor daily transaction activity in your game.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
)

SELECT
  t.block_date,
  COUNT(*) AS tx_count
FROM sei.transactions AS t
JOIN my_game_contracts AS c
  ON t."to" = c.address
WHERE
  t.success = TRUE
GROUP BY 1
ORDER BY 1 DESC
```

***

### 5. Daily active users (DAU)

**Purpose**: Track unique daily active users.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
)

SELECT
  t.block_date,
  COUNT(DISTINCT t."from") AS daily_active_users
FROM sei.transactions AS t
JOIN my_game_contracts AS c
  ON t."to" = c.address
WHERE
  t.success = TRUE
GROUP BY 1
ORDER BY 1 DESC
```

***

### 6. Weekly transaction volume

**Purpose**: Analyze weekly transaction patterns.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
)

SELECT
  DATE_TRUNC('week', t.block_date) AS week_start,
  COUNT(*) AS tx_count
FROM sei.transactions AS t
JOIN my_game_contracts AS c
  ON t."to" = c.address
WHERE
  t.success = TRUE
GROUP BY 1
ORDER BY 1 DESC
```

***

### 7. Weekly active users (WAU)

**Purpose**: Track unique weekly active users.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
)

SELECT
  DATE_TRUNC('week', t.block_date) AS week_start,
  COUNT(DISTINCT t."from") AS weekly_active_users
FROM sei.transactions AS t
JOIN my_game_contracts AS c
  ON t."to" = c.address
WHERE
  t.success = TRUE
GROUP BY 1
ORDER BY 1 DESC
```

***

### 8. Daily user acquisition

**Purpose**: Track how many new users you get each day.

```sql theme={null}
WITH my_game_contracts AS (
  SELECT
    address
  FROM UNNEST(ARRAY[
    0xYOUR_CONTRACT_ADDRESS_1,
    0xYOUR_CONTRACT_ADDRESS_2
  ]) AS _u(address)
),

user_first_seen AS (
  SELECT
    t."from" AS user_address,
    MIN(t.block_date) AS first_interaction_date
  FROM sei.transactions AS t
  JOIN my_game_contracts AS c
    ON t."to" = c.address
  WHERE
    t.success = TRUE
  GROUP BY 1
)

SELECT
  first_interaction_date,
  COUNT(user_address) AS new_users
FROM user_first_seen
GROUP BY 1
ORDER BY 1 DESC
```

## Customizing queries for your game

### 1. Replace contract addresses

In each query, find this section:

```sql theme={null}
FROM UNNEST(ARRAY[
  0xYOUR_CONTRACT_ADDRESS_1,
  0xYOUR_CONTRACT_ADDRESS_2
]) AS _u(address)
```

Replace the placeholders with your addresses, for example:

```sql theme={null}
FROM UNNEST(ARRAY[
  0xa1b2c3d4e5f6789012345678901234567890abcd,
  0x1234567890abcdef1234567890abcdef12345678,
  0xfedcba0987654321fedcba0987654321fedcba09
]) AS _u(address)
```

### 2. Filter by specific functions

To track specific game actions, filter by function signature:

```sql theme={null}
WHERE
  t.success = TRUE
  AND t.data LIKE '0x12345678%'  -- Replace with your function signature
```

### 3. Add time filters

To analyze a specific period, filter by date:

```sql theme={null}
WHERE
  t.success = TRUE
  AND t.block_date >= '2024-01-01'
  AND t.block_date <= '2024-12-31'
```

## Best practices

### Performance optimization

1. Always use date filters to limit the scope of the data.
2. Filter on indexed columns (block\_date, address) first.
3. Use `LIMIT` when you test large queries.

***

## Resources

* [Dune Analytics documentation](https://docs.dune.com)


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