On-Chain-Datenanalyse mit Dune SQL: Nutzerverhalten im Web3

Growth Marketer und Product Analysts machen oft denselben Denkfehler: Sie bewerten dApp-Nutzer nach ihrer Wallet-Größe, also nach TVL oder Balance. Sobald eine Adresse mit 50 ETH auftaucht, wird sie sofort als Core-User verbucht. Das ist ein fundamentaler Trugschluss.

In der Web3-Analytics zählt nicht die aktuelle Balance, sondern die ursprüngliche Funding Source. Eine saubere User Behavior Analytics steht und fällt mit der Deanonymisierung von Wallet-Clustern. Angenommen, ein Protokoll verzeichnet plötzlich 1.000 neue Adressen. Wenn sich beim Check herausdirigiert, dass alle ihren ersten Gas-Schnitt im selben engen Zeitfenster von ein und demselben CEX-Sub-Account oder über denselben Mixer-Kontrakt bekommen haben, ist das kein organisches Wachstum. Das ist eine professionelle Sybil-Farm. Hier wird systematisch Liquidität abgezogen oder Aktivität für einen zukünftigen Airdrop gefaked.

Um solche Pattern aufzudecken, analysiert man die Genesis einer Wallet: den Timestamp der allerersten Transaktion, die Donor-Adresse und die darauf folgende Transfer-Kette.

Praktische SQL-Query für Dune (Dune SQL)

Dieses Skript isoliert alle Adressen, die mit dem Smart Contract interagiert haben, und zieht für jede Adresse die historisch erste Transaktion auf der Chain. Damit lassen sich User sofort nach ihrem Funding-Typ clustern.

-- Filtert die erste Gas-Quelle für Adressen heraus, die mit dem Kontrakt interagiert haben
WITH target_users AS (
    SELECT DISTINCT "from" AS user_address
    FROM ethereum.transactions
    WHERE "to" = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Beispiel: Uniswap v3 USDC/WETH Pool
    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
)
-- Schneidet alle Transaktionen abgerundet ab, außer der allerersten pro Wallet
SELECT 
    g.user_address,
    g.funder AS parent_address,
    g.block_time AS creation_timestamp,
    -- Flaggt verdächtige Funder. Die Adressen unten sind Beispiele für große Mixer/Bridges
    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;
-- Query ist heavy wegen JOIN über die komplette Transactions-Tabelle. Bei großen Datenmengen nach block_date partitionieren.

Zielgruppen-Segmentierung: Execution Latency und Gas-Logik

Verhaltensanalysen teilen User anhand ihrer On-Chain-Disziplin in klare Segmente. Umfragen bringen hier nichts, denn User lügen – die Chain nicht.

Ein extrem mächtiger, aber selten genutzter Parameter ist die Approve-to-Swap Latency. Gemeint ist die Zeitspanne zwischen der Freigabe der Token und dem eigentlichen Tausch.

Ein normaler Retail-User nutzt Standard-UIs wie MetaMask oder Rabby. Zwischen dem Aufruf von approve() und dem Absenden von swap() vergehen meistens 15 Sekunden bis mehrere Minuten. Der User prüft den Slippage, überlegt kurz oder ist abgelenkt. Automatisierte Skripte und MEV-Bots signieren diese Transaktionen dagegen atomar. Sie landen entweder im selben Block oder direkt im selben Flashbots-Bundle. Geht das Delta zwischen Approve und Transfer gegen Null, läuft die Adresse als Bot.

Vergleich von On-Chain-Pattern

Kohorten-Retentionsanalyse via Dune SQL

Eine Kohortenanalyse zeigt, ob ein Produkt echten Product-Market Fit hat oder ob das Marketingbudget bloß für Einmal-User verbrannt wird. Dafür wird das Verhalten von Usern, die in einem bestimmten Monat gestartet sind, über die folgenden Perioden getrackt.

In Dune SQL nutzt man dafür Window-Funktionen und berechnet die Differenz über DATE_DIFF.

-- Berechnet die monatliche User Retention für einen spezifischen Smart Contract
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' -- Beispiel: USDT Kontrakt
),
cohort_volumes AS (
    -- Basisgröße der einzelnen Kohorten festlegen
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_address) AS cohort_size
    FROM user_activity
    GROUP BY 1
),
retention_summary AS (
    -- Zählt wiederkehrende Unique User pro Monat
    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
)
-- Baut die finale Matrix
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;
-- Red Flag: Wenn die retention_percentage im zweiten Monat (period_index = 1) unter 7% fällt, brennt die Hütte beim User-Investment.

Wichtiger Disclaimer: Wenn der Kontrakt zur Systeminfrastruktur gehört (wie USDT im Beispiel), werden die Kohorten gigantisch. Die Query läuft dann auf kostenlosen Dune-Tiers ins Timeout. Für eine normale dApp läuft der Code in wenigen Sekunden durch.

Hidden Events: Deep Behavior Trigger aus Logs extrahieren

Die meisten Analysten parsen nur die Tabellen transactions oder traces. Damit bleibt ein riesiger Datenschatz ungenutzt. Professionelle Analysen erfordern den Deep Dive in die System-Logs (ethereum.logs).

Warum? Viele moderne Protokolle setzen auf Meta-Transaktionen und Account Abstraction (ERC-4337). In diesem Setup zeigt das Feld tx.from in der Transaction-Tabelle immer die Adresse des Bundlers oder Relayers, nicht die des eigentlichen Users. Wer nur Standard-Transactions analysiert, sieht statt tausenden Unique Usern bloß die Aktivität von drei oder vier großen Kontrakten.

Die Lösung liegt im Parsen der Events. Man zieht sich zum Beispiel Topic 0 mit dem Hash des UserOperationEvent. Daraus geht hervor, ob jemand eine Smart Contract Wallet (Safe, Argent) nutzt, mit einer Hardware-Wallet agiert oder die dApp über Embedded Wallets in Telegram-Bots ansteuert. Kennt man die Wallet-Architektur der Audience, lässt sich die UX gezielt optimieren. Retail-Usern mit Smart Accounts muss man kein Gas erklären. Man setzt einfach Paymaster-Kontrakte auf, damit sie die Gebühren direkt in Stablecoins zahlen können.

End-to-End Liquidity Tracking: LP-Drain-Muster erkennen

Sobald ein Projekt eine Liquidity-Mining-Kampagne startet, füllt sich der Pool schnell mit Liquidity Providern (LPs). Das Problem dabei: Ein Großteil dieses TVL ist reines Söldnerkapital (Mercenary Capital). Diese Yield Farmer parken ihre Funds nur so lange im Pool, wie die künstlich gepushte APY heiß ist. Sinken die Rewards, ziehen sie die Liquidität sofort wieder ab und killen damit die Markttiefe (Market Depth).

Für ein funktionierendes Frühwarnsystem reicht es nicht, nur die Abzüge an sich zu überwachen. Man muss lückenlos tracken, wohin das Kapital danach fließt. Smart Money und Retail-Nutzer zeigen beim Profit-Taking ein völlig anderes On-Chain-Verhalten.

• Retail-Adressen: Lösen die LP-Token auf -> Swappen direkt in Stablecoins oder den nativen Gas-Token (ETH) -> Schieben die Funds über Standard-Einzahlungsadressen auf zentralisierte Börsen (CEX).
• Arbitrageure und MEV-Bots: Ziehen die Liquidität ab -> Rotieren die Funds sofort in einen ähnlichen Pool auf einem anderen Protokoll, selbst wenn die APY dort nur 0,5 % höher ist -> Die gesamte Aktion läuft innerhalb von ein bis zwei Blöcken ab.
• Institutionelle Pools (Whales / Smart Money): Transferieren die Funds auf Multisig-Wallets oder direkt in Lending-Protokolle wie Aave oder Maker. Statt direkt zu verkaufen, nutzen sie die Assets als Collateral für Kreditschleifen (Looping).

Fortgeschrittenes SQL-Query für das Tracking von LP-Migrationen

Mit diesem Skript isolieren wir Wallets, die Liquidität abziehen (Burn-Event), und prüfen, wo diese Token innerhalb der nächsten 24 Stunden landen.

-- LP-Verhalten nach dem Liquiditätsabzug analysieren
WITH lp_burns AS (
    SELECT 
        evt_tx_hash,
        evt_block_time AS burn_time,
        provider AS user_address,
        amount0 / 1e6 AS amount_usdc, -- Annahme: Token0 ist USDC
        amount1 / 1e18 AS amount_weth  -- Annahme: Token1 ist WETH
    FROM uniswap_v3_ethereum.Pair_evt_Burn
    WHERE contract_address = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Ziel-Pool
      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
)
-- Erste Aktion des Users nach dem Pool-Exit herausfiltern
SELECT 
    s.user_address,
    s.burn_time,
    s.tx_time,
    s.destination_address,
    -- Zieladresse identifizieren
    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;
-- Edge Case: Wenn der User mehrere Txs in derselben Sekunde schießt (Bundle), kann das Partitionieren nach tx_order ungenau werden.

Analyse von "Toxic Flow" und die Auswirkungen auf die Produktmetriken

Für jede DEX und jedes DeFi-Protokoll ist es überlebenswichtig, die Qualität des Trading-Volumens zu verstehen. Der On-Chain-Traffic teilt sich auf in organisches Volumen (Nutzer swappen für reale Usecases) und toxisches Volumen (Arbitrage, LVR - Loss Versus Rebalancing).

Toxisches Volumen wird von Bots erzeugt. Sie saugen den Wert passiver LPs in Sekundenbruchteilen ab, sobald der Kurs auf externen Märkten (wie Binance) heftig ausschlägt. Sieht ein Analyst ein rasant steigendes Volumen auf der dApp und meldet das direkt als Produkterfolg, liegt er meistens komplett falsch. Oft laggen in dem Moment einfach die Oracles des Protokolls, sodass Arbitrageure den Pool gnadenlos melken.

Wie man toxische Adressen via SQL identifiziert

Wir überwachen die PnL-Metrik einer Transaktion über ein Fenster von 5 Blöcken nach der Ausführung. Macht eine Adresse einen Swap und der Kurs ändert sich in den nächsten 5 Blöcken zum Nachteil des Pools, aber zum Vorteil der Adresse, handelt es sich um reine Arbitrage.

Wallets, bei denen dieser Toxic Flow mehr als 80 % ihres Gesamtvolumens ausmacht, gehören in der Analysedatenbank mit dem Tag Arbitrage_Bot geflaggt. Bei der Berechnung der echten Nutzerbindung (Retention) muss dieses Volumen komplett herausgefiltert werden. Andernfalls verfälschen diese Bots die Engagement-Metriken des Produkts massiv.

Architektur des Analyse-Dashboards: Von Raw Logs zu Produkt-Insights

Professionelle On-Chain-Analyse im Production-Umfeld läuft nicht über manuelle Ad-hoc-Queries. In der Praxis werden Rohdaten in Custom-Tabellen aggregiert (Spells in der Dune-Terminologie), die über Cron-Jobs automatisiert aktualisiert werden.

Hier ist eine praxiserprobte Data-Pipeline-Struktur für die Web3-Produktanalyse:

[On-Chain-Rohdaten (Blocks, Transactions, Logs)]
                      │
                      ▼
[Decoded Contracts (Events & Calls)] ──► Systemrauschen und abgebrochene Txs (reverted) bereinigen
                      │
                      ▼
[Business Logic Layer (Spell-Tabellen: Swaps, Deposits)] ──► Filtern nach Approve-to-Swap-Latenz
                      │
                      ▼
[Verhaltenskohorten (Finales Dashboard)] ──► Produktmetriken (LTV, CAC, Retention)

Das wichtigste Learning für Analysten

Hört auf, Web3-Produkte mit Web2-Metriken zu messen. Die Anzahl der Unique Active Wallets (UAW) und das reine Transaktionsvolumen (Volume) lassen sich über Sybil-Attacken für ein paar Euro Gasgebühren trivial fälschen. Echte Verhaltensanalyse funktioniert nur über die finanzielle Ahnenforschung der Wallets (Gas-Herkunft), Zeitabstände zwischen Low-Level-Systemaufrufen und das exakte Muster des Kapitalabflusses nach der Interaktion mit den Smart Contracts. Die Blockchain lügt nicht. Man muss nur wissen, wie man sie abfragt.