How To

How to Scrape Twitter User Data with Nothing but Excel

Published: 02.29.16

Sometimes you want Twitter usernames to use in a marketing campaign.  Maybe for a Twitter Ad campaign or even a nefarious purpose (hey, I’m not judging!), sometimes you just need a basic list of usernames. But you don’t have the time to go to Twitter.com and copy/paste each name.

There are tools that extract from webpages using Xpath (like Data Miner and Scraper) but even they struggle with the way Twitter has their infinite scroll setup.

But good news – you don’t need anything but a browser and good old Excel.  It’s a simple 5 step hack that I’ve been using for quite a while.  I’ve told a few about it in conversation, and it’s often a, “oh wow – that’s so obvious!” type of response. Since I’m a fan of keeping everything simple, I thought it was worth sharing.

Use Case

Let’s say I want to create a Twitter Ad campaign targeted at an audience who shops for guitar equipment online.  Let’s then say I identified the followers of @guitarcenter to be a great audience for my needs.  Finally, let’s say my need is gathering up a simple .csv file to upload to Twitter Ads’ Tailored Audience module (thus allowing me to send my ad only to my selected users).

Step 1

Track down the followers page for Guitar Center.  At this time, they have 185k followers.  Wow.  You’ll notice as you scroll down, Twitter starts loading in the next row of results (you’ll see a sort of “pop in” effect).  Have a wheel on your mouse?  Use it to keep scrolling until you feel you have enough users loaded on the page.  These are the users we’re going to pull off the page.  (I’ll be honest, this can get boring if you’re looking for a long list).

screenshot

Step 2

Next we’re going to copy all the contents on the page – all the text and pictures – with cntrl-A on Windows.  All the text and pictures should highlight.  Next, we hit cntrl-C to copy everything that’s highlighted.

Step 3

With the rendered text in your clipboard, you’re going to move over to Excel.  Right-click on A1, choose “paste special,” and choose HTML as the final option.

If you’ve copied a lot, this is going to get your computer cooking.  It might take a while (depending on processor and RAM), but you’ll eventually see all your copied items in a globulous mess (like the below):

screenshot2

The truth is, everything pasted into the first column.  This is good!  By using filters, we can easily clear out everything but your usernames.

Step 4

Highlight your column, and turn your A1 cell into a filter (Data > Filter), and click your newly created dropdown.

Next, you’ll want to filter by “begins with,” to which you’ll simply enter the “at symbol” as shown below.  Click OK.

beginswith

At this stage, your A column is sorted, but the messy images may still be obstructing you.  Simply copy all the usernames out of column A and paste into a new sheet. The images shouldn’t go with you.

Step 5

Spot check your list.  Sometimes you’ll get some anomalies.  For example, if a user is following you, you’ll get something like @billsebald Follows You.  That’s easy enough to fix en masse – use the “text to columns” feature to parse out the text after a space.

That’s really all there is to it.  Hacky?  Yes.  Quick and dirty for those who don’t have advanced tools?  Yes.  Your final list should look like this:

final list

There are other ways to import HTML into Excel, but don’t underestimate the power of a simple copy and paste!

Bill Sebald
Bill Sebald
Follow me on Twitter - @billsebald

I've been doing SEO since 1996. Blogger, speaker, and teacher at Philadelphia University. I started Greenlane in 2005 to help clients leverage search marketing to hit business goals. I love this stuff.

Read Bio
  • Altin

    Cdata Software has developed The Twitter Excel Add-In, which is a powerful tool that allows you to connect with live Twitter data directly from Microsoft Excel. Use Excel to search, aggregate, read, write, and update a wide variety of information, including but not limited to usernames, followers, accounts you are following, number of tweets, dates, URLs, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

    The Excel Add-In for Twitter provides the easiest way to connect with Twitter data. Users simply supply their credentials via the connection wizard to create a connection and can immediately begin working with live Twitter tables of data. The Excel Add-In is completely self-contained; no additional software installation is required.

    The Excel Add-In for Twitter is integrated with the Excel toolbar and ribbon, providing direct access to live data with a single click.

    For more information go to:
    https://www.cdata.com/drivers/twitter/excel/
    http://cdn.cdata.com/help/RTB/xls/

© 2017 Greenlane. All rights reserved.

2550 Eisenhower Avenue, A203, Eagleville, PA 19403

A Philadelphia SEO and Digital Marketing Agency    Privacy Policy    RSS

Subscribe to our Newsletter