Blockchain project analytics setup (Dune Analytics)

Setting Up Blockchain Project Analytics (Dune Analytics)

Dune Analytics is a SQL interface on top of decoded blockchain data. Instead of deploying your own indexer and writing event parsers, you write SQL queries against ready-made tables where transactions and events are already decoded by ABI. For most analytical tasks this is orders of magnitude faster than building your own stack.

Dune supports Ethereum, Arbitrum, Optimism, Base, Polygon, BSC, Solana, and dozens of other networks within a single SQL environment.

Data Structure in Dune

Tables are organized into several levels:

Raw data — raw blockchain data without decoding:

• ethereum.blocks — blocks
• ethereum.transactions — transactions
• ethereum.logs — raw events (topics + data as bytes)
• ethereum.traces — internal calls

Decoded data — events and calls decoded by ABI:

• uniswap_v3_ethereum.Factory_evt_PoolCreated — PoolCreated event from Uniswap V3 Factory
• erc20_ethereum.evt_Transfer — all ERC-20 Transfer events
• Format: {protocol}_{network}.{contract}_{type}_{event/function}

Spells — analytical tables prepared by community:

• dex.trades — all DEX trades across all networks in unified schema
• tokens.erc20 — token metadata
• nft.trades — NFT trades

Decoding Your Contract

For your contract's events to appear in decoded tables — upload the ABI:

1. Open dune.com/contracts/new
2. Specify network and contract address
3. Paste ABI (JSON)
4. Wait for approval (usually 1–3 business days)

After decoding, a table like yourproject_ethereum.YourContract_evt_YourEvent will appear.

Writing Queries: Practical Patterns

Basic Token Analysis

-- Number of unique holders of ERC-20 token
WITH transfers AS (
  SELECT
    "to"   AS address,
    value  AS amount
  FROM erc20_ethereum.evt_Transfer
  WHERE contract_address = 0xYourTokenAddress

  UNION ALL

  SELECT
    "from" AS address,
    -value AS amount
  FROM erc20_ethereum.evt_Transfer
  WHERE contract_address = 0xYourTokenAddress
    AND "from" != 0x0000000000000000000000000000000000000000
)
SELECT
  COUNT(DISTINCT address) FILTER (WHERE balance > 0) AS holders
FROM (
  SELECT address, SUM(amount) AS balance
  FROM transfers
  GROUP BY address
)

TVL (Total Value Locked) of Protocol

-- TVL via decoded Deposit/Withdraw events
SELECT
  DATE_TRUNC('day', evt_block_time) AS day,
  SUM(SUM(CAST(amount AS DOUBLE) / 1e18)) OVER (ORDER BY DATE_TRUNC('day', evt_block_time)) AS cumulative_tvl_eth
FROM yourproject_ethereum.Vault_evt_Deposit
GROUP BY 1
ORDER BY 1

Trading Volume via DEX

-- Using Spell table dex.trades
SELECT
  DATE_TRUNC('week', block_time) AS week,
  blockchain,
  SUM(amount_usd) AS volume_usd,
  COUNT(*) AS trades_count
FROM dex.trades
WHERE token_bought_address = 0xYourTokenAddress
   OR token_sold_address   = 0xYourTokenAddress
  AND block_time >= NOW() - INTERVAL '90' day
GROUP BY 1, 2
ORDER BY 1 DESC

User Cohort Analysis

-- First user transaction = cohort entry date
WITH first_tx AS (
  SELECT
    "from" AS user,
    MIN(DATE_TRUNC('week', evt_block_time)) AS cohort_week
  FROM yourproject_ethereum.YourContract_evt_SomeAction
  GROUP BY 1
),
weekly_activity AS (
  SELECT
    "from" AS user,
    DATE_TRUNC('week', evt_block_time) AS active_week
  FROM yourproject_ethereum.YourContract_evt_SomeAction
  GROUP BY 1, 2
)
SELECT
  f.cohort_week,
  DATE_DIFF('week', f.cohort_week, w.active_week) AS weeks_since_join,
  COUNT(DISTINCT w.user) AS retained_users
FROM first_tx f
JOIN weekly_activity w ON f.user = w.user
GROUP BY 1, 2
ORDER BY 1, 2

Dune API: Embedding Data in Product

To display analytics in your app — use Dune API:

const DUNE_API_KEY = process.env.DUNE_API_KEY!

// Run query and get result
async function getDuneQueryResult(queryId: number): Promise<DuneResult> {
  // Run execution
  const execRes = await fetch(`https://api.dune.com/api/v1/query/${queryId}/execute`, {
    method: 'POST',
    headers: { 'X-Dune-API-Key': DUNE_API_KEY },
    body: JSON.stringify({ performance: 'medium' }), // medium / large
  })
  const { execution_id } = await execRes.json()

  // Wait for completion
  let status = 'QUERY_STATE_PENDING'
  while (['QUERY_STATE_PENDING', 'QUERY_STATE_EXECUTING'].includes(status)) {
    await new Promise(r => setTimeout(r, 2000))
    const statusRes = await fetch(
      `https://api.dune.com/api/v1/execution/${execution_id}/status`,
      { headers: { 'X-Dune-API-Key': DUNE_API_KEY } }
    )
    const statusData = await statusRes.json()
    status = statusData.state
  }

  // Get results
  const resultRes = await fetch(
    `https://api.dune.com/api/v1/execution/${execution_id}/results`,
    { headers: { 'X-Dune-API-Key': DUNE_API_KEY } }
  )
  return resultRes.json()
}

For a dashboard with hourly updates — cache results, don't run query on every client HTTP request.

Parameterized Queries

Dune supports parameters in queries — convenient for universal dashboards:

-- Use {{parameter}} in query
SELECT *
FROM dex.trades
WHERE token_bought_address = {{token_address}}
  AND block_time >= NOW() - INTERVAL '{{days}}' day

// When calling via API, pass parameters
const body = {
  query_parameters: {
    token_address: '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2', // WETH
    days: 30,
  },
  performance: 'medium',
}

Alternatives to Dune for Specific Tasks

Dune has latency from minutes to an hour on free plan. For real-time metrics in production — use The Graph or own indexer.

Typical Dashboard for DeFi Protocol

Set of metrics worth setting up from day one:

• Daily/Weekly Active Users (unique addresses)
• TVL in USD with breakdown by tokens
• Transaction volume (count + USD value)
• Revenue (protocol fees)
• Retention cohorts
• Top users / whales
• Gas spent by protocol users

Creating a basic dashboard with 5–7 metrics, decoding contract, setting up Dune API for embedding in product: 1–2 days.