Análisis de Datos On-Chain con Dune SQL y Retención Web3

Los growth marketers y analistas de producto suelen cometer el mismo error: medir a la audiencia de una dApp por los fondos que tienen en la wallet (mirando solo el TVL o el Balance). Ven una dirección con 50 ETH y de inmediato la meten en el segmento de usuarios de alto valor. Es un sesgo sistémico.

En analytics de Web3, el saldo actual es secundario. Lo que manda es la fuente de financiamiento inicial (Funding Source). Un buen análisis de comportamiento (User Behavior Analytics) arranca desanonimizando los vínculos entre wallets. Si tu protocolo registra un pico de 1000 direcciones nuevas y el tracking muestra que todas fondearon su primer gas desde la misma subcuenta de un CEX grande o mediante un contrato de un mixer en una ventana de tiempo milimétrica, olvídate. No es crecimiento orgánico. Es una granja de Sybils industrial vaciando liquidez o inflando métricas para farmear un airdrop.

Para detectar estos patrones se analiza el "génesis" de la wallet: el timestamp de la primerísima transacción, la dirección donante y el rastro de los transfers siguientes.

Query de SQL práctica para Dune (Dune SQL)

Este script aísla las wallets que interactuaron con tu smart contract y saca la primera transacción histórica de cada una en la red. Esto permite agrupar al momento a los usuarios según su tipo de "donante".

-- Detectamos la fuente original de gas para las wallets que interactuaron con el contrato
WITH target_users AS (
    SELECT DISTINCT "from" AS user_address
    FROM ethereum.transactions
    WHERE "to" = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Ejemplo: pool de 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
)
-- Filtramos todo y dejamos solo la primera transacción histórica de cada wallet
SELECT 
    g.user_address,
    g.funder AS parent_address,
    g.block_time AS creation_timestamp,
    -- Marcamos funders sospechosos. Estas direcciones son ejemplos de mixers/bridges grandes
    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 pesada por el JOIN con toda la tabla de transactions. Para muestras grandes, meter partitions por block_date.

Segmentación de usuarios: Ventana de decisión y lógica de gas

El análisis conductual divide a los usuarios en categorías claras según su disciplina on-chain. Las encuestas directas aquí no sirven. Los usuarios mienten, la chain no.

Uno de los parámetros de segmentación más efectivos y menos aprovechados es el Approve-to-Swap Latency (el delay entre que se autoriza el gasto del token y se ejecuta el swap).

El usuario retail promedio opera desde interfaces estándar (MetaMask, Rabby). Entre que llama a la función approve() y manda el swap(), pasan de 15 segundos a varios minutos. Revisa el slippage, lo piensa o se distrae. Por el contrario, los scripts automatizados y los bots de MEV firman estas transacciones de forma atómica. Entran en el mismo bloque o dentro del mismo bundle de Flashbots. Si el delta de tiempo entre el approve y el transfer tiende a cero, hay que marcar esa dirección como bot.

Análisis comparativo de patrones on-chain

Análisis de retención por cohortes (Cohort Retention) con Dune SQL

Para saber si tu producto tiene un Product-Market Fit (PMF) real o si solo estás quemando presupuesto de marketing trayendo usuarios de un solo uso, necesitas un cohort analysis. Hay que trackear el comportamiento de los usuarios que hicieron su movimiento inicial en un mes específico a lo largo de los siguientes períodos.

En Dune SQL esto se resuelve metiendo window functions y calculando la diferencia de fechas con DATE_DIFF.

-- Calculamos la retención mensual de usuarios para un smart contract específico
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' -- Ejemplo: contrato de USDT
),
cohort_volumes AS (
    -- Fijamos el tamaño inicial de cada cohorte
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_address) AS cohort_size
    FROM user_activity
    GROUP BY 1
),
retention_summary AS (
    -- Contamos usuarios únicos que regresaron por mes
    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
)
-- Armamos la matriz final
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;
-- Alerta crítica: si el retention_percentage en el segundo mes (period_index = 1) cae por debajo del 7%, tienes un problema serio de retención.

Ojo, aquí hay un detalle. Si el contrato es de infraestructura core (como el de USDT del ejemplo), las cohortes van a ser gigantescas. La query podría meter un timeout en los tiers gratuitos de Dune. Para una dApp propia, el código corre en segundos.

Eventos ocultos: Cómo sacar disparadores de conducta profundos desde los logs

La mayoría de los analistas se queda en revisar las tablas de transactions или traces. Eso deja fuera un montón de información. El análisis profesional exige meterse de lleno en los logs del sistema (ethereum.logs).

¿Por qué importa esto? Muchos protocolos modernos usan meta-transactions y Account Abstraction (ERC-4337). En estos casos, el campo tx.from siempre va a mostrar la dirección del relayer (el bundler que empaqueta las operaciones) y no la del usuario real. Si intentas analizar las transacciones estándar, vas a ver la actividad de tres o cuatro contratos gigantes en lugar de miles de usuarios únicos.

La solución es extraer los datos directo de los Events. Por ejemplo, parsear el Topic 0 filtrando por el hash del UserOperationEvent. Esto te permite identificar si el usuario usa una smart contract wallet (Safe, Argent), si opera con una hardware wallet o si interactúa con la dApp mediante las embedded wallets de los bots de Telegram. Entender la arquitectura de las wallets de tu audiencia te permite optimizar la UX: al retail que usa smart accounts no tienes que explicarle qué es el gas. Solo necesitas configurar contratos de paymaster para que paguen los fees directamente con stablecoins.

Tracking de liquidez de punta a punta: detectando patrones de LP Drain

Cada vez que un proyecto lanza una campaña de incentivos (Liquidity Mining), cae una horda de proveedores de liquidez (LPs). El problema es que gran parte de ese TVL es capital mercenario (hot capital). Se quedan en el pool solo mientras rinda el APY inflado. En cuanto bajan los incentivos, retiran los fondos al toque y destruyen la profundidad de mercado.

Para armar un sistema de alerta temprana, no alcanza con trackear el retiro de liquidez. Hay que seguir el rastro de esos fondos en sus movimientos posteriores. El smart money y el retail se comportan de formas totalmente distintas a la hora de tomar ganancias.

• Direcciones de Retail: Remueven los tokens de LP -> Hacen swap a stablecoins o al gas nativo (ETH) -> Depositan en exchanges centralizados (CEX) mediante direcciones de depósito estándar.
• Arbitrajistas y Bots de MEV: Retiran la liquidez -> Rotan los fondos en el acto a un pool similar en otro protocolo por un 0.5% más de APY -> Liquidan todo en cuestión de uno o dos bloques.
• Pools Institucionales (Whales): Mueven los fondos a contratos multisig o a protocolos de lending no custodial (Aave, Maker). En lugar de vender directo, usan los activos como colateral para apalancarse.

Escribiendo una query SQL avanzada para trackear la migración de LPs

Armemos una query para aislar las direcciones que sacaron liquidez del pool (evento Burn) y verificar a dónde se movieron esos tokens en las siguientes 24 horas.

-- Comportamento de LPs pos-retire de fondos
WITH lp_burns AS (
    SELECT 
        evt_tx_hash,
        evt_block_time AS burn_time,
        provider AS user_address,
        amount0 / 1e6 AS amount_usdc, -- Asumimos token0 = USDC
        amount1 / 1e18 AS amount_weth  -- Asumimos token1 = WETH
    FROM uniswap_v3_ethereum.Pair_evt_Burn
    WHERE contract_address = c'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' -- Pool objetivo
      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
)
-- Filtramos la primerísima acción del usuario tras salir del pool
SELECT 
    s.user_address,
    s.burn_time,
    s.tx_time,
    s.destination_address,
    -- Identificación del destino
    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;
-- Ojo con este edge case: si el usuario mete varias txs en el mismo segundo (bundle), la partición por tx_order puede fallar.

Análisis de "Flujo Tóxico" (Toxic Flow) y su impacto en las métricas de producto

Para un DEX o cualquier protocolo DeFi, auditar la calidad del volumen de trading es un tema de supervivencia. El volumen on-chain se divide en orgánico (usuarios reales cambiando activos por necesidad de uso) y tóxico (arbitraje, LVR - Loss Versus Rebalancing).

El volumen tóxico lo generan los bots. Estos bichos se la pasan drenando el valor de los LPs pasivos cada vez que el precio se mueve fuerte en mercados externos (como Binance). Si un analista ve un pico de volumen en la dApp y sale corriendo a reportar un crecimiento de usuarios, seguro la está pifiando. Lo más probable es que los oráculos del protocolo tengan lag y los arbitrajistas estén ordeñando los pools.

¿Cómo cazar una dirección tóxica usando SQL?

Monitoreamos el PnL de la transacción en una ventana de 5 bloques post-ejecución. Si una dirección mete un swap y en los siguientes 5 bloques el precio del activo se mueve en contra del pool y a favor de esa wallet, es arbitraje puro.

Las wallets que tengan más del 80% de su volumen total metido en este tipo de transacciones se taguean como Arbitrage_Bot en la base de datos analítica. Para calcular la retención real (Retention), hay que limpiar este volumen de las estadísticas generales. Si no se hace, las métricas de engagement del producto van a quedar totalmente distorsionadas.

Arquitectura de un dashboard analítico: de logs crudos a insights de producto

El análisis on-chain profesional en entornos de producción no se hace con queries ad-hoc tiradas a mano. En la práctica, los datos crudos se agregan en tablas personalizadas (Spells en la jerga de Dune) que se actualizan de forma automatizada mediante un cron.

A continuación, el diseño de un pipeline de datos validado para análisis de producto en Web3:

[Datos Crudos On-Chain (Blocks, Transactions, Logs)]
                      │
                      ▼
[Contratos Decodificados (Events & Calls)] ──► Limpieza de ruido del sistema y txs fallidas (reverted)
                      │
                      ▼
[Lógica de Negocio (Tablas Spells: Swaps, Deposits)] ──► Filtrado por latencia de Approve-to-Swap
                      │
                      ▼
[Cohortes de Comportamiento (Dashboard Final)] ──► Métricas de Producto (LTV, CAC, Retention)

Conclusión clave para el analista

Dejen de medir productos Web3 con métricas de Web2. Las wallets activas únicas (UAW) y el volumen transaccional bruto son facilísimos de inflar con ataques Sybil gastando dos mangos de gas. El análisis de comportamiento real está en la genealogía financiera de las wallets (de dónde viene el gas), los deltas de tiempo entre llamadas de bajo nivel del sistema y la ruta del flujo de capital después de interactuar con tus smart contracts. La blockchain no miente, solo hay que saber cómo preguntarle.