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:
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 thetransaction_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 thetransaction_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:
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
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 |
Last updated