This article was contributed by Daniil Okhlopkov. The views expressed are those of the contributor and do not necessarily represent the opinions or endorsements of TON. This content is for informational purposes only and should not be considered financial or investment advice.
This is the second part of our guide to TON on-chain data analysis on Dune. This installment demonstrates how to analyze balances and value flows between real blockchain users.
Who Will Benefit From This Guide?
This post is designed for:
- On-chain analysts
- Experienced Dune wizards
- Founders and researchers
- Dashboard builders
- Anyone seeking deep understanding of TON’s ecosystem dynamics
In This Post, You'll Learn:
- How to analyze authentic users (excluding contracts and custodial wallets)
- How to calculate TVL using balance tables
- How to calculate USD volumes flowing through addresses
- How to track Telegram Stars volumes on TON Blockchain
- How to analyze both on-chain and estimated off-chain NFT trading volumes
1. Identifying Real Users
When analyzing large address datasets, the first critical step is filtering out non-user accounts. The following systematic approach ensures data quality:
Filtering Methodology
1. Filter by Wallet Interfaces: Real users typically interact through contracts implementing wallet_* interfaces or multisig contracts. These serve as reliable starting points for user identification.
2. Remove Masterchain Wallets: Masterchain addresses (prefixed with -1:) primarily serve staking, network infrastructure, or legacy wallet functions with elevated gas costs. In some cases, these should be excluded from user analysis.
3. Remove Custodial Wallets: We have identified over 8 million custodial addresses belonging to exchanges, trading bots, or payment providers. These represent institutional holdings rather than individual users and must be excluded from user-level analysis.
4. Remove Sybil Addresses: Programmatically created wallets designed for airdrop farming or app-based reward exploitation. While complete detection remains challenging, we have labeled many known instances. Exclude these when analyzing organic user behavior.
5. Remove Organizational Wallets: Addresses belonging to entities like Tradoor or TON Foundation represent organizational rather than individual user activity.
Implementation
The following SQL implements all filtering rules to identify real users:
Source: TON Wallets of Real Users
2. Calculating TVL and Balances
Dune provides two primary tables for token balance analysis:
ton.balances_history- Records every change in user Jetton balanceston.latest_balances- Contains current TON and Jetton balances for each user
For current balance analysis, query ton.latest_balances, filter for relevant addresses, and multiply amounts by latest token prices from ton.prices_daily to obtain USD-denominated balances.
Important: In both balance tables, TON native token appears as
asset = 'TON'(not the null address0:...000) due to legacy formatting constraints that cannot be modified without breaking existing dashboards.
Understanding Balance History Mechanics
The ton.balances_history table logs balance changes only. If a user receives tokens on January 1 and sends them on January 3, no record exists for January 2 since the balance remained unchanged. This creates gaps when plotting daily or monthly balance trends, requiring SQL interpolation techniques.
TVL Calculation Example: Lending Protocols
The following approach constructs daily token balances from change-only data:
Step 1: Generate Date Intervals
This CTE creates the date range for TVL tracking:
Step 2: Aggregate Balance Changes
For daily or monthly analysis, aggregate balance changes by interval to reduce data volume and improve query performance:
Step 3: Find Most Recent Balance Updates
For each date, identify the most recent balance change before that date:
Step 4: Reconstruct Complete Balance Timeline
Join the most recent balance data to each date, providing accurate balance snapshots:
This approach avoids generating full user × date matrix or using computationally expensive window functions with partition/sort operations.
Step 5: Calculate USD-Denominated TVL
Multiply reconstructed Jetton balances by historical prices to compute TVL:
Source: TVL of Lending Protocols
3. Calculate Volume, Inflow, Outflow, and Netflow
TON Blockchain supports three primary methods for transferring USD-equivalent value between addresses: native TON transfers, Jetton token transfers, and NFT transfers. While NFT valuations require complex estimation (e.g., collection floor price comparisons), token transfers provide straightforward analysis.
Data Sources
Building comprehensive token transfer analysis requires combining two data sources:
ton.messages- Contains all message data including TON transferston.jetton_events- Decoded table tracking all Jetton transfers between addresses
Important: Every Jetton transfer includes a small TON amount for gas fees, creating corresponding entries in
ton.messages. To avoid double-counting, filter out messages withopcode = 260734629when analyzing pure TON transfers.
Extracting TON Transfers
The following query extracts native TON transfers while excluding gas payments for Jetton operations:
Extracting Jetton Transfers
This query captures all successful Jetton token transfers:
Calculating Bidirectional Flows
Each transfer has a sender (source) and receiver (destination). To calculate inflows, outflows, volume, and netflows per address, expand the transfer table by duplicating each row with reversed address pairs and negative amounts:
Token Price Filtering
Critical Note: The token whitelist prevents price manipulation artifacts. Anyone can create DEX pools, add liquidity, and generate trading volume for worthless tokens while holding trillions in supply. This can result in artificial billion-dollar balances exceeding total blockchain TVL. The whitelist approach or TVL-based filtering (example implementation) mitigates these issues.
Aggregating Volume Statistics
With the bidirectional flow structure, you can efficiently calculate comprehensive volume metrics:
This methodology provides flexible, scalable analysis of asset movements across TON Blockchain, supporting address-level filtering, user group analysis, and temporal volume tracking.
Source: Custodial Wallets Detection
BONUS: Tracking Telegram Stars Volumes
Telegram Stars represents Telegram's off-chain currency used within mini apps and bots. Users can purchase Stars through Apple Pay (with 30% fees) or Fragment at discounted rates.
While most transactions occur off-chain, all Fragment-related operations are recorded on-chain, enabling comprehensive tracking:
- Star Purchases: $TON → Sent to Fragment → Converted into Stars
- Star Withdrawals: Stars → Sent to Fragment → Converted into $TON → Sent to your wallet
Although only approximately 10% of Gifts migrate to TON Blockchain, this provides valuable proxy signals for broader adoption analysis.
Tracking Star Purchases
Telegram Stars purchases appear as TON transfers to Fragment addresses with specific comment patterns:
Tracking Star Withdrawals
Star withdrawals manifest as TON transfers from Fragment addresses to user wallets:
This data enables estimation of Telegram Stars volume flowing through individual addresses and quantification of users engaging in Star transactions.
Source: https://dune.com/queries/4896663
4. Calculating On-Chain & Off-Chain NFT Trade Volumes
This section demonstrates calculation of on-chain NFT sales volumes and approximation of off-chain marketplace activity through on-chain footprint analysis.
On-Chain NFT Trading
On-chain NFT trades, including Telegram Gifts, Anonymous Numbers, Telegram Usernames, and other collections, are decoded and stored in ton.nft_items. Currently, all trades settle in TON tokens. For USD conversion, multiply by corresponding daily TON prices.
For demonstration purposes, this example uses an alternative TON price feed based on DEX trades across EVM chains:
On-Chain vs Off-Chain NFT Trading
Telegram Gifts exemplify the complexity: they can trade fully on-chain (as NFTs listed and transferred between wallets) or off-chain through internal marketplace systems that leave no direct on-chain sale records.
Off-chain marketplaces like MRKT, Portals, and Tonnel only create on-chain traces when users deposit TON into internal marketplace balances or withdraw proceeds.
Estimating Off-Chain Marketplace Volume
Assuming buyers deposit TON for purchases and sellers subsequently withdraw proceeds, estimated trading volume can be calculated as:
Volume ≈ (Deposits + Withdrawals) / 2
Since each successful trade generates one deposit (buyer funding) and one withdrawal (seller payout), this formula provides reasonable approximation.
⚠️ Methodology Limitation: This approach represents an approximation. Users may fund internal balances through alternative means, and single deposits may facilitate multiple purchases before withdrawal. However, comparisons with disclosed marketplace data indicate that our estimates typically underreport actual volumes by approximately 30% — providing a conservative off-chain activity analysis.
Implementation: Off-Chain Marketplace Analysis
First, load labeled addresses for off-chain NFT marketplaces from our public GitHub repository, already integrated into Dune:
Step 1: Calculate TON Withdrawals
Collect all TON withdrawals from off-chain marketplace wallets, excluding internal transfers between marketplace-controlled addresses:
Step 2: Calculate TON Deposits
Calculate deposits to off-chain marketplace wallets:
The same methodology applies to Jetton token deposits and withdrawals for comprehensive marketplace analysis.
Final Step: Aggregate Estimated Off-Chain Volume
Combine all transfer types and calculate estimated daily off-chain trading volumes:
This methodology provides baseline estimates for off-chain NFT marketplace volumes and enables comparison with on-chain activity. While not perfectly precise, it delivers sufficient accuracy for analytical and product decision-making, trend tracking, and ecosystem monitoring.
Source: TON NFT Market Stats
This represents a focused slice of how TON Foundation and the broader TON Data Hub community analyze TON Blockchain. As TON’s ecosystem continues rapid expansion, we persistently develop our analytical tools and capabilities.
If you're building on TON and require dashboards or on-chain analytics, TON Foundation provides comprehensive support.
Partner with us to host a Data Contest through TON Data Hub — an effective approach to engage top analysts, generate powerful Dune dashboards, and obtain actionable insights for your project.
Join TON’s Data Hub community for contest updates and advanced Dune techniques.
Stay tuned for additional practical guides covering advanced analytics, fraud detection, and ecosystem benchmarking!
