Orderflow

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