Analyse de données On-Chain avec Dune SQL : Guide Web3

Les growth marketers et analystes produit tombent un peu trop souvent dans le même panneau : évaluer l'audience d'une dApp au wallet size (via la métrique du TVL ou du balance brut). Dès qu'un outil remonte une adresse avec 50 ETH au compteur, c'est direct tagué "gros profil". C'est un biais d'analyse monumental en Web3.

En analyse Web3, le solde instantané n'est qu'une vanity metric. Le vrai signal, c'est la source de financement initiale (le Funding Source). Une bonne analyse comportementale (User Behavior Analytics) commence toujours par clean les liens entre les wallets. Si un protocole enregistre un pic soudain de 1 000 nouveaux comptes, et que le tracking montre qu'ils ont tous reçu leurs premiers frais de gaz depuis le même sub-account d'un gros CEX, ou via un smart contract de mixer custom sur un intervalle de temps hyper court, hors de question de parler de croissance organique. C'est une ferme de sybils industrielle configurée pour vider la liquidité ou farmer du volume en vue d'un futur airdrop.

Pour flag ces patterns, il faut disséquer le "genèse" du wallet : le timestamp de la toute première transaction, l'adresse du donneur et la suite de la chaîne de transferts.

Requête SQL pratique pour Dune (Dune SQL)

Ce script isole les adresses qui ont trigger votre smart contract pour extraire la toute première transaction on-chain de chacune d'elles. Pratique pour segmenter instantanément la base selon le profil du "donneur".

-- Extraction de la source de gaz initiale des adresses qui intéragissent avec le contract
WITH target_users AS (
    SELECT DISTINCT "from" AS user_address
    FROM ethereum.transactions
    WHERE "to" = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Exemple : pool Uniswap v3 USDC/WETH
    LIMIT 500
),
genesis_transactions AS (
    SELECT 
        t."to" AS user_address,
        t."from" AS funder,
        t.block_time,
        ROW_NUMBER() OVER (PARTITION BY t."to" ORDER BY t.block_time ASC) as rank_index
    FROM ethereum.transactions t
    JOIN target_users u ON t."to" = u.user_address
)
-- On drop tout sauf la première transaction historique de chaque wallet
SELECT 
    g.user_address,
    g.funder AS parent_address,
    g.block_time AS creation_timestamp,
    -- Identification des feeders louches. Les adresses ci-dessous ciblent des gros mixers/bridges bekannten
    CASE 
        WHEN g.funder = c'0x12d66748a9176f0af33dfc592215a425441047f1' THEN 'Tornado.Cash (0.1 ETH)'
        WHEN g.funder = c'0x47ce0c6ed5b0ce3d3a51fdb1c52dc66a7c3c2936' THEN 'Tornado.Cash (1.0 ETH)'
        ELSE 'CEX Sub-account / Standard EOA'
    END AS funder_classification
FROM genesis_transactions g
WHERE g.rank_index = 1
ORDER BY g.block_time DESC;
-- Requête lourde (gros JOIN sur la table tx). Sur des gros datasets, filtrez par block_date.

Segmentation d'audience : Vitesse d'exécution et logique de gaz

L'analyse comportementale permet de ranger les specs utilisateurs dans des cases bien nettes selon leur discipline on-chain. Inutile de faire des sondages : les gens mentent, le ledger dit vrai.

Un des indicateurs de segmentation les plus puissants (et pourtant sous-exploités) reste l'Approve-to-Swap Latency. Il s'agit du délai temporel entre l'autorisation de dépenser le token et le swap effectif.

Un utilisateur retail classique passe par des interfaces standard (MetaMask, Rabby). Entre l'appel de la fonction approve() et le clic sur swap(), il s'écoule généralement entre 15 secondes et plusieurs minutes. C'est le temps qu'il vérifie le slippage, qu'il réfléchisse ou qu'il soit distrait. Les scripts automatisés et les bots MEV signent ces transactions de manière atomique. Tout part soit dans le même bloc, soit au sein d'un unique bundle Flashbots. Si la valeur delta tend vers zéro, l'adresse doit être flagguée direct comme un bot.

Analyse comparative des patterns on-chain

Analyse de rétention par cohortes (Cohort Retention) via Dune SQL

Pour savoir si un produit génère un vrai Product-Market Fit ou si le budget marketing sert juste à onboarder des utilisateurs jetables, l'analyse par cohorte est obligatoire. L'objectif est de monitorer sur les périodes suivantes le comportement des profils qui ont drop leur première tx au cours d'un mois précis.

Dans Dune SQL, on gère ça proprement avec des fonctions de fenêtrage et un calcul d'écarts de dates avec DATE_DIFF.

-- Calcul de la rétention mensuelle des utilisateurs sur un smart contract précis
WITH user_activity AS (
    SELECT 
        "from" AS user_address,
        DATE_TRUNC('month', block_time) AS tx_month,
        MIN(DATE_TRUNC('month', block_time)) OVER (PARTITION BY "from") AS cohort_month
    FROM ethereum.transactions
    WHERE "to" = c'0xdac17f958d2ee523a2206206994597c13d831ec7' -- Exemple : contrat USDT
),
cohort_volumes AS (
    -- On fixe la taille de départ de chaque cohorte
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_address) AS cohort_size
    FROM user_activity
    GROUP BY 1
),
retention_summary AS (
    -- Compte des utilisateurs uniques qui reviennent par mois
    SELECT 
        a.cohort_month,
        DATE_DIFF('month', a.cohort_month, a.tx_month) AS period_index,
        COUNT(DISTINCT a.user_address) AS active_users_count
    FROM user_activity a
    GROUP BY 1, 2
)
-- Sortie de la matrice finale
SELECT 
    r.cohort_month,
    c.cohort_size,
    r.period_index AS months_after_first_tx,
    r.active_users_count,
    ROUND(CAST(r.active_users_count AS DOUBLE) / c.cohort_size * 100, 2) AS retention_percentage
FROM retention_summary r
JOIN cohort_volumes c ON r.cohort_month = c.cohort_month
WHERE r.period_index >= 0
ORDER BY r.cohort_month ASC, r.period_index ASC;
-- Alerte rouge : si le retention_percentage au mois 1 (period_index = 1) drop sous les 7%, le produit retient que dalle.

Attention au bémol classique : sur un contrat d'infrastructure ultra-exécuté (comme l'USDT ici), les cohortes vont être gigantesques. La requête risque de trigger un timeout sur les plans gratuits de Dune. Pour une dApp custom, le code s'exécute en quelques secondes.

Evénements cachés : Extraire les vrais déclencheurs comportementaux depuis les logs

Trop d'analystes se cantonnent à parser les tables transactions ou traces. Grosse erreur, ça laisse de côté une quantité folle de data. Pour bosser proprement, il faut aller creuser directement dans les logs système (ethereum.logs).

Pourquoi se prendre la tête ? Les protocoles récents reposent massivement sur les méta-transactions et l'Account Abstraction (ERC-4337). Avec cette archi, le champ tx.from renvoie systématiquement l'adresse du bundler ou du relayer, pas celle du user final. En analysant uniquement les transactions standards, on se retrouve à traquer l'activité de trois ou quatre gros contrats au lieu de voir les milliers d'utilisateurs uniques.

La solution consiste à extraire la data directement depuis les Events. On va par exemple parser le Topic 0 avec le hash de l'événement UserOperationEvent. C'est l'indicateur parfait pour savoir si le profil tourne sur un smart contract wallet (Safe, Argent), s'il passe par une stack hardware ou s'il interagit avec la dApp via des embedded wallets intégrés à des bots Telegram. Cartographier l'architecture des portefeuilles de l'audience change complètement la donne pour l'UX. Avec une cible retail sur smart accounts, inutile de leur expliquer le fonctionnement du gaz : il suffit de dev des contrats paymaster pour leur faire payer les frais directement en stablecoins.

Suivi de liquidité end-to-end : détecter les patterns d'asymétrie (LP Drain)

Dès qu'un projet lance une campagne de liquidity mining, les liquidity providers (LP) déboulent en masse. Le problème, c'est que la majeure partie de cette TVL est constituée de capital mercenaire. Les yield farmers campent sur le pool tant que l'APY est boosté. Dès que les récompenses baissent, ils retirent leurs billes instantanément, séchant ainsi la profondeur du marché.

Pour monter un système d'alerte précoce, monitorer le simple retrait de liquidité ne suffit pas. Il faut impérativement tracker la destination finale de ces fonds. La smart money et le retail ont des footprints comportementaux radicalement différents au moment de take profit.

• Adresses Retail : Retrait des jetons LP -> Swap en stables ou en gaz natif (ETH) -> Envoi des fonds vers des exchanges centralisés (CEX) via des adresses de dépôt standards.
• Arbitragistes et bots MEV : Retrait de liquidité -> Rotation instantanée vers un pool similaire sur un autre protocole pour gratter 0.5% d'APY de plus -> Exécution de la boucle en un ou deux blocs maximum.
• Pools Institutionnels : Transfert des fonds vers des contrats multisig ou des protocoles de lending non-custodial (Aave, Maker). Plutôt que de vendre directement, ils bouclent ou posent les actifs en collatéral.

Écriture d'une requête SQL avancée pour tracker la migration des LP

Voici une requête pour isoler les wallets effectuant un retrait de liquidité (event Burn) et analyser la destination des tokens sur une fenêtre glissante de 24 heures.

-- Analyse du comportement des LP post-retrait
WITH lp_burns AS (
    SELECT 
        evt_tx_hash,
        evt_block_time AS burn_time,
        provider AS user_address,
        amount0 / 1e6 AS amount_usdc, -- Token0 considéré comme de l'USDC
        amount1 / 1e18 AS amount_weth  -- Token1 considéré comme du WETH
    FROM uniswap_v3_ethereum.Pair_evt_Burn
    WHERE contract_address = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Pool cible
      AND evt_block_time > CURRENT_DATE - INTERVAL '30' DAY
),
subsequent_txs AS (
    SELECT 
        b.user_address,
        b.burn_time,
        t.block_time AS tx_time,
        t."to" AS destination_address,
        ROW_NUMBER() OVER (PARTITION BY b.user_address, b.evt_tx_hash ORDER BY t.block_time ASC) as tx_order
    FROM lp_burns b
    JOIN ethereum.transactions t 
        ON t."from" = b.user_address 
        AND t.block_time > b.burn_time 
        AND t.block_time <= b.burn_time + INTERVAL '24' HOUR
)
-- Extraction de la toute première action après le retrait de liquidité
SELECT 
    s.user_address,
    s.burn_time,
    s.tx_time,
    s.destination_address,
    -- Tentative d'identification de l'adresse cible
    CASE 
        WHEN s.destination_address = c'0x3cd751e6b0078be393132286c442345e5dc49699' THEN 'Binance Deposit'
        WHEN s.destination_address = c'0x4675c7e5baafbfc4687441c22105873cd246be3a' THEN 'Hop Protocol Bridge'
        WHEN s.destination_address = c'0x7a250d5630b4cf539739df2c5dacb4c659f2488d' THEN 'Uniswap V2 Router'
        ELSE 'Unknown EOA / Other Protocol'
    END AS next_action_destination
FROM subsequent_txs s
WHERE s.tx_order = 1
ORDER BY s.burn_time DESC;
-- Attention au edge case : si l'user envoie plusieurs tx dans la même seconde (bundle), le partitionnement par tx_order peut sauter.

Analyse du "Toxic Flow" et impact sur les métriques produit

Pour un DEX ou un protocole DeFi, auditer la qualité du volume de trading est une question de survie. Le flux se divise en deux catégories : le volume organique (les users qui swappent pour un besoin réel) et le volume toxique (arbitrage, LVR - Loss Versus Rebalancing).

Ce volume toxique est généré par des bots. Ils viennent vider la valeur des LP passifs dès qu'une plateforme externe (comme Binance) subit un mouvement de prix violent. Si un analyste voit le volume exploser sur sa dApp et court célébrer l'adoption du produit, il se plante complètement. Souvent, cela signifie juste que les oracles du protocole lagguent et que les arbitrageurs sont en train de saigner les pools.

Comment spotter une adresse toxique via SQL ?

Il faut monitorer le PnL des transactions sur un horizon de 5 blocs. Si une adresse exécute un swap et que, sur les 5 blocs suivants, le prix de l'actif bouge au détriment du pool mais au profit de cette adresse, on est sur de l'arbitrage pur.

Les adresses dont le volume est constitué à plus de 80 % de ce type de flux doivent être flagguées Arbitrage_Bot dans la DB analytique. Lors de l'évaluation de la rétention réelle (Retention), ce volume doit être totalement exclu des statistiques. Sinon, les métriques d'engagement produit seront complètement faussées.

Architecture d'un dashboard analytique : des logs bruts aux insights produit

Une analyse on-chain sérieuse ne peut pas reposer sur des requêtes ad-hoc exécutées à la main. En production, les données brutes sont agrégées dans des tables customisées (les Spells dans la nomenclature Dune), mises à jour via des tâches cron.

Voici un schéma de pipeline de données éprouvé pour l'analyse de produit Web3 :

[Primitives On-Chain Brutes (Blocks, Transactions, Logs)]
                      │
                      ▼
[Contrats Décodés (Events & Calls)] ──► Nettoyage du bruit système et des tx reverted
                      │
                      ▼
[Business Logic (Tables Spells: Swaps, Deposits)] ──► Filtrage par latence Approve-to-Swap
                      │
                      ▼
[Cohortes Comportementales (Dashboard Final)] ──► Métriques Produit (LTV, CAC, Retention)

Règle d'or pour l'analyste

Il faut arrêter de calquer les frameworks Web2 sur les produits Web3. Le nombre de portefeuilles actifs uniques (UAW) et le volume brut de transactions sont des métriques triviales à manipuler via des attaques Sybil pour un coût en gaz dérisoire. La vraie analyse comportementale se niche ailleurs. Elle nécessite d'auditer la généalogie financière des portefeuilles (provenance du gaz), de mesurer les deltas de temps entre les appels système bas niveau, et de cartographier la fuite des capitaux après l'interaction avec vos contrats. La blockchain ne ment pas, il faut juste savoir poser les bonnes requêtes.