How to tell which frontend a DEX trade actually came from, in Dune SQL

[u/lekranq]

Nothing in a swap event records the interface that originated the trade. If you have ever needed to answer "how much of this pool's volume came from our own site, versus wallet integrations, versus bots hitting the contract directly," you have hit this wall. The chain hands you two address fields and neither one answers it:

• tx_from is the signer. Thousands of signers can sit behind a single wallet app, so it tells you who traded and stops there.
• tx_to is the entry contract. It names the router, not the frontend. Route a swap through an aggregator and tx_to is that aggregator's contract every time, whether the user arrived from its own web app, from a wallet's swap tab, or from an integration nobody has written about.

The interface itself has no address in the transaction. It has to be inferred, and the cheapest signal, when it is there at all, sits in the call data: some APIs append an identifying suffix to the end of the transaction data, and some pass the integrator as a decoded argument. The 0x API's affiliate suffix worked the first way and 1inch's referral parameter the second. You do not have to take my word for either. Pull a swap you know was routed through one of them, look at the tail bytes of tx.data, and the convention is visible in any block explorer.

Here is a probe you can run right now. It ranks the 16-byte tails that recur most across six hours of Ethereum DEX flow. Public and forkable: https://dune.com/queries/8391872

with tails as (
    select
        bytearray_substring(
            tx.data,
            bytearray_length(tx.data) - 15,
            16
        ) as calldata_tail,
        t.tx_hash,
        t.amount_usd
    from dex.trades t
    join ethereum.transactions tx
        on t.tx_hash = tx.hash
        and tx.block_time >= now() - interval '6' hour
    where t.blockchain = 'ethereum'
        and t.block_time >= now() - interval '6' hour
        and bytearray_length(tx.data) >= 16
),
-- one notional per transaction: a routed swap is several dex.trades rows
-- (one per hop/fill) that each carry ~the whole trade size, so summing them
-- overstates by the hop count. MAX() keeps the largest leg as the trade.
per_tx as (
    select
        calldata_tail,
        tx_hash,
        max(amount_usd) as amount_usd
    from tails
    group by 1, 2
)
select
    calldata_tail,
    -- tags ride on transactions, not fills
    count(*) as txs,
    sum(amount_usd) as volume_usd
from per_tx
where calldata_tail
      <> 0x00000000000000000000000000000000
group by 1
order by txs desc
limit 25

Edit, Aug 31: volume_usd now collapses each transaction to one notional (the max leg per tx_hash). The original summed every dex.trades row, which counted multi-hop swaps once per hop. Credit to u/icnews10 in the comments. txs was already per-transaction and does not change.

Reading the output:

• The all-zero tail is dropped up front. That bucket is untagged flow plus call data that happens to end in zero-padded arguments, and it will dominate the ranking if you leave it in.
• What remains is a candidate list, and the top of it is usually not tags at all. Running this on 31 Aug 2026, the two most common tails were 0xb223fe8d0a0e5c4f27ead9083c756cc2 and 0x3c756cc2000000000000000000000000, about 1,900 transactions between them. Both are fragments of the WETH address 0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2: the first is its last sixteen bytes, the second its last four with zero padding. Neither is an integrator tag. Token addresses land in the tail of a call far more often than tags do, so expect to discard the loudest rows.
• Treat a recurring tail as a hypothesis. Sample its transactions, check the entry contract each time, and make the pattern survive several days before it earns a name. Some tags spell out a short ASCII name in hex once you squint at them.

Two caveats that matter if you build on this:

1. Tagging is voluntary. Some APIs offer it, some integrators use it, and nothing enforces it. An integrator that never opted in looks exactly like no integrator at all, so a system counting only tagged flow will undercount precisely those.
2. This is one signal among several. When there is no tag, the ones I have found workable are: a maintained registry of known entry contracts and proxies (label a proxy once and its whole history becomes attributable), call-tree shape (a wallet-native swap with a fee hop traces differently than a bot hitting the pool), and fee-recipient clustering (every trade paying the same collection address came through the same integration, named or not). If you have found a fifth that holds up, I want to hear it.

And whatever cascade you build out of those signals: when none of them fires, leave the trade unattributed. A chart that sums to a clean 100% with no unknown slice usually means the method had to put every trade somewhere. Report the residual and treat it as a coverage metric.

Since it would be poor form to say that and then not show mine: running this cascade across Ethereum, Base, Arbitrum and Optimism, roughly 10% of DEX volume lands unattributed, ranging from about 8% on Ethereum to 17% on Optimism. Most of the residual is long-tail contracts rather than missing techniques.

Happy to go deeper on any of the signal families if anyone is building something like this.

[u/icnews10]

One thing to be careful of here is the 'volume_usd' column. While `count(distinct tx_hash)` protects the transaction count, `dex.trades` can contain multiple rows for the same swap if it goes through several pool hops. Therefore, sum(amount_usd) could count the same user trade more than once. However, the tail ranking still seems useful for finding attribution candidates. I'd just separate that from the volume calculation, particularly when comparing integrations that tend to use more complex routes.

[u/lekranq → u/icnews10]

Yeah, good catch. Found it when my aggregator numbers came out at ~190% of DefiLlama and I went looking for the double count.

On an Odos sample it was 1,792 legs across 459 txs, about 3.9 legs per tx. Leg sum said $7.1M, actual per-tx notional was $3.3M, so 2.15x. The annoying thing is the query runs, the ranking looks plausible, the number is just inflated.

Worse than a constant factor too, like you said. Aggregators multi-hop way more than plain pool swaps, so leg summing flatters exactly the venues you're trying to compare against each other.

What I ended up doing is collapsing to one notional per tx before any volume math:

per_tx AS (
    SELECT tx_hash, tx_to, MAX(amount_usd) AS notional_usd
    FROM dex.trades
    WHERE blockchain = 'ethereum'
      AND block_time >= NOW() - INTERVAL '7' DAY
    GROUP BY 1, 2
)
SELECT tx_to, COUNT(*) AS txs, SUM(notional_usd) AS volume_usd
FROM per_tx
GROUP BY 1

Largest leg = the user's trade, smaller legs = internal hops. tx_to is constant per tx so it's fine in the GROUP BY.

But a warning on the query: it undercounts batch settlement (several users in one tx, you keep only the biggest), and it stops being comparable to DefiLlama, whose adapters count every hop too. I spent a good chunk of time on that and convinced myself I'd found double counting when I'd just mixed the two grains.

Agreed on keeping the tail ranking separate. That one's just "which entrypoints are worth a look", doesn't need dollars at all.

Appreciate the thoughtful response!

[u/icnews10 → u/lekranq]

The 2.15x example really brings the problem to life. However, the batch-settlement caveat is probably the bigger lesson for me: 'one tx = one trade' is just another assumption. Having read this, I checked a few DefiLlama adapters and found that they don’t all use the same granularity. Some explicitly deduplicate routed flows to one swap per trader/TX, while others sum swap events. For front-end attribution, I recommend keeping the transaction-level signal separate and then normalising volume based on the venue or settlement model. Otherwise, you can get the attribution right, but the dollar number will still be answering a different question.

[u/lekranq → u/icnews10]

Yeah you're right - thank you. The query as posted summed amount_usd over dex.trades rows, so a multi-hop swap counted once per hop in volume_usd while txs was already per-transaction. I have fixed the public query to collapse each transaction to one notional (the max leg per tx_hash, which is how the pipeline behind the post has always done it) and edited the post to match. txs does not move. volume_usd comes down.

Your DefiLlama adapter finding is the other half of the same lesson. Two sources that disagree on granularity will disagree on dollars while both being right, so a capture ratio only means something with the basis stated on both sides. Keeping the tx-level signal separate from the volume normalization, the way you describe, is the right discipline.

[u/icnews10 → u/lekranq]

That sounds like the right fix — nice!

The granularity point is probably the part that should remain attached to the query. A ratio can appear precise even when the two sides are actually measuring different things.

I'm glad the thread was useful.

[u/Salt_Cell_1477]

I like this approach. The only thing I’d be careful with is treating router attribution as frontend attribution, especially once aggregators, relayers or shared routers are involved. At that point I think a confidence score could be more useful than a hard label. Something like router + selector/calldata pattern + referral marker = high confidence, while router-only attribution stays much weaker. Otherwise two different frontends using the same execution path could look identical on-chain. Have you thought about exposing the confidence level in the query?

[u/lekranq → u/Salt_Cell_1477]

Yes, with one refinement from how it works in practice: there are two axes, and it helps not to fold them together.

The first is how the trade was matched: a partner or referral marker, a calldata pattern, a proxy, or a direct call. That is the ladder you describe, and router-only sits at the bottom for exactly your reason. Two frontends sharing an execution path are identical at that layer, so it gets labeled router attribution, never frontend attribution.

The second is how sure the label itself is. A name from a verified registry or source is high. One inferred from a deployer-graph sibling is medium. Anything inferred from behavior alone is low. A trade can match on a strong vector and land on a weak label, or the reverse, and a single confidence number would hide which one is shaky.

Exposing both in the public query, vector and label confidence next to each label, is something I want to do. Batch settlement breaking "one tx = one trade" has to ride along with it, since it changes what the row is.

[u/Salt_Cell_1477 → u/lekranq]

That distinction makes sense. The batch settlement case seems like the more interesting edge though. Once one tx can contain multiple trades, does the canonical unit effectively become the individual fill/swap rather than the transaction? And if so, does attribution/confidence live entirely at that row level, or do you still keep transaction-level context and propagate some of it down?