Dune Workshop | hildobby, Boxer and Danning Sui | ETHDam 2023

foreign [Music] welcome to this short workshop on finding out how to tune around with crypto data and if you're not familiar with Dune um that might be a bit confusing but what we're going to teach you today is data in web 3 is public by default public by default is most likely a feature not always a bug web3 data really levels the playing field and open data leads to better system-wide outcomes so first of all June is a data analytics platform we basically index ethereum and I think seven other evm chains and Bitcoin in Solana but that's not not the point today we're going to talk about web3 data more broadly but all of our researchers here are using June today but you can do this on any other platform as well um so what is so special about web3 data is that it's open and if we look on the flip side of this data traditionally in businesses has always been locked there's no way you can analyze what is happening on eBay or what is happening on Amazon and if I'm in employee of eBay I cannot look at yeah what what the other companies are doing and that's very sad because we can learn a lot from data in the world in general um if there's publicly if there's data sets that are broadly aggregated they mostly pay for Access so all of you guys are probably familiar with Bloomberg or statista where you're like yeah like you got to pay me like sixty thousand dollars a year to like even access this data and that's not very fun so in web three um auric and one final thing about this is also like if the data actually gets published it's like in quarterly reports like we're basically like everything happens at the speed of light um the Silicon Valley blank got blown up in a weekend and we're like yeah quarterly reporting is like that's the that's the speed of uh reporting we should have in the 21st century um not very great so in crypto on the other hand there's literally this website which is called transactionstreet.com in which you can see transactions are flying over the blockchain in real time um so very exciting and all publicly openly available so if you look on the left here um you can kind of see the different um yeah it looks like houses going out but that's really where the transactions are going in so it's like a bit inverted but that's not the point here um the point of this is um everything is live everything is accessible and everything is public um and why is that all the case um because we're all using this shared public backend which are evm chains or Bitcoin or uh Solana um anyone can analyze it so this is what Adobe and Denning are gonna go into in a bit and it's real time it's flexible and it's collaborative so um not only can I work on this data alone but rather I can team up with these guys and our whole Community basically teams up on a daily Manner and they make sense of this data together um so yeah instead of getting quarterly reports every three months like quarterly um and in an EPF where like we don't even know what's going on um we now have uh real-time financial statements on on June or in any other Webster data website and this is um from maker Dao and this is basically their profit and loss statement um I think of the last year so with that um who has like the wonderful thing about this web3 data is um that anyone in this room basically has the same access as vitalik himself does to this data because it's all public out in the open so it really levels the playing field and with that I think I'm gonna hand off to hildoby who's one of the researchers in June or one of the users of Dune and uh he's done a lot of crazy research hello everyone um so I'm Hill Dobby I work at dragonfly Venture Capital firm and I actually got this job by first freelancing on this Dune thing so uh I want to showcase how it's kind of different kinds of users Dune caters to and the first and foremost is probably the biggest User it's just the guy who wants to view dashboards and there's already a lot of dashboards available on June if you just search for yourself you'll find stuff that's already out there for nft dexes these are just random high level charts but I think they're pretty they're pretty interesting and the cool thing is the way I started building on it is by looking at what other people built and then forking their queries and basically not there that's also How I Learned SQL and how it all worked and this is what like the collaborative aspects kind of a boxer touched on I think is heavily under leveraged uh basically there's already the stone of work that is already out there and you can base it on this and build on top of it and yeah then you can also use something which is called a spell book that is a dune product where it's basically a GitHub repo where anyone can contribute and build the back end of June of all these tables that are available so some some of those are the high level like nft tables dext tables get coin I don't know I put some random stuff where those are like two sectors then you'll have uh some community members contributing and creating git coin tables so people anyone can look into those and then there's also like optimism team who is putting available the whole CSV of like who was eligible for the airdrop so that anyone can actually look into it and analyze the data and I think that's pretty cool um and it also abstracts a lot of the work away for most users where if you want to look at nft trades uh if you want to do it from raw tables that would be kind of Hell uh because there's a lot of stuff going on but here you can just look at NFC or trades and then you got all this work already done and you can look into the actual data analysis part so it kind of splits the data engineering from the data analysis I kind of like doing both I also think there's a lot of interesting stuff in the engineering Parts because the data analysis is only as good as your understanding as well as what's on the back end but you don't necessarily need this if the backend is built when and I think that's the case for uh for June which has a growing level of data sets that at this point is probably one of the better uh amount of free data sets that you can use all around so I actually hacked on something over the weekend which is the Mev trades which is the big topic this weekend I feel so I thought it was pretty relevant and I want to show how I could leverage student to just show to look into some stats everyone's building on stuff but few actually looking into the stats and I think that's unfortunate so I wanted to actually create a data set of all the sandwich trades on Dune and I Define those with a few heuristics that I'm just going to quickly explain so you it makes sense but basically here I'm joining the Dex dot trades table on itself checking that in the same block so here I'm looking at block time because it basically means to say block but don't worry about it uh that is a different transaction that they happen in the first one happened before the second one that it was the same pool that it was either submitted by the same person or that the taker of the trade is the same person uh that the tokens sold in the first transaction was the token ball in the second one uh and vice versa and also uh this is a heuristic to make sure that like sometimes they won't sell the exact amount they bought during the sandwich so that's kind of like buffer of 0.9 to 1.1 so 10 kind of buffer uh both ways and I also want to make sure that well this isn't necessarily necessary but basically I'm looking at is it profitable of a trade uh I could look at also unprofitable by just commenting this out and then I also checked that there was actually a transaction a trade or two at least one trade that uh was sandwiched in between so that is fairly simple heuristics that I want to put out there in like on Spellbook and I'm working on like having Spellbook uh data on this and here are like a few charts I looked into and I think I just want to show like The Power of how Dune can use can be used to like Leverage uh those kind of topics that I feel like I have heavily discussed but don't necessarily have a lot of data backing it and of course there's a lot of data websites that focus on it but it probably is gathering a lot to protocol Builders and more people who are deep into it I kind of want to make sure this data is also accessed and shown to more uh normies I don't know how you call them other users so here are like some high level and you can see the share of gas how it's been spiking lately the number of front-end protection so if you want to look at the sandwich it's actually times two because there's the front run and the background and then this is also interesting I think is the average number of transaction in a block that are between the front row and the background you can see how this is around when flashbots launched and this is basically it's been launched in January 2021 and it took like a few months to get adoption but before it was a lot of kind of guesswork there was there was a bit of everything but there was a lot of people trying to optimize with gas to maybe with gas and like optimize so it means you weren't actually bundling exactly in between the trades but could be you know somewhere else in the block as long as it was in between somewhere so that's where you can see the flashbots like now there's a lot of a lot fewer transaction being sandwiched and these are like transaction not just trades so just to so you know and now also you can see the number of blocks on ethereum with Mev is actually spiking a lot uh and you can attribute it to a lot of things but uh I think it's just the the the space moving towards optimization in a more optimized State um yeah and I actually want to leave this on to uh friend Danning who's a networking with flashbots all right cool yeah I actually want to build on top of what Dobby was talking about me so like first of all we need to explain what is the MVP to people yeah so so like a very simple scenario say you're trading on a decks like any Swap and you say I'm a user I'm coming in I want to trade uh usdc for ethereum and then you submit your transactions through a public RPC and basically your transaction gets broadcasted to all the people who are looking at the public member like basic transaction pending pool basically the other charts that um boxer showed where everyone's like lining up before the bus and then usually the mechanism is that like your your position in a line is designed decided by your gas price that's basically the whole gas auction design of ethereum as a layer one but then there's are there are people who have privileged knowledge for example if they are like looking at their public mempool um which is like what we cannot do as a normal user if we're not running a node or we're like checking the node um they will be like okay so there is a fat trick it that's like trading a meme coin where the liquidity distribution is very hard to say um spiky and then like I can submit my trade before it and I like my background after it and in this case I'll be like I'm gonna catch that transaction before POS it's gonna be like I'm gonna try to um raise my gas price and then try to put my trade um exactly in the position I want and so to make some Arbitrage and that's like the biggest bad thing is um it will cost a gas congestion for the Ethereal Network and then after POS and then now it's all changed to another sort of like a immediate supply chain thing it's basically um now there's a this row called Builder they're like compiling all the bundles they receive um The Searchers are sending the bundles basically who are the those people who are looking at the transaction pool and trying to make a you know profitable Arbitrage um they're gonna be like oh now I I want to compile my trade before or after this trade and put them together and as a bundle and I submit to the bill Builder and then the Builder will be basically then sequencing the bundles and then the Builder can also V2 relay who doesn't not supposed to touch the content or on bundle it but just like validating the syntax and everything and then now there's another bidding layer for the validator who is basically used to be minor but now after POS it's proposer they are not supposed to change the broad content or whatever but they're just like picking whatever the block with highest value sending towards them so this is like a basically implementation from flashbots called Meb boost and which is a sort of like implementation for the PBS Pro poster Builder separation so to make sure the proposal don't have too much power to decide everything and because now after POS or like proof of staking um the basically Network powers in those who are staking ease and so if you are a validator and you can accumulate more and more ease from the Mev you can attack then they can re-stake more and then it becomes very centralized so that that's a big problem after POS and a lot of people are working on it and so I go back on the Dune topic here is that right now because I mean booster is a off chain piece of um infra and so the data is like living in the different relays API it's definitely accessible um but we also seen the others like Community websites and and charts which are like really great you can check out like MiMi boost pictures uh relay scan but um as W was saying that like we want more know me people right to to query it or basically make it more accessible or easy to understand and so more people can try to translate or interpret it and that's the whole point about Dune is basically um I think I think like transparency of a blockchain is only guaranteed when people are able to like analyze the data and people are able to make sense of the data so yeah I go back on that um um okay so here are my three slides um so I I want to like summarize what you can actually analyze on Dune so I would say it's like a very interesting journey to see that like maybe starting from 2018 when I was just starting to analyze like blockchain data um there wasn't much like interesting activity going on maybe there were like some NFC like crypto kitties or um maybe one or two decks but then um most of the stats were much more boring more like hey how is the ethereum Network Health look like um so actually you can still do it right now today on June and there's a bunch of like really insightful charts uh here and there um looks a little bit out of the style but I was just like cool um for example um the first one is a net ethereum emission by time from Michael so basically after eip1559 all the East gas fee part of it is base fee which is going to be burned and so this introduced a potential of like ethereum Supply or emission will be negative basically it can become a deflationary assets and so this chart is actually starting from like May 14th till today and the the timeline was a little bit hidden um so so yeah like actually starting from there if you look at the data like ethereum being deflate deflate deflationary every day um well we looked at further uh cross-checked with like ultrasound money the data seems cracked and likely it's it's because of the meme coin um case you know a lot of people are trading those meme coins like burning their gas which is great if our you know economics of ethereum right and for example like starting maybe from two years ago there's different L1 L2 coming up like optimism Arbitron polygon BSC and people would start to wonder like how much assets are flowing into those layer one layer two um one way to look at it is the ethereum bridging tvl so basically there's a lot of bridge protocols where you can stake your assets in ethereum and then basically withdraw it from another chain and so one One dashboard from Ilias is on the right upper side looking at like what's the TV all that sticked into others Bridge across different Layer Two um apparently polygon has the most assets right now and then it's like Arbitron and optimism there were some other layer one layer two coming up and then now kind of like died down um the other charts down here is basically compare different networks cost of like basically making a trade we kind of know I I'm pretty sure everyone has the impression that like ethereum is too expensive to use for a majority of the people um it's usually if we look at this chart we can clearly have an understanding that you're gonna spend averagely like seven dollars for doing anything on ethereum um comparatively um optimism and uptron are like much much lower cost like one is 35 cents another is like 10 cents um and also Avalanche BNB also have like a 10 cents kind of cost um so I think it gives us our normal user a better a better understanding of like which one is more you know possible to use which one is very costly um so so okay so actually re-entry arranged in another slide sorry right okay cool um right so those are stats about like very basic or house at low level like Network Health stats um and I would say like go one level up to look at like a more higher level thing is is that like one very powerful thing is that you can look at the market state of our industry sector or like vertical or subdomain right like so for example um we can look at the Dex aggregator market share um since the beginning of um when they come up um the timeline is hidden it's starting from like something like 2020 and you can pretty much see that like one inch started as a first one and then the Xerox API Power Swap become pretty much the other two big players um as of today it one interest is still very dominant um so but it's good to see that the market getting you know more and more um split up like decentralized there are more solutions for people to try it out um and yeah it's very interesting for both of the projects to look at like say what's our market share um usually you wouldn't get that data in like tradify or anywhere else because it's very if the your competitor is not a public company it's very hard to find out the data about them but everything on blockchain is indexable like so any of you can basically figure out like what's the market share of say a DEX or a DEX aggregator or like an empty aggregator another example from Dolby is this interesting comparison actually of the entity nft market share in terms of volume and also trades it's very interesting that we see that the one on the left which is blur they have like the gigantic volume share which is like 60 of the market share but then we've looked at the trades then somehow it becomes openc becomes like slightly more dominant which is like half of the market share one thing you can tell is that basically apparently like blur trades are much higher volume or basically maybe um they're they're having a lot of maybe Blue Chip trades right now is happening on blur you know um if you can maybe have another look on the trader spillet that would be also interesting um to bring different perspectives um projects look at it and also investors in the crypto space also look at all these on-chain data for uh due diligence I guess that's why they hired Dolby right on VC yeah uh besides a market share projects health and like Network you can also analyze the user base of project one chart here it might be slightly small it's basically looking at the how the user segmentation of uni swap look like both on V2 and V3 I'll explain it slightly in detail so the new means like this month how many new wallets we see made made a trade on uniswap that's that's never active on unit swap in the past so meaning we kind of Define it as a new wallet and then retain it means this wallet was trading last month and also trading this month and then churned means this wallet was trading last month but not trading this month and then return means this wallet traded before but not trading last month but trading this month so basically new and retain and return together basically is like monthly active user or wallets to be accurate and then turn is like churn user so it's very very interesting to see like the TR you can analyze apply this you can copy paste this query and just like change how you define user into any projects and to understand like how's the user distribution look like how the user growth look like maybe a project encrypt like crypto project they did like a huge campaign they're like compensating people to hey come trade but then people are going to be you're gonna see like a huge Spike of new and then next week like huge backup turn and then uh maybe um if the return will be pretty small because a lot of people may be just like hunting on or like farming a little bit of incentive um so it's a it's a good way to analyze um your marketing campaign or basically user growth um another maybe further metrics you can develop on top of this like so if you are working in a product team your gross manager will be like hey we want to design this campaign um but I don't know how much budget we can put on it can you help me to maybe Define lifetime user value um and then so we know how much um how many users we may be able to convert and then on top of that how much lifetime value those users will bring and with that you can probably tie this number of user kind of data to maybe how your projects is making Revenue so basically if one user bringing in like 10 cents Revenue then from there you can Define how much budget you can cost for you know all those growth campaign all that another chart interesting here is that um it's similar basically okay cool cool cool um yeah yeah so it's a similar one just look at new user growth um yeah I just want to show uh all kinds of data you can analyze and uh thanks for listening and coming everyone [Applause]