Skip to main content

Documentation Index

Fetch the complete documentation index at: https://seilabs.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

​
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:
  1. Dune Account: Sign up at dune.com
  2. Basic SQL Knowledge: Understanding of SELECT, JOIN, WHERE, GROUP BY clauses
  3. Contract Addresses: Know your game’s smart contract addresses
  4. 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

  1. Navigate to the Query: Click on any visualization in the dashboard
  2. Access Query Editor: Click “Edit Query” or the query title
  3. Fork the Query: Click the “Fork” button in the top right
  4. Rename Your Fork: Give it a descriptive name like “MyGame - Daily Active Users”
  5. 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'

​
Best Practices

​
Performance Optimization

  1. Use Date Filters: Always include date ranges to limit data scope
  2. Index Awareness: Filter on indexed columns (block_date, address) first
  3. Limit Results: Use LIMIT for testing large queries

​
Resources