DEV Community: Andrew Maury

The Liquidity Mirage: $3.28B of Reported Liquidity, Backed by 0.0007 ETH

Andrew Maury — Wed, 02 Sep 2026 15:27:40 +0000

Originally published at rantum.xyz, which is the canonical source.

A Uniswap V3 pool on Ethereum mainnet reports $3,286,019,931 of liquidity. The real assets backing it are 0.000739 WETH, which at $2,410.77 per ETH is about $1.78. Both figures are accurate, and the gap between them is the whole story.

This is what turned up while building a much less interesting thing: a browser extension that flags impersonator tokens in DEX listings.

The challenge

Token discovery surfaces have a verification problem that price feeds do not. A ticker is not an identifier. Anyone can deploy a contract whose name() returns "Dai Stablecoin" and whose symbol() returns "DAI", and nothing in the resulting search result distinguishes it from the real one. The contract address does, and the contract address is exactly the field that gets truncated in every list view.

The usual answer is a blocklist of known scams, which is a race you lose: new contracts deploy faster than any list is maintained. The inverse works better. Instead of enumerating what is fake, enumerate what is canonical, keyed on (ticker, chainId), and flag a row that shares a ticker on the same chain with a different address.

Building that check surfaced something more interesting than the check.

One bytecode, two deployers

Searching a handful of blue-chip tickers on DexScreener returned rows that looked like ordinary duplicates. They were not. Fourteen contracts on Ethereum mainnet impersonated nine well-known tokens and ran an identical playbook rather than being fourteen coincidences.

Those fourteen turned out to be a rounding error. Enumerating every contract the two deployer wallets ever created returns 1,031 deployments, 1,005 of which carry that same 5,150 byte bytecode, split almost exactly evenly at 503 and 502. Running the detector described below across all 1,005 finds 184 impersonating a canonical ticker outright, across 143 distinct real tokens, plus 59 more cloning tickers that have several legitimate claimants. The remaining 762 use names no curated list carries, which is ordinary memecoin noise. That is still a floor: only two deployer wallets were enumerated.

Every one of the fourteen returns the same 5,150 byte runtime bytecode, SHA-256 prefix 7ff6feb2ee87face. The tokens they impersonate do not resemble each other at all: real DAI is 7,904 bytes, CRV 4,369, COMP 6,795, PEPE 4,517, every one distinct. Identical bytecode across fourteen supposedly unrelated tokens is a deployment fingerprint, not a coincidence.

Blockscout's contract metadata names the source MintBurnTeamToken, an off-the-shelf mintable template, and attributes all fourteen to just two externally owned accounts, split eight and six. No creation transaction is indexed for any of them, which is consistent with factory deployment instead of direct creation.

The tickers spoofed: DAI, CRV, APE, SHIB and LDO twice each, plus COMP, ENS, GRT and SNX. Nine tickers, fourteen contracts.

The mirage: $3.28 billion from an initialization tick

The impersonation is cheap and common. The reported liquidity is the interesting part. Take the fake DAI pool, 0x90e6...5a87, a Uniswap V3 pool at the 0.3% fee tier, and read its state directly:

balanceOf(fakeDAI, pool)  = 2,506,024,274.84   // worthless token
balanceOf(WETH,    pool)  =         0.000739   // the entire real backing
slot0().tick              =          -75,180   // chosen at initialization
price(token1/token0)      =    0.0005434 WETH  // derived from that tick

A Uniswap V3 pool is initialized at whatever price its creator chooses, because that price is a tick rather than a consequence of deposited capital. A V3 position is then defined by a price range, and that is the part that does the work here.

The Mint event for this pool provides liquidity across the range [-75180, 887220]. The lower bound is exactly the initialization tick, the price the pool still sits at today. The upper bound, 887220, is the highest tick the 0.3% fee tier permits, so the range runs from the chosen price to effectively infinite price. When the price sits at the very bottom of a range, the position is denominated entirely in token0 and requires none of token1 at all.

So the deposit was 2,506,024,276 minted tokens and 0.000000000 WETH. Not a rounding error of WETH. None. The same structure appears on the CRV, SHIB and COMP pools, each opened with a single-sided position and zero WETH. The 0.000739 WETH sitting in this pool today did not come from the deployer; it is the accumulated dust of the daily cron buying its own token, a few thousandths of a cent at a time.

DexScreener values those reserves at the pool's spot price and reports the product as liquidity:

2,506,024,274 tokens x $1.31 = $3,286,019,931 of "liquidity."

The displayed price rounds to $1.31, and reproducing the total to the dollar takes the unrounded $1.3112. That rounding is worth $3.1 million on its own, which is a fair illustration of the scale being discussed.

The pool holds 0.000739 WETH, and that is a hard ceiling: it is the most anyone could ever extract by selling the token back into it, whatever the quoted price says. At $2,410.77 per ETH that ceiling is $1.78. The reported figure exceeds it by a factor of roughly 1.8 billion to one. Nothing is wrong with the arithmetic. The arithmetic is correct on inputs that were manufactured.

The fingerprint repeats, exactly

What makes this tractable to detect is that the operation never varied its parameters. Every one of the six pools sampled shows the same signature: a large reported liquidity, one cent of reported 24 hour volume, and precisely one buy and zero sells. Re-read 24 hours later, on 2026-09-02, all six returned identical figures.

That is not neglect. It is a schedule. The fake DAI pool's full swap history is 103 trades between 24 February and 2 September 2026, and in the last sixty days it traded on forty out of sixty days, thirty-seven of them between 04:00 and 04:59 UTC. The other three sampled pools cluster in the same window. Each trade is dust: roughly 0.012 of the fake token out for about 0.0000066 WETH in, about one and a half cents.

So the "one buy, $0.01 of volume" reading is not a stale cache and not a dead pool. It is a daily cron job buying a fraction of a cent of its own token, which is exactly enough to keep the pair looking traded to an indexer that sorts on recent activity. The listing is maintained.

Ticker spoofed	Pool created	Reported liquidity	24h volume	Buys / sells
DAI	2026-02-24	$3,286,019,931	$0.01	1 / 0
CRV	2026-02-28	$431,462,383	$0.01	1 / 0
ENS	2026-03-07	$389,657,165	$0.01	1 / 0
LDO	2026-02-28	$212,719,545	$0.01	1 / 0
COMP	2026-03-08	$130,141,448	$0.01	1 / 0
SHIB	2026-03-08	$42,017,195	$0.01	1 / 0

Those six pools alone account for $4,492,017,667 of reported liquidity. All six were created between 24 February and 8 March 2026, and all six were still live, still listed, and still returned by ticker search on 1 September 2026, more than six months later.

Where the exposure is, and where it is not

This is not evenly distributed across venues, and it would be sloppy to imply it is. A live ticker search for CRV in the Uniswap interface returns six genuine Curve deployments across six chains, plus yCRV and cvxCRV, and none of the fleet. Searching by contract address is a different matter: the picker resolves any address pasted into it, fleet contracts included. What the curated list governs is what surfaces when a user types a ticker, and none of the fourteen appear on the Uniswap Labs default list.

The exposure sits on aggregators like DexScreener that index every pool by design, where comprehensiveness is the product and filtering is left to the reader. That is a stated design choice and not an oversight, which is precisely why the filtering has to come from somewhere else.

Detecting it without accusing the innocent

The detector is a canonical allowlist keyed on (ticker, chainId), built from independently maintained token lists rather than hand entered: 2,289 tickers and 3,675 ticker and chain pairs. Two decisions carry most of the weight.

The chain key is not optional. Keying on ticker alone flags every legitimately bridged token as an impersonator. The same real PEPE appears on seven chains, the same real CRV on six. A ticker only check would have called all of them fraudulent on day one.

Sources are ranked, not pooled. Curated lists (Uniswap Labs Default, 1inch) decide the canonical address. A permissive source (CoinGecko), which lists essentially every token with a market page, gets no vote on the address. It only reports how many other projects claim the same ticker on that chain. Treating all sources as equal peers deletes the tickers that matter most: PEPE, SHIB, UNI and APE all vanish, because a permissive list carries "Pepe King Prawn" and "El Sapo Pepe" alongside the real thing. Their presence is not evidence that real PEPE is ambiguous.

That second signal then gates the accusation. A red "not the canonical contract" verdict is only issued when every source agrees the ticker has exactly one claimant on that chain. Where a ticker has several legitimate claimants, the verdict degrades to "more than one project uses this ticker, check the address yourself."

That gate exists because the first version did not have it, and was wrong twice on a single page. It flagged Milady Cult Coin, its own project with several million in liquidity and trading since December 2024, for not being Cult DAO. It flagged the wojak token doing $202k a day for not being the Wojak Coin doing $5.5k a day, calling the more actively traded asset the fake. Neither was impersonating anything. Both tickers simply have two real claimants, and the canonical holder of a ticker is a curator's assignment rather than a fact. A measurement layer that cannot tell those two situations apart should not be issuing verdicts on either.

What this does not show

Nobody appears to have fallen for it, and that is worth stating precisely because it is stronger than the usual "no victim flow was traced". Reading every swap on four of the pools, the counterparty is always the wallet that deployed the token: 100 of 100 on the fake DAI pool, 99 of 99 on CRV, 93 of 93 on SHIB, 77 of 77 on COMP. In six months these pools have had no external participants at all. The contracts are live, findable and misrepresented, and so far the only person trading them is the person who made them.

Nor is "one operator" established. The two deployer wallets each service only their own tokens, and they are not linked. Both were touched early by the same address, which looked like a possible controller until it was read: it sends one identical 514 byte message to each of 2,996 distinct recipients, and the message decodes to a freelance designer advertising branding work to token deployers. It is unsolicited marketing, not a funding link.

The two deployer accounts were not deanonymised. The MintBurnTeamToken label comes from public contract metadata and was not independently source verified. Pool economics were measured on six pools and the daily schedule on four; the other 999 contracts are confirmed by bytecode and ticker, not by reading each one's pool.

Every number above was read directly from Ethereum mainnet via eth_getCode, eth_call, pool state and swap logs, or from the public DexScreener and Blockscout APIs, on 2026-09-01 and 2026-09-02. Prices move and pools can be removed, which is why the measurements are timestamped.

Why it matters

Verification and indexing are different jobs, and conflating them scales badly. An indexer that reports what a pool says about itself is not lying. It is faithfully reporting manufactured inputs. The gap between reported and real is where the work is, and closing it takes ordinary discipline: read the underlying state directly, key your joins on identifiers instead of labels, and be willing to return "I do not know" instead of a confident wrong answer.

Fabrication is getting cheaper. Seven ten-thousandths of an ETH bought a $3.28 billion listing that survived six months on a major discovery surface. Independent verification gets more valuable in exact proportion.

The Revert Rate That Lies: Why You Cannot Rank a DEX Metric Across Execution Models

Andrew Maury — Wed, 02 Sep 2026 14:53:54 +0000

Originally published at rantum.xyz, which is the canonical source.

A revert rate answers one question: how often did a swap fail on-chain? It reads like a clean reliability number, and a leaderboard that ranks venues by it looks like a fair reliability ranking. It is not one, because the number only means the same thing across venues that fail the same way.

This is a short account of a measurement trap that is easy to fall into, hard to see once you have, and directly hostile to a benchmark that claims to be neutral. It is also a record of ClearTrace falling into it, shipping the mistake to production, and then building the fix into the pipeline so the invalid comparison cannot be made again.

The same number, two different measurements

Start with how a swap actually fails, because that is what decides whether its failure is countable.

On a router, the user signs and submits the swap. 1inch, Uniswap, KyberSwap, ParaSwap, and Odos all work this way. If the swap fails, it fails on-chain, ClearTrace sees it, and the revert rate is exactly what it looks like: the odds your transaction does not land.

On a batch-auction venue like CoW Protocol, the user signs an order, not a transaction. A solver wins the batch and submits one settlement transaction for everyone in it. An order that cannot be filled, from a bad price, no solver, or expiry, simply never becomes a transaction. The user's failure is invisible on-chain by construction.

On an intent venue like 1inch Fusion, the shape is the same. The user signs an intent, a third-party resolver fills and submits it, and an unfilled or expired intent leaves no trace on-chain.

So the on-chain revert rate of a batch-auction or intent venue is structurally near zero, and it is near zero regardless of how reliably the venue actually fills. It is not measuring reliability. It is measuring settlement.

The direction of the error is the dangerous part

Measured live on Ethereum over the trailing seven days (September 2026), CoW's revert rate reads 0.53% and 1inch's Limit Order Protocol reads 0.56%, while the routers 1inch and KyberSwap read 1.11% and 1.75%. Sort that single column and the two off-chain venues sit at the top as the most reliable.

That ordering is a category error, and the specific way it is wrong is what makes it corrosive. It flatters exactly the venues a third party can measure least, because their failures never reach the chain to be counted. A benchmark that systematically favors the venues it cannot see is not neutral, whatever its intentions. That is the fastest way to lose the one thing a third-party benchmark is for.

The discriminator is who submits, not where it settles

The tempting shortcut is "off-chain settlement means no reverts." It is wrong in a way that matters. In classic request-for-quote, the market maker signs a quote, but the taker still submits the transaction carrying that signature. An expired quote or a short maker balance therefore does revert on-chain, in the user's own transaction. A venue like that is router-shaped for measurement even though its price came from off-chain.

So the rule is not about where the price originated. It is a single question: does a user's failed swap become a transaction anyone can count? If yes, the venue is a router and its revert rate is comparable. If no, its revert rate is structurally near zero and is not a signal. If the answer is not established, or the venue runs more than one model, it is unclassified and never ranked.

The taxonomy, and the guard that enforces it

ClearTrace encodes those four models as one taxonomy that every consumer imports from a single module, so the rule cannot drift between the dashboard, the API, and the pre-send checks. A short excerpt:

# Execution models
ROUTER, BATCH_AUCTION, INTENT, UNCLASSIFIED = "router", "batch_auction", "intent", "unclassified"

# The ONLY model whose tx-level revert rate measures user reliability, because it
# is the only one where the transaction that fails belongs to the user.
REVERT_COMPARABLE_MODELS = frozenset({ROUTER})

class CrossModelComparison(ValueError):
    """Raised when code tries to rank/compare revert rates across execution models."""

def rank_by_revert(rows, rate_key="revert_rate_pct", project_key="project", strict=True):
    """Rank venues by revert rate, best first, WITHIN ONE EXECUTION MODEL.
    Refuses to return a list that mixes models, because such a list ranks nothing."""
    rankable, refused = [], []
    for r in rows:
        (rankable if is_revert_comparable(r.get(project_key)) else refused).append(r)
    if strict and refused:
        raise CrossModelComparison(
            "refusing to rank revert rates across execution models: these venues do "
            "not submit the user's transaction, so their on-chain revert rate is not "
            "comparable to a router's.")
    rankable.sort(key=lambda r: r.get(rate_key))
    return rankable

Every published row is stamped with its execution_model and a revert_comparable flag. Those two fields are live on the public API today, so a downstream consumer, whether the ClearTrace dashboard or an outside script, can never read a batch-auction revert rate as reliability without being told otherwise. And a compare page does not print a suppressed head-to-head as a blank. It replaces the number with the reason: a sentence explaining that the two venues settle differently, that the off-chain venue's on-chain revert rate is therefore structurally near zero regardless of how reliably it fills, and that putting it beside a router's would overstate its reliability rather than measure it. The refusal is legible, not silent.

Under-claiming is the default

A venue ClearTrace has not established is unclassified, and unclassified never ranks on revert. Absence of evidence is not evidence. A venue that runs both models is also unclassified: Bebop settles taker-submitted quotes, where a failed fill reverts in the user's own transaction, and solver-submitted intents, where failures never land. A rate over that mixed population answers no question, so ClearTrace does not rank it until the two are split. The same discipline applies to a venue like Tokenlon, whose very low on-chain rate would top any reliability ranking, which is exactly why it needs evidence about who submits rather than an assumption.

Why the guardrail exists

None of this is hypothetical. ClearTrace once shipped the sentence "CoWSwap's revert rate is 0.04% versus ParaSwap's 1.41%" to production, in body copy and in indexed FAQ schema. That is a 35x reliability claim, and it was purely an artifact of settlement: CoW's number was structurally low because unfillable orders never became transactions, not because it executed 35 times more reliably than a router. A flattering wrong number costs a neutral benchmark its credibility exactly as a damaging one does. The taxonomy, the CrossModelComparison guard, and a pre-send claim gate that blocks any cross-model revert claim before it leaves the building are the response to that specific mistake.

What it proves

The rare skill here is not a model or a scraper. It is knowing which comparison is invalid and building the refusal into the pipeline, so a metric that looks universal is only ever computed over the population where it means something. Finding signal in adversarial data includes declining to manufacture signal that is not there, even when the invalid version would make the product look more complete.

For a foundation, a grant program, or a protocol team spending real money against a metric, that is the difference between a dashboard and a source of record: a benchmark whose numbers survive contact with the teams they describe, because the one comparison that would embarrass it is blocked in code rather than caught in review.

The full case study is at rantum.xyz. ClearTrace is at cleartracedata.com.

Andrew Maury is the founder of Rantum, a senior data science and ML studio that turns messy, fragmented, and adversarial data into models, APIs, and products that ship. He has 6+ years in crypto: he previously scaled data at 0x Labs, contributed to Dune's Spellbook, and advised the Uniswap Grants Program.

Where Did This Trade Come From? Attributing DEX Volume in Dune SQL

Andrew Maury — Thu, 27 Aug 2026 15:14:59 +0000

This is an adapted excerpt from Chapter 6 of my book, The DeFi Data Quick-Start: Reading On-Chain Truth with Dune Analytics. The book goes from your first Dune query to a dashboard you can defend. This chapter is the hardest problem in it.

The one field that isn't there

A row in dex.trades tells you the chain, the venue, the tokens, the amounts, and the transaction hash. It tells you everything except the one field half the money in DeFi wishes existed: where the trade came from. No origin: uniswap-web-app, no frontend: metamask-swaps, nothing.

That missing field is worth a great deal. Incentive programs, business development deals, and market-share claims all hinge on an answer nobody can read directly off the chain. Reconstructing it is the discipline called attribution, and it is mostly a matter of knowing where to look.

Two identical rows, two different worlds

Consider two rows from dex.trades: same day, same pool, each a swap of 2 WETH for USDC minutes apart. In the curated table they are nearly indistinguishable.

Behind the first is a person. She opened her wallet app, tapped the built-in swap button, and accepted a quote. Her wallet's backend called an aggregator, the aggregator picked the route, and the trade settled through a router into the pool. Four parties touched that flow, and if anyone deserves credit for bringing the user, it is the wallet whose button she tapped.

Behind the second is a bot: a market-making program calling the pool contract directly. No frontend, no human, no quote, just an automated rebalancing loop.

A volume dashboard counts these two rows identically. An incentive program that rewards "adoption" pays them identically. Attribution is the work of telling them apart, at scale, from public data alone.

Why the obvious answers are wrong

The chain gives you two address fields per transaction, and it pays to see exactly why neither answers the question.

tx_from is the wallet that signed the transaction. It tells you who traded, not which interface brought them. Ten thousand different tx_from addresses might all be customers of a single wallet app.

tx_to is the first contract the transaction called. This is the field naive dashboards actually use, and it systematically credits the wrong layer. It names the router, the plumbing, not the frontend. Every trade through a given aggregator's contract shows the same tx_to, whether the user came from the aggregator's own site, a wallet's swap tab, or an integration none of us has heard of. Attributing by tx_to is like attributing retail sales to the delivery truck.

The true origin, the app or interface where the user actually was, usually never appears on-chain as an address at all. It has to be inferred from the shape of the transaction. Different origins leave different fingerprints. There are four kinds in practice, and the first is nearly free when it is present.

When the trade signs its own work

Sometimes a transaction carries more than the trade itself. A few APIs append a few identifying bytes to the end of the call data: a tag the contract never reads, riding along to say "this order was built by integration X." The 0x API's affiliate suffix worked this way. Others pass the integrator as an explicit argument that the contract decodes, the way 1inch's referral parameter routes referral fees. Both land on-chain, permanently, in every block explorer.

Where these tags exist, attribution is one join away: extract the marker, join to dex.trades on the transaction hash, and map tags to names. Here is the appended-tag form as a runnable probe. It ranks the most common 16-byte call-data tails across a few hours of Ethereum DEX flow:

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
)
select
    calldata_tail,
    -- tags ride on transactions, not fills
    count(distinct tx_hash) as txs,
    sum(amount_usd) as volume_usd
from tails
where calldata_tail
      <> 0x00000000000000000000000000000000
group by 1
order by txs desc
limit 25

Run it and the tagging convention stares back at you. The query drops the all-zero tail up front, because that bucket is mostly untagged flow and call data that ends in zero-padded arguments. What remains is candidates. When I ran this in July 2026 while drafting the book, one high-frequency tail held the hex spelling of a short ASCII name with a counter beside it: an integrator marking its own orders in plain sight. Run the probe yourself and you will find it, or whoever is tagging this month.

One caution before you attribute anything to a tail. It can also be the end of an ordinary function argument, since token addresses produce repeating fragments too. Treat a candidate tag as a hypothesis. Pull a handful of its transactions, check the entry contract, and confirm the pattern holds across several days before it earns a name.

The real limit is the word voluntary. Tagging is a convention some APIs offer and some integrators use. It is not a standard, and it is not enforced. Absence of a tag is not evidence of absence of a frontend. A system that counts only tagged flow will undercount exactly the integrators who never opted in, and overstate the "direct" share by the same amount.

The other three techniques, in brief

When a trade carries no tag, three more techniques pick up the flow:

Known-contract registries. Routers, aggregator entry points, wallet swap contracts, and custom proxies all have stable addresses. Label one once, and every past and future trade through it becomes attributable retroactively. A single proxy label can reclassify millions in volume. This is why ClearTrace tracks over 172,000 attributed contracts across Ethereum, Base, Arbitrum, and Optimism: the unlabeled long tail is where misattribution hides.
Trace fingerprints. The internal call tree has a shape. A protocol's own frontend produces a compact tree, a wallet-native swap adds a fee hop and an extra transfer, an aggregator split fans across several pools, and a bot calling the pool directly shows almost no tree at all. No single shape is definitive, but across thousands of transactions from one entry point they classify with high confidence.
Fee-recipient clustering. Most frontends skim a small fee to a collection address as part of the swap. That address is a signature: every trade paying the same recipient came through the same integration, named or not. I wrote a full walk-through of this one on its own, Who Got Paid: Identifying Anonymous DEX Frontends by Their Fee Recipient.

The part most dashboards skip

The four techniques combine into a cascade, strongest evidence first. One sensible ordering: explicit call-data tag, then registry match, then trace fingerprint, then fee-recipient cluster. A real system tunes that order to its own confidence in each signal, and screens out known bot and MEV contracts before classifying anything. And then the step that matters most:

If none of them fires, leave the trade unattributed.

That is the design, not a failure mode. A neutral attribution system reports its unattributed share instead of forcing every trade into a bucket, and it treats that residual as a headline number about its own coverage. When you see an attribution chart that sums to a clean 100% with no "unknown" slice, you are looking at a method that either got very lucky or made something up. No serious system attributes everything; a real one measures its own coverage and reports the gap.

So here is ClearTrace's gap, for the record. As of August 2026, roughly 10% of DEX volume across Ethereum, Base, Arbitrum, and Optimism sits in the unattributed bucket, ranging from 8% on Ethereum to 17% on Optimism. The shape underneath is the interesting part: nearly 99% of tracked contract rows are unnamed, while nearly 90% of volume is named. The head is easy. The long tail is the job.

State the method, show the denominator, publish the residual.

The DeFi Data Quick-Start is on Amazon now, in Kindle and paperback. The book's runnable queries and a live dashboard are on the free companion page at rantum.xyz/quickstart.

Who Got Paid: Identifying Anonymous DEX Frontends by Their Fee Recipient

Andrew Maury — Thu, 20 Aug 2026 17:40:08 +0000

Originally published at rantum.xyz, which is the canonical source.

Most swaps do not announce their origin. A meta-frontend, a wallet's in-app swap tab, and a Telegram trading bot can all route through the same public router, emit the same Swap event, and leave calldata that looks generic. Three of ClearTrace's four attribution vectors work on routing evidence: was the entrypoint a known aggregator, did the user call a pool directly or through a proxy, is there a fingerprint appended to the calldata. When all three come up empty, the transaction is anonymous by every structural measure.

But an anonymous frontend is usually still a business, and a business takes a cut. When that cut is taken inside the swap transaction itself, the way affiliate and referral skims are, it has to move on-chain where the receipt records it. That is the evidence this vector reads. A frontend that sweeps its revenue later, or monetizes off-chain entirely, leaves nothing here. And one dependency shapes everything downstream: finding who got paid is mechanical, but naming them requires a label somebody has already attached to that address.

The problem is that the cut does not identify itself. There is no fee standard on Ethereum. Individual projects emit their own fee events, LiFi's collector contract logs a FeesCollected event a decoder can subscribe to, but nothing generic marks one token movement as revenue and another as settlement across the long tail of routers this vector exists for. A frontend's revenue is an ordinary ERC-20 Transfer log sitting in a receipt beside the swap's own transfers, structurally identical to them. You cannot decode it, because there is nothing to decode. You can only work out which transfers are not part of the swap and see who is left.

Trap 1: identification by exclusion puts every error in the exclusion set

Turning "which transfer is the fee" into "which transfers are not the swap" is the move available here, and it relocates all of the risk into the exclusion list. Every category you forget to exclude becomes a false frontend. The user receiving their own output tokens is not a fee. The contract that was called is not a fee. A pool receiving the input side of the trade is not a fee. A token contract receiving its own token, which some fee-on-transfer designs do, is not a fee. A burn address is not a fee. Miss any one of those and the vector confidently reports a counterparty as the frontend that originated the trade.

Trap 2: a fee sink is a signal in one direction only

This one is not obvious until it breaks something. Once a candidate recipient is found, its identity comes from a resolved label, and the labels contain infrastructure names as well as product names. A contract labelled as a fee vault is strong evidence when it is receiving the skim: something paid it, and that something is the monetization path you are trying to name. The same label on the contract the user called means the opposite, or rather it means nothing, because a fee vault is not a user interface and treating it as one invents a frontend out of plumbing.

So the same marker has to be a positive signal in one position and no signal in the other. In the taxonomy this is a single boolean, as_fee_recipient, threaded through the name classifier to select which exclusion set applies. It is one flag standing in for a real asymmetry: evidence about who got paid does not transfer to evidence about who was called.

Trap 3: getting paid is not the same as being the frontend

Even a clean recipient with a clean label is indirect evidence. Referral programs pay addresses that belong to partners rather than to the interface. A single fee address can serve several products from the same team. An affiliate skim names whoever holds the affiliate relationship, which is often but not always the surface the user actually touched. The vector answers "who monetized this trade," and that is a strong hint about the frontend rather than a proof of it.

The implementation

# app/attribution_kernel.py: the fee-recipient vector.
# ERC20 transfers to an address that is not the user, not the called
# contract, not a pool, not a token contract, and not a burn sink —
# i.e. a party skimming a fee out of the swap.
excluded = {tx_from, tx_to} | BURN_ADDRESSES | pools
token_contracts = {t[0] for t in transfers}
fee_recipients = []
seen = set()
for token, recipient, raw_amount in transfers:
    if not recipient or recipient in excluded or recipient in token_contracts:
        continue
    if recipient in seen:
        continue
    seen.add(recipient)
    fee_recipients.append({
        "address": recipient,
        "name": resolve_name(recipient),
        "token_contract": token,
        "raw_amount": str(raw_amount),  # exact; no USD/decimals applied
    })

Two decisions in that block are worth pulling out.

The amount is stored exactly and never converted. raw_amount is the integer from the transfer's data field, kept as a string, with no token decimals applied and no USD price attached. That looks like an omission and is a refusal. Converting it requires the token's decimals and a price at that block, and both are lookups that can fail quietly and produce a number that is wrong by orders of magnitude while still looking plausible in a table. Attribution does not need the fee's value.

Recipients are deduplicated within a transaction. A single address can receive several transfers in one swap, on a multi-hop route or when both sides of a trade pay the same collector. Reporting it repeatedly would imply several fee events where there was one relationship.

The confidence tier, and the bug that set it

A matched recipient is handed to the frontend taxonomy, which resolves the label into a kind (wallet, frontend, MEV bot, or nothing) and returns a bucket with an explicit confidence. A calldata-suffix match returns high. A fee-recipient match returns medium, always, by construction: indirect but specific, which is exactly what an affiliate payment is.

The tiering earns its keep, and the repository records what happened when the name matching underneath it was too loose. An earlier version treated 0x as a plain substring marker for the 0x protocol. But 0x is also the prefix of every hex address, and the labeler routinely embeds addresses in names, rendering them as Wintermute (0x51c7…8ac2) or emitting heuristic labels like Ethereum First Funder: 0xa4aF…. Every such label matched. The comment in the source states the damage plainly: any suffix-tagged transaction carrying any non-MEV, non-wallet label was booked as a named frontend at high confidence. One loose substring rule silently promoted a large class of unknowns into confident, named, wrong attributions.

The fix was a stricter entity pattern, but the durable lesson is about tiering. A system that only emits identities cannot be audited after a matching bug. A system that emits identities with the evidence class attached lets you find every attribution that rested on the broken rung and re-evaluate exactly those.

Under-claiming, in code

The taxonomy sorts every transaction into one of five buckets: named frontend, aggregator API direct, wallet embedded, bots and MEV, or unattributed. The interesting case is a transaction that carries a calldata fingerprint proving some frontend tagged it, where the identity cannot be resolved. The obvious move is to guess from the router. The implemented move is to return unattributed with a fingerprinted flag set, so the leaderboard can show "fingerprinted, unidentified" as its own quantity rather than folding it into either a named frontend or the raw unknown pile.

What one real run returns

Method pieces are cheap without a run, so here is one: the 50 most recent Ethereum swaps in ClearTrace's synced dex.trades sample, a seven-minute window from 2026-07-01, receipts refetched from an archive node on 2026-08-20 and pushed through the kernel in receipt-only mode with ClearTrace's own label store answering resolve_name. All 50 classified. On 39, at least one transfer survived the five exclusions. The calldata-suffix vector settled 23, the aggregator and bot rungs took four each, fourteen resolved nothing, and the fee vector decided the final attribution on five.

Then we inspected the five, and this is the part that earns the confidence tier. Every one rested on a label attached to routing infrastructure rather than to a monetizing interface. Two resolved through an adapter contract whose name contains a venue string, so the frontend marker matched plumbing. The three we dissected transfer by transfer were not fees at all. In a $4,165 stablecoin swap routed through TransitSwapRouterV5, the surviving recipients were a market-making counterparty that received 2.655 WETH and paid back 4,165 USDC, a routing contract that both received and forwarded funds, and an address carrying an aggregator label that received the full trade notional. The taxonomy walked the list, hit the aggregator label, and booked a named frontend at medium confidence. In a $172k swap, three surviving recipients received the identical amount, which is not three fees, it is the notional in flight.

So the run's honest summary: in receipt-only mode, the exclusion set cannot tell a fee from a hop. Everything in a multi-hop route that is not the user, the entry contract, a pool, a token contract, or a burn survives, wearing whatever label it has. The vector found the route, not the revenue. The structural fix is known and not yet built: exclude every address that also sends within the same receipt, or use trace data to remove the whole call path. Neither exists in the kernel today. This run is the argument for building both.

What the tier system did do is exactly its job. All five wrong names went out the door marked medium and fee_recipient, which means one query finds every attribution resting on this rung, and re-evaluating them after the exclusion fix touches nothing booked on suffix or aggregator evidence. An untiered system would have published the same five names as flat facts and left no seam to find them by.

And that is also the honest answer to where this rung lives in production until the fix lands. The receipt-only kernel serves single transactions on demand, with the method and confidence in every response, and feeds no leaderboard. The vector's SQL twin, which does feed the aggregate dashboards, already treats fee-recipient volume as an overlay rather than as truth: the query credits the full trade amount to every qualifying intermediate, reports the result as its own column, and never folds it into the conserved volume total, with a comment in the repo measuring the over-crediting it would otherwise cause at roughly 80% of an Ethereum total. The rung stays because a quarantined hint is still a hint; nothing it produces reaches a published total.

What this does not claim

It does not cluster frontends by fee rate. The obvious next step from "who got paid" is "how much, as a rate," on the theory that a consistent basis-point cut would group several anonymous routers under one operator. That is not implemented, and the reason is in the design above: the vector deliberately holds the amount as a raw integer, so a rate would require the decimals-and-price conversion it exists to avoid, applied to both the fee and the trade notional, before any two swaps could be compared. A fee rate is also weak identity evidence in a way an address is not: interface fees cluster around a handful of conventional sizes, so a rate is a habit shared across unrelated products, where an address belongs to exactly one operator. The address is the identifier.

It also inherits every limit of the labeling layer beneath it. The vector can isolate a recipient cleanly and still return nothing, because there is no label for that address, and the run above adds the sharper failure: when the store's labels are attached to infrastructure, or simply wrong, the vector inherits those errors at medium confidence. Whatever it names, it names because someone already labelled the collector, so on genuinely anonymous frontends it can only surface what the label store already holds, and on this sample it correctly named none. And the exclusion set is a fixed list of five categories, not a learned classifier: a fee routed through an intermediate contract that resembles a pool, or paid in a later transaction rather than inside the swap, is invisible to this pass by construction.

Nothing in a swap receipt is labelled as revenue. The fee is legible only as the transfer left over once everything structural has been ruled out, so the exclusions are the entire product, and every category they miss walks into the output wearing a name. The run above is that account for this vector, published rather than filed.

The full case study, including the taxonomy cascade and what this proves to a client, is at rantum.xyz. ClearTrace is at cleartracedata.com.

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

Andrew Maury — Wed, 05 Aug 2026 16:04:07 +0000

Originally published at rantum.xyz, which is the canonical source.

A blockchain records what happened, but not the story that connects it. Every DEX trade is a row in a wide, flat table: an address, a venue, a timestamp, an amount. There is no session, no user ID, no ordered clickstream. So when someone asks an ordinary product question, "after a user swaps on our frontend, where do they go next, and where did they come from?", 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 its 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 the query does

A per-chain reconstruction 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:

From entrypoint	To entrypoint	Wallets	Hops	Median gap
`Unknown Proxy`	`UniversalRouter`	935	1,233	2h 39m
`UniversalRouter`	`Unknown Proxy`	893	1,165	3h 39m
`Unknown Proxy`	`Router02`	765	1,278	2h 30m
`Unknown Proxy`	`UniswapV2Factory`	762	1,216	2h 14m
`UniswapV2Factory`	`Unknown Proxy`	720	1,210	3h 49m
`Router02`	`Unknown Proxy`	716	1,176	3h 19m
`UniswapV2Factory`	`Router02`	628	1,112	2h 14m
`Router02`	`UniswapV2Factory`	619	1,104	2h 12m
`FWA`	`UniversalRouter`	292	506	6h 50m
`Unknown Proxy`	`DexRouter`	285	379	3h 29m

Names are Dune's decoded contract labels, verbatim. Several describe a contract rather than a product (Router02, UniswapV2Factory), a useful reminder that a decoded name is not a brand; mapping those to the interface a user actually touched is a separate entity-resolution problem, 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.

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.

Why this generalises

The technique is about ordering, not about blockchains. Wherever records exist but the sequence connecting them does not (wallets, users, devices, transactions), the same window-function pass reconstructs it, and the same three traps decide whether the resulting funnel describes customers or machines. Collapse the rows that represent one action, exclude the automated actors, and keep separate timelines separate. The reconstruction is the easy half.

Andrew Maury is the founder of Rantum, a senior data science and ML studio that turns messy, fragmented, and adversarial data into models, APIs, and products that ship. Recent work: ClearTrace (neutral on-chain execution intelligence) and AddressIntel (predictive real-estate intelligence).

The Dashboard That Lied: Why DeFi Volume Isn't the Same as Adoption

Andrew Maury — Tue, 28 Jul 2026 14:36:39 +0000

This is an adapted excerpt from the opening of my new book, The DeFi Data Quick-Start: Reading On-Chain Truth with Dune Analytics. If it makes you want to run the queries yourself, that is the whole idea.

The dashboard that lied

Picture a scenario that plays out somewhere in DeFi every quarter.

The foundation behind an L2 launches an incentive program: trade on our network, earn rewards proportional to your volume. Six weeks in, the growth dashboard looks spectacular. Daily DEX volume has tripled, unique trading wallets are up 40%, and someone drafts a blog post about "organic adoption."

Then someone on the data team pulls the underlying trades. A third of the new volume traces back to a few dozen wallets trading the same two tokens back and forth with each other, dozens of times an hour, at sizes calibrated to just clear the rewards threshold. Another slice is bot flow whose activity scales with volume mechanically. Much of what remains routes through aggregators, so nobody can actually say which frontend, if any, deserves credit for bringing the user.

The chart was not wrong. Every transaction on it really happened, and every one is publicly verifiable by anyone with a block explorer. The chart was still a lie, because "volume" was standing in for "adoption," and on-chain those are very different things.

The transparency paradox

Blockchain data is strange: it is at once the most transparent dataset in finance and one of the easiest to misread.

In traditional markets, data arrives pre-interpreted. An exchange reports volume through regulated channels, an auditor signs the books, a vendor cleans and labels the feed before you see it. You trust a chain of institutions, and in exchange the numbers come with meaning attached.

On-chain, the trust model is inverted. You do not have to trust anyone, because every transaction is public, permanent, and independently verifiable. But nothing arrives labeled. The chain records what happened with perfect fidelity and says almost nothing about why it happened or who was really behind it. It is a flawless court stenographer that transcribes every word and identifies none of the speakers.

The specific gap that matters: when a swap executes on a decentralized exchange, no standard field announces where the trade came from. Nothing says "this user came from the Uniswap web app" or "this was a wallet's built-in swap feature" or "this is a market-making bot talking directly to the contract." Everything except the raw movement of tokens has to be inferred.

Skip that inference and just count, and three kinds of flow masquerade as adoption.

Wash trading is a trade with yourself: one entity controls both sides and swaps an asset back and forth to be counted trading, not to trade. Anywhere volume is rewarded, and round-tripping costs a fraction of a cent, washing becomes a yield strategy and people industrialize it. A volume counter cannot tell it from real demand, because it counts every leg the same.

Proxy routing is subtler, because the trades are usually real. Most DEX flow passes through aggregators, routers, wallet swap buttons, and custom proxy contracts before it lands in a pool. A naive dashboard sees the router as the trader, credits the wrong layer, and can tally the same order two or three times as it moves through the stack. The interface that actually brought the user is often invisible on-chain.

MEV-farm flow is the perverse one. In a sandwich attack a bot buys right before a user's swap, lets the user execute at a worse price, and sells right after. Three transactions land where there was one real trade. A single instance of a user being exploited registers as a tripling of activity, so a venue with heavy sandwich activity shows inflated volume precisely because its users are getting worse execution.

What neutral measurement actually requires

If dashboards can be this wrong on data this transparent, honest measurement takes four commitments. They sound obvious. Few published DeFi metrics survive all four.

Independence from the measured party. The methodology is not controlled by anyone with a stake in the result. That is the difference between an audit and a press release.
Stated, reproducible methodology. Which trades counted, which were excluded and why, how origin was assigned, what the denominator is. On-chain, anyone can re-run the query and check your work, so faith is not required.
The same ruler for everyone. One standard across every venue, even when the result is unflattering, measured against an external baseline rather than each venue's self-reported one.
Honest denominators. State the base rate and pick the denominator before seeing which one tells the better story.

That is the real sense in which on-chain data is different. Not that it is harder to misread than other data, but that for once you can check. The book exists to make you the person who checks.

What the book does with it

The DeFi Data Quick-Start is short and hands-on. In about a hundred pages it goes from your first query against live DEX trades to a dashboard you can defend: navigating the data stack from raw nodes to curated tables, attributing volume to the frontend that actually originated it, detecting sandwich attacks from in-block ordering, and measuring execution quality against a neutral price baseline. Every query in it was run against live on-chain data. The methodology draws on years building data infrastructure at 0x Labs and on ClearTrace, the execution-intelligence system we built to attribute DEX flow across Ethereum, Base, Arbitrum, and Optimism.

If you want to stop taking on-chain numbers at face value, that is what it is for.

The DeFi Data Quick-Start: Reading On-Chain Truth with Dune Analytics is on Amazon in Kindle and paperback, and more is on the author page. The book's queries, runnable and forkable, plus a live dashboard, are free on the companion page at rantum.xyz/quickstart.

If you read it and it is useful, an honest review on Amazon helps a new title like this get found. No pressure either way. Leave a review.

Andrew Maury is the founder of Rantum, a senior data science and ML studio that turns messy, fragmented, and adversarial data into models, APIs, and products that ship.

What DEX Volume Actually Measures — and Why Attribution Has to Come First

Andrew Maury — Fri, 24 Jul 2026 12:00:39 +0000

Originally published at rantum.xyz, which is the canonical source. The full preprint is on Zenodo, DOI 10.5281/zenodo.21513262.

Decentralized exchange volume is one of the most-cited numbers in crypto. Liquidity-mining programs pay against it. Layer-2 foundations report it as adoption. Grant committees read it as evidence a product is being used. It is treated as a proxy for real people doing real trades.

It is a weak proxy, for a structural reason: the trade-execution infrastructure everyone shares exposes no standard way for a frontend to announce who it is on-chain. A wallet-native swap widget, an aggregator, an institutional smart contract, and a meta-router can all produce the same kind of settled trade, and none is required to leave a signature saying which one it was. Once volume is not tied back to a true origin, it is trivially inflated — by wash trading, proxy routing, and MEV-adjacent flow — and any incentive program built on top of it rewards the wrong behavior.

I wrote a preprint on the attribution methodology we built for ClearTrace, a neutral third-party execution-intelligence system covering Ethereum, Base, Arbitrum, and Optimism. This is the plain-language version; the full methodology, validation, and limitations are in the paper.

The problem stated plainly

If you cannot say which frontend produced a trade, you cannot say whether the volume is organic. And you usually cannot say which frontend produced a trade, because that information is not natively recorded anywhere. Attribution is the missing first step every downstream number quietly depends on.

Four independent heuristics

The paper describes four ways to recover frontend origin from raw transaction and trace data. They are deliberately independent — each fails on different trades, so where several agree the signal is strong, and where they disagree you have found something worth a closer look.

Calldata-suffix trapping — many frontends append an identifying tag to the end of the transaction calldata. Where it exists, it is the cleanest signal available.
Proxy-router mismatch detection — when the contract that appears to send a trade is not the one that actually originated it, the mismatch itself is evidence of routing.
Multi-hop origin tracing — following a trade back through intermediary contracts to the interface a user actually touched, rather than stopping at the last hop.
Fee-recipient clustering — grouping trades by where the fee ends up, which tends to cluster by frontend even when nothing else does.

No single heuristic is trusted on its own. The point is the ensemble.

Attribution first, then execution quality — kept separate

On top of the attribution layer sits an execution-quality layer that benchmarks the price a trade actually got against a volume-weighted average price (VWAP) oracle. That quote-accuracy score is kept deliberately separate from a sandwich/MEV-exposure score. They answer different questions — "did you get a fair price?" and "were you exposed to predatory ordering?" — and blending them into one number hides more than it reveals.

What the paper reports

Rather than assert the method works, the preprint reports what happened running it in production:

Validation from an in-production quote-accuracy sampler, with 112 of 168 tracked cells reaching statistical significance over a 9.6-day window.
A worked hypothesis reversal: a major aggregator that looked strong until additional data revealed order-size-dependent quote degradation.
A structural finding on Arbitrum: the near-zero sandwich-attack count there is genuine structural immunity, not a coverage gap, confirmed by reading detector active-days alongside the raw victim count and consistent with Arbitrum's first-come-first-served private sequencing. It is a distinction that is easy to get backwards.

It also states the limitation plainly: this is single-team validation, and the paper argues for treating cross-frontend attribution as a first-class, auditable input to incentive design rather than an afterthought bolted onto a volume dashboard.

Read the paper

The full preprint — methodology, validation, and limitations — is on Zenodo under DOI 10.5281/zenodo.21513262, licensed CC-BY 4.0. The abstract and citation live at rantum.xyz/research, and the methodology is deployed in production at cleartracedata.com.

Naming the “Unknown Proxy”: On-Chain Entity Resolution With a Cheapest-First Ladder

Andrew Maury — Wed, 22 Jul 2026 16:08:47 +0000

On-chain attribution has a stubborn tail. You can trace DEX volume back to the contract that carried it, and still be left staring at the most important rows labeled as nothing more useful than Unknown Proxy (0x…). Those anonymous entries are the flow that matters most: institutional desks, custom enterprise frontends, and wallet-native swap extensions. This is how ClearTrace turns them into named entities, cheaply, without guessing, and without burning paid API credits on answers a free source already holds.

The challenge

ClearTrace attributes DEX volume to the frontend that originated each trade, across Ethereum, Base, Arbitrum, and Optimism. But a single unnamed proxy can sit near the top of a chain’s volume table and quietly distort the entire picture of who is actually transacting.

No single data source names them all. Etherscan misses verifications that Sourcify holds. A proxy hides the implementation that carries its real identity. The same router, redeployed byte-for-byte across four chains, looks like four unrelated unknowns. Paid entity APIs would name a chunk of it, but running them across every unknown, on every sync, burns credits fast.

The hard part is resolving each address to the best available label without paying for answers a free source already holds.

What we built: a resolution ladder

For each unknown contract, ClearTrace walks a fixed sequence of sources ordered cheapest-and-most-authoritative first, stopping at the first real name. Roughly a dozen rungs, from a free eth_call to paid entity APIs tried last. The order itself does most of the work: by the time an address reaches a paid source, the free rungs have already named the vast majority of volume.

Ground truth first: the deployer’s own list. The single highest authority is a protocol publishing its own deployment addresses. This rung was added after a hard lesson: a contract’s absence from a third party’s decoded set is not evidence that it is unofficial. When the deployer publishes the addresses, that file is the answer, and it outranks every heuristic below it.

On-chain registries. Some protocols track their own deployments in on-chain registries. A single free eth_call (for example, an ownerOf against the 0x Settler deployer's ERC-721) returns the currently active contract for a feature. Authoritative, no third-party API.

Free local and public sources. Intel tables already in the local database, then the Etherscan v2 multichain API, then the keyless public mirrors Sourcify and Blockscout, which frequently hold verifications Etherscan does not.

Structural resolution. When no directory has a name, the bytecode does. Identical normalized runtime bytecode to an already-named contract on any chain inherits its label. Proxies are followed to their implementation via the EIP-1167 minimal-proxy target or the EIP-1967 implementation and beacon storage slots, and the proxy inherits that name.

Deployer-graph attribution. Still-unnamed contracts are attributed through their creator: a named deployer EOA, its ENS primary name, or the majority label of sibling contracts the same deployer already shipped.

Paid sources, last and gated. Arkham (only when a session is configured) and Nansen (paid credits) sit at the bottom. Nansen halts for the rest of the run the moment it reports credit exhaustion, and a final pass batches every still-unnamed address into one Dune labels.contracts execution, never one call per address.

Cross-chain identity

Two facts collapse a lot of duplicate unknowns into single known entities.

First, teams deploy byte-identical routers across chains, so ClearTrace fingerprints each contract as a SHA-256 of its runtime bytecode with the Solidity CBOR metadata trailer stripped. That trailer differs between otherwise-identical builds, so removing it lets one labeled instance name its twins everywhere.

Second, the same key controls an address on every EVM chain, so a mainnet ENS primary name identifies a wallet cross-chain. The pipeline also recognizes EIP-7702 delegated EOAs by their 0xef0100 designator and labels them through their delegate, so a delegated wallet never masquerades as a high-volume "Unknown Proxy" whale.

Refusing to guess

A wrong label is worse than an honest “Unknown.” Every rung is filtered against a blocklist of meaningless names: generic activity tags like “High Activity,” and proxy wrapper class names like TransparentUpgradeableProxy or ERC1967Proxy that describe the shape of a contract, not its owner. Masking those forces the ladder to fall through to a rung that resolves the actual implementation instead of propagating a name that says nothing. Each label also carries a confidence tier and is re-checked on a fixed interval, so a name from a weaker rung is replaced when a more authoritative source later resolves the same address.

Spending the effort where the volume is

Unknown volume is heavily concentrated in a few large addresses, so the pipeline ranks unresolved contracts globally by attributed volume and resolves the top of that list each sync, rather than sweeping every address round-robin. It is the cheapest way to move the most attributed volume from “Unknown” to named per run. In a recent snapshot of the label store, 259 of the resolved contracts carried entity names, and about 89% of those (231 of 259) were named by the free and structural rungs before any live paid lookup to Arkham, Nansen, or the batched Dune call.

-- Rank still-unnamed contracts by the volume they carry, across all chains,
-- so the resolution ladder spends its effort (and any paid rungs) on the
-- addresses that actually distort attribution.
SELECT
    resolved_contract_name,
    blockchain,
    total_volume_usd
FROM dune_attribution
WHERE (resolved_contract_name LIKE 'Unknown%'
       OR resolved_contract_name GLOB '0x[0-9a-fA-F]*')
  AND blockchain IS NOT NULL
ORDER BY total_volume_usd DESC
LIMIT 100;

What it proves

Any one vendor names part of the on-chain world. The value is in the ladder that combines authoritative, free, and paid sources in the right order, resolves structurally when none of them answer, and knows when to stop. For a foundation, grant program, or protocol team, that is the difference between “a large unknown near the top of the table” and a named entity you can actually reason about. It runs today inside ClearTrace’s live dashboard and public API at cleartracedata.com.

Andrew Maury is the founder of Rantum, a senior data science & ML studio that turns messy, fragmented, and adversarial data into models, APIs, and products that ship. Recent work: ClearTrace (neutral on-chain execution intelligence) and AddressIntel (predictive real-estate intelligence). More at andrewmaury.com.

Designing a Point-in-Time Backtest You Can Actually Trust

Andrew Maury — Mon, 20 Jul 2026 22:06:24 +0000

Originally published at rantum.xyz, which is the canonical source.

Almost any predictive model can be made to look good on historical data. The hard part is a backtest that reports what was actually knowable at decision time, so the number survives contact with production. Here is the discipline we hold every model to at Rantum, and the worked example where it walked a flattering 5.3x result down to an honest 2x.

The challenge

Every predictive product is sold on a backtest: "our model would have caught X% of these events in advance." The trouble is that a backtest is the easiest thing in machine learning to fake, usually by accident. Read a feature that was only knowable after the fact, shuffle your train and test sets at random, or report accuracy on an event that almost never happens, and you can produce an impressive number that means nothing. The model then ships, and the edge that looked real on paper quietly disappears in production.

The gap between a flattering backtest and a defensible one is not a modeling trick. It is a set of disciplines about time: what was knowable, when, and whether the test respects that. Below is the checklist we run on every predictive engagement, and a worked example, a teardown-probability model inside AddressIntel, our real-estate intelligence platform, where following it changed the answer.

What we built

A repeatable point-in-time backtesting discipline, applied here to predicting which aging houses on the San Francisco Peninsula get demolished and rebuilt. Teardowns are rare, well under 1% of parcels a year. The outcome is only visible in fragmented municipal permit records, and the features tempt you to cheat at every turn, which makes it a good stress test for a method meant to travel to any domain with rare events and messy history. Five rules do the work.

1. Score as-of T: features from data dated at or before T, nothing else

Pick a set of historical scoring dates T, and at each one, build every feature using only information that existed on or before that date. This sounds obvious and is constantly violated, because the most predictive-looking features are exactly the ones that leak. A teardown changes the structure: score a parcel as of 2018 but read its year_built, square footage, or listing photos as they stand today, and every one of those attributes describes the new house that replaced the old one. The feature would silently encode the answer. Only dated history, assessment rolls, sales, and permits, each stamped with the year it was true, reconstructs cleanly as-of T. Enforcing this disqualifies most of the obvious features before a single metric is computed.

2. Time-based folds, never random cross-validation

Standard k-fold cross-validation shuffles rows at random into train and test sets. For anything with a time dimension, that is leakage by construction: the model trains on rows from 2020 and is tested on rows from 2018, learning from a future it would not have had. Pool observations across several as-of dates instead, and split on time: fit on T at or before 2018, test on T = 2020. Use enough distinct dates, we like six or more, that no single lucky window can carry the result, and report the metrics per fold so temporal instability shows up instead of hiding in an average.

3. Lead with the base rate; measure lift, not accuracy

When positives are rare, accuracy is worse than useless: a model that predicts "no teardown, ever" is 99%+ accurate and completely worthless. The honest frame is the base rate first, then lift (how much richer the top-ranked slice is than chance), alongside precision-at-k, capture (recall) at k, and the area under the precision-recall curve. Because positives are few, bootstrap a confidence interval on every figure so you don't over-read noise.

# Rare positives (teardowns run <1% of parcels): accuracy is a trap —
# "never a teardown" scores 99%+. Lead with base rate, then lift.
def evaluate(observations, score):        # observations: (features, label), each built as-of T
    base = mean(label for _, label in observations)              # e.g. 0.0072
    ranked = sorted(observations, key=lambda o: score(o[0]), reverse=True)
    k = len(ranked) // 10                                         # top decile
    precision_at_k = mean(label for _, label in ranked[:k])
    return base, precision_at_k / base                           # lift is the headline

# Pool across as-of dates T with TIME-based folds — fit on T <= 2018,
# test on T = 2020. Random k-fold shuffles future rows into training: leakage.

4. The label must be independent of the feature it validates

This is the rule that saved the model from shipping a lie. The first teardown label was a cheap proxy: California's Proposition 13 caps assessed-value growth except on new construction, so a teardown-rebuild shows up as a sharp jump in a parcel's building value. Scored against that label, a single heuristic (sort parcels by their land-to-improvement value ratio) looked spectacular: a 5.3x lift at the top decile, capturing over half of all "teardowns," on 17,589 observations at a 0.72% base rate. It read as an undefeated baseline.

It was an artifact. The proxy label is a building-value spike, and the winning feature is land divided by building value. Both are functions of the same number, so scoring by the ratio was, in effect, scoring against a shadow of the label it was supposed to predict. We caught it two ways. First, a direct cross-validation: of 140 proxy-flagged rebuilds, exactly one had a confirming demolition or new-build permit at the same parcel. Second, rebuilding the label from real, independent data (actual San Jose demolition and new-single-family permits, keyed to the parcel number across a 36,896-parcel universe). On that clean, non-circular test the ratio's edge as a standalone score evaporated to no signal at all, though the underlying lot-size economics still carried a smaller, defensible signal (about 2x lift), which is the number the shipped model stands on.

5.3x lift on the circular proxy label, then 0x for the same feature against an independent label.

The rule generalizes past this one case: whenever a label is derived from the same source as a feature (a churn label inferred from the activity you also feed the model, a fraud label built from the rules you're scoring against), the backtest measures arithmetic, not prediction. Build the label from a source the features can't see, or the number is a mirror.

5. Pre-register the go/no-go, and build a gate that lets you fail cheap

Write down the bar for success before you look at any results, so you can't move the goalposts to fit the number you got. Ours here was concrete: on out-of-sample dates, a top-decile lift of at least 5x, and a material margin over the best single heuristic (not within noise), and a positive median lead time so the call is early enough to act on. Just as important is a cheap feasibility gate up front. Before running any metrics, we counted teardown positives in the candidate windows; the pre-set rule was that fewer than roughly 30 to 50 positives is dead on arrival. The first pass returned zero strict positives against a ceiling of 19, so the metrics were never run on noise, and the real work, assembling an independent permit label, was correctly identified as the project rather than a detour.

Backtest is not the same as the live model

The final piece of the discipline is knowing what the backtest does and does not license. The leakage that disqualifies year_built, square footage, and photos is a property of scoring a parcel that was already rebuilt in the historical window. Score a house that is still standing today, and those same attributes describe the actual candidate structure: no leak, and they are strong features. So the backtest validates the economic and assessment layer under strict point-in-time rules, while the live product can honestly be richer than the backtest could ever prove. Conflating the two is how teams either cripple a live model with backtest-only caution or ship a backtest number the production features quietly violate.

What it shows

The unglamorous core of trustworthy predictive ML: reconstructing features as-of a decision date, splitting on time rather than at random, measuring rare events by lift instead of accuracy, keeping the label independent of what it validates, and pre-committing to a bar. None of it is exotic. All of it is routinely skipped, because skipping it produces better-looking numbers. It is the same through-line as the rest of Rantum's work (finding real signal in messy data) turned inward on the measurement itself.

What it proves to a client

That we will tell you whether a model works before you bet a roadmap on it. This case study is the proof: a 5.3x result that looked ready to ship as a "proprietary teardown score" got walked back to a defensible 2x lot-size signal once a clean test was possible, and we would rather deliver the smaller true number than the larger false one. Anyone can produce a flattering backtest by leaking the future into the features; the value is in the rigor that refuses to. For a business sitting on fragmented, underused data, that is the difference between a predictive product you can defend to a customer and a demo that falls apart in production. The method transfers directly to any domain with rare outcomes and messy history: insurance, lending, fraud, marketplaces, logistics, churn.

Andrew Maury is the founder of Rantum, a senior data science and ML studio that turns messy, fragmented, and adversarial data into models, APIs, and products that ship. Read the full case study at rantum.xyz.

From Fragmented Permit Data to a Teardown-Probability Model

Andrew Maury — Thu, 09 Jul 2026 03:20:49 +0000

A building permit is the earliest signal that a house is about to be torn down and rebuilt. It is also some of the messiest data in real estate. This is how AddressIntel turns permits scattered across four municipal systems into a teardown-probability model — and, just as importantly, how it backtests that model without lying to itself.

A teardown is worth knowing about early. A developer who can spot, a year or two ahead, which aging house on an outsized lot is likely to be demolished and rebuilt has a real edge over the market. The signal that precedes almost every teardown is a building permit: a demolition filing, then a new single-family-residence filing on the same parcel.

The trouble is that permits are the messiest data in real estate. On the San Francisco Peninsula alone they are filed across four different portal technologies — Accela ACA (Palo Alto, Cupertino, Santa Clara), Tyler EnerGov CSS (San Mateo, San Carlos), eTRAKiT (Hillsborough, Atherton, Woodside, Burlingame), and San Jose’s open-data CKAN API. Each has its own schema, its own free-text vocabulary for what a permit is, its own history depth, and no shared identifier that ties a permit to a specific parcel or listing. The hard part is not the model. It is making the underlying data joinable and trustworthy at all.

What we built

A permit pipeline and a teardown-probability model inside AddressIntel, our predictive real-estate intelligence platform. The pipeline normalizes those four sources into one schema, resolves every record to a parcel, and classifies free-text permit types into a fixed taxonomy. On top of that sits the part most predictive PropTech skips: a label and a backtest built so the numbers can be trusted.

One schema, joined on the parcel APN

The parcel Assessor’s Parcel Number (APN) is the join key that makes everything else possible. San Jose’s open-data API carries the APN natively on every permit, along with clean fields for permit type, issue and final dates, valuation, and contractor — the highest-volume market, unlocked with a free JSON call rather than a brittle scrape. For the Selenium-scraped portals, the APN is recovered from the assessor record and used to stitch permits, sales, and listings onto a single parcel timeline. Permit type descriptions, which range from "NEW SINGLE FAMILY RESIDENCE" to "Single Family (New) - New" to a bare "Demolition", are classified into a stable taxonomy (demolition, new construction, addition, remodel, roof, ADU, and so on) so a query means the same thing in every city.

A label that doesn’t cheat

The first version of the label was a proxy: California’s Proposition 13 caps assessed-value growth except on new construction, so a teardown-rebuild shows up as a sharp jump in a parcel’s building value. It was cheap to compute and looked plausible. We cross-validated it against real permits anyway — and it failed. Of 140 proxy-flagged rebuilds, exactly one had a confirming demolition or new-build permit at the same APN. The proxy was catching ownership resets and additions, not teardowns.

So the real label comes from the permits themselves: a parcel is a teardown if it has a demolition or new-single-family permit in the forward window. Because that label is built from permits rather than assessed value, it is independent of the model’s strongest feature (the land-to-improvement value ratio) — which means the backtest measures prediction, not an artifact of the label sharing arithmetic with the feature.

Point-in-time, or it means nothing

A teardown changes the structure, and that is the whole trap. Score a parcel as of 2018, but read its year_built, square footage, or listing photos today, and every one of those attributes describes the new house that replaced it. The features would silently encode the answer. This is the point-in-time leakage principle, and it disqualifies most of the obvious features from the backtest: only dated history — assessment rolls, sales, and permits, each stamped with the year it was true — is clean.

The label construction, reduced to its essentials, makes the discipline explicit:

# Point-in-time teardown label from real permits — no lookahead, no leakage
for T in (2016, 2018, 2020): # historical scoring dates
    for parcel in universe:
        roll = assessment_asof(parcel, T) # land/improvement split dated <= T
        if not roll:
            continue

        permit_years = teardown_permits[parcel.apn] # DEMO or NEW-SFR at this APN
        if any(y <= T for y in permit_years):
            continue # already torn down by T — drop it

        label = 1 if any(T < y <= T + 3 for y in permit_years) else 0

        # Features use ONLY dated history (assessed values, sales, permits).
        # year_built, house sqft, and photos describe the *rebuilt* house — they leak.
        observations.append((point_in_time_features(roll), label))

What the honest number turned out to be

The first run, scored on the proxy label before it was cross-validated, looked like a strong result: 17,589 parcel-date observations across three scoring dates, a 0.72% base rate, and a single heuristic (sort parcels by land-to-improvement value ratio) capturing about half of all “teardowns” in the top decile, a 5.3x lift. A hand-blended model, land value alone, and a cheapest-structure heuristic all trailed it. It looked like an undefeated baseline.

It wasn’t. Once the label was rebuilt from real permits, a well-powered, non-circular test on 36,896 San Jose parcels and 266 confirmed teardowns, the ratio’s edge evaporated: no signal, arguably inverted, on the low-power sample (11 positives) where it could be checked. The 5.3x had been an artifact of the proxy label sharing arithmetic with the ratio. A building-value spike triggers both, so scoring by the ratio was, in effect, scoring against a shadow of the label it was supposed to predict.

(You can explore this reversal yourself in the interactive chart on thelive case study: the proxy-label and clean permit-label backtests side by side, toggle-able by scorer.)

The one signal that held up on the clean test was plainer than any of the discarded ones: parcels with an outsized lot for the neighborhood carry a real, if modest, 2.0x lift, well-powered at 266 positives. Maintenance-permit history, hoped to be a clean negative signal (an owner investing in a structure they intend to keep), added nothing blended with lot size. Neighborhood-contagion, tenure, and assessment-growth features, tested earlier against the still-trusted proxy label, had already added nothing or diluted the ratio; none of them got a second look once the ratio itself stopped being trustworthy.

That is a result, not a failure, and a sharper one than the model simply beating its baselines would have been. A backtest that would have shipped as a “proprietary 5x teardown score” turned out, on closer inspection, to be measuring its own label. The honest output is a much smaller claim (an outsized lot is worth about 2x) and an explicit rule against overselling it. It also clarified where a real edge would have to come from if one exists at all: explicit seller intent in listing language (“value in the land,” “as-is,” “contractor special”) remains untested and is the cheapest next thing to try. Just as important, it clarified the line between the backtest and the live product: physical features like lot coverage and structure age leak when backtesting a rebuilt parcel, but are clean and strong when scoring a house that is still standing today.

What it shows

The hard, unglamorous discipline underneath predictive ML: unifying fragmented public records onto a single joinable spine, engineering a label that is independent of the feature it validates, and enforcing point-in-time hygiene so a backtest reports what was actually knowable at scoring time. It is the same through-line as ClearTrace — finding signal in messy, real-world data — applied to civic records instead of on-chain flow.

What it proves to a client

That we will tell you whether a model works before you bet a roadmap on it. This piece is itself the proof: an early 5x number looked good enough to ship, and got walked back to a smaller, defensible 2x once a cleaner test was possible. Anyone can produce a flattering backtest by leaking the future into the features; the value is in the rigor that refuses to. For a business sitting on fragmented, underused data, that means the difference between a predictive product you can defend to a customer and a demo that quietly falls apart in production. The same capability transfers to any domain with messy records and a hard-to-measure outcome: insurance, lending, marketplaces, logistics.

Andrew Maury is the founder of Rantum, a senior data science & ML studio that turns messy, fragmented, and adversarial data into models, APIs, and products that ship. Recent work: ClearTrace (on-chain execution intelligence) and AddressIntel (predictive real-estate intelligence). More at andrewmaury.com.

AddressIntel: Turning Fragmented Permit Data into a Predictive Real-Estate Product

Andrew Maury — Tue, 07 Jul 2026 16:26:01 +0000

Canonical version: rantum.xyz

Predictive real-estate intelligence that fuses fragmented municipal permit records with multimodal AI to score teardown probability, flippability, and off-market opportunity.

The Challenge

The most valuable real-estate signals are buried in the messiest data. Building permits, zoning changes, and code-enforcement records are filed across dozens of municipal systems — each with its own format, portal, and lag — and almost never connected to live listing data. By the time an off-market teardown or value-add opportunity shows up in a normal feed, the developers who watch permits already know.

The hard part isn’t the model; it’s making the underlying data usable at all: fragmented, civic, unstructured, and constantly changing.

What We Built

AddressIntel is a live real-estate intelligence platform covering the San Francisco Peninsula and Nantucket Island. A Python ingestion fleet, orchestrated on GitHub Actions, continuously pulls municipal permits and public listing data; the data is normalized, fused, and scored, then served through a B2B GraphQL API.

It runs on a hybrid public-benefit model — a free public transparency layer over neighborhood development trends, funded by a monetized enterprise API for developers and institutional investors.

At a glance: 10K+ sales records ingested, 3 predictive models in production, 2 live markets.

Predictive Scoring & Multimodal AI

Teardown & flippability models — proprietary probability scores for teardown, flippability, and bidding-war likelihood, trained on fused permit + sales history. The permit data is the signal incumbents don’t have: a demolition permit filed six months before a property hits the market is invisible to anyone watching standard listing feeds.

Vision-based condition scoring — Google Gemini reads listing photos to score interior and exterior property condition automatically, turning unstructured images into a model feature. No manual tagging, no scraped review data — just the photos that already exist on every listing.

Permit alpha — joining civic permit filings to listings surfaces off-market developer activity before it becomes visible in standard feeds. The latency between a permit filing and public awareness is weeks to months. That gap is the product.

Architecture

The stack is built to be cheap to operate and easy to extend into new markets:

Pipeline: Python ingestion fleet on GitHub Actions cron, normalizing multi-source civic and listing data, with Gemini handling unstructured vision and NLP tasks
Backend: Firebase Data Connect (GraphQL over Cloud SQL Postgres) with Firebase Auth; Stripe-metered access for enterprise clients
Frontend: Next.js (App Router) + React on Firebase App Hosting, with Sentry edge error tracking

GitHub Actions as the orchestration layer keeps infra costs near-zero for a market-coverage product where freshness matters more than sub-second latency.

What It Shows

Turning messy, fragmented public records into a predictive product — and crossing domains, from data science into real estate, with point-in-time ML discipline so the scores reflect what was knowable at the time, not hindsight.

It’s the same through-line as ClearTrace: find the signal in data others write off as too messy to use, ship it as infrastructure. There it’s on-chain trace data. Here it’s municipal permit filings. The skill transfers.

What It Proves

That you can find signal in a data-rich space incumbents underuse, and turn it into a monetizable product — pipeline, models, API, and billing included. The same capability transfers to any business sitting on fragmented or underused data: marketplaces, fintech, insurance, logistics.

The question is never whether the data exists. It’s whether anyone has built the infrastructure to make it usable.

Andrew Maury is the founder of Rantum, a senior data science & ML studio. He previously scaled data infrastructure at 0x Labs and contributed to Dune’s open-source Spellbook. AddressIntel is live at addressintel.co.

Detecting MEV & Sandwich Attacks On-Chain: A Practical Methodology

Andrew Maury — Wed, 01 Jul 2026 14:41:42 +0000

Canonical version: rantum.xyz

A practical methodology for separating adversarial extraction from normal execution cost — and why the naive approach gets it badly wrong.

The Challenge

Sandwich attacks are the most common form of on-chain MEV against retail traders. A bot spots a pending swap in the mempool, inserts a frontrun buy just before it and a backrun sell just after — capturing the price impact the victim caused, at the victim’s expense. The victim’s trade executes at a worse price than expected, and the difference flows to the bot.

The problem for anyone trying to measure this: sandwich attacks are invisible in standard analytics. They look like normal slippage. A $10,000 swap that gets sandwiched shows up in a dashboard as “executed at 42 bps of slippage” — indistinguishable from a large trade in a thin pool. Without a methodology that isolates the adversarial component, every slippage number is a mix of real execution cost and extraction, and you cannot act on either.

This matters most to DEX aggregators, L2 foundations, and grant programs that are trying to evaluate which protocols actually protect their users — and which ones quietly route flow into sandwichable positions.

What We Built

ClearTrace’s MEV detection layer covers Ethereum, Base, Arbitrum, and Optimism. It identifies victim swaps, quantifies the volume sandwiched over rolling 7-day windows, and reports MEV exposure as a distinct metric — kept separate from the execution quality score so they can be read independently.

The v1 Mistake: tx_hash Is Not Execution Order

The first cut at sandwich detection tried to reconstruct in-block ordering directly from dex.trades, using tx_hash comparisons as a proxy for sequencer position — treating a lower hash as an earlier transaction. That logic is wrong: tx_hash is a cryptographic hash, not a sequence number. Sorting by hash produces an arbitrary permutation of transactions, not the order they were actually included in the block. A "sandwich" identified this way is noise.

The correct source is Dune’s curated dex.sandwiched table, built from Dune's Spellbook detection macro — which uses actual evt_index (on-chain event position) to reconstruct frontrun → victim → backrun triples across every block. Switching to this source eliminated the false positives entirely.

The Detection Query

WITH victims AS (
    SELECT
        blockchain,
        block_time,
        project,
        token_pair,
        amount_usd,
        tx_hash,
        tx_from
    FROM dex.sandwiched
    WHERE block_time >= NOW() - INTERVAL '7' DAY
      AND blockchain IN ('ethereum', 'base', 'arbitrum', 'optimism')
      AND amount_usd BETWEEN 1 AND 100000000
),
enriched AS (
    SELECT
        v.*,
        COUNT(*) OVER () AS total_attacks_7d,
        SUM(amount_usd) OVER () AS total_sandwiched_usd_7d,
        COUNT(*) OVER (PARTITION BY blockchain) AS chain_attacks_7d,
        SUM(amount_usd) OVER (PARTITION BY blockchain) AS chain_sandwiched_usd_7d
    FROM victims v
)
SELECT *
FROM enriched
ORDER BY block_time DESC
LIMIT 500;

The window columns mean the 7-day KPIs are accurate across the full dataset — not just the 500-row page — so a per-chain breakdown shows the right totals without a second query.

Separating MEV from Slippage

Slippage and MEV exposure are both costs, but they have different causes and different remedies. ClearTrace scores them separately. Execution quality compares each fill’s realized price against a 1-minute VWAP oracle — both legs valued explicitly against prices.usd, avoiding the circular-pricing trap of comparing against Dune's internally derived amount_usd.

The benchmark across major aggregators:

| Aggregator | Median slippage vs VWAP | MEV toxicity | Revert rate | | — -| — -| — -| — -| | CoW Swap | −0.1 bps | Zero | 0.0% | | 1inch | −0.4 bps | Low | 1.2% | | Odos | −0.6 bps | Medium | 2.1% |

CoW Swap’s zero MEV toxicity is structural: its batch-auction settlement mechanism bypasses the public mempool entirely, making sandwich attacks impossible by design.

The L2 Finding: Structural Protection vs Coverage Gap

Arbitrum and Optimism show near-zero sandwich counts compared to Ethereum. This is worth stating carefully — two very different things can produce near-zero counts: the detector runs and finds little (structural protection), or the detector has no data for the chain (a coverage gap that would be wrong to call protection).

The verification checks active_days alongside sandwich_victims_30d. If the chain has ~30 active days but tiny victim counts, the detector is running and genuinely finding almost nothing — the Arbitrum case, consistent with its FCFS private-mempool sequencing that eliminates the pending mempool window bots need to frontrun.

What It Shows

The ability to detect adversarial extraction in data that’s structured to hide it — and to distinguish a real signal from an absence of data. The v1 → v2 rewrite is the concrete example: identifying why the wrong model was wrong (hash ≠ sequence) required understanding the underlying data structures deeply enough to know what to use instead.

What It Proves

That you can build measurement infrastructure that holds up to scrutiny. ClearTrace’s MEV layer gives L2 foundations, DEX aggregators, and grant programs a neutral answer to a question their own analytics cannot answer honestly: how much of the slippage users pay is real execution cost, and how much is being extracted by bots?