Analyze Millions of NFT Sales on OpenSea Using PostgreSQL and TimescaleDB
Introducing the Timescale NFT Starter Kit, a step-by-step guide to start collecting, storing, analyzing, and visualizing NFT data from OpenSea. And the Time Travel Tigers NFT Collection!
The Rise of the NFT
Looking back, 2021 was a big year for crypto, but the front-runner for the biggest winner in crypto for 2021 belongs to one idea: NFTs.
NFT stands for non-fungible token. Unlike Bitcoin (BTC) and Ether (ETH), where each token is mutually interchangeable, each NFT is a unique digital asset representing ownership of a digital item stored on the blockchain.
In 2021, we saw a Cambrian explosion of NFT projects as crypto enthusiasts and artists—musicians, illustrators, and filmmakers—created NFTs to share their work with the world innovatively and created communities around NFT projects.
We saw the emergence of the Twitter profile picture flex, where new NFT owners would flex their purchase of a piece from collections like Bored Ape Yacht Club, CryptoPunks, and AfroDroids, driving FOMO and a flurry of interest and new money into the NFT space. Even Snoop Dogg revealed himself as an avid NFT collector under the pseudonym Cozomo de’ Medici! NFT marketplaces like OpenSea, Rarible, and Foundation benefited as new NFT prospectors joined and bought items out of curiosity, hype, or because something “looked rare'' and hoped others thought the same.
With a new crypto market once again awash with speculation and hype, it’s important to leverage all the tools at our disposal to make sense of the noise. Sometimes, reading articles, Twitter threads, and email newsletters isn’t enough. You have to go directly to the data.
As the developers of TimescaleDB, an open-source time-series database powered by PostgreSQL, we build tools that empower people and companies worldwide to harness the power of data.
Handling NFT Data With the NFT Starter Kit
So, to help fellow developers make sense of the NFT space, we created the NFT Starter Kit, a step-by-step guide to collecting, storing, analyzing, and visualizing NFT data from OpenSea, the world’s largest NFT marketplace. We give you all the tools, code, and queries to take you from zero to NFT hero.
The NFT Starter Kit will give you a foundation for analyzing NFT trends to bring some data to your purchasing decisions or just learn about the NFT space from a data-driven perspective. It also serves as a solid foundation for your more complex NFT analysis projects in the future.
The NFT Starter Kit and accompanying tutorial will help you use PostgreSQL and TimescaleDB to unearth insights from NFT data. It includes:
- A database schema to efficiently store and query data.
- SQL queries to use as a starting point for your own analysis.
- Pre-built dashboards and templates in popular data visualization tools like Apache Superset and Grafana for visualizing your data analysis.
- A Python script to collect real-time data from OpenSea, as well as a sample dataset to play around with.
Read on for more information about the NFT Starter Kit, a sneak peek into the kinds of questions you’ll answer, and demo videos of how to use the NFT Starter Kit’s pre-built dashboards and templates.
But first, one more thing: Because we’re just as curious about the NFT space as you are, we at Timescale decided to make an NFT collection of our own: Time Travel Tigers!
Time Travel Tigers is a collection of 20 hand-crafted NFTs featuring Timescale’s mascot, Eon, the friendly tiger, as they travel through space and time, spreading the word about time-series data wearing various disguises to blend in with the times.
NFTs in the Time Travel Tigers collection cannot be bought. They were earned and claimed by the first 20 people to complete the NFT Starter Kit tutorial in a competition we held.
NFT Starter Kit: What’s Included
Timescale’s NFT Starter Kit is a step-by-step guide to collecting, storing, analyzing, and visualizing NFT data from OpenSea, the world’s largest NFT marketplace. It’s an ideal starting point for developers interested in crypto (or just crypto-curious) who want to learn more about NFT trends from a data-driven perspective.
The easiest way to complete the NFT Starter Kit tutorial is by using a fully managed Timescale database. Sign up here—it’s 100 % free for 30 days, no credit card required.
Using PostgreSQL and TimescaleDB as our database of choice, the NFT Starter Kit contains all the tools, code, and queries to give you a solid foundation for analyzing NFT trends so that you can better monitor your portfolio and bring data to your future purchasing decisions, or build more complex NFT analysis projects using the building blocks in the NFT Starter Kit.
The NFT Starter Kit includes:
- A data ingestion script (in Python), which collects real-time data from OpenSea and ingests it into TimescaleDB.
- A sample dataset containing a week’s worth of NFT sales for those who want to skip real-time ingestion and get straight into analysis.
- A database schema to efficiently store and query data about NFT sales, assets, collections, and owners.
- SQL queries to use as a starting point for your own analysis.
- Pre-built dashboards and templates in popular open-source data visualization tools Apache Superset and Grafana for visualizing your data analysis.
- A local TimescaleDB database, pre-loaded with sample NFT data.
To get started, download the NFT Starter Kit on GitHub.
Sneak peek of what you’ll learn
Here’s a sneak peek of three questions you’ll answer using the NFT Starter Kit:
- How many NFTs did Snoop Dogg (yes, Snoop Dogg) buy?
- How do the daily sales of CryptoKitties compare to that of Ape Gang?
- Which NFTs did someone flip on the same day for a crazy profit?
Snoop Dogg's NFT activity
How many NFTs did a particular person buy in a certain period of time? This sort of query is useful for monitoring the activity of popular NFT collectors, like American rapper Snoop Dogg (also known by his NFT persona Cozomo_de_Medici.) In the query below, we analyze Snoop Dogg’s NFT trades from the three-month period of 12 July 2021 to 12 October 2021:
/* Snoop Dogg's transactions in the past 3 months aggregated */
WITH snoop_dogg AS (
SELECT id FROM accounts
WHERE address = '0xce90a7949bb78892f159f428d0dc23a8e3584d75'
)
SELECT
COUNT(*) AS trade_count,
COUNT(DISTINCT asset_id) AS nft_count,
COUNT(DISTINCT collection_id) AS collection_count,
COUNT(*) FILTER (WHERE seller_account = (SELECT id FROM snoop_dogg)) AS sale_count,
COUNT(*) FILTER (WHERE winner_account = (SELECT id FROM snoop_dogg)) AS buy_count,
SUM(total_price) AS total_volume_eth,
AVG(total_price) AS avg_price,
MIN(total_price) AS min_price,
MAX(total_price) AS max_price
FROM nft_sales
WHERE payment_symbol = 'ETH' AND ( seller_account = (SELECT id FROM snoop_dogg) OR winner_account = (SELECT id FROM snoop_dogg) )
AND time > NOW()-INTERVAL '3 months'
trade_count|nft_count|collection_count|sale_count|buy_count|total_volume_eth |avg_price |min_price|max_price|
-----------+---------+----------------+----------+---------+------------------+------------------+---------+---------+
59| 57| 20| 1| 58|1835.5040000000006|31.110237288135604| 0.0| 1300.0|From the query result, we can see that Snoop Dogg made 59 trades overall in three months—he bought 58 times and sold only once (no paper hands here). His trades included 57 individual NFTs from 20 different collections, totaling 1835.504 ETH spent, with a minimum price paid of 0 ETH and a maximum price of 1300 ETH.
Note that the query above only includes transactions that are reachable from the OpenSea API and doesn’t include purchases on other platforms.
We chose Snoop Dogg’s address to analyze his trades, but you can analyze the activity of any address you’re interested in by modifying the WHERE clause to see the trades of your chosen account.
Next, you might want to see the activity of African NFT evangelist Daliso Ngoma or even compare the trading patterns of multiple collectors. Since NFT transactions are public on the Ethereum blockchain and our database contains seller (seller_account) and buyer (winner_account) columns as well, we can analyze the purchase activity of a specific account if we know their address.
Daily NFT sales of CryptoPunks vs. Ape Gang
How do the daily sales of NFTs in one collection compare to that of another collection? In the query below, we compare the daily sales of two popular NFT collections: CryptoKitties and Ape Gang, in the three-month period from 12 July 2021 to 12 October 2021:
/* Daily number of NFT transactions, "CryptoKitties" vs Ape Gang from past 3 months? */
SELECT bucket, slug, volume
FROM collections_daily cagg
INNER JOIN collections c ON cagg.collection_id = c.id
WHERE slug IN ('cryptokitties', 'ape-gang') AND bucket > NOW() - INTERVAL '3 month'
ORDER BY bucket DESC, slug;
bucket |slug |volume|
-------------------+-------------+------+
2021-10-12 02:00:00|ape-gang | 58|
2021-10-12 02:00:00|cryptokitties| 48|
2021-10-11 02:00:00|ape-gang | 208|
2021-10-11 02:00:00|cryptokitties| 61|
2021-10-10 02:00:00|ape-gang | 248|
2021-10-10 02:00:00|cryptokitties| 84|
...
Here’s how the query looks when plotted as a time-series chart in Apache SuperSet:
This sort of query is useful to track sale activity in collections you’re interested in or own assets in so you can see the activity of other NFT holders. Moreover, you can modify the time period under consideration to look at larger (e.g., nine months) or smaller periods of time (e.g., 14 days).
Which NFTs did someone flip the same day for a crazy profit?
We often want to identify interesting behavior, such as an asset being bought and then sold again for a much higher (or lower) amount within the same day.
We can examine which assets had the biggest intraday sale price change to answer this question. This can help us identify good “flips” of NFTs or perhaps owners whose brand elevated the NFT price thanks to it being part of their collection.
The query below finds the five assets with the biggest intraday sale price change during the six-month period between 12 April 2021 and 12 October 2021:
/* Daily assets sorted by biggest intraday price change in the last 6 month*/
WITH top_assets AS (
SELECT time_bucket('1 day', time) AS bucket, asset_id,
FIRST(total_price, time) AS open_price, LAST(total_price, time) AS close_price,
MAX(total_price)-MIN(total_price) AS intraday_max_change
FROM nft_sales s
WHERE payment_symbol = 'ETH' AND time > NOW() - INTERVAL '6 month'
GROUP BY bucket, asset_id
ORDER BY intraday_max_change DESC
LIMIT 5
)
SELECT bucket, nft, url, open_price, close_price,
intraday_max_change
FROM top_assets ta
INNER JOIN LATERAL (
SELECT name AS nft, url FROM assets a
WHERE a.id = ta.asset_id
) assets ON TRUE;-[ RECORD 1 ]-------+---------------------------------------------------------------------------
bucket | 2021-08-25 00:00:00+00
nft | Book
url | https://opensea.io/assets/0x73da73ef3a6982109c4d5bdb0db9dd3e3783f313/9
open_price | 0.99
close_price | 2
intraday_max_change | 419.7301
-[ RECORD 2 ]-------+---------------------------------------------------------------------------
bucket | 2021-09-04 00:00:00+00
nft | CryptoPunk #6275
url | https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/6275
open_price | 1000
close_price | 1319
intraday_max_change | 319
-[ RECORD 3 ]-------+---------------------------------------------------------------------------
bucket | 2021-09-22 00:00:00+00
nft | Page
url | https://opensea.io/assets/0xa7206d878c5c3871826dfdb42191c49b1d11f466/1
open_price | 0.6942
close_price | 0.9999
intraday_max_change | 239.37
-[ RECORD 4 ]-------+---------------------------------------------------------------------------
bucket | 2021-05-22 00:00:00+00
nft | Meebit #16920
url | https://opensea.io/assets/0x7bd29408f11d2bfc23c34f18275bbf23bb716bc7/16920
open_price | 1.44
close_price | 201
intraday_max_change | 199.56
-[ RECORD 5 ]-------+---------------------------------------------------------------------------
bucket | 2021-08-28 00:00:00+00
nft | CryptoPunk #4189
url | https://opensea.io/assets/0xb47e3cd837ddf8e4c57f05d70ab865de6e193bbb/4189
open_price | 138.55
close_price | 0
intraday_max_change | 138.55
(5 rows)
From the query above, we can see that on 22 May 2021, Meebit #16920 began the day priced at 1.44 ETH and ended the day priced at 201 ETH, an intraday change of 199.56 ETH! We also see that CryptoPunk #6275 was bought for 1000 ETH and sold for 1319 ETH on the day of 4 September 2021, a tidy 319 ETH profit! Finally, we can notice interesting cases like the NFT Book, which had an intraday price change of as much as 419.73 ETH, despite starting the day in question, the 25 August 2021, at 0.99 and ending the day at 2 ETH.
That’s just a taste of three of the questions you’ll answer and queries you get as part of the NFT Starter Kit. To continue your learning, download the NFT Starter Kit on GitHub.
Demo Videos
As part of the NFT Starter Kit, we include pre-built dashboards to help you visualize your analysis of the NFT dataset. Here are two demo videos, one explaining how to use our pre-built dashboards in Apache SuperSet and the other how to use our dashboard templates for Grafana:
Analyze and visualize OpenSea NFT sales in Apache Superset
Analyze and visualize OpenSea NFT sales in Grafana
Time Travel Tigers NFT Collection
Meet Eon—a friendly time-traveling tiger on a mission to spread the word about time-series data to all beings across time and space. Time Travel Tigers is an NFT collection by Timescale featuring 20 hand-crafted Eons that travel through time to complete their mission, wearing various disguises to blend in with the crowd.
Each Eon is a 1-of-1 NFT with unique properties that distinguish it from other Eons. Take Eon 11, for example:
NFTs in the Time Travel Tigers collection cannot be bought. They were earned and claimed by the first 20 people to complete the NFT Starter Kit tutorial.
Shout-out to Julia Nasser and Shane Ermitano from the Timescale Design team for making the Time Travel Tigers a reality!
Get Started
To start analyzing NFT trends to bring some data to your purchasing decisions or just learn about the NFT space from a data-driven perspective, download the NFT Starter Kit on GitHub and complete the accompanying tutorial today.
The easiest way to complete the NFT Starter Kit tutorial is by using a fully managed database on Timescale. Create a free account.
Once you are using TimescaleDB, please join the Timescale community and ask any questions you may have about time-series data, databases, and more.
