Order Flow

This model is in beta. The columns types are subjected to change. This model updates on a daily basis.

The Order Flow model captures detailed information about decentralized exchange (DEX) transactions, providing insights into the routing and execution of trades across various platforms and liquidity sources.

This model includes data on individual swap transactions, including block details, transaction specifics, aggregator involvement, and liquidity sources.

Methodology

Data Source

This model combines both dex.trades and dex.aggregator_trades by transaction_hash. Within each transaction we roll up all swaps that are routed by aggregators and all swaps that are executed by pools.

Internally, we maintain a mapping of router and aggregator routers which we use to label the transaction by the most probable frontend. The list contains routers, aggregators and meta-aggregators and telegram bots.

Aggregation

All dex.trades and dex.aggregator_trades are aggregated at the transaction level. For transaction, there will be only one row represented. The volume and swaps executed by all dex pools are aggregated in the pools column. Likewise for aggregators, it will be wrapped in the aggregators column.

Example:

"[
  {
    ""pair"": ""L3P-WETH"",
    ""pool"": ""0x414b0de19d69c18657bcef0f30c72500598b1b88"",
    ""pool_index"": 104,
    ""project"": ""uniswap"",
    ""protocol"": ""uniswap_v2"",
    ""usd_amount"": 3.845936420455126e+03
  }
]"

Frontend Labeling

We attempt to make the best guess on identifying the frontend used for each transaction by inferring from labeling the transaction. First, we label all transactions by transaction_to_address, which tells us the contract that the user interacted with.

These contracts may include a meta_aggregator, aggregator, router, mev_bot contract or a direct liquidity pool interaction (particularly for curve AMM pools).

• meta_aggregator: A meta-aggregator is a contract that routes transactions to multiple other routers or aggregators.

• aggregator: A contract that routes transactions to multiple other routers or aggregators. If the transaction_to_address is routed to a single router or aggregator, we label it as such. Otherwise, we will take the first aggregator or router that the transaction is routed to as the aggregator.

• router: A contract that routes transactions to multiple other routers or aggregators. If the transaction_to_address is a router or telegram bot, we label it as a router.

For frontend labeling, we take the following approach by preferentially selecting the meta_aggregator, then aggregator, then router, then liquidity pool and finally the mev_bot:

coalesce(meta_aggregator, aggregator, router, curve_direct, mev_bot_label)

The coalesce function returns the first non-null value in the list of arguments. If all the arguments are null, the coalesce function returns null and we cannot confidently label the frontend for the dex trades.

Sample Query

select
  date_trunc('week', block_timestamp) as date,
  -- Alternatively, one may assume the first dex aggregator or pools swapped, using:
  -- coalesce(frontend, aggregators[0]:project::varchar, pools[0]:project::varchar) as frontend,
  coalesce(frontend, 'others') as frontend,
  count(distinct swapper) as user_count,
  sum(usd_volume) as usd_volume
from ethereum.dex.orderflow_beta
group by all
order by 1 desc

Output of this query: https://app.allium.so/s/NxGVkkU4

Table Columns

Unique Key: transaction_hash

Column Name	Data Type	Description
block_number	BIGINT	The number of the block containing the transaction
block_timestamp	TIMESTAMP	The timestamp of the block
transaction_index	BIGINT	The position of the transaction within the block
is_aggregator	BOOLEAN	Indicates whether the transaction involves an aggregator
frontend	VARCHAR	The frontend interface used for the transaction
meta_aggregator	VARCHAR	The meta-aggregator used, if any
aggregator	VARCHAR	The aggregator used, if any
router	VARCHAR	The router contract used for the transaction
liquidity_source	VARIANT	An array of liquidity sources used in the transaction
swapper	VARCHAR	The address of the account initiating the swap
contract_address	VARCHAR	The address of the contract involved in the transaction
usd_volume	NUMERIC	The total volume of the transaction in USD
aggregators	VARIANT	An array of aggregators trades details used in the transaction
aggregators_swapped	BIGINT	The number of aggregators involved in the swap
usd_aggregator_volume	NUMERIC	The volume handled by aggregators in USD
pools	VARIANT	An array of pool trades details used in the transaction
pools_swapped	BIGINT	The number of pools involved in the swap
usd_pool_volume	NUMERIC	The volume handled by pools in USD
transaction_hash	VARCHAR	The unique identifier of the transaction