How to start analyzing any Web3 protocol or product using SQL (in just five minutes)
Get started with analyzing any contract on any EVM chain (on Dune.com)
The “cold start” analysis problem is a big one in web3 - protocol contract structures and flows all vary wildly, and learning solidity patterns isn’t necessarily easy. Figuring out what functions/events from what contracts to analyze is tough, and finding example transactions of all the different cases can be time consuming.
So, I’ve created a Dune dashboard to make every analysts life easier (linked here). Go give it a star if you find it helpful!⭐
If you don’t know anything about Ethereum or SQL yet, you should start with this guide and then come back here after.
How to choose which contract from a protocol to start with
We’re going to use Seaport 1.1 (Opensea’s most recent marketplace protocol version) as our example contract for this article. If you don’t know what protocol to start with try out one of trending contracts from 0xKofi’s dashboard.
I typically look for the list of contracts and deployment addresses either in a protocol’s GitHub repo or gitbook. In Opensea’s case, I found the addresses in their repo readme.
You’ll see they have two contracts listed there, a “ConduitController” and a “seaport 1.1” deployed on multiple chains. Sometimes you’ll see a dozen different contracts listed - I know that’s overwhelming, and that its hard to even figure out which contract to start analyzing. There’s almost always one contract that acts as the main “point of interaction” for users, and then another contract that holds more of the low level logic (and often acts as the actual token “storage”). For seaport, the “seaport 1.1” contract handles all the kinds of trades and the “conduit” actually manages the transfers and permissions on tokens.
The easiest way to figure out which is the “interaction” contract versus “storage” contract is to check which one has more direct transactions. You’ll notice that the “seaport 1.1” contract has more than 10 million direct transactions (user called the contract directly in the “to” field).
Whereas the ConduitController contract only has 15 direct transactions to it. Cool - so now we have chosen a contract to start working with, let’s take the address and select the “ethereum” chain and apply it to the dashboard:
ABIs, Functions, and Events
The first step once you’ve selected a contract is making sure it’s been decoded. The “Contract Metadata” box will tell you if it has or hasn’t - if it hasn’t, then you’ll need to submit the contract here.
A contract’s Application Binary Interface (ABI) is essentially like the OpenAPI/Swagger UI interfaces you usually get with a usual API. All of the publicly callable functions and emitted events will be shown here. Internal functions like _mint() on an ERC20 token wouldn’t show up.
I’ve organized the ABI into functions and events sections, allowing you to get a quick overview of the parameters involved with each one.
The “Decoded Table Name” can be directly copy-pasted into the Dune query UI, and each parameter will be it’s own column in the table. But don’t go querying just yet! We need some context behind which functions/events are most popular and some intuition about how they’re used.
So - that’s where the next two tables come in! These will give you the signatures, examples, and times called to really narrow down starting points for analysis.
In our case we can see that “fulfillBasicOrder()” is the most popular function called, with “cancel()” coming right after that. If I have no background on the contract, then I’d focus on understanding what these two functions do first before trying to look at any other cases/functions.
On the events side, I’ve added a function match so that you can see what functions emit each event.
We can see that almost every function emits “OrderFulfilled”, so I can probably assume that I’ll be using this event to drive analysis (rather than combining all the different function calls).
In the future, I might discover edge cases that require me to join some specific function table back in to get data that is missing from the event table. But that’s a later problem - the more tables you choose to ignore at the start, the better. You’ll never get started if you get stuck in a loop of endlessly exploring every function/event table.
Also remember, even if an event and function seem to have the same parameters that doesn’t mean that for a given transaction those values will match! Always double check the values are expected with some example transactions before you start your analysis - you’ll thank me later.
Getting context around contract usage
Now that you have a high level intuition around functions and events, let’s do some basic time series analysis to see if there are any interesting trends to immediately dig into.
In Seaport’s case, it doesn’t look like their are any wild spikes or changes in events emitted. However for calls, I can see that in October there was a large increase in “fullfillAvailableOrders()”. Nothing in the contract could have changed, so this means there was likely some product/frontend change that led to this function being called more than others. I would make a mental note of this, and see if I can figure out the root cause of this spike either from the data itself or by asking the developers in the Opensea discord.
If you had put in Aave v2 into this dashboard, you would see that there was a large spike in liquidations in certain time periods. This would help you narrow down periods of activity to analyze further.
We also want to better understand who is using this contract, so let’s do some basic wallet analysis. Note that all the interaction counts here are of EOAs (i.e. addresses that initiated the transaction.
I can see here that Seaport has seen a stabilization in both new users and returning (old) users.
The top users are listed in the following table, clicking into them and looking at their wallet balances/holdings might give us some telltale signs about the protocol too.
I can look at the basic labels here to see if I recognize their ENS, and what their NFT/DEX activity looks like. I might have wallet balances in here in the future too! The top user here has no labels, and that’s because it’s actually Moonpay transacting on behalf of users who are using fiat to trade NFTs.
You should already have enough to go off of to quick start your analysis. Go and start querying already!
Studying the composability of the contract
Now this is the most advanced part of the dashboard. If you’re specifically interested in what protocols are building off of this one (or what protocols this one builds off of), then this part is for you.
By looking at internal transactions (traces) of contracts calling contracts, we can see which other protocols are calling Seaport the most.
If you’ve heard of “floor sweepers” or “aggregators” before, then you’ll recognize many of the names here - Gemswap, Blur, and Reservoir to name a few. From here you can see which protocols are integrating most competitively on top of Seaport! Notice the big shift from green to purple as Blur has taken market share from Gemswap (mostly due to the airdrop though). You could compare this chart with the usage ones above to understand what percentage of transactions are being driven by direct use of Seaport versus through another protocol. All blockchain protocols are composable, so it’s good to keep in mind that there are always multiple channels of competition and growth to study.
The next table gives you the cumulative view, with every column being clickable to help you explore the contracts above with transaction examples!
And last but not least - all the top contracts that Seaport calls. Remember the conduit from earlier? Well, here we can see that Seaport always “passes on” the function call to the conduit to actually pull off the trade. You could put the conduit address into this dashboard next to then see what it calls!
Building a data wizard’s etherscan
I hope you’ve found this dashboard and this guide helpful for getting started with analysis. I’ll be using this more in my upcoming data dives and classes, and hope to build more data wizard tooling to make our lives all much easier.
If you have any ideas or suggestions for data analyst tools, please email me or DM me on Twitter!
continue learning! subscribe now :)