# Blockchain analytics

By [rbarniker](https://paragraph.com/@barniker) · 2021-12-14

---

There are at least three places to get useful data on blockchains: [BigQuery](https://cloud.google.com/blog/products/data-analytics/introducing-six-new-cryptocurrencies-in-bigquery-public-datasets-and-how-to-analyze-them), [Nansen](https://pro.nansen.ai/), and [Dune Analytics.](https://dune.xyz/browse/queries) I will show how each of these can be used to answer a very basic question: what is the historical floor price of a popular NFT collection on [OpenSea](https://opensea.io/)? In this process, I will highlight the trade-offs between data sources (summarized below):

![Summary of trade-offs between blockchain data sources](https://storage.googleapis.com/papyrus_images/6be77dee9168b1c639c3e1b6a7f65ab9f12eb09fcf0018a232b8c7714375e7e5.png)

Summary of trade-offs between blockchain data sources

### Nansen

[Nansen](https://pro.nansen.ai/) is a subscription service. For the [standard](https://www.nansen.ai/plans) tier ($399 / quarter), you get access to an excellent UI with many views (see the many great videos on usage [here](https://www.youtube.com/c/Nansen-ai/videos)). There is one central limitation: the data is read-only unless you pay $3990 / quarter for access to csv download. This may not matter to users that only want to view the dashboards. In any case, [NFT God Mode](https://pro.nansen.ai/nft-god-mode?nft_address=0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d) answers our question (e.g., for [BAYC](https://opensea.io/collection/boredapeyachtclub)) over the past month.

![NFT god mode in Nansen for BAYC](https://storage.googleapis.com/papyrus_images/6a6aeb1ab12718323dcfd9f70ed8b8b5ffcaf9efdc37b096b2a3959b7c65d078.png)

NFT god mode in Nansen for BAYC

### Dune

What if we want to see a longer historical timeframe of floor price? Without access to the Nansen raw data, we are limited to what the dashboard provides. [Dune Analytics](https://dune.xyz/browse/queries) provides an [extensive library](https://dune.xyz/browse/queries) of existing, user-generated queries and dashboard for [free](https://dune.xyz/pricing). The queries can be easily forked, making the logic composable. In addition, some of the [dashboards are extremely good](https://dune.xyz/hagaetc/dex-metrics) (rivaling Nansen in terms of information quality).

![The DEX metrics dashboard on Dune](https://storage.googleapis.com/papyrus_images/a1b3927d0b5ee9a5258468eac7c407f32f23bc45c5f8018f34e8a28f4ff654d6.png)

The DEX metrics dashboard on Dune

There is also a [python client](https://github.com/itzmestar/duneanalytics) for Dune, which allows you to easily pull data for any query into python. [Here](https://dune.xyz/queries/294423) is my Dune query to answer our question. And below is the Python code I use to access the raw query result and further manipulate / plot the data:

    import pandas as pd
    from duneanalytics import DuneAnalytics
    
    # login
    dune = DuneAnalytics('name', 'pw')
    dune.login()
    
    # fetch token
    dune.fetch_auth_token()
    
    # query
    result_id = dune.query_result_id(query_id=294423)
    
    # fetch query result
    data = dune.query_result(result_id)
    result_list=data['data']['get_result_by_result_id']
    result_list_clean=[e['data'] for e in result_list ]
    d=pd.DataFrame(result_list_clean)
    
    # reshape and plot
    d['Day'] = pd.to_datetime(d.Day)
    d_=d.pivot(index='Day', columns='Name', values='Floor (Approx)')
    d_.plot(figsize=(15,5))
    

This plot shows the BAYC floor (along with MAYC and Runners) for the past year:

![Floor price of BAYC, MAYC, and ChainRunners over the past year](https://storage.googleapis.com/papyrus_images/eddc4e9d6eed11f6f76e0bdffae12f571921fa8fa8d51d74d73c2b7e5f04d948.png)

Floor price of BAYC, MAYC, and ChainRunners over the past year

### Big Query

[nick.eth wrote](https://twitter.com/nicksdjohnson/status/1464393250253389824) a [very good overview](https://mirror.xyz/nick.eth/INhEmxgxoyoa8kPZ3rjYNZXoyfGsReLgx42MdDvn4SM) of Ethereum analytics using BigQuery already. In short, Big Query can be used to access a lot of raw blockchain transaction data (full list of datasets [here](https://github.com/blockchain-etl/public-datasets)). Big Query is populated by the [Blockchain ETL library](https://github.com/blockchain-etl) (authored by [Evgeny Medvedev](https://twitter.com/EvgeMedvedev), the co-founder of Nansen). It is easy to access data for any erc721 contract from the tables below (see [example](https://console.cloud.google.com/marketplace/product/ethereum/crypto-ethereum-blockchain?project=western-glazing-305418)). Big Query is well-optimized, familiar to many, and offers a [Python client](https://googleapis.dev/python/bigquery/latest/index.html) for composing queries in [Jupyter notebook](https://jupyter.org/).

    SELECT 
    contracts.address
    COUNT(1) AS tx_count
    FROM 
    `bigquery-public-data.crypto_ethereum.contracts` AS contracts
    JOIN 
    `bigquery-public-data.crypto_ethereum.transactions` AS transactions 
    ON (transactions.to_address = contracts.address)
    WHERE 
    contracts.is_erc721 = TRUE
    GROUP BY contracts.address
    ORDER BY tx_count DESC
    LIMIT 10
    

### Summary

In short, Nansen is an excellent source of data if you strictly want read-only access to high quality dashboards or if you have a large budget to pay for API access. In addition, Nansen has labeled ~100M wallets (associating an address with an ETH name and / or institution), which is very useful for grouping and interpretability.

Dune took a different approach, allowing the community to create dashboards and queries. It’s the most composable (e.g., it’s easy to fork queries from others and re-build dashboards), but less streamlined (e.g., dashboards must be searched for and saved) than Nansen. It’s excellent for developers because you can fork logic and it has a Python client to pull query results into python for further analysis or plotting.

BigQuery is a great source of raw data with fast execution and familiar tools (e.g., a great python client, etc). Of course, it lacks the extensive library of example queries / dashboards that Dune offers. In addition, Dune (along with Nansen) appears to have several tables (e.g., for Opensea and DeFi) that are missing from BigQuery.

---

*Originally published on [rbarniker](https://paragraph.com/@barniker/blockchain-analytics)*