This blog is written by Arran, one of the co-founders of Token Flow Insights and is written as a step-by-step exploration of the public sample of our decoded event and call data for April 2022 which was announced here.

As an experiment I wanted to see how easy it would be for a less technical user to work with our data warehouses, and also to better understand where the friction points are and to identify how we could make the user journey easier particularly in terms of documentation, example queries and analytics tools.

I have some SQL and Python knowledge but I am not an engineer or data scientist. My aim was to try to onboard from zero and to then get answers to some questions about a specific smart contract using basic SQL alone. I registered without telling the team in advance, and used an anon account so that it didn’t impact the onboarding process or support received - the other team members were not told until this blog had been drafted.

Below is a summary of the key steps, including the basic queries used so that other less technical users can reuse them.

Onboarding

Going from zero to being set up on Snowflake was relatively straightforward, starting with the EDW launch blog and then following the onboarding guide linked from there.

In full transparency the main downside is that it is not a short process - in total there were 21 steps to get to the point of being able to run queries - and personal data needed to be entered several times (name, company, email address, website).

From start to finish it took 40 minutes to be set up with Snowflake and to have access to the chosen dataset. This was admittedly quick since it was done during European working hours so a team member was online to approve the data access request.

The positives were that Snowflake handles the set up of the cloud services so there is no need to open a separate AWS account, there is no need to enter payment details to get trial access and Snowflake provides plenty of links to docs and user information.

Using the Data Warehouse

Once the tables replicated to the user account they can be queried with SQL in an easy-to-use interface.

The sample decoded dataset was used as this is much easier for less technical users. When accessing it for the first time two example queries are included in a worksheet which gives an idea of how the tables are structured and what queries are possible:

As a fan of the in-chain NFT work of the Divergence team, I chose to analyze the recent The Kiss Precise NFT mint and subsequent secondary market sales (disclaimer: I own Divergence NFTs, including The Kiss Precise).

Understanding the semantics

Understanding the schema and the semantics in the separate events and calls tables is straightforward with standard SQL queries and using the * wildcard:

// for events table:

SELECT * FROM events
LIMIT 100;

// for calls table:

SELECT * FROM calls
LIMIT 100;

I enter the queries in the SQL panel, highlight one of the queries at a time and then click the run button in the top right. In just over a second I have the results table and can see the various column names and get a feeling for the data available:

Next I can look for the specific contract that I want to analyze. From OpenSea I know that The Kiss Precise contract is 0x976...b97.

I can now run queries to filter only by that contract. Since I don’t need to see a lot of results to get just the decoded contract name I LIMIT the number of results:

SELECT * FROM events
WHERE CONTRACT_ADDRESS = '0x976a145bce31266d3ed460a359330dd53466db97'
LIMIT 10;

At first no results were returned which was a surprise. I had copied the address from Etherscan, but that was in a mix of upper and lower case. When I switched it all to lowercase in the query it worked (this can be done manually or more reliably with the LOWER() function in SQL).

Contracts are automatically decoded in this layer of Token Flow data so there is no need to request greenlisting of the contract - the decoded results were immediately available which is a great feature if you want to look at a less common contract quickly (for more info on the layers of data we provide see our roadmap post).

After running the query above I scroll to the right in the results panel and can see that the decoded CONTRACT_NAME is “KISS”. I can now search by this instead of the 0x address to make the queries more readable:

If I click on the EVENT_NAME column heading and then look in the summary panel to the right (after scrolling down) Snowflake will let me see all different distinct Events emitted by this contract with a count of the number of each Event which I can then use later to run other queries using their readable name now that I know what EVENT_NAME to filter by:

The process is the same for calls but this time instead of querying the contract address I query calls made to the KISS contract. I can also amend and use one of the examples that was in the original worksheet to run this as a query.

The queries are:

// for events emitted:

SELECT EVENT_NAME, count(*) AS count
FROM events
WHERE CONTRACT_NAME = 'KISS'
GROUP BY EVENT_NAME
ORDER by count DESC;

// for functions called:

SELECT FUNCTION_NAME, count(*) AS count
FROM calls
WHERE TO_NAME = 'KISS'
GROUP BY FUNCTION_NAME
ORDER by count DESC;

These two simple queries are the most important that I will run since they tell me all events emitted by the contract by EVENT_NAMEand all functions called in the contract by FUNCTION_NAME, both in decoded and easily readable form:

I will use this list of decoded events and functions called to target our queries in the next section.

Running simple queries

Now that I understand the decoded names for functions and events I can start to build some basic queries using the readable decoded names.

Since this is an NFT contract I want to look at mints and secondary market transactions:

Mints

Using the calls query above I can see that there are two different mint functions: mintPublic and claimCollectorMints.

So if I now filter by these two functions with WHERE and OR it shows me all mints during the period. Since Token Flow data also captures reverts I add another WHERE filter on STATUS so that any reverts are excluded and I only see successful calls.

To make it easier to look through the results table I also ORDER the result BY the TIMESTAMP.

The query is:

SELECT * FROM calls
WHERE TO_NAME = 'KISS'
AND FUNCTION_NAME = 'mintPublic' OR FUNCTION_NAME = 'claimCollectorMints'
AND STATUS = True // exclude reverts
ORDER BY TIMESTAMP ASC; // order by ascending timestamp

This query takes a little longer to run, but still only 30 seconds, and when done I can see that there were 44 calls to the contract to mint during the period with the two different mint functions:

A nice feature of Snowflake is that it has simple in-built charting. I can switch from the results to see a chart which I can tailor as needed:

To double check my results I downloaded a CSV of all transactions from Etherscan. The number of 44 calls to mint is different from the number of total mints in the Etherscan download which shows 77 mints during the same period. This is because there are calls with multiple mints during the period.

I can see this in the Token Flow data if I scroll to the right to the ARGUMENTS column - here for the mintPublic calls I see “n” argument which is the number of NFTs that were minted in each call.

The difference is 33 - so adding the two together I now get to the same total of 77 NFTs minted by 44 calls (see the addendum below on querying arguments for how to do this):

Secondary Market Transactions on OpenSea

Next I want to look at all OpenSea transactions.

I can run a query filtering by calls to the contract, and again using the decoded data from the first steps where I got all function and contract names.

I filter with WHERE for all calls that involve a transferFrom as FUNCTION_NAME and then also by the OpenSea contract which is named “MerkleValidator” in the decoded data. As before I exclude reverts and order the results by Timestamp to make the results table more readable.

The query is:

SELECT * FROM calls
WHERE TO_NAME = 'KISS'
AND FUNCTION_NAME = 'transferFrom'
AND FROM_NAME = 'MerkleValidator' // note this includes tx via Gem
AND STATUS // exclude reverts
ORDER BY TIMESTAMP ASC; // order by ascending timestamp

And the results are:

And again I can use the Snowflake charting feature to see the results as a bar chart:

When I compare this chart to the OpenSea activity page I can see that there is a match. Token Flow data shows 47 trades via OpenSea, and when I manually count the numbers on each of the columns in the OpenSea activity summary I also get 47:

Interestingly the numbers from Token Flow and OpenSea initially looked different from the CSV download from Etherscan that was used to double check the query results since there were only 41 transactions that had the method “Atomic Match” in the Etherscan CSV.

This is because during April 2022 there were 6 more NFTs transacted on OpenSea via Gem as an aggregator - this uses method "0x976a145b" in the Etherscan download rather than “Atomic Match” (manually checked on Etherscan). Adding the two together I get to the same total of 47 marketplace transactions that I saw with the Token Flow data which already included the aggregator transactions as well as direct matches.

All Secondary Market Transactions

Now to try something a little bit more complicated I want to see what secondary market transactions happened on OpenSea AND LooksRare over the period.

During the month there was volume on LooksRare, but via an aggregator so the decoded FROM_NAME that I need to use isn’t “LooksRareExchange” but is “0x31837aaf36961274a04b915697fdfca1af31a0c7” - since this contract is not verified there is no name or other semantics which is why we see the 0x address instead of a readable name.

I want to include both marketplaces and also to rename the results so that they graph better.

To do the former I need to use a combination of AND and OR with the FROM_NAME filter, but need to be careful with the syntax (it can’t just be two separate AND + OR statements as I’ve been doing so far to make the queries as simple as possible or I will end up with >11,000 rows of results).

For the latter I use a CASE statement which acts like an IF statement - if the FROM_NAME meets a condition then it renames it with the defined marketplace name - again I need to be careful with syntax as the FROM needs to be after the END that concludes the CASE statement.

The query is:

SELECT *,
CASE
	WHEN FROM_NAME = 'MerkleValidator' THEN 'OpenSea and OpenSea via Gem'
	WHEN FROM_NAME = '0x31837aaf36961274a04b915697fdfca1af31a0c7' THEN 'LooksRare via Genie'
END AS MarketPlace
FROM calls
WHERE TO_NAME = 'KISS'
AND FUNCTION_NAME = 'transferFrom'
AND (FROM_NAME ='MerkleValidator' OR FROM_NAME = '0x31837aaf36961274a04b915697fdfca1af31a0c7') // different syntax
AND STATUS = True // exclude reverts
ORDER BY TIMESTAMP ASC; // order by ascending timestamp

Even with these additions the query still completes in 3 seconds and looks like this:

In the bottom right of the results panel above you can now see our renamed marketplaces and the two LooksRare transactions added to the transaction count.

We can then graph this and see the results for both marketplaces with our new names used for the key to make it more readable:

And that’s it - all the mint and marketplace statistics that I wanted for a specific NFT with basic SQL only.

Addendum - querying arguments

As mentioned above, for calls that resulted in multiple mints this data was available in the arguments.

Once the draft of this blog was completed Tomek suggested adding the query to sum the total mint number via the arguments in case a user wants to add this to their own queries.

The query is:

select sum(coalesce(arguments['n'], 1)) as mints
from calls
where to_name = 'KISS' and
function_name in ('mintPublic', 'claimCollectorMints');

And the query results from this are 77 mints in total as expected.

Key observations

Even with basic SQL skills it is relatively straightforward to run queries on the decoded data in the Ethereum Data Warehouse, and since Snowflake includes charting it is also easy to get simple analytical outputs.

In the examples above the main information of interest for an NFT contract - mints and secondary transactions - was easily obtainable. All the queries and analysis above took less than an hour in total even with limited SQL skills.

There are a few steps to go through to get access to Snowflake, but once you are in it is a user friendly and extremely powerful platform.

The experience could be made even easier with an analytics layer on top of the data and we are already running Proofs of Concept for several analytics tools that can sit on top of Snowflake and that would allow users to easily run queries without needing to write (too much) SQL. We will communicate the results of this in the near future as this is key to making the experience more friendly, particularly for less-technical users.

This will also help with the friction that comes from the Snowflake onboarding process which is straightforward but has a lot of steps in it. An analytics toolkit would allow users to go straight to seeing outputs rather than having to set themselves up on Snowflake directly, but would still allow the power of Snowflake to be realized.

In the meantime a library of simple queries and examples would help less technical users to be able to run and to edit queries rather than having to write them completely themselves. We are working on this and will share more examples soon. This blog includes a lot of the basic queries used and is a starting point in this effort.

As always we welcome feedback and hope that this summary of our “mystery shopper” approach to testing our own products, and our frank feedback including the negatives, is useful to less technical users who might want to try to run their own queries.

Learning SQL

If you want to learn SQL (or data science more widely) the Ethereum Data Warehouse is a perfect playground since you can run endless queries on decoded data from the sample and quickly see results that are easy to understand.

A good starting point is W3 Schools which has free tutorials and also a helpful SQL keyword glossary.

If you want something more structured both Codecademy and DataCamp have free courses and reasonably priced pro tracks for SQL, Python and data science in general.

If you want to challenge your new skills Codewars will put you to the test.

Follow us for news and announcements

You can follow us, sign up to our newsletter, and join the conversation at:

Web: tokenflow.live

Blog: tokenflow.live/blog

Roadmap and ideas board: roadmap.tokenflow.live

Twitter: @insightsFlow

Discord: discord.gg

Banner image credit: Photo by Jordan Madrid on Unsplash

A non-technical user’s guide to the Ethereum Data Warehouse