Dune SQL ile On-Chain Veri Analizi: Kullanıcı Davranışı

Growth marketing ve ürün analitiği ekiplerinin en sık düştüğü hata, dApp kitlesini sadece cüzdandaki varlığa (yani TVL veya ham bakiyeye) bakarak ölçmektir. 50 ETH bakiyesi olan bir adres gördükleri an "balina, değerli kullanıcı" etiketini yapıştırıyorlar. Bu tamamen temelden hatalı bir yaklaşım.

Web3 analitiğinde anlık bakiye sadece bir vitrin süsüdür. Asıl bakılması gereken şey ilk fonlama kaynağıdır (Funding Source). Doğru bir kullanıcı davranışı analizi (User Behavior Analytics) yapabilmek için önce cüzdanlar arasındaki bağları deşifre etmek gerekir. Protokolünüzde aniden 1.000 yeni adreslik bir sıçrama gördünüz diyen. Detaylı incelemede bu adreslerin hepsinin ilk gaz ücretini büyük bir borsanın tek bir alt hesabından (sub-account) ya da özel bir mikser kontratından, hem de çok dar bir zaman diliminde aldığını tespit ettiniz. İşte bu organik büyüme değildir. Bu, likiditeyi sömürmek veya ileride yapılacak bir airdrop için hacim taklidi yapmak üzere kurulmuş endüstriyel bir sybil çiftliğidir (sybil farm).

Bu tür kalıpları yakalamak için cüzdanın "genesis" yani doğuş aşaması incelenir: ilk işlemin timestamp değeri, fonu gönderen adres ve sonraki transfer zinciri tek tek analiz edilir.

Dune için Pratik SQL Sorgusu (Dune SQL)

Bu script, akıllı kontratınızla etkileşime giren adresleri ayıklar. Ardından her birinin ağdaki ilk işlemini bulup getirir. Böylece kullanıcıları aldıkları "ilk fon" tipine göre anında gruplayabilirsiniz.

-- Kontratla etkileşime giren adreslerin ilk gaz kaynağını tespit ediyoruz
WITH target_users AS (
    SELECT DISTINCT "from" AS user_address
    FROM ethereum.transactions
    WHERE "to" = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Örnek: Uniswap v3 USDC/WETH havuzu
    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
)
-- Her cüzdanın tarihindeki ilk işlem hariç tüm kayıtları eliyoruz
SELECT 
    g.user_address,
    g.funder AS parent_address,
    g.block_time AS creation_timestamp,
    -- Şüpheli fon sağlayıcıları etiketliyoruz. Aşağıdaki adresler bilinen büyük mikser/köprü kontratlarıdır
    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;
-- Tüm tx tablosuna JOIN atıldığı için ağır bir sorgudur. Büyük veri setlerinde block_date partisyonu kullanın.

Kitle Segmentasyonu: Karar Alma Hızı ve Gaz Mantığı

Davranış analizi, kullanıcıları zincir üstü (on-chain) disiplinlerine göre net kategorilere ayırmanızı sağlar. Burada anket yapmak hiçbir işe yaramaz. Kullanıcılar yalan söyler ama ledger yalan söylemez.

Segmentasyon için en etkili ama değeri en az bilinen parametrelerden biri "Approve-to-Swap Latency" değeridir. Yani token harcama izni (approve) ile takasın (swap) kendisi arasında geçen süre.

Standart bir bireysel (retail) kullanıcı MetaMask veya Rabby gibi popüler cüzdan arayüzlerini kullanır. Bu yüzden approve() fonksiyonunu çağırması ile swap() işlemini onaylaması arasında genelde 15 saniyeden birkaç dakikaya kadar zaman geçer. Kullanıcı o esnada slippage oranını inceler, düşünür ya da dikkatini başka bir şey çeker. Otomatize scriptler ve MEV botları ise bu işlemleri atomik olarak imzalar. İşlemler ya tamamen aynı blokta ya da tek bir Flashbots bundle'ı içinde ağa gönderilir. Eğer approve ile transfer arasındaki zaman farkı sıfıra yakınsa, o adresi doğrudan bot olarak etiketlemek gerekir.

Zincir Üstü Pattern Karşılaştırması

Dune SQL ile Kohort Retansiyon Analizi (Cohort Retention)

Ürününüzün gerçekten bir Product-Market Fit yakalayıp yakalamadığını, yoksa pazarlama bütçesini tek kullanımlık kullanıcılar için mi harcadığınızı anlamanın tek yolu kohort analizidir. Belirli bir ayda ilk işlemini yaparak sisteme dahil olan kullanıcıların sonraki dönemlerdeki davranışlarını takip etmemiz gerekir.

Dune SQL üzerinde bu işlem, pencere fonksiyonları (window functions) ve DATE_DIFF ile tarih farkı hesaplanarak çözülür.

-- Belirli bir akıllı kontrat için aylık kullanıcı retansiyonunu hesaplıyoruz
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' -- Örnek: USDT kontratı
),
cohort_volumes AS (
    -- Her kohortun başlangıçtaki üye sayısını sabitliyoruz
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_address) AS cohort_size
    FROM user_activity
    GROUP BY 1
),
retention_summary AS (
    -- Aylara göre geri dönen tekil kullanıcı sayısını hesaplıyoruz
    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
)
-- Final matrisini oluşturuyoruz
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;
-- Kritik eşik: Eğer 1. ayda (period_index = 1) retention_percentage değeri %7'nin altına düşüyorsa üründe tutundurma problemi var demektir.

Yalnız burada teknik bir detay var. Analiz ettiğiniz kontrat, örnekteki USDT gibi tüm ağın kullandığı altyapısal bir kontratsa kohortlar devasa boyuta ulaşır. Bu durum Dune'un ücretsiz paketlerinde sorgunun timeout yemesiyle sonuçlanabilir. Kendi özel dApp kontratınız için ise bu kod saniyeler içinde çalışacaktır.

Gizli Eventler: Derin Davranış Tetikleyicilerini Loglardan Çıkarmak

Analistlerin büyük kısmı sadece transactions veya traces tablolarını parse etmekle yetiniyor. Bu durum çok büyük bir veri katmanını ıskalamalarına yol açar. Profesyonel analiz, doğrudan sistem loglarına (ethereum.logs) inmeyi gerektirir.

Buna neden ihtiyacımız var? Güncel protokollerin çoğu artık meta-transaction yapılarını ve Hesap Soyutlamayı (Account Abstraction - ERC-4337) kullanıyor. Bu mimaride işlem tablosundaki tx.from alanı hiçbir zaman son kullanıcının adresini vermez; her zaman işlemi paketleyip madenciye ileten relayer veya bundler adresini gösterir. Standart işlemleri analiz etmeye çalışırsanız, binlerce tekil kullanıcı yerine sadece üç-dört büyük kontratın aktivitesini görürsünüz.

Çözüm, veriyi doğrudan Event'lerden çekmektir. Örneğin UserOperationEvent olayının hash değerine sahip Topic 0'ı parse edebilirsiniz. Bu sayede kullanıcının bir akıllı kontrat cüzdanı (Safe, Argent) mı kullandığını, donanım cüzdanıyla mı işlem yaptığını yoksa dApp ile Telegram botları içindeki gömülü cüzdanlar (embedded wallets) üzerinden mi etkileşime girdiğini net olarak görebilirsiniz. Kitlenizin cüzdan mimarisini bilmek, UX optimizasyonunun kurallarını değiştirir. Akıllı cüzdan kullanan retail kitleye gaz ücreti mantığını anlatmanıza gerek kalmaz; paymaster kontratları kurarak komisyonu doğrudan stabilcoinlerle ödemelerini sağlayabilirsiniz.

End-to-End Likidite Takibi: LP Drain Pattern'lerini Yakalamak

Bir proje ne zaman liquidity mining teşvikleri başlatsa, havuza anında bir likidite sağlayıcı (LP) ordusu akın eder. İşin kötü tarafı, bu TVL'in çok büyük bir kısmı "mercenary capital" dediğimiz vur-kaç parasıdır. Yield farmer'lar, yüksek APY aktığı sürece havuzda bekler. Ödüller düştüğü an parayı anında çekip market depth'i darmadağın ederler.

Erken uyarı sistemi kurmak için sadece likidite çıkışlarını izlemek yetmez. Havuzdan çıkan fonların sonrasında nereye aktığını da track etmek gerekir. Kâr alma anında smart money ile retail kitlenin on-chain footprint'i tamamen farklıdır.

• Retail Adresler: LP token'ları çözer -> Stablecoin'e veya native gaz token'ına (ETH) swap'ler -> Standart deposit adresleri üzerinden merkezi borsalara (CEX) aktarır.
• Arbitrajcılar ve MEV Botları: Likiditeyi çeker -> APY oranı sadece %0.5 daha yüksek diye fonu anında başka bir protokoldeki muadil havuza köprüler -> Tüm bu aksiyonu bir veya iki blok içinde tamamlar.
• Kurumsal Havuzlar (Whales / Smart Money): Doğrudan satış yapmak yerine fonları multisig cüzdanlara ya da Aave, Maker gibi borçlanma (lending) protokollerine taşır. Varlıkları nakde çevirmek yerine teminat (collateral) gösterip kredi döngüsüne sokarlar.

LP Göçünü İzlemek İçin İleri Düzey SQL Sorgusu

Havuzdan likidite çıkaran adresleri (Burn eventi) izole eden ve bu token'ların sonraki 24 saat içinde nereye gittiğini bulan bir script yazalım.

-- Likidite çıkışı sonrası LP davranış analizi
WITH lp_burns AS (
    SELECT 
        evt_tx_hash,
        evt_block_time AS burn_time,
        provider AS user_address,
        amount0 / 1e6 AS amount_usdc, -- token0 = USDC varsayımı
        amount1 / 1e18 AS amount_weth  -- token1 = WETH varsayımı
    FROM uniswap_v3_ethereum.Pair_evt_Burn
    WHERE contract_address = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Hedef havuz
      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
)
-- Kullanıcının havuzdan çıktıktan sonraki ilk aksiyonu
SELECT 
    s.user_address,
    s.burn_time,
    s.tx_time,
    s.destination_address,
    -- Hedef adresi tanımlama
    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 uyarısı: Kullanıcı aynı saniyede birden fazla tx attıysa (bundle), tx_order kırılımı kayabilir.

"Toxic Flow" Analizi ve Ürün Metriklerine Etkisi

Bir DEX veya DeFi protokolü için işlem hacminin kalitesini anlamak ölüm kalım meselesidir. On-chain hacim temelde ikiye ayrılır: Organik hacim (kullanıcıların gerçek ihtiyaçları için yaptığı swap'ler) ve toksik hacim (arbitraj, LVR - Loss Versus Rebalancing).

Toksyik hacmi botlar yaratır. Dış borsalarda (örneğin Binance'te) fiyat sert hareket ettiği an, bu botlar pasif LP'lerin değerini saniyeler içinde sömürür. Eğer bir analist dApp üzerindeki hacim artışını görüp bunu doğrudan kullanıcı büyümesi olarak raporluyorsa muhtemelen büyük yanılıyordur. Çoğu zaman bu durum, protokolün oracle'larının lag yapmasından ve arbitrajcıların havuzu hunharca sağmasından ibarettir.

SQL ile Toksik Adres Nasıl Yakalanır?

Yöntem, işlem sonrasındaki 5 blokluk süreçte PnL değişimini izlemektir. Bir adres swap yapıyor ve sonraki 5 blok içinde varlığın fiyatı havuzun aleyhine, swap'i yapan adresin ise lehine değişiyorsa, bu net arbitrajdır.

Toplam hacimlerinin %80'inden fazlası bu tarz işlemlerden oluşan adresler, analitik veri tabanında Arbitrage_Bot olarak etiketlenmelidir. Gerçek kullanıcı tutma oranı (Retention) hesaplanırken, bu botların hacimleri ana istatistiklerden tamamen ayıklanmalıdır. Aksi takdirde, kullanıcı bağlılığı metrikleri (engagement metrics) tamamen manipüle edilmiş olur.

Analitik Dashboard Mimarisi: Raw Loglardan Ürün Insight'larına

Üretim (production) ortamında on-chain analizler tek seferlik ad-hoc sorgularla yürütülemez. Pratikte ham veriler, belirli bir cron takvimine göre otomatik güncellenen özel tablolara (Dune terminolojisiyle Spells) aktarılır.

Aşağıda, bir Web3 projesinin ürün analitiği için test edilmiş veri boru hattı (data pipeline) yapısı yer alıyor:

[Ağın Ham Verisi (Blocks, Transactions, Logs)]
                      │
                      ▼
[Decoded Contracts (Events & Calls)] ──► Sistem gürültüsünü ve hatalı işlemleri (reverted txs) ayıkla
                      │
                      ▼
[İş Mantığı Katmanı (Spell Tabloları: Swaps, Deposits)] ──► Approve-to-Swap Latency kırılımına göre filtrele
                      │
                      ▼
[Davranışsal Kohortlar (Final Dashboard)] ──► Ürün Metrikleri (LTV, CAC, Retention)

Analistler İçin Temel Çıkarım

Web3 ürünlerini Web2 metrikleriyle ölçmeyi artık bırakın. Benzersiz aktif cüzdan sayısı (UAW) veya toplam işlem hacmi (Volume) gibi veriler, minimum gaz ücretiyle Sybil saldırıları yapılarak çok rahat manipüle edilebilir. Gerçek davranışsal analitik; cüzdanların finansal soy ağacını (gaz kaynağını), sistem çağrıları arasındaki zaman farklarını ve akıllı sözleşmelerinizle etkileşime girdikten sonraki sermaye kaçış rotalarını incelemekten geçer. Blockchain yalan söylemez, sadece ona doğru sorguyu sormayı bilmek gerekir.