・Footprint Analytics

In the past, this kind of data required a lot of developer resources to index and organize. Although we know that blockchain data is transparent , it doesn’t mean that you can get on-chain data & understand it easily and correctly.

But while much of the critical data, like transactions, wallets, and prices, is transparent, it’s hardly meaningful to most people. The strings of numbers and letters on the blockchain need to be indexed to make sense, and dozens of platforms exist to help the general public understand what’s going on in web3.

But where do these platforms get their data from?

What about enterprises and organizations that need highly particular data for their internal use, like VCs and GameFi projects?

Until recently, most developers would build out their SDKs for data ETL, a strategy that is pricey and difficult to maintain. Now, there are several DaaS (Database-as-a-Service) companies that make it easier. While Footprint Analytics is one such platform, it goes above and beyond what others offer in terms of breadth and customization options.

By providing access to historical data from 24 chains including game chains (far more than any other provider), 17 NFT marketplaces, and nearly 2,000 GameFi protocols plus their in-game assets, Footprint Data API is one unified API for blockchain developers of all stripes, across the industry.

Now, it only takes a few minutes to realize multi-sector data analysis of the blockchain.

This article will explain how to track cash flow using Footprint analytics feature by taking stable coin as an example.

・ Crypto World vs Real World

In Real world , various types of money supply are generally classified as M0, M1, M2, M3. In the crypto world , once you exchange fiat money via OTC or something else, all these crypto currencies are almost traceable, when they interact with a certain kind of wallet or some protocol. Governments issue the currency through their central banks usually , on the other hand those smart contracts issue crypto currency either through smart contracts or by some reward mechanism.

Some secondary markets will allow you to to exchange your asset in exchange , this is similar for crypto but what’s more some volume come from DEX.

So all these scenarios we mention above , we’ll take a look at what happened using the on-chain data analytics platform.

・Token Mint and Burn and Transfer

Firstly , Let’s talk about stable coin distribution in recent times, you can track this info via this chart. As you can see, USDT & USDC occupied most of the market share quite stably during last year.

What will happen when token is mint and burn , you could track from this query(mint) and this query(burn)

There are some interesting scenario for token burn ,

• One is normal burned behavior :

  select * from
  ethereum_token_transfers
  where 1=1
  and to_address = ‘0x0000000000000000000000000000000000000000’
  and transaction_hash = ‘0x2953d30a67abf584f673561abe6879ef0ffde33af4577dd1eee043adac93a9da’ — burn
  and block_timestamp >= date(‘2023-01-03’) and block_timestamp <= date(‘2023-01-06’)
  limit 100

• Another transaction is bridging across different blockchain :

select * from
ethereum_token_transfers
where 1=1
and to_address = ‘0x0000000000000000000000000000000000000000’
and transaction_hash = ‘0x3e76dd4c4c2ca3e4662964ad936e90c5dc82fad8956cfcbd6f1d5191a013e1a1’ —bridge
and block_timestamp >= date(‘2023-01-03’) and block_timestamp <= date(‘2023-01-06’)
limit 100

• Which you could track this on etherscan

• Token Transfer is most common case , so I won’t explain much details , keep in mind that our gold is tracking stable coin we’ll using this key info : token_address to filter stable coin

And usually we’ll have transaction fee, so who get this fee as reward, you could track these on this [query](https://www.footprint.network/chart/miner-fee-fp-35761):

select * from “ethereum_blocks”
where 1=1
and hash = ‘0x6579fc0e9adf0cb4eb8db8dcb49558357f9830bcca3d0e8f1a37f034fc98a8fb’
and timestamp >= date(‘2023-01-03’) and timestamp <= date(‘2023-01-06’)

・Tracking cash flow of CEX and stable coin

After we know the basic transfer, and in this scenario, we need to:

• have tags on the wallet to filter CEX wallet

• Know the token_address of stable coin
  Luckily, this is done by the community and Footprint analytics team. You could find these tags from entity_tag table and stable coin token_address in here

After this material is ready, we need to break down to several steps:

• The first step is to find a stable coin transfer to or from CEX, here . Since the whole process is a bit complex, we used the footprint analytics feature of the model table, as a temp table to store the data so that we could query later from another query.
  To make your data more intuitive for your teams, you can ask a question, either in the query builder or the SQL editor, to create derived tables in Metabase, called models, that can pull together data from different tables. You can add custom, calculated columns, and annotate all columns with metadata so people can play around with the data in the query builder as a starting point.

• Second step: query the price of ethereum using this query :

with price_eth as (
SELECT
DATE_FORMAT( “timestamp” ,’%Y-%m-%d %H’) AS ts_hr ,
avg(“price”) AS “avg_price”
FROM “token_price_5min”
WHERE (“chain” = ‘Ethereum’
AND “token_symbol” = ‘ETH’
AND “timestamp” >= date(date_add(‘day’,-90,now()))
AND “timestamp” < now())
GROUP BY 1
ORDER BY 1 ASC
)

• Third : combing these to check the collationship between the moving sum of net inflow and price of ETH

• This result contains net inflow and moving sum of net inflow in 4 tick(hourly) , tricks in the red box of following chart are using Footprint Analytics feature : model table

• Fourth : to check the chart whether you could find some relationship between the net inflow and the price movement

  One interesting thing is: take a look at the when the price drop a lot causing by FTX collapse around Nov, 8 , 2022, lots of netflow transfer to CEX tried to buy the dip

• Last but not least, if you could not find a pattern between those price movements and net inflow, it’s normal trading is usually not that easy , and contains lots of factors. But I suggested if you wanna dig more , you could download the data , using Python to calculate for details , also adjust different parameter sets of SQL and download different parameter sets and use Python to calculate the correlation.
  After all, this requires lots of mathematics work , but at least footprint helps you a lot for doing related dirty work.

You could duplicate my dashboard here and adjust or redo all of these from the ground.

Tracking CEX inflow and outflow is only part of the work . You probably do more, such as tracking how money interacts with DEX protocol and other protocols. You could check the details from this few query DEX interaction and Lending interaction.

Once you find the pattern you need, you could use the Footprint analytics alert feature to significant fluctuation of your metrics. You could even use our REST API and SQL API to build your own application.

• API

  Footprint Analytics has the most comprehensive warehouse of indexed on-chain data, covering 23 chains, 14 NFT marketplaces, over 1,900 GameFi projects, and over 100,000 NFT collections.

It allows developers to make calls from a database containing structured data for all these protocols with a single query, or easily customize the data API when required. As the API is cross-domain, users can access NFT, GameFi and address data through a single tool. Wash trading filters give users the option to filter out meaningless and fraudulent data.