Analiza danych on-chain w Dune SQL: Zachowania użytkowników

Growth marketerzy i analitycy produktowi ciągle popełniają ten sam błąd: oceniają bazę użytkowników dAppów po TVL lub stanie konta na portfelach. Widzą adres z 50 ETH i z miejsca oznaczają go jako wieloryba o wysokiej wartości. To fundamentalne nieporozumienie w analizie danych web3.

W analityce Web3 obecny balans to tylko tzw. vanity metric. Kluczowy sygnał daje źródło pierwotnego finansowania (Funding Source). Rzetelna analiza behawioralna zaczyna się od deanonimizacji powiązań między portfelami. Jeśli w protokole nagle wystrzeli tysiąc nowych adresów, a szybki research wykaże, że wszystkie dostały pierwszy gaz z tego samego subkonta na giełdzie lub przez dedykowany smart contract miksera w krótkim oknie czasowym, to nie jest to ruch organiczny. Masz do czynienia z przemysłową farmą sybili, która wysysa płynność albo nabija transakcje pod przyszły airdrop.

Wykrywanie takich wzorców wymaga przeanalizowania „genezis” portfela: timestampu pierwszej transakcji, adresu sponsora oraz całego łańcucha kolejnych transferów.

Praktyczne zapytanie SQL dla Dune (Dune SQL)

Ten skrypt izoluje adresy wchodzące w interakcję z Twoim smart contractem i wyciąga dla każdego z nich absolutnie pierwszą transakcję on-chain. Pozwala to błyskawicznie pogrupować użytkowników według sposobu zasilenia konta.

-- Wyciągamy pierwotne źródło gazu dla adresów wchodzących w interakcję z kontraktem
WITH target_users AS (
    SELECT DISTINCT "from" AS user_address
    FROM ethereum.transactions
    WHERE "to" = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Przykład: pula 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
)
-- Zostawiamy tylko pierwszą historyczną transakcję dla każdego portfela
SELECT 
    g.user_address,
    g.funder AS parent_address,
    g.block_time AS creation_timestamp,
    -- Flagujemy podejrzanych sponsorów. Poniższe adresy to znane miksery i mosty
    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;
-- Zapytanie jest ciężkie ze względu na JOIN do całej tabeli tx. Przy większych zbiorach filtruj po block_date.

Segmentacja użytkowników: Opóźnienie egzekucji i logika gazu

Analiza behawioralna pozwala precyzyjnie podzielić użytkowników na kohorty na podstawie ich dyscypliny on-chain. Ankiety nie mają sensu. Użytkownicy kłamią, ledger nigdy.

Jednym z najskuteczniejszych, a zarazem rzadko używanych parametrów segmentacji jest Approve-to-Swap Latency, czyli odstęp czasowy między zatwierdzeniem tokenu a samą wymianą.

Przeciętny użytkownik detaliczny korzysta ze standardowych interfejsów jak MetaMask czy Rabby. Między wywołaniem funkcji approve() a kliknięciem swap() mija od 15 sekund do kilku minut. W tym czasie sprawdza slippage, zastanawia się albo po prostu coś go rozprasza. Boty i MEV searcherzy podpisują to atomowo. Ich transakcje wpadają w tym samym bloku albo bezpośrednio w jednym bundle'u Flashbots. Jeśli opóźnienie między zatwierdzeniem a transferem dąży do zera, adres dostaje flagę bota.

Porównanie wzorców on-chain

Analiza kohortowa retencji (Cohort Retention) w Dune SQL

Analiza kohortowa to jedyny sposób, by zweryfikować Product-Market Fit i upewnić się, że budżet marketingowy nie idzie na jednorazowych użytkowników. Musimy prześledzić zachowanie ludzi, którzy zaliczyli onboarding w konkretnym miesiącu, na przestrzeni kolejnych okresów.

W Dune SQL realizuje się to funkcjami okna i kalkulacją dat przez DATE_DIFF.

-- Liczymy miesięczną retencję użytkowników dla konkretnego smart contractu
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' -- Przykład: kontrakt USDT
),
cohort_volumes AS (
    -- Wyciągamy bazową wielkość każdej kohorty
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_address) AS cohort_size
    FROM user_activity
    GROUP BY 1
),
retention_summary AS (
    -- Liczymy unikalnych powracających użytkowników miesięcznie
    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
)
-- Budujemy ostateczną macierz retencji
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;
-- Czerwona flaga: jeśli retencja w miesiącu pierwszym (period_index = 1) spada poniżej 7%, produkt nie utrzymuje bazy.

Małe ostrzeżenie: odpalenie tego na mocno obciążonym kontrakcie systemowym typu USDT wygeneruje ogromne kohorty. Zapytanie najpewniej wywali timeout na darmowym planie Dune. Przy własnym dAppie kod wykona się w kilka sekund.

Ukryte eventy: Jak wyciągnąć głębokie triggery behawioralne z logów

Większość analityków zamyka się w tabelach transactions lub traces, co ucina potężną część danych. Profesjonalna analityka produktowa wymaga zejścia do logów systemowych (ethereum.logs).

Dlaczego to ważne? Nowoczesne protokoły coraz częściej stawiają na meta-transakcje i Account Abstraction (ERC-4337). Przy takiej architekturze pole tx.from w standardowej tabeli transakcji wskaże na adres bundlera lub relayera, a nie realnego użytkownika. Analizując transakcje wprost, zobaczysz aktywność kilku dużych kontraktów zamiast tysięcy unikalnych portfeli.

Rozwiązaniem jest wyciąganie danych bezpośrednio z eventów. Parsowanie Topic 0 pod kątem hasha zdarzenia UserOperationEvent pozwala sprawdzić, czy użytkownik ma smart wallet (Safe, Argent), korzysta z rozwiązań hardware'owych, czy klika w dApp przez wbudowane portfele w botach na Telegramie. Znajomość architektury portfeli Twoich odbiorców determinuje strategię UX. Użytkownikom detalicznym na smart kontach nie trzeba tłumaczyć działania gazu. Im trzeba wdrożyć kontrakty typu paymaster, żeby płacili za opłaty sieciowe bezpośrednio w stablecoinach.

Śledzenie przepływu płynności end-to-end: Wykrywamy schematy LP Drain

Odpisanie kampanii liquidity miningowej zazwyczaj ściąga tłum dostawców płynności (LP). Sęk w tym, że większość tego TVL-u to kapitał spekulacyjny, czyli tzw. mercenary capital. Yield farmerzy trzymają środki w puli tylko tak długo, jak długo dropi podbite APY. Gdy tylko nagrody spadają, od razu wyciągają kasę i niszczą głębokość rynku (market depth).

Żeby postawić system wczesnego ostrzegania, nie wystarczy monitorować samych faktów wycofania płynności. Musimy trackować, gdzie te środki lecą dalej. Smart money i degeni detaliczni zachowują się zupełnie inaczej w momencie realizowania zysków.

• Adresy detaliczne (Retail): Wyciągają LP tokeny -> Swapują je do stajni (stablecoinów) lub na natywny gaz (ETH) -> Ślą środki na giełdy scentralizowane (CEX) przez standardowe adresy depozytowe.
• Arbitrażyści i boty MEV: Wyciągają płynność -> Błyskawicznie przerzucają ją do bliźniaczej puli w innym protokole, gdzie APY jest wyższe chociażby o 0.5% -> Zamykają całą akcję w obrębie jednego lub dwóch bloków.
• Pule instytucjonalne (Whales / Smart Money): Przelewają środki na portfele multisig albo do protokołów pożyczkowych (lending protocols) typu Aave czy Maker. Zamiast bezpośredniej sprzedaży, wrzucają aktywa jako zabezpieczenie (collateral) pod pętlę kredytową.

Piszemy zaawansowane zapytanie SQL do trackowania migracji LP

Napiszmy skrypt, który izoluje adresy wycofujące płynność z puli (event Burn) i sprawdza, gdzie te tokeny wylądowały w ciągu kolejnych 24 godzin.

-- Analiza zachowania LP-ków po wyciągnięciu płynności
WITH lp_burns AS (
    SELECT 
        evt_tx_hash,
        evt_block_time AS burn_time,
        provider AS user_address,
        amount0 / 1e6 AS amount_usdc, -- Zakładamy, że token0 to USDC
        amount1 / 1e18 AS amount_weth  -- Zakładamy, że token1 to WETH
    FROM uniswap_v3_ethereum.Pair_evt_Burn
    WHERE contract_address = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Targetowa pula
      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
)
-- Wyciągamy pierwszą akcję usera po wyjściu z puli
SELECT 
    s.user_address,
    s.burn_time,
    s.tx_time,
    s.destination_address,
    -- Identyfikacja adresu docelowego
    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;
-- Uwaga na edge case: przy kilku tx w tej samej sekundzie (bundle), partycjonowanie po tx_order może się rozjechać.

Analiza toksycznego wolumenu (Toxic Flow) i jego wpływ na metryki produktu

Dla każdego DEX-a czy protokołu DeFi kluczowe jest rozumienie jakości generowanego wolumenu. Ruch na twardych danych dzieli się na organiczny (gdy userzy robią swapy pod realną użyteczność) oraz toksyczny (arbitraż, LVR - Loss Versus Rebalancing).

Toksyczny wolumen to robota botów. Wyciągają one wartość z pasywnych LP-ków w ułamku sekundy, gdy tylko cena na zewnętrznym rynku (np. na Binance) gwałtownie tąpnie lub wystrzeli. Jeśli analityk widzi nagły skok wolumenu na dAppie i od razu raportuje to jako sukces produktu – najpewniej grubo się myli. Najczęściej oznacza to po prostu lagujące oracle, przez co protokół jest bezczelnie dojony przez arbitrażerów.

Jak namierzyć toksyczny adres przez SQL?

Monitorujemy metrykę PnL transakcji w horyzoncie 5 bloków w przód. Jeśli dany adres robi swapa, a w ciągu kolejnych 5 bloków cena aktywa zmienia się na niekorzyść puli, ale na korzyść tego adresu – mamy do czynienia z czystym arbitrażem.

Adresy, na których taki toksyczny flow stanowi ponad 80% ich całkowitego wolumenu, powinny dostać w analitycznej bazie danych flagę Arbitrage_Bot. Przy liczeniu realnego utrzymania użytkowników (Retention), ich wolumeny trzeba całkowicie odciąć od statystyk. Inaczej obraz zaangażowania (engagement metrics) będzie totalnie przekłamany.

Architektura dashboardu analitycznego: od surowych logów do insightów produktowych

Profesjonalna analityka on-chain nie może stać na zapytaniach ad-hoc. W warunkach produkcyjnych dane agreguje się do dedykowanych tabel (Spells w nomenklaturze Dune), które odświeżają się według harmonogramu (cron).

Poniżej znajduje się sprawdzona struktura rurociągu danych (data pipeline) do analityki produktu Web3:

[Surowe dane z sieci (Blocks, Transactions, Logs)]
                  │
                  ▼
[Zdekodowane kontrakty (Events & Calls)] ──► Wycinamy szum systemowy i błędy (reverted txs)
                  │
                  ▼
[Logika biznesowa (Tabele Spells: Swaps, Deposits)] ──► Filtrowanie po Approve-to-Swap Latency
                  │
                  ▼
[Kohorty behawioralne (Dashboard końcowy)] ──► Metryki produktowe (LTV, CAC, Retention)

Kluczowy wniosek dla analityka

Czas przestać mierzyć produkty Web3 metrykami rodem z Web2. Liczba unikalnych portfeli (UAW) czy całkowity wolumen transakcji (Volume) są banalnie proste do sfałszowania za grosze wydane na gas przy użyciu sybili. Prawdziwa analityka behawioralna siedzi na styku badania genealogii finansowej portfeli (źródła gazu), opóźnień czasowych między wywołaniami systemowymi oraz struktury ucieczki kapitału po interakcji z kontraktami. Blockchain nie kłamie. Trzeba tylko wiedzieć, jak go pytać.