Analisis Data On-Chain dengan Dune SQL untuk Web3 Analytics

Growth marketer dan analis produk sering banget terjebak error yang sama: ngejudge audiens dApp cuma modal metrik TVL atau balance dompet. Begitu lihat address punya saldo 50 ETH, langsung dilabeli user premium. Ini jelas fallacy yang fatal banget di dunia web3.

Di ranah analitik Web3, saldo berjalan itu cuma vanity metric. Signal aslinya ada di funding source. User behavior analytics yang beneran akurat itu wajib mulai dari deanonimisasi hubungan antar-wallet. Kalau tiba-tiba ada spike 1.000 address baru di protokol lo, terus pas dicek ternyata semuanya dapet gas fee pertama dari satu sub-account CEX gede atau lewat custom contract mixer dalam rentang waktu yang mepet, fix itu bukan organic growth. Itu namanya industri sybil farm yang lagi nguras likuiditas atau nyepam tx buat nyari airdrop.

Buat nemuin pattern kayak gini, lo harus bedah "genesis" si wallet: mulai dari timestamp transaksi pertama, donor address-nya, sampai rentetan transfer setelah itu.

Praktik Query SQL di Dune (Dune SQL)

Script ini bakal nge-isolate semua address yang berinteraksi sama smart contract lo, terus ditarik data transaksi paling pertamanya secara on-chain. Jadi lo bisa langsung nge-bucket user berdasarkan tipe funding source mereka.

-- Cari sumber gas pertama untuk address yang interaksi dengan contract
WITH target_users AS (
    SELECT DISTINCT "from" AS user_address
    FROM ethereum.transactions
    WHERE "to" = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Contoh: 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
)
-- Filter dan sisakan hanya transaksi pertama historis untuk setiap wallet
SELECT 
    g.user_address,
    g.funder AS parent_address,
    g.block_time AS creation_timestamp,
    -- Flag funder mencurigakan. Address di bawah adalah contoh contract mixer/bridge gede
    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 berat karena JOIN ke seluruh tabel tx. Untuk dataset gede, pake partition berdasarkan block_date.

Segmentasi Audiensi: Latency Eksekusi dan Logika Gas

Analisis perilaku bisa ngebantu lo mecah user ke beberapa cohort yang jelas berdasarkan disiplin on-chain mereka. Gak usah pake survei, user bisa bohong tapi ledger gak bakal bisa.

Salah satu parameter segmentasi yang powerful tapi jarang dipake itu Approve-to-Swap Latency. Ini adalah selisih waktu antara token approval sampai transaksi swap dieksekusi.

User retail biasa pasti pake UI standar kayak MetaMask atau Rabby. Jeda waktu pas manggil fungsi approve() sampai ngeklik swap() biasanya makan waktu 15 detik sampai beberapa menit. Mereka butuh mikir dulu, ngecek slippage, atau keganggu hal lain. Kebalikannya, bot dan MEV searcher bakal ngesign transaksi ini secara atomik. Transaksinya bakal masuk di block yang sama atau dibundel lewat Flashbots. Kalau latency antara approve dan transfer nendang ke angka nol, langsung tandai address itu sebagai bot.

Komparasi Pattern On-Chain

Analisis Cohort Retention via Dune SQL

Biar tahu produk lo beneran dapet Product-Market Fit atau lo cuma bakar budget marketing demi user sekali pakai, lo wajib jalanin cohort analysis. Lo harus nge-track behavior user yang baru masuk di bulan tertentu buat dipantau pada periode-periode berikutnya.

Di Dune SQL, urusan ini diselesaikan pake window function dan nyari selisih tanggal lewat DATE_DIFF.

-- Hitung retention rate bulanan user untuk smart contract spesifik
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' -- Contoh: contract USDT
),
cohort_volumes AS (
    -- Ambil ukuran baseline untuk masing-masing cohort
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_address) AS cohort_size
    FROM user_activity
    GROUP BY 1
),
retention_summary AS (
    -- Hitung unique returning user tiap bulan
    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
)
-- Ambil matriks akhir
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: kalau retention_percentage di bulan kedua (period_index = 1) jeblok di bawah 7%, artinya produk lo bermasalah.

Tapi tunggu dulu, ada catatannya. Kalau contract-nya tipe sistem yang rame banget kayak USDT, ukuran cohort-nya bakal super gede. Query-nya rawan kena timeout kalau lo pake free tier Dune. Tapi buat custom dApp, code ini kelar dalam hitungan detik.

Hidden Events: Cara Bongkar Behavioral Trigger Lewat Log Sistem

Kebanyakan analis cuma puas nge-parse tabel transactions atau traces. Padahal itu ngebuang banyak data penting. Analisis produk level pro itu wajib nge-dive sampai ke log sistem (ethereum.logs).

Kenapa harus repot? Protokol modern sekarang makin condong pake meta-transaction dan Account Abstraction (ERC-4337). Di arsitektur kayak gini, field tx.from di tabel transaksi standar bakal kebaca sebagai address bundler atau relayer, bukan real user-nya. Kalau lo nekat menganalisis dari transaksi biasa, yang kelihatan cuma aktivitas dari 3-4 contract gede doang, padahal aslinya ada ribuan unique user di balik itu.

Solusinya adalah nge-pull data langsung dari event kontrak. Contohnya nge-parse Topic 0 pake hash event UserOperationEvent. Dari sini lo bisa tahu user-nya pake smart contract wallet (Safe, Argent), eksekusi lewat hardware wallet, atau malah interaksi via embedded wallet di dalam bot Telegram. Kalau lo paham arsitektur wallet audiens lo, urusan optimasi UX bakal jauh lebih gampang. User retail yang pake smart account gak perlu lo ceramahi soal cara hemat gas fee; lo tinggal pasang contract paymaster biar mereka bisa bayar gas fee langsung pake stablecoin.

Pelacakan Likuiditas End-to-End: Mendeteksi Pola LP Drain

Peluncuran kampanye liquidity mining biasanya langsung memicu lonjakan penyedia likuiditas (LP). Masalahnya, sebagian besar TVL ini adalah modal spekulatif (mercenary capital). Para yield farmer hanya memarkir dana di pool selama APY tinggi masih aktif. Begitu reward turun, mereka akan langsung menarik likuiditas, sehingga menguras kedalaman pasar (market depth).

Membuat sistem peringatan dini tidak cukup hanya dengan memantau aktivitas penarikan likuiditas saja. Aliran dana tersebut setelah keluar dari pool juga wajib dilacak. Kelompok smart money dan investor ritel memiliki pola perilaku yang sangat berbeda saat melakukan take profit.

• Alamat Ritel: Menarik LP token -> Melakukan swap ke stablecoin atau aset gas natif (ETH) -> Mengirim dana ke bursa tersentralisasi (CEX) melalui alamat deposit standar.
• Arbitrageur dan Bot MEV: Menarik likuiditas -> Langsung memindahkan dana ke pool serupa di protokol lain yang menawarkan APY lebih tinggi, bahkan hanya selisih 0.5% -> Mengeksekusi seluruh transaksi dalam satu atau dua blok saja.
• Pool Institusional: Mentransfer dana ke kontrak multi-sig atau protokol pinjaman non-kustodial (Aave, Maker). Mereka biasanya melakukan looping atau memasukkan aset sebagai agunan (collateral) daripada langsung menjualnya.

Membuat Query SQL Kompleks untuk Melacak Migrasi LP

Mari buat query untuk mengisolasi alamat dompet yang melakukan penarikan likuiditas (event Burn) dan memeriksa ke mana token tersebut berpindah dalam kurun waktu 24 jam berikutnya.

-- Analisis perilaku LP setelah penarikan likuiditas
WITH lp_burns AS (
    SELECT 
        evt_tx_hash,
        evt_block_time AS burn_time,
        provider AS user_address,
        amount0 / 1e6 AS amount_usdc, -- Asumsi token0 adalah USDC
        amount1 / 1e18 AS amount_weth  -- Asumsi token1 is WETH
    FROM uniswap_v3_ethereum.Pair_evt_Burn
    WHERE contract_address = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Pool target
      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
)
-- Mengambil tindakan pertama user setelah menarik likuiditas
SELECT 
    s.user_address,
    s.burn_time,
    s.tx_time,
    s.destination_address,
    -- Identifikasi alamat tujuan
    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;
-- Hati-hati edge case: kalau user eksekusi banyak tx di detik yang sama (bundle), urutan tx_order bisa berantakan.

Analisis Toxic Flow dan Dampaknya pada Metrik Produk

Bagi DEX atau protokol DeFi, memahami kualitas volume perdagangan adalah hal yang krusial. Volume transaksi terbagi menjadi volume organik (user melakukan swap untuk kebutuhan riil) dan volume beracun atau toxic flow (arbitrase, LVR - Loss Versus Rebalancing).

Toxic volume ini dihasilkan oleh bot. Mereka mengeksploitasi nilai dari LP pasif tepat saat terjadi pergerakan harga yang tajam di bursa luar (seperti Binance). Jika seorang analis melihat lonjakan volume di dApp lalu langsung menyimpulkan bahwa produk mengalami pertumbuhan organik, kemungkinan besar analisis itu salah. Kondisi tersebut biasanya terjadi karena oracle protokol sedang lag, sehingga pool dikuras habis oleh para arbitrageur.

Cara Mendeteksi Alamat Toxic lewat SQL

Metode terbaik adalah dengan memantau metrik PnL transaksi dalam rentang 5 blok ke depan. Jika suatu alamat melakukan swap, lalu dalam 5 blok berikutnya harga aset bergerak merugikan pool tetapi menguntungkan alamat tersebut, ini adalah murni aktivitas arbitrase.

Alamat dompet dengan proporsi transaksi toxic di atas 80% dari total volume harus diberi tag Arbitrage_Bot di database analitik. Saat menghitung retensi pengguna organik (Retention), volume dari dompet-dompet ini wajib dikeluarkan dari statistik utama. Jika tidak, metrik keterikatan pengguna (engagement metrics) akan menjadi bias dan tidak akurat.

Arsitektur Dashboard Analitik: Dari Raw Log ke Insight Produk

Analisis on-chain skala produksi tidak bisa mengandalkan query ad-hoc yang dijalankan sekali-sekali. Pada praktiknya, data mentah harus diagregasikan ke dalam tabel kustom (disebut Spells dalam terminologi Dune) yang diperbarui secara otomatis menggunakan jadwal cron.

Berikut adalah struktur data pipeline yang sudah teruji untuk keperluan analitik produk Web3:

[Data Mentah Jaringan (Blocks, Transactions, Logs)]
                      │
                      ▼
[Decoded Contracts (Events & Calls)] ──► Hapus noise sistem dan transaksi gagal (reverted txs)
                      │
                      ▼
[Business Logic Layer (Tabel Spells: Swaps, Deposits)] ──► Filter berdasarkan Approve-to-Swap Latency
                      │
                      ▼
[Kohort Perilaku (Dashboard Akhir)] ──► Metrik Produk (LTV, CAC, Retention)

Kesimpulan Utama untuk Analis

Berhentilah mengukur produk Web3 menggunakan kerangka kerja metrik Web2. Jumlah dompet aktif unik (UAW) dan total volume transaksi sangat mudah dimanipulasi melalui serangan Sybil dengan modal biaya gas yang minim. Analitik perilaku pengguna yang sesungguhnya berada pada titik temu antara pemeriksaan silsilah finansial dompet (sumber dana gas), jeda waktu antar pemanggilan fungsi level rendah, dan struktur pelarian modal setelah berinteraksi dengan kontrak pintar Anda. Data di blockchain tidak pernah bohong, Anda hanya perlu tahu cara menanyakannya dengan query yang tepat.