How to Bulk Extract Twitter User Data with Google Sheets

Have a Twitter account you want the user data for? Or want to know more about your own followers? If you’ve ever tried to find out, you’ll have found out that Twitter does not give easy bulk access to this information. If you don't have the ability or know-how to connect to the API, you'll have a hard time getting the information. Despite this, I’ve found a workaround to this issue and, in this article, I’ll be going in depth on how to mass index twitter user accounts with only a Google sheet. While there are many steps involved, if you follow them as seen here you should have no problem getting your first list set up. And just so you don’t get lost, here’s a quick overview of all the steps involved:

  1. Find target Twitter Account

  2. Export Twitter ID’s of that accounts followers

  3. Convert Twitter ID’s to Twitter account names

  4. Scrape Twitter accounts for domain names and account info

Note: If you want to bulk exports Tweets or Twitter follower data for free, check out minebase.

Benefits of Indexing Twitter User Data

Typically, performing market research using only Google can be quite a time intensive task. However, we'll be able to use Twitter as a gauge for for user interest. Since we’ll be using Twitter accounts to export users, we’ll have one key advantage over other research techniques: Twitter accounts are tied to a niche. Meaning you can get a targeted user list by using the followers of your competitors or the followers any other accounts across your vertical. For instance, if you have a website that is primarily about home decor, you can go to a home decor bloggers twitter account or a competitors account and generate a list of users that will be much more likely to convert than any other. This will be important to understand when beginning when using this method of acquiring a list.

Extracting Twitter User Data with a Google Sheet

This is a long “how-to” so, to get through this, it is important to do an overview of what this whole process entails. Most of the work involved, however, will be to actually get a workable list of Twitter users to obtain the domain names from. Once we have this list, the rest of the process is rather easy. And using the list of Twitter usernames, we will be able to export their Twitter user data. With that in mind, let’s get started.

Find a Target Account

The first thing you’ll need to do is determine a user’s followers that you’d like to get the followers from. Keep in mind that you’ll want to use an account that is similar to your own and not filled with spam accounts (spam accounts typically have no profile picture, cover photo and have silly names).

Export Twitter User ID's

 

UPDATE July 2017: you can skip this step easily by using minebase to export a user's Twitter followers for free.

 

With your account in hand, head over to dd-css.com. This website offers a free service that will export Twitter account IDs. Once you create an account, click on “Twitter Data” in the site header and you’ll be prompted to authorize access to your Twitter account.

If you’re wondering why this is necessary, it is because Twitter restricts API calls per user. Doing it this way makes the site more quick for everyone. Once complete, you’ll be prompted to select which kind of user export you’d like to make. Click the first option to export IDs of friends and followers.

Then enter the username your target account (without the ‘@’ sign) in the “screen name” text box and click “Get Friends”.

You’ll then be redirected to your account page where all of your exports will be. After a minute, refresh the page and your complete Twitter ID export should be present. Click download and select the CSV option.

Format Twitter User ID's In Excel

The CSV file you’ll just have downloaded will have all of the Twitter IDs of the target account you’ve entered, but they won’t be in the proper format for our Google sheet to read, so we’ll clean them up in Excel. To start, open the CSV file in excel and copy the entire first row (note: the row will be very long) and paste them using the transpose option.

Last, select the entire row and make sure the cell format is set to number and remove the trailing zeroes (Important!). This step is possible in Google sheets, but that is out of the scope of this tutorial. In any case, leave this file open, we’ll get back to it after we’ve created the Google sheet.

Convert Twitter ID's to Twitter Usernames

UPDATE JULY 2016: The website required for the following step (id.twidder.info) is down most of the time. So in order to retrieve the list of usernames of the followers of a certain user, use the following link: https://open.blockspring.com/pkpp1233/get-a-users-followers Once complete, you can skip ahead to the next step.

Unfortunately, we won’t be able to scrape Twitter accounts using only Twitter ID’s since you can’t scrape actual Twitter accounts with them. So we’ll need to convert the Twitter ID’s to usernames. To do this, we’ll be using a Google Sheet since there is a function within that lets us mass search websites for certain information. We’ll use a site called Twidder.info to take the ID’s and output usernames and we’ll do this all within Google Sheets. To do this, follow these steps:

  1. Paste your Twitter Id’s from the previous step in a Google Sheet.
  2. Paste the following formula in cell B1 then copy it for all rows containing a Twitter ID. This formula creates a URL using the Twitter ID using the concatenate formula. Then using the ImportXML formula, it grabs the resulting username from this generated URL (NOTE: Depending on the number of accounts you are processing this could take a couple of hours):   

    =index(IMPORTXML(CONCATENATE("http://id.twidder.info/?user_id=",A1),"//input[1]/@value[1]"),1)
     
  3. Last, you’ll need to create a URL from these usernames, to do this simply paste the following code, which creates a Twitter url, in cell C1:

    =CONCATENATE("https://www.twitter.com/",B1)
  The final product should look something like this.

 

The final product should look something like this.

Scrape Twitter Accounts For Domain names

Now that you have all of the Twitter account URLs for the followers of your target Twitter account, you can scrape them for any domain they have listed on their accounts. To do this, simply paste the following code in cell D1:

=importxml(C1,"//a[@class='u-textUserColor']/@title")

This code simply searches a Twitter account for a domain name. Twitter limits the amount of accounts you scrape for a given amount of time, so you’ll find that many or even all cells will be listed as error. This is normal and it may take up to an hour for all of your URLs to generate.

 

What to do with Twitter Account Information

Depending on the the country where you’re based in, you might be allowed to use the extracted information to find email addresses of the users you've just exported, but for most, this is a bad idea. However, here are a few things you can do:

  • Better Understand the makeup of your following: You can use this method to learn more about the makeup of your following and use that info to build better and more relevant marketing campaigns.
  • Get in touch with other website owners: Not that you have a list of URLs, you can easily reach out to blog owners in your niche who would likely be willing to collaborate or promote your content (because it's probably similar to theirs).
  • Learn more about your competitors: By analyzing the accounts of your competitors, you can learn more about how to attract more users to your business or service. Furthermore, you can use this information to reach out to users of a competing service.

So there it is. If you were able to complete the tutorial on how to export follower data from Twitter you should have quite a lot of information to help quickly grow your online presence. 

Found this guide useful? Feeling generous?