Using Dashboards and Writing Your Own Queries

11.1 Two dashboards, two answers

Mint found two public dashboards showing a protocol's daily users. One said 45,000. The other said 12,000.

"Which one is right?" Mint asked.

"Maybe both," said Hash. "Maybe neither. One might count addresses, the other entities. One might exclude bots. One might have a bug. The only way to know is to understand how the numbers are built. And the best way to understand that is to build one yourself."

11.2 The on-chain analyst's toolkit

Tool	Best for	Skill needed
Block explorers	Checking individual transactions and addresses (Module 3, Chapter 6)	Low
Analytics platforms with pre-built metrics	Exchange flows, holder metrics, cost basis metrics (Chapters 3 to 7)	Low
Protocol and DeFi dashboards	TVL, fees, revenue across protocols	Low
SQL-based query platforms	Custom questions using decoded blockchain data	Moderate: basic SQL
Running a node and custom code	Full independence and raw data	High

Most people start with pre-built dashboards and move to custom queries when they want to answer questions no dashboard covers, or to check whether a dashboard is right.

11.3 From raw data to tables

Raw blockchain data is hard to read directly. Smart contract interactions are encoded, amounts are stored as large integers, and addresses are long strings.

SQL-based analytics platforms solve much of this by decoding blockchain data into tables:

Table type	What it contains
Blocks	Block numbers, timestamps, gas used
Transactions	Sender, recipient, value, fees, success or failure
Logs or events	Events emitted by contracts, like transfers and swaps (Module 3, Chapter 6)
Token transfers	Decoded token movements, with token addresses and amounts
Decoded protocol tables	Specific protocol events, such as deposits, borrows, or trades
Prices	Token prices over time, for converting amounts to dollars
Labels	Known addresses, like exchanges and protocols

Chapter 11 · How a dashboard number is built

Four steps left to right. Raw chain data: encoded calls, huge integers, and long addresses. Decoded tables, highlighted: blocks, transactions, logs, token transfers, prices, and labels. A SQL query selecting from the token transfers table filtered by token address. A chart or dashboard, for example USDC transferred per day. A note says every step can hide a mistake, from wrong decimals to fake tokens, so spot-check the final numbers on a block explorer.

SQL (Structured Query Language) is a standard language for asking questions of tables. You don't need to be a programmer to learn the basics.

11.4 A first query

Mint wanted to know: how much USDC was transferred on Ethereum each day over the past 30 days, and by how many unique senders?

Here's a simplified query. Table and column names vary between platforms, so treat it as an illustration of the structure.

SELECT
  DATE_TRUNC('day', block_time) AS day,
  SUM(amount) AS usdc_transferred,
  COUNT(DISTINCT sender) AS unique_senders
FROM token_transfers
WHERE blockchain = 'ethereum'
  AND token_address = '0x...official USDC contract address...'
  AND block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1;

Line by line:

Part	What it does
SELECT ...	Chooses what to calculate: the day, total amount, and unique senders
DATE_TRUNC('day', block_time)	Groups timestamps by day
SUM(amount)	Adds up the amounts transferred
COUNT(DISTINCT sender)	Counts unique sending addresses
FROM token_transfers	Uses the decoded token transfers table
WHERE ...	Filters to Ethereum, the USDC contract, and the last 30 days
GROUP BY 1	Produces one row per day
ORDER BY 1	Sorts by day

Notice that the query filters by contract address, not by the symbol "USDC." As Module 1, Chapter 5 warned, anyone can create a token called USDC. Filtering by symbol could include fake tokens and distort the results.

11.5 The decimals trap

Tokens store amounts as whole numbers with a set number of decimals. Many tokens use 18 decimals. USDC uses 6. A raw amount must be divided by 10 to the power of the token's decimals to get the real amount.

Raw amount	Decimals used	Displayed amount
1,500,000,000	6 (correct for USDC)	1,500 USDC
1,500,000,000	18 (wrong)	0.0000000015 USDC

Most decoded tables handle this automatically, but custom tables and new tokens sometimes don't. A dashboard showing absurdly large or tiny numbers often has a decimals error.

11.6 Verify your results

Every query and dashboard can be wrong. Before trusting a number, check it.

1. Spot-check on a block explorer. Pick a few transactions included in your results and confirm they match the explorer.
2. Compare with another source. If your daily USDC transfer volume is ten times higher than every other dashboard, find out why.
3. Check for double counting. Are you counting internal transfers, bridged versions, or transactions that appear in multiple tables?
4. Check the filters. Are you including failed transactions? Fake tokens? The right time zone?
5. Sanity-check the size. Does the number make sense compared with what you know about the protocol?

"So I should be as skeptical of my own dashboard as I am of anyone else's," said Mint.

"More skeptical," said Hash. "You'll want it to be right."

11.7 Evaluating other people's dashboards

Public dashboards are a great resource, but quality varies widely.

Question	Why it matters
Who made it, and are they independent of the project?	Project-made dashboards may present data favourably
Is the query visible?	Open queries let you check the method
When was it last updated?	Abandoned dashboards may use outdated contract addresses or labels
How are key terms defined?	"Users," "revenue," and "volume" mean different things to different people (Module 5, Chapter 7)
Does it handle sybils and internal transfers?	Affects activity metrics (Chapter 10)
Do its numbers match other sources?	Big differences need explanation

11.8 Questions worth querying

Once comfortable with basic SQL, you can answer questions that pre-built dashboards rarely cover:

1. How many of a protocol's users from three months ago are still active? (Retention)
2. How much of a protocol's volume comes from its top 10 addresses? (Concentration)
3. How much did a token's insider wallets send to exchanges after an unlock? (Module 5, Chapter 5)
4. How much of a protocol's activity is subsidised by token incentives? (Module 5, Chapter 7)
5. What share of a new token's buyers bought within its first hour? (Early concentration)

11.9 Try it yourself

Sign up for a free SQL-based blockchain analytics platform. Find a public query someone else wrote, read it line by line, and make sure you understand each part. Then adapt it: change the token, the time period, or the chain. Finally, write the query from section 11.4 for a stablecoin on a blockchain of your choice, and spot-check three of its transactions on a block explorer.

Key takeaways from this chapter

1. Tools range from block explorers and pre-built dashboards to SQL query platforms and running your own node.
2. Analytics platforms decode raw blockchain data into tables for blocks, transactions, events, transfers, prices, and labels.
3. Basic SQL lets you answer your own questions and check others' numbers.
4. Always filter tokens by contract address, not symbol, to avoid fake tokens.
5. Token decimals vary; errors produce absurdly large or small numbers.
6. Verify results by spot-checking, comparing sources, checking for double counting, and sanity-checking size.
7. Evaluate public dashboards for independence, visible methods, freshness, definitions, and consistency.