Orderflow

Identify flow of DEX volume through frontend

The dex.orderflowtable provides transaction-level swap data to illustrate DEX transaction volumes through frontends.

Data Lineage

The dex.orderflow model builds upon swap events identified from both dex.trades and dex.aggregator_trades.

The orderflow table aggregates swaps by transaction hash. The exception here is cow-protocol swaps, which are treated as individual transactions. 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

Frontend Labeling

The orderflow model utilises both contract interactions and the integrator field in dex.aggregator_tradesto identify the most probably frontend where the transaction was executed.

Labelling based on Contract Interaction

Internally, Allium maintains a mapping of key telegram bots, MEV contracts, and aggregator routers which we use to label the transaction by the most probable frontend by the transaction's to_address

The to_address would be the ParaSwap Augutus Router.

Labelling Based on Event Log

In the event logs of certain transactions, the aggregator may emit fields which tell us where the swap source is executed from. This can be from the details of the swap referrals or the actual name of the frontend that was used.

• In OpenOcean's event swap log, the referrer field indicates the fee recipient and frontend integrator where the swap was executed from.

  • In this swap, the referrer points to the Rabby Fee Wallet.

• In Kyberswap Aggregator's client data event log, the hex-decoded data will tell us the source of the swap: {"Source":"dextools","AmountInUSD":"27.418064752878504","AmountOutUSD":"25.55357350955812","Referral":"...}

These fields are parsed and used to label the integratorfields in the dex.aggregator_tradesmodel.

Heuristics to determine frontend

A combination of integrator and contract labels is used to infer the frontend. The orderflow currently uses the following logic. Users can use the dex.unhydrated_orderflowto tune their labelling logic.

select
    coalesce(
        orderflow.integrators[0]::varchar,
        entities.project::varchar,
        case
            when orderflow.aggregator_source[0] is not null
            then orderflow.aggregator_source[0]::varchar || ' Integrator'
        end
    )::varchar as frontend,
    orderflow.*
from <chain>.dex.unhydrated_orderflow orderflow
left join
    common.identity.entities
    on entities.address = orderflow.to_address
    and lower(entities.chain) = '<chain>'
    and entities.category in ('dex', 'dex_aggregator')

Sample Query

select
  date_trunc('week', block_timestamp) as date,
  -- Alternatively, one may assume the first dex aggregator or pools swapped, using:
  -- coalesce(frontend, aggregator_source[0]::varchar, liquidity_source[0]::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

Table Columns

Unique Key: unique_id

Column Name	Data Type	Description
frontend	VARCHAR	The frontend interface used for the transaction
block_number	BIGINT	The block number in which the transaction was included
block_timestamp	TIMESTAMP_NTZ(9)	The timestamp of the block in which the transaction was included
transaction_hash	VARCHAR	The hash of the transaction
transaction_index	BIGINT	The index of the transaction within the block
swapper_address	VARCHAR	The address of the user performing the swap
from_address	VARCHAR	The address initiating the transaction (msg.sender)
to_address	VARCHAR	The address receiving the transaction
is_aggregator	BOOLEAN	Flag indicating if the transaction is an aggregator
usd_volume	NUMERIC	The volume of the transaction in USD
total_usd_volume	NUMERIC	The total volume of the transaction in USD
senders	VARIANT	Array of addresses sending tokens in the transaction
recipients	VARIANT	Array of addresses receiving tokens in the transaction
user_token_input	VARCHAR	Address of the token the user is selling
user_token_input_symbol	VARCHAR	Symbol of the token the user is selling
user_token_output	VARCHAR	Address of the token the user is buying
user_token_output_symbol	VARCHAR	Symbol of the token the user is buying
tokens_sold	VARIANT	Array of tokens sold in the transaction
tokens_bought	VARIANT	Array of tokens bought in the transaction
integrators	VARIANT	Array of integrator names involved in the transaction
integrator_tags	VARIANT	Array of integrator contract addresses
aggregator_source	VARCHAR	Source of the aggregator
aggregator_usd_volume	NUMERIC	USD volume of the aggregator portion
aggregators	VARIANT	Array of aggregator contracts involved
aggregators_count	BIGINT	Count of aggregators involved
aggregator_details	VARIANT	JSON details of aggregator swaps
liquidity_source	VARIANT	Array of liquidity sources used
liquidity_usd_volume	FLOAT	USD volume from liquidity sources
liquidity_pools	VARIANT	Array of liquidity pool addresses used
liquidity_pools_count	BIGINT	Count of liquidity pools involved
liquidity_details	VARIANT	JSON details of liquidity pool swaps
total_trades	BIGINT	Total number of trades in the transaction
unique_id	STRING	Unique identifier for the transaction