Order Flow

The dex.orderflow model aggregates swaps from both dex.trades and dex.aggregator_trades by transaction_hash , allowing you to see the dex aggregator(s) that a single transaction utilised and the underlying liquidity sources (AMM pools) that it utilised

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.

The usd_volume of the swap is determined by the max usd_amount of the swap within the entire transaction.

The volume and swaps executed by all dex pools are aggregated in the liquidity_details column. For volume and swaps routed through dex aggregators, they are wrapped in the aggregator_details column

Example:

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

Frontend Labeling

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

We attempt to make the best guess on identifying the front end used for each transaction by inferring from labelling the transaction. First, we label all transactions by the transaction's to address, which tells us the contract that the user interacted with.

The to_address would be the ParaSwap Augutus Router

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 it to_address is routed to a single router or aggregator, we will 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 there to_address is a router or telegram bot, we label it as a router.

For frontend labelling, 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 senders) as user_count,
  sum(usd_volume) as usd_volume
from ethereum.dex.orderflow
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	Description
frontend	The frontend interface used for the transaction
meta_aggregator	Meta aggregator involved in the transaction
aggregator	Aggregator involved in the transaction
router	Router used in the transaction
block_number	The block number in which the transaction was included
block_timestamp	The timestamp of the block in which the transaction was included
transaction_hash	The hash of the transaction
transaction_index	The index of the transaction within the block
from_address	The address initiating the transaction, also known as msg.sender
to_address	The address receiving the transaction, the smart contact address receiving the transactions
is_aggregator	Flag indicating if the transaction is an aggregator
usd_volume	The volume of the transaction in USD
total_usd_volume	The total volume of the transaction in USD
senders	Array of addresses sending tokens in the transaction
recipients	Array of addresses receiving tokens in the transaction
tokens_sold	Array of tokens sold in the transaction
tokens_bought	Array of tokens bought in the transaction
aggregator_source	Source of the aggregator
aggregator_usd_volume	USD volume of the aggregator
aggregators	Aggregators contracts involved in the transaction
aggregators_count	Count of aggregators involved in the transaction
aggregator_details	Details of the aggregators swaps involved
liquidity_source	Source of liquidity
liquidity_usd_volume	USD volume of liquidity
liquidity_pools	Liquidity pools involved in the transaction
liquidity_pools_count	Count of liquidity pools involved in the transaction
liquidity_details	Details of the liquidity pools swaps involved
total_trades	Total number of dex and dex aggregator trades in the transaction