AlfaFrens Subscriber Management with Google Sheets and Airstack

Are you as obsessed with AlfaFrens as I am? If you’re managing a channel, knowing who your supporters are—and being able to reward your most loyal stakers—is crucial. But until now, efficiently gathering useful information and obtaining Ethereum addresses for your devoted followers has been a major hurdle.

Fear not! I've developed a streamlined method to automatically retrieve detailed subscriber data, including ETH addresses, directly from FIDs. This guide will walk you through setting up a system that not only personalizes your interactions but also makes rewarding your valuable supporters a breeze.

If you've been exploring or building within the Farcaster ecosystem, you're probably aware that Airstack is a treasure trove of tools, APIs, and documentation. It's invaluable, especially since it provides essential data that AlfaFrens doesn’t yet offer. Here’s how to get started:

• Create an Account: Head over to the Airstack website and sign up. Simple and straightforward.

• Secure an API Key: While setting up your account, request an API key. This key is crucial as it will bridge Airstack’s data with your Google Sheets, unlocking new depths of subscriber insights. Keep this key secure and handy.

With your Airstack account ready, you're set to revolutionize how you manage and engage with your subscribers. Next, we'll dive into extracting your subscriber data and setting the stage for automation.

AlfaFrens simplifies the process of exporting the FIDs of your subscribers and stakers directly from a Farcaster frame.

Farcaster

AlfaFrens on Warpcast

You asked. We delivered 🛠 Now you have no excuse not to drop to your supporters 🫂 h/t @youssea https://alfafrens-frames.vercel.app/export

Here’s how to do it:

1. Start Exploring: Visit the specific Farcaster frame linked here and click "Start Exploring"

2. Select Data Type: Choose to export data for either "subs" or "stakers".

3. Check Status and Export: Click "Check status" to see if the data is ready. Once it is, a link will appear allowing you to download the data as a CSV file.

With your data in hand, you’re ready to move on to the exciting part—setting up Google Sheets for automation and bringing this data to life.

It's time to turn these FIDs into actionable insights. Google Sheets, coupled with Google Apps Script, offers a powerful platform to automate this data transformation. Here’s how to get it all set up:

1. Prepare Your Spreadsheet:

   • Open Google Sheets and start a new spreadsheet.

   • Name your spreadsheet for easy identification (e.g., "AlfaFrens Subscribers").

2. Set Up Google Apps Script:

   • Click on Extensions in the menu, then select Apps Script.

   • Delete any pre-existing code in the script editor that opens up.

   • Copy and paste the script provided below or on Github (ensure you replace placeholders like YOUR_AIRSTACK_API_KEY with actual values from your Airstack account). This script will pull additional data like Ethereum addresses and profile names based on the FIDs.

   • Save and name your project (e.g., "AF Sub Data Fetch").

3. Import Your CSV Data:

   • Return to your Google Sheet.

   • Bring in the FIDs from the CSV file you downloaded into the spreadsheet. Ensure that the FIDs are in the first column.

4. Execute the Script:

   • Under the Extensions menu, you should now see a custom menu item titled "⚡⚡⚡️ " or similar, based on your script setup. If you don't see it, you may need to refresh.

   • Click this menu and select Update to run your script.

   • The script will execute, populating your spreadsheet with rich data fetched via Airstack.

5. Verify and Utilize the Data:

   • Once the script completes, your sheet will be filled with information about each subscriber like user name, follow count, follower count, and a link to their Warpcast account. If a user has an attached ENS domain, you'll find that here. If not, you can grab one of their additional connected Ethereum addresses.

   • Use this data to enhance your interactions, tailor your content, or directly send rewards and communications.

function updateETHAddresses() {
  // Access the active sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Define the range of user IDs to update
  const range = sheet.getRange("A2:A" + sheet.getLastRow());
  const fids = range.getValues();
  
  // Set API credentials and endpoint
  const apiKey = 'YOUR_AIRSTACK_API_KEY';
  const url = 'https://api.airstack.xyz/gql';

  // Iterate over each user ID to fetch and update information
  fids.forEach((row, index) => {
    // GraphQL query for user's social profile and associated Ethereum addresses
    const userQuery = `query MyQuery($userId: String, $blockchain: Blockchain!) {
      Socials(input: {filter: {userId: {_eq: $userId}}, blockchain: $blockchain}) {
        Social {
          profileName
          followerCount
          followingCount
          isFarcasterPowerUser
          userAssociatedAddresses
        }
      }
    }`;

    // Define variables for the GraphQL query
    const userVariables = {
      userId: row[0].toString(),
      blockchain: "ethereum"
    };

    // Set options for HTTP request
    const userOptions = {
      'method' : 'post',
      'contentType': 'application/json',
      'headers': {
        'Authorization': 'Bearer ' + apiKey
      },
      'payload' : JSON.stringify({
        query: userQuery,
        variables: userVariables
      }),
      'muteHttpExceptions': true
    };

    // Fetch user data from Airstack API
    const userResponse = UrlFetchApp.fetch(url, userOptions);
    const userJson = JSON.parse(userResponse.getContentText());
    
    // Check if user data is available and process it
    if (userJson.data && userJson.data.Socials && userJson.data.Socials.Social && userJson.data.Socials.Social.length > 0) {
      const social = userJson.data.Socials.Social[0];
      const profileName = social.profileName || "";
      const profileUrl = profileName ? `https://warpcast.com/${encodeURIComponent(profileName)}` : "";
      const profileLink = profileName ? `=HYPERLINK("${profileUrl}", "${profileName}")` : "";
      const followerCount = social.followerCount || "";
      const followingCount = social.followingCount || "";
      const isPowerUser = social.isFarcasterPowerUser ? "Yes" : "";
      const addresses = social.userAssociatedAddresses || [];

      // Fetch ENS domains for the Ethereum addresses
      const ensQuery = `query MyQuery($address: [Identity!]) {
        Domains(input: {filter: {owner: {_in: $address}}, blockchain: ethereum}) {
          Domain {
            name
          }
        }
      }`;

      // Set options for HTTP request to fetch ENS domains
      const ensOptions = {
        'method' : 'post',
        'contentType': 'application/json',
        'headers': {
          'Authorization': 'Bearer ' + apiKey
        },
        'payload' : JSON.stringify({
          query: ensQuery,
          variables: { address: addresses.length > 0 ? addresses : [""] }
        }),
        'muteHttpExceptions': true
      };

      // Fetch ENS domain names
      const ensResponse = UrlFetchApp.fetch(url, ensOptions);
      const ensJson = JSON.parse(ensResponse.getContentText());
      const ensDomain = ensJson.data && ensJson.data.Domains && ensJson.data.Domains.Domain && ensJson.data.Domains.Domain.length > 0
                        ? ensJson.data.Domains.Domain[0].name : "";

      // Update sheet with fetched data
      sheet.getRange("B" + (index + 2)).setFormula(profileLink);
      sheet.getRange("C" + (index + 2)).setValue(followerCount);
      sheet.getRange("D" + (index + 2)).setValue(followingCount);
      sheet.getRange("E" + (index + 2)).setValue(isPowerUser);
      sheet.getRange("F" + (index + 2)).setValue(ensDomain);
      sheet.getRange("G" + (index + 2)).setValue(addresses.join(", "));
    } else {
      // Handle cases where no data is found
      sheet.getRange(index + 2, 2, 1, 6).setValues([["", "", "", "", "", ""]]);
    }
  });
}

/**
 * Adds a custom menu to the Google Sheet on open, facilitating the execution of the update function.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('⚡️⚡️⚡️')
      .addItem('Update', 'updateETHAddresses')
      .addToUi();
}

You're now equipped to manage your subscribers and stakers more effectively, personalize interactions, and reward your community in meaningful ways.

Was this helpful? Stay tuned for more tips and strategies:

• Follow me on Farcaster

• Join the conversation on My AlfaFrens Channel