So, Have you ever wondered How you can extract verified farcaster addresses of people who have minted a specific NFT or hold a specific token? If you are not a programmer like me, this guide is for you. In this guide, I'm going to use Copy/paste, Excel, and dune.com to show you how you can find the farcaster accounts of people who hold two specific NFTs right now to give a shoutout to them or airdrop something new to their addresses.
So, Trebor69 on Farcaster (amazing artist btw) wanted to airdrop a NFT to anyone holding both The Master and The Mistress. First, you have to find the Token page on the chain Explorer for these NFTs, and in this case, They are on Zore. If you open the mint page of one of them on zora and click on the ... menu, You can find the contract address and Its Contract page on Zora Explorer:
Now We open The Zora Explorer and Click on the Token name, For this Example, Both Tokens use the same Contract and we know that their Token Ids are 10 and 11, You can find The Token ID on the mint transaction page or Opensea by clicking on details for that NFT.
When You click on the Token name This page Opens and you can find all the NFTs created with this contract. Now We click on the NFTs we need. On the page for The master on Zora Explorer, You can scroll down and find all of the holders in the Holders Tab:
Now Comes the time-consuming part, Zora Explorer does not give you the option to download this data as a CSV, So you either should rely on their API (which needs coding and knowing what to do with JSON files) or you can just Copy and paste them into Excel, which I did, Now I just select both columns:
And go to Home tab > styles > Conditional Formating and click on Highlight Cell Rules and then Duplicate Values:
a pop-up comes up and you just have to select Duplicate Values, Excels just shows you the Values That are Present in both columns:
in the next step, you should select one of the columns, Go to data tab, and click on filter (funnel Icon). Then Click on the triangle that appears in the first cell of the column, click on sort by color, and select the color you want. Now you can copy the filtered data and copy it on a new file and save It. It's the addresses Of the people who currently hold both NFTs.
This NFT was on Zora but If you want to do this for other chains, The Steps are similar, for example if you open this link : (https://basescan.org/token/0x806e9501e4638ffad50880afd6dd6706d4d9a73f#balances) you can find the holders of this nft in the Holders tab and Copy and paste them or use some coding and a web scraper.
Now that we have a list of people who have both of these NFTs, maybe we should give them a shoutout on Farcaster. To do that, You need to some how connect these addresses to farcaster account. This is only possible if these people have minted these NFTs with their farcaster-verified addresses. To do that you have to know some SQL which is not that difficult. I have written a simple query on dune that does this and you can fork that to your account and make the needed changes to get your desired results. First you should save your Excel file as a Csv and upload it as a dataset on dune. I used the Example on the first part of this post as a data set. This is the query:
SELECT fname, verified_addresses FROM dune.heliasan.dataset_farcaster_profile_with_addresses
WHERE SUBSTRING(verified_addresses, 3, 42) IN ( SELECT TRY_CAST("column name" AS VARCHAR) AS holders FROM dune.yourduneusername.yourdataset)
dune.heliasan.dataset_farcaster_profile_with_addresses is a database of Farcaster user accounts with data like addresses and fid and fname. The verified addresses in this database may include solana address and some extra stuff so we get rid of those with SUBSTRING(verified_addresses, 3, 42), you have to replace "column name" with the name of the column from your dataset and dune.yourduneusername.yourdataset with the name of your data set. the query I wrote for the addresses of people who hold both of trebor69's NFTs returns 68 Farcaster accounts:
If you have any questions about this post you can find me as @miladgh on Farcaster, And bear in mind that I'm not a programmer and I'm extremely noob in writing SQL queries.