Using Dune Analytics for Crypto Research
Updated: Nov 12, 2022
Wait what? Analytics for crypto? Isn't it all scam or ponzi?
Yeap, whatever you do on the Ethereum network is all recorded in the blockchain. If you do bad stuff on Ethereum, you can bet that someone will track your ass to the ends of the earth. To view these data, the most prevalent method would be to head over to etherscan.io but it has limitations with regards to data extraction/visualization.
This is where Dune Analytics fits the niche.
Dune Analytics is a blockchain analytic tool that allows you to query, retrieve and visualize data. The team has pre-prepped the data for most major protocols to make it easier for Dune wizards.
If you are familiar with SQL, this might interest you. If you aren't, don't worry as there are tutorials to get you started.
So, the idea I had was to learn how to create a dashboard which will present the relevant information to figure out how market feels about CEL token. Why CEL token? Uh... cause there wasn't really one for it lol.
So yeah, managed to piece together a dashboard (link) using my noob SQL skills and limited data analytics knowledge lol.
Let's go through the dashboard starting with the charts on the top left and talk about my thought process of setting up.
I have a counter that sums up the total number of CEL holders based on any CEL amount they currently possess in their cold wallet.
Annnnddddd it looks like the total addresses has dropped compared to when I was working on the dashboard. An indication that some CEL holders had been dumping or deposited their CEL into Celsius wallet.
On the right is a chart that tracks the number of wallets that had previously held CEL. Total amount of wallets is actually three times of the amount showed on counter amount which could mean 60% of holders have either stored it in Celsius wallet or they dumped lol. Nonetheless, still a positive sign to see a growing trend.
Here is a chart and a table that showcase the top 100 CEL addresses. Note that it doesn't include wallets that have deposited in Celsius Network. I treat it as some kinda leaderboard to see how many CEL you need to make it to top 100. Currently it's minimally 58888 CEL tokens (huat ah), worth about $400k+ USD at the point of writing.
No. 100th address must be non-asian, holding 58887 CEL lol.
Under the address column of the table are links that brings you to the wallet owner's address etherscan which makes life easier to see more details such as latest transactions etc.
It is also interesting to observe that these people were unwilling to give up their personal details for 4.86% yield on Celsius Network.
These two bar charts show the monthly outflow and inflow of CEL from exchanges. FTX listing CEL was definitely beneficial towards increasing trading volume and allowed more people to have exposure.
It's also a good sign when more CEL tokens are taken out of exchanges because it reduces the available liquidity.
Unfortunately, the opposite trend was observed on the DEX side as we're seeing way more CEL being sold particularly in May. Despite having over $200 mil sold, it doesn't appear to faze CEL's valuation much.
Uniswap is still the preferred DEX to trade CEL tokens, dominating in combined volume.
WETH is trader's top token of choice, with a slight exception of USDC. It's no wonder some say that ETH is money.
For anyone interested to LP your CEL/WETH, this might be the pool for you.
Finally, the most important table that checks whether whales are dumping on us or are they buying up all the tokens so us plebs have lower chance to accumulate. Basically, this table will record any CEL transfer that exceeds $1 mil in value.
Why watch whales? Maybe try to catch on what strategy they are executing? I mean they must have some edge which allowed themselves to reach whale status lol.
Based on recent entries, whales had been sending their CEL to FTX so I'm guessing either they are risking off or they are trying to spook the market to sell so that they can accumulate for cheap.
Likewise for this table, evt_tx_hash column contain hyperlinks which leads to etherscan... gotta love the quality of life.
I also paired the table with a bar chart below to easily visualize daily CEL transfers.
Closing thoughts
On-chain analytics is a very intriguing topic (at least to me) that provides great insights on any protocol or even the Ethereum ecosystem. These data can be utilized to make better decisions or verify whether dev teams are trustworthy, carrying out tasks as they claimed to be.
One example of good usage of on-chain analytics was the SAFEMOON protocol where the devs claimed that they burned the tokens. Instead of sending the tokens to a burner address, they actually transferred all those supposedly burned tokens to another address. Of course, it didn't take long for people to catch on and became a huge hoo-hah. The price crashed yet some still believed in SAFEMOON and bought more.
Overall, it didn't take me very long to pick up and come up with something. Probably less than 2 weeks to learn and create some queries? I will place more focus into this, picking up SQL courses, play around with the data to gain more experience and hopefully get paid doing so in the future.
Disclaimer: The information listed here is accurate at the point of writing. I am not endorsed by any of the platforms mentioned here. It is strictly my personal opinion and should not be regarded as investment advice. Please do your own due diligence.
If you like what you're reading, do like my Facebook page to receive the latest blog updates!