ETH Penang: Onchain Data Analytics 101

hi everyone uh let me just start sharing my [Music] screen can you all see that okay uh okay guessing yes okay cool um hi everyone thanks so much for joining tonight my name is Sam I work for flip site flip site is a analytic company for blockchain and you know it's a platform where anyone can uh query onchain data for free so I want to share about what is onchain data and like how you can make use of um onch data right so before jumping right into it maybe just a quick uh question you know given this age a time and age where everyone is buying from uni have you all ever wondered uh how do I avoid wearing the same t-shirt as someone else um like maybe you can ask you know what are people specifically buying at Queens Bay in the last month or maybe something more uh intimate or intrusive like what are my neighbors ordering for lunch on grab um or maybe you want to know what restaurants near by you are you know not not having that many sales and maybe you want to support local right well I find uh answers to all this very interesting unfortunately the data is just not available right the data is closed right it's behind closed doors um but of course this is different for blockchain right uh in the blockchain data is on chain and subsequently it's public which means anyone can uh see the data and verify verify the data so okay so blockchains uh you know data is public so what right how does that help me how does how can I make use of that um I think one way is data helps you to make more informed decisions so think of a time where uh you are looking at a DEX say uniso or like sushi sushi Swap and you're thinking you know do I provide liquidity right what are the uh like do I get wrecked by impermanent uh loss uh um and I think this is where data can come in you can you know use data to help you see whether or not it's worth providing liquidity the screenshot you're seeing here is from um unisoft they have like a mini analytics page for show pool and you can see the volume and fees here but I think it just stays at this level with uh blockchain data that Prov provided by you know analytics platforms uh you can go beyond just that right you can see for example okay in the past uh few months what is the trend looking like is it on the downtrend if it's on the downtrend maybe doesn't really make sense uh for me to provide liquidity right because maybe the volume is low I may get wrecked by impermanent loss um because there's not enough fees to cover it or you could see like are people swapping their e or are they swapping their usdc meaning are they selling e or are they buying e right and all those are you know uh very cool metrics that you can use to determine whether or not you want to H it right another example are nfds so if you're if if you're in the NFD space you would be familiar with auki um for the first half of like this year azuki was trading at you know somewhere between 14 to 16 e and then right when they announced their new collection the azuki Elementals prizes just plummet right the holders of the NFD aren't that um pleased with the decision and it shows because everyone is selling and you know today it's lost over like 80% of its value um since since June right so wouldn't it be nice if say you own anuki wouldn't it be nice if you saw that trend of people drastically selling like that huge amount of sell or you know people accepting offers for the nfts and you were able to like sell you know maybe somewhere here right before it really plummets down to you know 80% uh decrease in value so those are the uh few ways that you can benefit from using onchain data right blockchain data but I think the main challenge here is like okay let's say you do want to use it uh how do you get started I think one of the main challenges is that blockchain data can be intimidating uh can be not easily understood because most most most of the time it comes with in this form of like hex values uh a lot of zero ones twos and alphabets and it may not be easy to decipher what's happening right this is for example uh a sale on open C in the event logs this is something that that an event loog emits but it's not really it's not really uh easily understandable like What's Happening Here and that's where because of this issue that's where analytics companies come in and feel this uh Niche or not Niche but like feel this need of abstracting all this complexity and creating uh curator tables right uh if you can see here I I think it's a bit blurry because it's on Zoom but what you're seeing here is a screenshot uh from flip sites query editor and if you can just bury me a few seconds um you can imagine there are tables where we already curate um you know uh blockchain events such as NFD sales uh Dex swaps you know providing liquidity on dexes all those events are created and by cated we mean they are placed nicely in the tables so that anyone can use SQL SQL uh very spirit for language to query right on the left here this is the query and you can see all the results right so you can imagine the table is quite big um so I couldn't fit all in one screenshot but you can imagine there's a buyer address column there is a seller address column and the nft address uh token ID can you get the price the platform fees Creator fees so on and so forth so with all that complexity abstracted and you know arranged nicely in tables like this uh the the platforms like analytics platforms allow the analyst to focus on finding the insights so they don't have to spend time on you know um deciphering the raw events or raw traces they can focus on insights discovery right with this you can create Trends um and I have a short video to show again Zoom so the video I think is a bit choppy on Zoom but if you can bear with me this is what it looks like it's a quy editor uh I'm just looking at ethereum right now this is selecting I'm selecting from the nft sales table and I just want the platform name and the the price price which is the the sale price right and I say give me all the NFD sales after or in September um and I want to see like which platform uh corresponds to how much sales in USD so then I get the results and the results tell me you know openc blur no variable so and so forth how much sales do they have in September and I can create a graph right uh yeah so creating a graph change the colors and changing the title of the graph but this is like uh this is like a core component of what blockchain data analytics looks like it's as simple as knowing what tables you want to query from selecting The Columns you are interested in in this case we are looking at know different NFD platforms marketplaces like open C blur uh variable looks rare and then look at uh the amount of sales right of course there's so much more things you can do you can look at buyers sellers um you know royalty fees and if you do a bunch of these and aggregate them you can also create a dashboard right um this is something I created a while ago I think a year plus ago but you can create a that dashboard U with all the metrics that you have created you can you know have something like um the total sale right in lifetime or you can have on a more in a more time scale uh level like what's the average Sal the median sales and plug it on on in graphs and and yeah it's a dashboard that updates over time if you rerun the query right so this is one example that you can what you can do with on data a few other things you can do are for example uh verify claims so blockchains like to use TPS or like transactions per second to um Market their their blockchain speed like for example or if can do you know 200 transactions per second for example um because analytics platforms also have a lot of other blockchains you can query from you can then compare all these different blockchains find out what are their transactions per second and then you know make that comparison and potentially that could be an Insight right another way is um uh macro Trends so you know those reports that coin Geo Masari nanson producers how do they produce their reports they obviously have some access to the blockchain data and then they do analysis right exactly like this they plot graphs um and then explain what are the trends that they are seeing right so you know with tools like this you can produce those uh research reports yourself too and of course blockchain data doesn't have to be all about metrix uh it can also be fun so there is a protocol called ens if you're not familiar ens stands for ethereum name service it's something like you can register for a domain name for example if it's available I can buy sam. e and Associate that with my address so whenever people sends let's say people want to send me money they can just send to sam. e right and and they will send to my address so uh feeling bit curious and trying to see like what names have people registered with Emojis right uh I chose a rabit Emoji so you know selecting all enss names that has rabit emojis in their domain nams and you know these are the results I've got right so apparently lots of people likes the r emojis uh I also wanted to see what about the word Malaysia right and turns out there is a lot of uh results of people of I'm guessing Malaysians buying these domain names know Malaysia Nestle Malaysia Malaysia verse right and I thought hey what about Pang and guess what there there are ens um registered with with the with the Pang name in it h Rock Pang Pang Lang right Pang foodi uh so these s of things can be searchable can be quable um on chain right using onchain data as well so you know doesn't have to be all metrics it can be fun too and yeah um I would say for people who are more savy with data uh we don't just have you know specifically for flip site we don't just have curated um models like nft sales or Dex swaps we also have the raw tables maybe you want dig deeper into um like you know traces transactions event logs uh decod event logs and also balances uh and uh for those of you who want to start uh you know your onchain data journey I would say uh it's a skill that is very uh useful because not only you get to learn new skill you get to sort of like contribute to uh a protocol that you like or a community that you that you that you associated with right like for example if you want if you have a favorite nftd maybe do analysis of the NFD right uh if you have a favorite protocol you can do the same and I think that's a very uh positive way to contribute um to the things you like in crypto uh also some resources to get started metrix D flip site on YouTube um bunch of tutorials there but yeah so if you're ready to S on no onch data training head on to flip site. new on your browser and and yeah I think that's it yes same uh how how up to date is the data uh for ethereum I think well the there are different uh models we have I think for the more like urgent is ones like event locks traces I think those run every 15 minutes if I'm not mistaken about 15 to 20 minutes but something big like balances which captures you know every erc20 and ethereum uh for every address on ethereum I think that runs um once today I see all right so do do you support layer to data yeah um we do I think we have optimism arbitrum base uh what else in total I think we have 20 plus blockchains including Bitcoin um and then a bunch of other all1 like Avalanche BC okay got it um Sam right I saw in the like the fite there there's a leaderboard like uh any incentive if you're like a very high rank analyst or something yeah good question um so I think that's more on the community side uh another thing that flip side does is they want to uh incentivize people to produce good dashboards so I think they have something called the top eight and uh if your dashboard is highly rated like publicly and you Mak it into the top eight I think you get paid like a dollar every hour depending on like what chain that you are analyzing oh I see I also said like there's a bounty uh like like like something like earning while learning is it something like that um we used to run bounties I think bounties are more like questions we post to the community anyone can try to answer it and if your answer is the most accurate you win uh I'm not too sure if we're too active on that right now I think it's uh dial down [Music] oh how how about the query the query syntax is is very much similar to SQL right yes it is SQL and it is uh snowflake snowflakes version I see it supports all the the keywords or including all those aggregation yes that's right so all all functions from Snowflake uh we also have like custom functions that we introduce uh for example hex to in HEX to in conversion hex to string um the K hash function as well so basically it's just a relational database at the back relational I don't think it's relational I think it's okay yeah