# How to use Dune Analytics like a degen

By [Alex Kroeger](https://paragraph.com/@alex-kroeger) · 2021-11-16

---

Querying events from unverified contracts
=========================================

So you want to write a Dune query for some quick and dirty analysis, but the contract isn’t verified on Dune! \*starts sobbing uncontrollably 😭😭\*

But degens aren’t deterred! With a little understanding of Ethereum events and a little effort, you can be on your way to querying.

In this post we’ll walk through analyzing some data from a contract that is yet to be verified on Dune--Juicebox’s [Terminal V1 contract](https://etherscan.io/address/0xd569d3cce55b71a8a3f3c418c329a66e5f714431#code) that is being used by [Constitution DAO](https://www.constitutiondao.com/).

I realized partway through writing this tutorial that Richard Chen already used the methodology I’m discussing to create a query for summing the amount given by each contributor to ConstitutionDAO, so to give due credit to the Gandalf of Dune wizards, I’ll be breaking down the steps to get to [his result](https://dune.xyz/queries/244441/456924).

### Ethereum events

Events (also known as logs) on Ethereum are a way of storing historical information that is significantly cheaper than contract storage. While this data can’t be accessed by contracts, they can be easily queried from an application.

They are typically used as records of pertinent information from a transaction, so they are also great for analysis.

In this example, we’ll look at `Pay` events from the Terminal V1 contract, which have information on who paid into the DAO and how much. I pasted an example of one of these events below [from Etherscan](https://etherscan.io/tx/0xd0f984134f92d81e243a83af1305afc9e827bec1606e681aaa61b695bb6e98b4#eventlog).

![An example Pay event from tx 0xd0f...8b4](https://storage.googleapis.com/papyrus_images/4932bb9d80ab4777a272e5a57f4e6a8a6cd54a9a4c7fe30cebe0132a033d70c2.png)

An example Pay event from tx 0xd0f...8b4

### Getting the events we want

Dune Analytics has a table of all the events that take place on Ethereum--`ethereum.logs`. But how do we get the events we want?

Events have up to 4 indexed parameters to make querying from a node easier and faster. Dune stores these in `ethereum.logs` as `topic1`, `topic2`...

The first topic is almost always the “event signature,” an identifier for the event.

There are two ways to get this:

1.  Grab it from an example event on a block explorer
    
2.  Compute it yourself using the event information
    

For the first method, if you have an example transaction and the contract is verified on Etherscan, you can grab the first topic from the event log. In our on-chain example, you can see that the signature is \`0x3deb3bb81c337489381f7685daa204d30e6a265dac443a718949c258e3e17317\`.

![Event signature as the first topic.](https://storage.googleapis.com/papyrus_images/25ff6a317838e667915096c3384c54b5461967eb8c94bff4fea7559dffbeec57.png)

Event signature as the first topic.

Say you don’t have an example transaction handy or the contract isn’t verified on Etherscan, doesn’t matter if you’re a degen! If you know the event name and parameter types, you can compute the signature yourself!

The signature is the keccak256 hash of the event name and argument types:

`keccak256('eventName(arg1 type,arg2 type…)')`

For the `Pay` event, it’s:

`keccak256('Pay(uint256,uint256,address,uint256,string,address)')`

Using node and [\`ethers\`](https://www.npmjs.com/package/ethers), we can run:

    const ethers = require('ethers');
    
    ethers.utils.keccak256(ethers.utils.toUtf8Bytes('Pay(uint256,uint256,address,uint256,string,address)'));
    

We get the same result:

![Using ethers to compute to the event signature.](https://storage.googleapis.com/papyrus_images/a3638563c11cb43ca47f0dde598937d0d782a073cc9064175bacb09a5528119a.png)

Using ethers to compute to the event signature.

Finally, we can use the event signature and contract address to fetch these events [in Dune](https://dune.xyz/queries/247743):

![Using the event signature and contract address to grab the events we want.](https://storage.googleapis.com/papyrus_images/47cd9e88a94b406bc63b974beaeafeb687f6e6e996c8793340360c9ab779efdd.png)

Using the event signature and contract address to grab the events we want.

### Using the event data

Now that we have the event data, we can start to look at some numbers.

Looking back at our sample event, let’s look at what information we have:

![Event parameters.](https://storage.googleapis.com/papyrus_images/23bc971ce5cda7944865eb3d8f406f1841ce9d63188cd307893a5c4d63b8b55b.png)

Event parameters.

We want to see the contributions to ConstitutionDAO by contributor. You can see there is a `projectId` field on which we’ll want to filter, a `beneficiary` field showing who contributed (and therefore should be the beneficiary of DAO tokens), and an `amount` that we’ll want to sum.

`ethereum.logs` has 4 columns (topic1-topic4) for each of the (up to) 4 indexed fields. `projectId` will be in `topic3` and `beneficiary` will be in `topic4`. `amount` is concatenated with the other non-indexed parameters in the `data` field.

You’ll notice that the data fields (`topic*` and `data`) are not nice decoded values. Rather, everything is a bytes data type.

For addresses, we want to the hex string anyway, so all we want to do is effectively cut out the leading zeros in the bytes with the `substring` function like so:

`SUBSTRING(topic4 FROM 13 FOR 20) AS address_from_topic4`

For `projectId`, we want to get a numeric value. We can use the `bytea2numeric` function to convert the raw bytes to a decimal number like so:

`BYTEA2NUMERIC(topic3) AS project_id_from_topic3`

Last but not least, we need to combine the previous two functions to get the `amount` parameter. Since `amount` is one of the non-indexed parameters, it is concatenated with the other non-indexed parameters (`note` and `called`). Since `amount` is the first parameter and it’s 32 bytes long, we can clean it up like so:

`BYTEA2NUMERIC(SUBSTRING(data FROM 1 FOR 32)) AS amount_wei_from_data`

You’ll notice that this is a large number, because it is the amount of ether specified in its smallest denomination--wei. We need to divide it by 10^18 to get the amount in ether:

`BYTEA2NUMERIC(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_ether_from_data`

We can check all these parameters are correct by comparing [our query](https://dune.xyz/queries/247747) with our sample event.

![Spring data cleaning.](https://storage.googleapis.com/papyrus_images/f8396bc0ebadf52fbd8b7151cdf6c3e7131b264025348471098753f544ca1681.png)

Spring data cleaning.

![The sample event.](https://storage.googleapis.com/papyrus_images/dfed7ab62b057b8f4458ae9ba755f8f5409970a0301ce032a58e6c099d3802ff.png)

The sample event.

Looks great!

If we apply everything discussed herein, we end up with [Richard Chen’s query](https://dune.xyz/queries/244441/456924) for the largest contributors:

![The final query.](https://storage.googleapis.com/papyrus_images/034f7fe40ed3ae5469a98514b83bf50286202316d6ea9a5b22f49005487b74ee.png)

The final query.

Go forth and query, my fellow degen wizards.

Appendix
--------

One thing to watch out for when doing this is are parameters that aren’t a fixed width in bytes. For example, in the `Pay` event, there is a `note` field of type `string`. The `string` type can be an arbitrary length, so if you’re querying in the data field for a parameter after it (like `caller`), it takes some extra tricks. I’ll leave that to you to figure out :)

---

*Originally published on [Alex Kroeger](https://paragraph.com/@alex-kroeger/how-to-use-dune-analytics-like-a-degen)*