Dune Analytics

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:

Dune account: Sign up at dune.com
Basic SQL knowledge: Understanding of the SELECT, JOIN, WHERE, and GROUP BY clauses
Contract addresses: Know your game’s smart contract addresses
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 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

To open a query, click any visualization in the dashboard.
To open the query editor, click Edit Query or the query title.
To fork the query, click Fork in the top right.
Give your fork a descriptive name, such as “MyGame - Daily Active Users”.
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.

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.

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.

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.

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.

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.

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.

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.

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:

FROM UNNEST(ARRAY[
  0xYOUR_CONTRACT_ADDRESS_1,
  0xYOUR_CONTRACT_ADDRESS_2
]) AS _u(address)

Replace the placeholders with your addresses, for example:

FROM UNNEST(ARRAY[
  0xa1b2c3d4e5f6789012345678901234567890abcd,
  0x1234567890abcdef1234567890abcdef12345678,
  0xfedcba0987654321fedcba0987654321fedcba09
]) AS _u(address)

2. Filter by specific functions

To track specific game actions, filter by function signature:

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:

WHERE
  t.success = TRUE
  AND t.block_date >= '2024-01-01'
  AND t.block_date <= '2024-12-31'

Overview

Essentials

seid CLI

Frontend Development

Smart Contracts

sei-js Library

Videos

Ecosystem Tutorials

Reference

Hardware Wallets

What is Dune Analytics?

Key features:

Prerequisites

SQL knowledge requirements:

Getting started

Step 1: Access the template dashboard

Step 2: Understanding the dashboard structure

How to fork and use query templates

Forking a query

Making queries private/public

Query templates for game analytics

1. Total unique users

2. Cohort retention analysis

3. Weekly user acquisition

4. Daily transaction volume

5. Daily active users (DAU)

6. Weekly transaction volume

7. Weekly active users (WAU)

8. Daily user acquisition

Customizing queries for your game

1. Replace contract addresses

2. Filter by specific functions

3. Add time filters

Best practices

Performance optimization

Resources

​What is Dune Analytics?

​Key features:

​Prerequisites

​SQL knowledge requirements:

​Getting started

​Step 1: Access the template dashboard

​Step 2: Understanding the dashboard structure

​How to fork and use query templates

​Forking a query

​Making queries private/public

​Query templates for game analytics

​1. Total unique users

​2. Cohort retention analysis

​3. Weekly user acquisition

​4. Daily transaction volume

​5. Daily active users (DAU)

​6. Weekly transaction volume

​7. Weekly active users (WAU)

​8. Daily user acquisition

​Customizing queries for your game

​1. Replace contract addresses

​2. Filter by specific functions

​3. Add time filters

​Best practices

​Performance optimization

​Resources

What is Dune Analytics?

Key features:

Prerequisites

SQL knowledge requirements:

Getting started

Step 1: Access the template dashboard

Step 2: Understanding the dashboard structure

How to fork and use query templates

Forking a query

Making queries private/public

Query templates for game analytics

1. Total unique users

2. Cohort retention analysis

3. Weekly user acquisition

4. Daily transaction volume

5. Daily active users (DAU)

6. Weekly transaction volume

7. Weekly active users (WAU)

8. Daily user acquisition

Customizing queries for your game

1. Replace contract addresses

2. Filter by specific functions

3. Add time filters

Best practices

Performance optimization

Resources