Custom Analytics Dashboards on Dune: End-to-End Development

You launched a DeFi protocol and want the real picture: how many users call your smart contract daily, liquidity volume in pools, TVL changes. Manually pulling data from Etherscan or The Graph wastes hours of clicks, and off-the-shelf dashboards miss your tokenomics and specific events. We solve this: we build custom dashboards on Dune Analytics that deliver answers in seconds, not hours. Analytics savings reach $3,000 per month, with ROI under 3 months.

Our experience — 10+ years in blockchain development, over 50 successful dashboards for DeFi protocols, NFT collections, and infrastructure projects. We don't just write SQL: we build products that save your team's resources. The complexity of Dune isn't the tool — it's understanding blockchain data structure in a relational model. One wrong JOIN and your query runs 5 minutes instead of seconds. We know how to avoid that.

Data Structure in Dune

Dune works with two table levels. Using decoded tables cuts query time by 70%.

Additionally, data abstractions exist: dex.trades, prices.usd from Spellbook. They aggregate data across multiple protocols, eliminating the need to write JOINs on each project.

Typical Mistakes and Optimization

Why SQL Query Optimization Matters

A full scan without date filter is the number one cause of timeouts. Always filter block_time:

-- BAD: full scan of entire history SELECT date_trunc('day', block_time), count(*) FROM ethereum.transactions WHERE "to" = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48 -- GOOD: limit to 90 days SELECT date_trunc('day', block_time), count(*) FROM ethereum.transactions WHERE "to" = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48 AND block_time >= now() - interval '90 days' 

Excessive JOINs on erc20_ethereum.evt_Transfer — the table contains billions of rows. Add a time range:

-- BAD: JOIN without filter SELECT t.from, SUM(t.value) FROM erc20_ethereum.evt_Transfer t JOIN my_users u ON t."from" = u.address WHERE t.contract_address = 0xdAC17F958D2ee523a2206206994597C13D831ec7 GROUP BY 1 -- GOOD: with evt_block_time filter SELECT t.from, SUM(t.value / 1e6) as usdt_sent FROM erc20_ethereum.evt_Transfer t JOIN my_users u ON t."from" = u.address WHERE t.contract_address = 0xdAC17F958D2ee523a2206206994597C13D831ec7 AND t.evt_block_time >= now() - interval '30 days' GROUP BY 1 ORDER BY 2 DESC 

Snapshot + Delta Pattern

For dashboards with historical depth over 30 days, we use a two-tier schema:

• Snapshot: heavy query runs once per day (cached).
• Delta: lightweight query for the last 24 hours.
• Merge via UNION ALL.

This gives the user current data without 5-minute wait times. Computation time savings — up to 90%.

WITH historical AS ( SELECT * FROM snapshot_table -- recalculated daily ), delta AS ( SELECT * FROM live_table WHERE evt_block_time >= now() - interval '1 day' ) SELECT * FROM historical UNION ALL SELECT * FROM delta 

What Is Spellbook and Why Use It?

Spellbook (Dune V2) is a dbt project with ready-made models: prices.usd (token prices), dex.trades (all DEX swaps), tokens.erc20 (symbols and decimals). No need to JOIN price feeds every time — use pre-built tables. We integrate Spellbook into every dashboard to speed development by 2x. Official Spellbook documentation contains the full list of models.

Example: Protocol TVL

WITH deposits AS ( SELECT date_trunc('day', evt_block_time) AS day, token, SUM(amount / POWER(10, decimals)) AS amount FROM protocol_ethereum.Pool_evt_Deposit JOIN tokens.erc20 ON token = contract_address AND blockchain = 'ethereum' WHERE evt_block_time >= now() - interval '180 days' GROUP BY 1, 2 ), withdrawals AS ( SELECT date_trunc('day', evt_block_time) AS day, token, -SUM(amount / POWER(10, decimals)) AS amount FROM protocol_ethereum.Pool_evt_Withdraw JOIN tokens.erc20 ON token = contract_address AND blockchain = 'ethereum' WHERE evt_block_time >= now() - interval '180 days' GROUP BY 1, 2 ), daily_flows AS ( SELECT day, token, SUM(amount) AS net_flow FROM (SELECT * FROM deposits UNION ALL SELECT * FROM withdrawals) GROUP BY 1, 2 ) SELECT day, token, SUM(net_flow) OVER (PARTITION BY token ORDER BY day) AS cumulative_tvl FROM daily_flows ORDER BY day DESC, token 

Dashboard Architecture

A good dashboard is a product. Metric hierarchy: top-level KPI (TVL, Volume, Users) → drill-down (by network, token) → details (top addresses). Use parameters like {{token_address}} for interactivity. For example, an AMM dashboard lets you select a pool, time range, and immediately see volume, fees, impermanent loss.

How We Build a Dashboard: Step-by-Step Process

1. Analyze the protocol and define key metrics. Study the contract ABI, identify Deposit, Withdraw, Swap events. Map fields.
2. Write SQL queries with time optimization (reduce latency by 70%). Use decoded tables and Spellbook.
3. Configure visualizations: choose chart types (line, bar, area), parameterization (network filters, date ranges), caching.
4. Document calculation logic for your team — describe each metric, formula, contract links.
5. Publish publicly with support. After release, we monitor errors and adjust queries on forks.

Work Process

• Analytics: examine smart contract, identify key events.
• Design: create a dashboard prototype on Dune.
• Implementation: write optimized SQL queries, configure parameters.
• Testing: check time ranges, aggregation correctness.
• Deployment: publish dashboard, enable caching.

Timelines — from 3 to 10 days depending on complexity. Contact us for a custom solution tailored to your protocol. Order turnkey development: we guarantee all queries run under 30 seconds, cache updates at configured intervals, and the dashboard is published publicly. Get a consultation today.