Reconstructing Wallet Journeys Across DEX Frontends, One Chain at a Time

The Challenge

A product analyst asks a simple question: after someone swaps on our frontend, where do they go next, and where did they come from? On a normal app that is a session query. On-chain there are no sessions. There is no user ID, no login, no ordered clickstream, just a wide flat table where each row is one swap leg by some address at some block time. The ordering that a journey is made of does not exist as a column. It has to be reconstructed.

Reconstructing it with window functions is the easy part, and it is where most write-ups on this stop. The work that decides whether the output means anything happens before the first LAG, in three places where the raw data will happily produce a confident, wrong answer.

Trap 1: a leg is not a trade

Dune's dex.trades stores one row per swap leg. A single aggregator route through three pools writes three rows for one user action. Sequence those rows directly and you manufacture hops the user never made, several within the same second, separated by a zero-minute gap. ClearTrace measured this leg inflation at 2.15x on Odos/Ethereum while building the attribution engine, which is why every volume path in that engine collapses to one row per transaction before any arithmetic. A journey query needs the same collapse for the same reason.

Trap 2: the busiest wallets are not users

On an unfiltered transition count, MEV searchers and arbitrage bots take the entire top of the table. They hop venues thousands of times a day, which is exactly the behaviour the query is built to surface, and they are not people whose journey anyone wants to understand. Any transition map published without a bot guard is largely a map of arbitrage routing wearing the label "user behaviour."

Trap 3: the chains are separate timelines

It is tempting to run one query over every chain at once and call the output a cross-chain journey. dex.trades spans all chains, so partitioning by wallet without a chain filter interleaves that wallet's Ethereum, Base, Arbitrum, and Optimism swaps into one sequence, then reports the gaps between unrelated chains as dwell time. The wallet did not travel from a Base entrypoint to an Arbitrum one because a bridge, a different key, and a different session sat in between. The honest unit of reconstruction is one wallet on one chain. Cross-chain behaviour is a separate problem, and answering it takes bridge-level evidence that a timestamp sort cannot supply.

What We Built

A per-chain reconstruction query with the guards in front of the window function. It resolves the entrypoint of each trade (the router or frontend contract the transaction was sent to) rather than the venue it settled on, collapses legs to one row per transaction, drops dust and implausible wallets, then lets each remaining swap read its own predecessor. The output is a ranked edge list: for every ordered pair of entrypoints, how many distinct wallets crossed, how many times, and the median wait in between.

One detail worth naming: project in dex.trades is the DEX venue (Uniswap, Curve), not the interface a user touched. Building hops from it reports venue routing as user behaviour. Resolving tx_to against the chain's decoded contracts, and leaving anything unresolved as Unknown Proxy, keeps the map about interfaces and keeps the unknowns visible instead of silently absorbed into a named venue.

-- Dune SQL (Trino) query for the Multi-Hop Origin Trace (Vector #3)
-- Reconstructs each wallet's ordered path between ENTRYPOINTS (the router or
-- frontend contract a swap was sent to) and rolls the hops up into a ranked
-- transition map: which entrypoint feeds which, for how many distinct wallets,
-- and how long they wait before the next swap.
--
-- SCOPE IS ONE CHAIN PER RUN. dex.trades spans every chain, so an unfiltered
-- PARTITION BY tx_from interleaves a wallet's Ethereum, Base, Arbitrum and
-- Optimism swaps into a single timeline that never happened, and reports the
-- gaps between them as user dwell time. {CHAIN} is substituted per chain the
-- same way queries/master_attribution_query_template.sql is templated
-- (ethereum | base | arbitrum | optimism); {{Params}} are Dune runtime params.

WITH base_trades AS (
    SELECT
        t.tx_hash,
        t.tx_from,
        t.block_time,
        t.amount_usd,
        -- The entrypoint is the contract the user's transaction was sent TO.
        -- Resolve it to a name the same way queries/sankey_routing_edges.sql
        -- does, and leave it as 'Unknown Proxy' when nothing resolves it rather
        -- than substituting the venue it happened to settle on.
        --
        -- NOTE: t.project is the DEX VENUE (uniswap, curve), NOT the interface
        -- that originated the trade. Building hops out of t.project reports
        -- venue routing as if it were user behaviour: a single aggregator route
        -- that touches three pools looks like a user visiting three frontends.
        REPLACE(COALESCE(c.name, 'Unknown Proxy'), '{CHAIN}: ', '') AS entrypoint
    FROM dex.trades t
    LEFT JOIN {CHAIN}.contracts c ON c.address = t.tx_to
    WHERE t.blockchain = '{CHAIN}'
      AND t.block_time >= NOW() - INTERVAL '{{Days_Back}}' DAY
),
per_tx AS (
    -- Collapse legs to ONE row per transaction before any sequencing.
    -- dex.trades stores one row PER SWAP LEG; a multi-hop route prices several
    -- legs for ONE user trade (measured at 2.15x inflation on Odos/ethereum,
    -- see master_attribution_query_template.sql). Sequencing raw legs invents
    -- transitions the user never made, several per second, separated by a
    -- 0-minute gap. tx_to is the transaction's `to` field, so the entrypoint is
    -- constant per tx and one row survives the collapse.
    SELECT
        tx_hash,
        tx_from,
        entrypoint,
        MIN(block_time) AS block_time,
        MAX(amount_usd) AS notional_usd
    FROM base_trades
    GROUP BY 1, 2, 3
),
eligible_wallets AS (
    -- Bot and dust guard. An unfiltered transition count is dominated by MEV
    -- and arbitrage wallets that hop venues thousands of times a day. That is
    -- real on-chain activity, but it is not a user journey, and left in it sets
    -- the entire top of the table. Drop dust trades first, then drop any wallet
    -- whose swap count over the window is implausible for a human.
    SELECT tx_from
    FROM per_tx
    WHERE notional_usd >= {{min_notional_usd}}
    GROUP BY 1
    HAVING COUNT(*) <= {{max_swaps_per_wallet}}
),
ordered_swaps AS (
    -- The reconstruction: partition by wallet, order by time, and let each swap
    -- read its own predecessor. tx_hash breaks ties so two swaps in the same
    -- block order deterministically instead of shuffling between runs.
    --
    -- LAG alone is enough for the transition map: every ordered pair in a
    -- wallet's path is emitted once as (previous -> current), so adding LEAD
    -- would restate the same edges in the opposite direction and double-count
    -- them. LEAD is the same primitive for a forward-looking per-wallet view.
    SELECT
        p.tx_from,
        p.block_time,
        p.entrypoint AS current_entrypoint,
        LAG(p.entrypoint, 1) OVER (
            PARTITION BY p.tx_from ORDER BY p.block_time, p.tx_hash
        ) AS previous_entrypoint,
        LAG(p.block_time, 1) OVER (
            PARTITION BY p.tx_from ORDER BY p.block_time, p.tx_hash
        ) AS previous_swap_time
    FROM per_tx p
    JOIN eligible_wallets e ON e.tx_from = p.tx_from
    WHERE p.notional_usd >= {{min_notional_usd}}
)
SELECT
    previous_entrypoint AS from_entrypoint,
    current_entrypoint  AS to_entrypoint,
    -- Distinct wallets is the honest headline: COUNT(*) alone lets one wallet
    -- that crosses the same pair 500 times read as 500 users.
    COUNT(DISTINCT tx_from) AS wallets,
    COUNT(*) AS transitions,
    -- Median, not mean: one wallet returning three weeks later drags an average
    -- dwell time far past anything a typical user did.
    APPROX_PERCENTILE(
        DATE_DIFF('minute', previous_swap_time, block_time), 0.5
    ) AS median_gap_mins
FROM ordered_swaps
WHERE previous_entrypoint IS NOT NULL
    -- A journey is movement between entrypoints; ignore consecutive self-swaps.
    AND current_entrypoint != previous_entrypoint
    -- Minimum dwell. A wallet that lands on a second entrypoint inside the same
    -- minute did not decide anything; that is automation, and on ethereum/7d it
    -- is what a swap-count cap alone leaves behind -- the top of the unfiltered
    -- table came back as near-symmetric A->B / B->A pairs at a 0-minute median
    -- (pool-to-router and back), which is the shape of in-block bot activity
    -- rather than navigation. Costs the fastest legitimate users; worth it.
    AND DATE_DIFF('minute', previous_swap_time, block_time) >= {{min_gap_mins}}
GROUP BY 1, 2
-- Suppress edges thin enough to be one wallet's habit rather than a pattern.
HAVING COUNT(DISTINCT tx_from) >= {{min_wallets}}
ORDER BY wallets DESC;

Why distinct wallets and a median

Two small choices at the end carry most of the interpretive weight. Counting rows lets a single wallet that crosses the same pair five hundred times read as five hundred users, so the headline column counts distinct wallets and keeps the raw event count beside it as a separate, clearly labelled number. And dwell time is a long-tailed distribution: one wallet returning three weeks later drags a mean far past anything typical, so the query reports a median. Neither choice makes the chart look better, and both make it hold up when someone checks it.

What One Run Returns

Run against Ethereum on 5 August 2026 over a 7-day window, with a $100 minimum trade size, wallets capped at 50 swaps for the week, a one-minute minimum gap between hops, and edges suppressed below 20 wallets. The query returned 103 entrypoint-to-entrypoint edges covering 19,518 hops in about seven seconds. These are the ten largest by distinct wallets:

Names are Dune's decoded contract labels, verbatim. Several of them describe a contract rather than a product (Router02, UniswapV2Factory), which is a useful reminder that a decoded name is not a brand; mapping those to the interface a user actually touched is the job of the entity-resolution ladder, and it is why Unknown Proxy is left standing here instead of being guessed at.

The bot guard earning its place

An earlier pass of the same run used only a swap-count cap (100 for the week) and no minimum gap. It returned 115 edges and 34,674 hops, and the top of the table looked like this:

• UniswapV2Factory → Router02, 2,269 wallets, median gap 0 minutes
• Router02 → UniswapV2Factory, 2,265 wallets, median gap 0 minutes

A near-perfectly symmetric pair, in both directions, with no time passing between the hops. That is not a person moving between two interfaces; it is automated in-block activity, and a swap-count cap alone never caught it because each individual wallet stayed under the threshold. Across the whole first pass, edges with a zero-minute median carried 40% of all hops. Adding the one-minute floor removed them entirely, and that same Uniswap pair fell to 628 wallets at a 2h 14m median, which is a number that behaves like a person deciding something.

The other thing the run says plainly: 58% of the surviving hops still touch an Unknown Proxy on one side. Journey reconstruction inherits whatever the labeling layer knows, and on this window the largest single destination in DEX navigation is a contract nobody has named yet.

What This Does Not Claim

The table above is one query execution on one chain over one week, which is enough to demonstrate the method and not enough to characterise anybody's product. A single seven-day window on Ethereum says nothing about seasonality, nothing about the other three chains, and nothing about any named venue's quality. Within ClearTrace, the live surface today covers routing-edge and wallet-retention views backed by synced tables and a public API; this trace is the analysis query behind the transition map, and it runs on demand rather than as a scheduled pipeline feeding that dashboard.

Two more limits worth stating. Entrypoint resolution inherits whatever the chain's decoded contract set knows, so a genuinely unresolved router stays Unknown Proxy and appears in the map as an unknown node, which on this run is most of the map. And the bot guards are heuristic thresholds, not a classifier: a swap-count cap and a minimum dwell remove wallets whose behaviour is implausible for a person, and they will also drop the fastest legitimate power users along the way. Every threshold is a query parameter, so a reader can move them and watch the answer change, which is why they are exposed at all. The 40% figure above is exactly what moving one of them cost.

What It Shows

Behavioural reconstruction on data that was never designed to answer behavioural questions, with the failure modes handled in the open. The window function is a few lines; the judgment is in collapsing legs before sequencing them, refusing to let bot traffic masquerade as users, keeping each chain's timeline separate, and naming what the output cannot support. That is the same through-line as the rest of ClearTrace: find the signal that is present but not stored, and stop short of what the data will not carry.

What It Proves to a Client

That we can build a behavioural analytics layer on raw, unordered event data, and that we will tell you where it stops being trustworthy. Wallets, users, devices, transactions: wherever records exist but the sequence connecting them does not, the same reconstruction applies, and the same three traps decide whether the resulting funnel describes customers or machines. For an L2 ecosystem or a protocol team, the useful deliverable is a transition map whose top rows are people, with the filtering assumptions written down next to it.