Analyzing Your Community Using Big Data with Danny Franklin

foreign welcome to my presentation uh analyzing your community using big data my name is Danny Franklin I'm a co-founder and CTO of chasm which brings us to why we're here why are we talking about big data and analyzing your community Chasm is a web 3 CRM so we bring together communities uh Twitter Discord smart contracts and try and give you an overview of your members and your community provide insights labeling analysis and tell you you know who are your most valuable holders who are the people who are just around to try and make a quick buck and who are the people you should be trying to meet with to get to know better to do that we use a lot of different Big Data tools and we've made a lot of learnings about how you can use big data to do this analysis so today I'll be presenting some of those tools show you how you can do that yourself and talk a little bit about how we use those tools in Chasm so why are you wanting to do this web3 is all about Community I think all of us here in web3 know um you know whether you're a dow an nft project if you're a web 3 game it's all about your users um but in web3 there's also this decentralized nature which makes it kind of tough you have limited visibility in who your members are there's also an anonymity aspect that can make it tough to know who is who and there's quite a bit of scammers and people that are trying to take advantage so it's important to know who are your Champions who are the people you can rely on who are the people that you can help bring in to guide your project um and who can help bring in more users and then there's also a lot of questions around growth how are you doing could you be doing better what's the return on investment on your growth strategies these are some of the questions that big data can help answer so what can we answer with big data questions like who is in my community who in my community who that owns my nft is a whale who on Twitter is high reach if you have someone come in that has 30 000 followers on Twitter and buys your nft do you know that that happened and if you did would you reach out to that person maybe think about a collaboration something like that and then what about people that come in that are scammers there's a lot of information on chain that can give you insights into other things that they've bought how long they hold things for are they going to flip your nft immediately um and then also what are your members doing what are they doing on chain what are they other projects are they interested in what are they doing in your project it can be tough to get insights into that are they active are people slowly becoming inactive do you need to re-engage your community and then finally are they flipping your nfts or are they holding them um and then how effective are your growth channels and then what other projects similar to your projects are your users in these could be good partnership opportunities collaboration opportunities and big data can help provide some of these insights so what tools do we use there's a lot of different tools available the big one that we make use of a lot is bigquery bigquery and snowflake are data warehouses that allow you uh to process lots of information very quickly so you can put in terabytes petabytes of data and analyze that to gain these insights interestingly they also have lots of public data sets so there are groups out there that are open source projects to bring in blockchain data into bigquery uh so that you can without having to start a node and query transactions things like that all that data is already there available for you to use additionally some projects like lens protocol provide all of their data in bigquery available for people to use there's also tools like Dune analytics and Flipside crypto that do similar things but interestingly what these projects do is they have a lot of community involvement so the community creates queries they create dashboards you don't have to start from the ground zero you can search and say I'm interested in the holders of an nft in the last month and I'm sure you can find a query that someone has written to do that and then they also have lots of tables that have things like labels prices for nft transactions Roll-Ups of various things that can make your life easier so you don't have to rebuild these things from the ground up so those can be very useful another thing if you really need to go deeper is the graph protocol the graph protocol allows you to find subgraphs for specific smart contracts so you can get more granular information on what's happening on chain but that's not all often when you have a project it's not just public information that you want you have your own private information sometimes you do a pre-mint allow list sometimes you do have a survey or as part of your Discord Community you find out you know who are my people on Twitter who are they on Discord you have some first party information so you want to kind of combine all these systems together along with the information you have that isn't available in the out there publicly to really get these insights I mentioned the bigquery public data sets these can be very valuable one of the biggest projects is called the blockchain ETL project so they have all transactions all token transfers balances event logs much more on lots of different chains ethereum polygon Dogecoin even and they're kept pretty up to date so they're not real time but uh you know if you're not trying to do real-time analysis which we're not talking about here they can be very useful and then it's just an example of other ones lens protocol is a decentralized social uh platform so all of that data is also kept up to date in bigquery so it's just an example of the kind of data that you can get from these public data sources so if anyone's not familiar with bigquery here's an example of how you can use one of these this is the lens public data so it's very very simple they just have this data with lots of different tables you can go in there and you can say I just want to see the name the address and the handle of these people and it will show you public profiles so you could dig in deeper it has all kinds of information around like nfts they own things they've done on lens comments they've posted lots of information like that do analytics for anyone that's not familiar I think a lot of people probably are but these are kind of the community uh uh created queries I was talking about so you can go in here and explore and find anything that you want if you need insight into like how to build different queries when you dig in deeper to one of those queries there's an example you can write your query directly in Dune run it against all ethereum data or even cross-chain data and we'll give you nice dashboards with results interestingly recently they launched an API so if you don't want to do it in the dashboard and you're more technical you can write your queries and then access them via the API to download that information another one I mentioned is the graph so I mentioned this is more for specific smart contracts so people that have projects with smart contracts and data that they want to provide to others they've set up these subgraphs so you can go in and they've done all the analysis for you you can write simple graphql queries and get all kinds of insight into what's happening on chain even more than just raw transactions but like decoded so if you have a game or a D5 protocol or something where your smart contracts emit like specific events and you want to be able to interpret those the graph protocol is a good way to do that if you want to play with it they have this nice interface or you can run sample queries and get the results and then if you want to go deeper this is an example of a subgraph so this is pretty technical but if you have you know a smart contract and you have different events like new gravatar in this game then you could say how to handle it how to interpret it and get that data so these are lots of different tools you can use for big data analytics but how do we bring it all together so this is an example of using the public data set for ethereum to find out who your holders are so I went and I found art blocks which was the number two on etherscan nft collection and it's pretty simple you just say I want to pull from that table all the transfers I'm getting all the people that have been transferred to ordering it by their first purchase date and so you can see here on the right immediately we have a list of addresses ordered by their purchase date but that's not really that interesting these are just boring addresses what does that tell us so we can combine that with the lens public data which has ens data so we can join it with that data with another few lines and then suddenly these aren't just addresses these are ens names what can you do with the NS names well one interesting thing that we've done in the past is that many people make their ens names also their Twitter profile handles so you could start to expand a little bit and kind of find out who are these users by going on Twitter or elsewhere and finding more information about them and then you can start to make other insights who among my community has the most balance so expanding on that previous query we look up how much balance everyone has order by the balance and you can see vitalik.ether Rises to the top with 6257 eth I looked that up this morning and that's 9.8 million USD so he's doing all right um but this can help you find out you know who are the whales who are my community have a lot of ethereum a lot of balance you can expand this to other erc20 tokens other things like that to kind of find out are there people I should be connecting with people I should be targeting with um you know more involvement um balance can be a good proxy for you know General uh crypto participation or um I guess sophistication so it's not just about trying to land the whales but also just knowing you know who are the people that are more sophisticated uh but I wanted to show also how we do this in Chasm so if you don't want to write these queries you just want to use a no code UI when you upload your smart contracts your Twitter your Discord we do all this processing so we have a table where you can just say I want to see uh everyone who's on ethereum who holds some nfts of mine and order that by balance and then you can see sort of a similar thing without having to do all this uh query writing another thing you might want to know is what nfts do they own so using that same query this table I've done a little bit of building ahead of time but we can get the balance of any nft and join it with our members table and suddenly we see an nft that each person owns so I found that continue.eth owns a pride punks 2018 nft um and you could do it not just like a one at a time like you'd have to do an ether scan or elsewhere but you could do it in Mass across your community which can help you get lots of insights again on the chasm side we do this kind of analysis out of the box so you can look through your lists we have lots of different columns so you can see what nft was the last one that they minted when did they mint it um lots of information like that another thing you'd like to know is what are my members doing on chain I mentioned you can use the subgraph for this but you can also there's plenty of public data available in bigquery again using lens protocol and just joining again with just a few lines of data you can see those same users if you were curious if your project was lens how much are they posting on lens how active are they on social media so certainly someone who's posted 426 times probably pretty active someone you might want to collaborate with uh in Chasm we have a system where if you want to look for specific contract events we have this uh UI where you can say here's my smart contract I want to select an event that this one emits and we could track that too so if you're a game and you have a you know I'm creating a pickaxe in the game and you want to know how many people are have done that more than five times you can set it up in our UI go to Our member leaderboard and just we'll keep it up to date and you can always see in the last six days how many people how many pickaxes has everyone created in my game so it's a great way for seeing uh deeper on-chain data so I talked a lot about on-chain data and the public data that's available from bigquery but what about Twitter and Discord this is very valuable especially in the web 3 world Discord is where most of our communities live but yeah Community touch points are fragmented you've got nft and token holders you got Discord members you got Twitter followers maybe you have a website so you have first party app users so to answer all these questions we need to extract and load the data from each of these different resources and transform them into a Consolidated place for us that's bigquery and then after you do this and get them in this format you can query them to say you know start to bring in Twitter stats Discord activity combined with on-chain data and it starts to become really valuable but there's some problems exporting Twitter and Discord users is hard extracting via apis you have to avoid the rate limits which can be a big pain for Discord if you want to capture all of your data you need to have a Discord bot which we have found often has a arduous approval process if you want to use it in a lot of communities then you need to unify all this data into a single identity it's different formats different IDs things like that and there is public blockchain social data as well there's a lot of public identity providers on chain where you can try and go in and extract you know ens protocol you can upload who you are on Twitter who you're on Discord but it's not comprehensive only a very small subset of your users will have done that and so we asked the question what if our users can tell us who they are so I alluded this earlier but if you've done a pre-med whitelist often you have you're collecting Twitter you're collecting Discord information sometimes people use typeform surveys when they're onboarding or even during the Discord onboarding process you know your token gated community so you then know who has who is what Discord profile with what wallet so often communities have this sort of first party data and they want to know how to leverage it so if you do have this data in a CSV and you want to start using it in the same way we were talking about earlier you can upload it to bigquery they have a local file upload and then once you do it it's just as simple you can just join the same way that we were talking about earlier on those tables from your CSV I actually use this a lot when I'm just trying to analyze the CSV just upload it and you can write whatever SQL queries you want but we take a different approach we have these onboarding forms that we are hosted onboarding forms so you can use to bring your community into wherever you want if you want to bring them into your Discord if you want to have them mint we partner with unlock protocol to Mint free or paid nft when they fill out your form and you can have requirements like you need to follow me on Twitter you need to retweet this tweet you need to be in my Discord you need to have this much eth in your wallet you need to own this nft and so if you do that when they complete the form you know that these users are going to be activated users they're going to be engaged because they've met your criteria and so the nice thing about this is when you get those different identities they feed directly into our CRM so there's no uploading of csvs there's nothing like that it's just you automatically know oh wow as things happen on chain this is the user's Twitter following this is how active they are on Discord um here's how much eat they have and you can see all of that in our UI we're rolling out a loyalty program as well so not just having them come in and mint in nft but uh kind of be more involved so you can have leaderboards to see who in your community like public facing leaderboards so they can see how they're doing your community and you can have no code tools to specify uh being in my Discord is worth 10 points retweeting is worth Five Points getting this pull out from this event I threw is worth 100 points and that can help get you to different tiers and then we issue and revoke nfts on chain so you can reflect that membership without having to do anything on your side um so it's like a out of the box almost like a Starbucks program without having to do any work so like I said at Chasm we do the heavy lifting it's difficult to bring all these things together uh if you have Twitter Discord you can upload csvs when you have them we even have an API if you have first party data and you just want to say someone logged into my website here's their ethereum address also we captured their Twitter um send that to us and then it'll show up in our UI and then be available via our apis so another question I mentioned is how effective are my marketing campaigns um you could do that again using the Big Data analysis tools you could tag your users in Google analytics with wallet addresses coincidentally Google analytics is also available in bigquery and then you would have that data again that you can join with all the bigquery data I was showing before you can even get pricing data or something like that from dunes apis and pull that in and you can kind of make these insights we try and do this out of the box as well you can Define your on-chain events and we have this kind of attribution and Analysis page where you can see which of your users are doing what events and track kind of their funnel over time and this is not necessarily part of the big data but then you can trigger automations through zapier and then one of the last questions I mentioned is what similar projects are used involved in one of the big channels for growth in web 3 is collaborations and Partnerships so you can look at you know your holders and see everything that they own and we can show you other projects that have a lot of overlap with them so those kind of projects might have a lot more users that would be interested in your product your game your Dow um so bigquery analysis can show that once you have that you have the social data you can find people who are good matches for doing advertising on social 2 or even Partnerships to say you know maybe you can tweet about our product and we'll bring you in and give you some loyalty points something like that um and so you could certainly do that in bigquery but we provide a nice UI where you can see what those projects are as well so yeah in summary there's lots of big data tools that can offer many different solutions to discovering who's in your community you can combine on-chain data and social data to help find your power users and judge your community growth efforts and then Chasm tries to handle the heavy lifting of combining all these different resources and making it uh easy for you to use thanks feel free to email me or connect on Telegram