How to Bulk Extract Twitter Email Addresses with Google Sheets

Have a Twitter account you want the email addresses for? If you’ve ever tried this, you’ll have found out that Twitter does not give public access to a user’s email addresses. There’s nowhere in the API or on the site that allows for this - and for good reason. Despite this, I’ve found a workaround to this issue and, in this article, I’ll be going in depth on how to mass extract twitter emails 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

  5. Scrape domain names for email addresses

Update: A robust paid version of this tool using the Twitter API can be found here

Benefits of Using Twitter to Gather Emails

Typically, if you acquire an email in any way other than through traditional website sign up forms (social media contest, etc…), you might find that the list does not perform well and isn’t interested in whatever you are sending them. Since we’ll be using Twitter accounts to generate an email list, we’ll have one key advantage over other email generation techniques: Twitter accounts are tied to a niche. Meaning you can get a targeted email 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 emails 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.

...with great power comes great responsibility... - <Your Email Service Provider>

Disclaimer: For good reason, Twitter does not let you access the emails of it’s userbase. So don’t use this to spam people. Furthermore, some countries, like Canada, don’t let you send emails to people who haven’t initiated the opt-in process, so you won’t be able to directly send them emails using this method. However, there are other ways you can use these emails and I’ve listed them at the end of this guide.

Extracting Twitter Email Addresses 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. Since Twitter doesn’t let us access the email addresses of its users, we have to use the next best piece of information it offers us: their domain names. We can then search their domain names for their email addresses. 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 domain names listed by Twitter users, we will be able to get access to the publicly available email addresses that belong to those users. 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 your emails 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 2016:

 

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.

Scrape Domains for Email Addresses

This last step is where you’ll get the email addresses of those who have listed a domain name on their Twitter account. To do this, set up a second sheet and paste your URLs in the first column. Next we’ll be searching all of the common places that an email would be listed on a site: Home Page, contact, contact us, about and about us pages. For each page create a new column and paste the following code and emails should start to populate:

  • Home: =substitute(IMPORTXML(A1,"//a[contains(@href, '@')]/@href"),"mailto:","")
  • Contact: =substitute(IMPORTXML(CONCATENATE(A1,"/contact"),"//a[contains(@href, '@')]/@href"),"mailto:","")
  • Contact Us: =substitute(IMPORTXML(CONCATENATE(A1,"/contact-us"),"//a[contains(@href, '@')]/@href"),"mailto:","")
  • About: =substitute(IMPORTXML(CONCATENATE(A1,"/about"),"//a[contains(@href, '@')]/@href"),"mailto:","")
  • About Us: =substitute(IMPORTXML(CONCATENATE(A1,"/about-us"),"//a[contains(@href, '@')]/@href"),"mailto:","")

Obviously, there are more places you could search for an email, but this covers the basics. And now you’ll be left with a page peppered with email addresses of those domain names you’ve just scraped. The best way to clean these up is to paste them all into one column in Excel and then use the “Remove Duplicates” option.

So, finally, you’ve scraped (a portion) the email addresses of a targeted Twitter userbase. However, since this process can be automated, you can get thousands of targeted emails while doing hardly any work. So even if you didn’t get the emails of all the Twitter users in your list, note that you can repeat this process for many other users and grow you list that way.

What to do with Twitter Account Emails

Depending on the the country where you’re based in, you might be restricted in what you can actually do with the emails you’ve harvested. For instance, Canada restricts usage of email blasts sent to people who you’ve never had contact with before. However, even though you can’t send directed emails to them, there are other things that can be done with these emails. Here are a few:

  • Invite them your Google Plus Page: You can cross over your twitter list to your Google plus page and invite them to see if they’ll want to follow you on Google plus as well. To do so, you’ll need to put your emails in a csv export and upload them to Google Plus as shown below.
  • Upload to LinkedIn: If you have a personal or business LinkedIn page that you want to grow, you can use these emails to send invitations to connect on LinkedIn. Again, you’ll need to create a csv export to get this to work.
  • Facebook Ads: There is a great feature in Facebook that lets you create ads that will be sent only to a certain list of emails. These ads typically have great conversion rates so it would be fitting to use this email list to set up Facebook ads with
  • Invite to a Google Calendar Event: There is a little known trick where you can invite any email address to a Google calendar event. In the event note you can write down whatever offer you are giving, just like an email. However, you can only do this 500 times in one day so watch out so your account doesn’t get banned.

So there it is. If you were able to complete the tutorial on how to harvest emails from Twitter you should have quite of emails to play with now. And even if you can't direct mail anyone on these lists, there are other options at your disposal to get the most out of them.

Found this guide useful? Feeling generous?