Dune Analytics 🛠 Open Data for Open Finance

[Music] [Applause] [Music] welcome everyone um this is my talk on open data for finance and i'm gonna try to break down what is special about web3 data and how you can work with it on yoon and maybe maybe a bit of background around myself i'm a this is administration dropout so i wasn't too technical before working on june and i think it's a really approachable career to break into web 3 using data science or data analyst since the space is like very nascent so yeah if anybody's looking for a job web 3 analyst is definitely something that is very badly needed and you can you can become one in no time i think um [Music] so yeah um what i want you to take away from today is that with web3 data is radically open it's fairly straightforward since we're just analyzing the outputs of a computer and web 3 data really levels the playing field and we're going to go into each of these in a bit more detail now so who here has traded on a dex in the last 30 days decentralized exchange dex yeah um so yeah congratulations you made it into this picture um so yeah this is pretty cool um this is all the decentralized exchange volume uh over the last 30 days of all the different decentralized exchanges and this is pretty cool already but we can go a level deeper so if you look at this right now the big pink bars that's uni swap so you can see that uni swap is really dominating this field and there's already like one special property is that we can just analyze all the different projects and all the different products of those projects against each other in a very open manner uh that's not possible in web 2 try to like see the like say a volume of playstations on like amazon with ebay with best buy or something it's just not possible but in web 3 since we're all using the shared backend and it's public um we can just analyze these projects against each other um and then we can even go a level deeper so this is all the decentralized exchange pools for the usdc wrapped ether trading pair so what we see here is that the uniswep v3 uh 0.05 ftier pool um that one is dominating by far so that's like this purple color which which are the big bars and then uh surprisingly unicef v3 with the 0.30 percent uh feet here is the second place so that shows you how good uniswap's concentrated liquidity mechanism algorithm however they call it is working in a decentralized exchange market that they can even like even if they switch to a higher heater it's still better execution and better and better experience for the end user um that um this this this pool is still better than let's say sushi swap or balancer or a dodo or whatever so yeah that's fairly interesting um so we can go like all the way back to like project level then we can break it down into projects and then we can even go into like the different products of those projects so uh it's pretty open and then if we want to go like even one level deeper we can actually just look at the individual traits of those pools and see like our whales trading in here um so um the the other spa here is this this blue color and um yeah it's fairly interesting that um most of the volume that gets made up so the others bar that's like way more people um but there's just like a few individual players in the market who make up all the volume um also known as wales i guess um so yeah web3 data is radically open um everything is super public there's no hiding um i have no affiliation with uni swap or curve or balancer or whatever um june of course like we have we have like um we talk to them but they can't stop us from analyzing their data um so there is no hiding if your product is failing you're failing in public um but there's also this thing where um if somebody has tried something before i can actually like analyze what they did so if i want to start like a very similar project to what urine is doing or to what uniswap is doing i can just like go into their actual product metrics which would in web 2 that's like unthinkable that i would like have access to all these metrics in like such a granular form um and i can just go in there and actually learn from them or see um if i'm sushi swap i'm like which trading pairs am i actually winning is there even any like what what can i do to make my product better um and then also um so if we break it down to this user level um we can actually analyze our users very easily without having to utilize like google analytics or um i don't know like third-party trackers but rather we can just take the wallet um and see what the wallet otherwise has done so we can see like is this like i don't know is he just using uni swap out of convenience is it because we have the best trade execution is it because he's using one inch in the back end so like an aggregator and um one inch just rolls over unisrop so you can make all those kinds of analysis um and then uh one last thing um which i'm gonna explain afterwards as well is like if you don't deal with your on-chain data someone else will and what i mean by that is like if there's like a lot of hype around your project and you basically can't back it up with data then the dune community will come in and be like guys what the like you're actually like you're not doing so great look at your data um so a good example of this is the luxury project so for anyone who's not familiar with luxor um it's a new nft marketplace it's a competitor to openc they're trying to be like very general open uh nft marketplace um and the way looks were launched is basically they incentivize trading volume by subsidizing the volume traded so for each given day there was a certain amount of luxury tokens um that they were giving out and then it was like however much percentage of volume in eth on that day of the platform you made up um you just got the amount of luxury tokens corresponding to that um so yeah people just use this subsidy to generate free money so that didn't work out too well i don't i still don't know if it was a buck or a feature if the luxury team was like aware that this was going to happen or um because it made it it makes for some pretty good charts um so yeah this is the formula that they use um so if you look at this this is the nft market volume uh per day um by the most like these are like the most important projects in the in the market pretty much um and um if you look at this you'd think like hey looks where he's doing great like um they launched like three months ago and they're already at like let's say 15 20 market cap and uh on some days they're even doing like uh 200 million of volume or something like that would be a very successful product launch if you don't like if looks rare was a centralized company and they just like published a quarterly report saying like hey look at all our volume like we're doing great but then if you actually like go deeper into the data um so in nfts there's this concept which is called royalties and royalties basically means that whenever so if i launch an nft and i want to keep profiting from the upside i can basically say my royalty fee for my nft will be five percent and then on every sale transaction um five percent of the sale price will go to me um but there's also collections which have zero royalty volume and of course um if you want to watch trade on looks where a zero royalty um collection actually is uh like that's the only way you're pretty much gonna do it otherwise on every um ether you put in you're gonna lose like 0.05 ether just just for the royalty fee and so people used zero royalty collections on looks where to wash trade so the blue bar is the total sales volume and the orange bar is the zero royalty volume so if we look at this um and then we think for a second it's like obviously like so all like zero royalty is probably most likely in 99 of the cases it's just wash trading and then if we uh if we look at the remaining volume which is the gray bar yeah there's nothing much left um so um yeah that's really the whole the whole story of luxury and then um if we look at the or the daily users so open c averages like 50k over the last month i guess and luxury is sitting at like 1k if even and then if we actually so if we charge the the total volume of open c and the look square volume which is um cleaned for the wash training data um then we get to this and this is quite a stark difference to uh to what we were looking at earlier um so this is really the power of web three and uh the web three data stack basically because like you can't hide your data if luxor was a centralized company they could have just said yeah we're doing like a ton of volume and it's all going great and meanwhile it is actually just uh watch trading volume and their their user numbers are like uh yeah they're pretty minimal uh compared to openc um [Music] and then maybe because this story is not really finished yet lots of people are speculating that aggregators will help looks very succeed so this is just the share of aggregator transactions that happen in the nft market at the moment and um yeah you can see that gem is now sitting at like seven to eleven percent or seven to fifteen percent uh market share of all uh nft transactions and um i think the more this will rise the better it will be for luxury because in the end they actually like offer a pretty good platform which has lower trading fierce than openc so any rational actor would prefer to trade an open c especially for like if you do like if you buy like a board ape for 100 eath then 0.5 which is the difference between the opencv and the luxury is actually pretty relevant um but yeah we'll see how this turns out um but yeah this is also like we we can actually analyze like which transactions are coming from aggregators and which are done directly which is also like really cool so yeah this is basically the first part web3 data is radically open i hope i explained this somewhat sufficiently and then my next point is web3 data is fairly straightforward and basically we're going to try to understand how does this work so web 3d data concepts like very very simple and if there's developers they're probably like don't listen to closely i might butcher something but very simple concept is that ethereum is a state machine so basically um these different smart contacts they just have they have different states so for example if i own an if i own 20 000 i um then somewhere in the esc 20 contract which is the die contract um or in the smart contract which is the esc-20 die contract there's just uh an entry in the database and obviously it's not a it's not a database in plain format uh but rather it's a patricia merkle tree i think but um anyways um there's just a entry there which is like my wallet address and then the balance of of the tokens that i own and um so that's basically the state and then if i want to change that state um i i need to send a transaction so i will basically call the die contract tell the die contract i want to move my die to another address and doing that the die contract will then output the lock so basically i have a state i send a transaction and that transaction will oftentimes emit something that is called an event lock and those events event locks are very useful for analyzing what's happening on the chain and basically all of this is fairly straightforward and like of course like you need to kind of understand what's happening inside of this like system of smart contracts and like in the die case it's pretty easy because it's just like one die contract but if we like analyze like um i don't know aggregator transactions where one inch starts like routing over uni swap and curve and like whatever um it it gets more complicated but basically um the individual elements of a transaction are fairly straightforward since it's just output of a computer so there's no like user input errors the data comes out very clean out of the evm so that's pretty cool um so yeah um yeah i actually i almost explained this right now already but uh basically um on the left side we have the world state uh in the old format and then on the right side uh so we input the transaction and then we we just uh we just arrive at a new uh world state so that's basically this this concept of state and um then if i send out an transaction i give the uh yeah i basically call a smart contract um that smart contract will um yeah will execute whatever i say it and uh what might happen internally is that that smart contract starts calling other smart contracts so this is basically this this can be like this is a finite loop like uh it's not really like un like the you send something into dvm um or like into a smart contract and that smart contract can can start calling other smart contracts until the transaction is finally finished and while that happens we we output locks so now um basically what we what that lisa is is like we can analyze state but states we don't have state on dune at the moment um because as you can imagine if we would like we don't the blockchain already is like pretty big um but if we unroll all states over all time since we we on dune like we wouldn't only have like it would be fairly useless on june because we are like mostly historical data um to have only the state that is current but rather we would have to have like all states of all blocks so unrolling all of that it's like a lot of work we're working on it and there's actually teams out there who have already done this and yeah kudos to them will get there eventually as well but in the meantime basically we can work with transactions um so who is trying to do what um traces uh or internal transactions they're called an ether scan this like a bit of debate uh how to properly call them um so what happened while executing the transaction so that's basically like if one smart contract starts calling other smart contracts that leaves traces on the chain um and then we have event locks and event logs are what just happened so an event log you can basically when you write a smart contract there is a function called image and or i don't know how to call it but basically if you if you write the mid um it will spit out an event lock and those are really really useful to actually analyze what's happening on the chain and those are like the most used uh data type that we that we do on june because they're just like very reliable and they're actually made for like seeing what's happening on chain right like the like you developers like you will uh analyze like what's happening inside your smart contract to see like that everything's working properly and just as well like us data analysts can then check like um not is this working properly but what is it actually doing um so yeah so um then we come to web3 data creating on june because you guys actually haven't seen june yet so all methods to get the data public um then once it runs it runs forever so if you like build a dashboard on dune it will just like stay there indefinitely um then standardization and abstraction happen on the macro level um that's basically so webster data even though it is fairly straightforward if you want to do a table like oh man this is going to be dexter traits or nft or traits for that matter um basically you would need to deal with all of the different um smart contracts of all the different exchanges right so what we do in june is basically we have a layer of abstract so we have like raw data decoded data and then on top of that is basically our community and our team will write kind of standardized tables normalized tables which uh abstract away those like levels of complexity which you would have to deal with if you are just analyzing like raw smart contract data um so we basically built a table which is called nft.

trades and then in that nft trade table is just like all the individual transactions of all the different marketplaces are in that table and that makes it very easy to write queries like this this is probably like 10 lines of code or something and that's uh it's pretty massive and those tables are really really useful um to like do these sector level analysis um because otherwise you'd have to write like a thousand lines of code probably just to like deal with all the complexity of all the different smart contracts of all the different marketplaces and um we just pretty much do that in the back end and that way anyone can basically do like very broad level analysis of the of the space so that's pretty cool and then also social learning is uh overpowered and uh am i good on time yes i think i'm okay on time um so if we actually want to go into dune so this is the dune interface so um on the left side we have a data explorer and that's basically i can just put in stuff here so if i want to search for curve um oh we actually i think it's called curfi yeah um so this these are all the different curve contracts and um so i can just go in here and and see like um for each different smart contracts it's like they output events and they output so they they have traces um which are like the calls to them or the calls they make and um we just have all of those tables um in a very clean format so um if we actually go into here uni should be three dot and then there's like some event which is called um yeah i'm not going to search for this mod but basically um all the events that are uh so for each smart contract uh we have like a bunch of tables depending on the individual events that they put out or the calls that they make um or that have been made to them i think that's a that's a proper way to put it um so yeah so then we we can just like um we can uh take something from our data explorer and just um so this is a table that's called uni-swap v3 uh factory event pool created so this is the event that the uniswap v3 factory will put out uh yeah basically if i create a new pool and um yeah then we just get uh fun stuff like uh which tokens are involved uh what's the fee level what's the tick spacing um what what is the address of this pool now and the contract is just this um the the factory contract and of course uh metadata like the event transaction hash the event time number and so on and um what we can very easily do with this now so this is like the raw data and then if i write uh like one line of sql or like two lines three lines if i actually would format this correctly then we can just um do a chart which is basically show me how many unisquare pools are show me the cumulative amount of uniswell pools that have been deployed over time and yeah it was just easy like that june is very much plug and play you can just come to us uh write a bunch of sql and uh immediately get your results um and then if we want to go a level deeper because this is like fairly boring data i would think so if we go level detail level deeper and um partition this by the by the fee amounts uh we get to something like this um so this is the why is there one i don't know where that one comes from um but yeah so basically how many how many pools in which fee uh this is this is hard to uh how to formulate um but basically if we look at this uh the 0.01 percent feet here um there's like uh 2 600 pools deployed with that um and then the 0.05 uh tier there's 730 pools over time and the 0.3 puts here there's only uh 3 or there is 3 000. um so basically that just shows you like how many like which pools are being deployed on uni swap v3 with which uh not which pools but which tea fear like which type of pools basically um yeah a bit of a bit of a weird uh formulation here but i i hope you guys understand what i mean um so yeah and then um this is just the the like june landing page pretty much and then if we actually so i was saying earlier like dunas social learning is op and what i mean by that is basically everything that i do on dune is public so the methods to get the data are public so if i'm like hey this is actually really interesting that this guy has analyzed the nesting of moon birds and i want to like build on top of his analysis then i can just uh click in here and i can actually just see this guy's query so this is not my query this is just like some random guy i don't know him and i can just like look into his query and now if i was like hey i actually want to know like uh what are like the top people who are doing this or something i could um i could go into his query um focus work um so do this and um then the query basically sits under my namespace and i can just like uh keep continuing on that work and uh very quickly iterate iterate on his work um so that's very useful while you're like starting out on june or you're starting a new project you can just um so i was looking at rarity capital uh a while ago and basically like i could just look at the compound dashboard and basically like each rari capital pool is like its own compound fork so i just like looked at the compound stuff and like adapted it to rare capital and that's like it's super it's super useful since uh the space is like very like like forking is very common so we can just like do that do the same stuff on the data layer as well so that's uh it's pretty cool um so yeah what else would i want to show yeah that is part of our pro tier though so june june is free um basically like for for all like intents and purposes it's like if you just want to like push out public data and not export data dunes like totally free um but then if you want to export data uh you're gonna pay us money and um if you wanna actually work on in private on june you're also gonna pay us money because you're not contributing to this like public pool of knowledge that that we're kind of trying to create on june so yeah um so yeah live demo of our time and um yeah and then my final point is basically uh web3 data levels the playing field and um this is basically this alludes to the fact that everyone from everywhere has access to this data they can just go on dune and like if they have a little bit of technical knowledge or if they have like a few hours to spare to learn something or um have some money to spare to like hire one of our wizards so we have like a mountie program and you could just like go in there and like i want this data and like a few days later you'll probably have that data um and then you can literally have better access to the data than the project itself which is like from coming from like a web to background or something it's like radically different like if i write the better queries then whatever your data analyst is doing like i have a better overview over your systems um then than you yourself have so that's that's pretty crazy um then what what we are trying to create is like equal access for everyone with no pay walls um so everyone should be able to have that data and kind of we hope that the space matures more over time as we like service more data we have more analysts and um yeah this space just becomes more transparent um and then um kind of june creators are building public goods and private goods so and sometimes that is even the same so this looks where dashboard is probably like even the luxury team like they were probably like looking at it all the time um so that's kind of like a private good for them like they they're using it like to like further their business but at the same time like the whole space and like we as investors or like participants in the space can also look at this and then it kind of turns into a public good so just like servicing this data is like a really powerful thing for the space to basically advance and mature because then we can all make better decisions based on data and not on twitter threads um so yeah and then like all of us literally have better access to the data if we like have some skills um than the ceos of web two companies because like the data is live like on the blockchain there's like june trails like one minute behind the behind the head of the head of the chain um and that's just like our data pipelines like we we can probably try to get that more live in the future um and like compare that so like one minute of reporting time and then compare that to like a web 2 ceo where it gets like a quality or like a quarterly report like uh once every four months it's like pretty crazy to to to like see that difference um so yeah um that was my uh short introduction to uh open data for finance um this is our discord link um we have a really strong community in discord and like if you ever have questions uh around like anything dune related um we'll try to answer you as quickly as possible and there's also like like-minded annualists who you can like embark on janelle's journey with um there's bounties posted there it's a great space [Applause] questions anyone yeah api where i can use it in my app working on an api and this is like everyone we meet is like win api guys and uh yeah we we like we know this is this will be like this is really important going forward and uh yeah we're working on it and we hope to yeah no real clear timeline but it's like very high on our list of priorities nothing else all right thank you guys for coming yeah happy querying oh and uh we have a bunch of swag over there uh the best the best socks in the universe uh and uh some cool sunglasses as well so uh yeah feel free to take some [Music] [Applause]