Dune Analytics | Sei Docs

What is Dune Analytics?

Dune Analytics is a powerful blockchain analytics platform that allows you to query, visualize, and share blockchain data using SQL. For game developers, it’s an invaluable tool for:

Player Analytics: Track user acquisition, retention, and engagement
Transaction Analysis: Monitor game economy and player behavior
Performance Metrics: Measure daily/weekly active users, transaction volumes
Cohort Analysis: Understand 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 SELECT, JOIN, WHERE, GROUP BY clauses
Contract Addresses: Know your game’s smart contract addresses
Understanding of Your Game Logic: Know what transactions represent in your game context

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

Visit the Sei Games Query Templates: Game Query Templates

Step 2: Understanding the Dashboard Structure

The template dashboard contains several key metrics:

Total unique users
Cohort retention analysis
User acquisition trends
Transaction volume analysis
Daily/Weekly active users

Below are the SQL queries powering these metrics.

How to Fork and Use Query Templates

Forking a Query

Navigate to the Query: Click on any visualization in the dashboard
Access Query Editor: Click “Edit Query” or the query title
Fork the Query: Click the “Fork” button in the top right
Rename Your Fork: Give it a descriptive name like “MyGame - Daily Active Users”
Customize: Replace placeholder values with your actual contract addresses

Making Queries Private/Public

Private Queries: Only visible to you
Public Queries: Visible to all Dune users
Unlisted: Not searchable but accessible via 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 actual 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 first interaction (0 = first week, 1 = second week, etc.)
retention_percentage: Percentage of cohort still active

3. Weekly User Acquisition

Purpose: Track how many new users you’re acquiring 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 new user acquisition on a daily basis.


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

Find this section in each query:


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

Replace with your actual addresses:


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

2. Filter by Specific Functions

To track specific game actions, add function signature filtering:


WHERE
  t.success = TRUE
  AND t.data LIKE '0x12345678%'  -- Replace with your function signature

3. Add Time Filters

To analyze specific periods:


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