Link Prospecting with Twitter Tools and APIs

Most linkbuilders constantly hunt for new ways to find new link prospects for their clients. Many of us use tools like SEOmoz, Ontolo, Buzzstream, and others to find new search queries. Sometimes, though, we have to think creatively, outside the box, to gather prospects that no one else has leveraged yet. One such way is to use Twitter for linkbuilding.

I have been playing around with ImportXML scripts in Google Docs for a few months now, thanks to inspiration from a few other Distilled guys. One day I decided to figure out how to grab prospects from Twitter using some free tools.

*Disclaimer: I originally began gathering these prospects by scraping a resource site. Don’t do this. The Google Doc linked to below uses the free Twitter API, and the JSON was (relatively) easy. Don’t scrape. Simply open my Google Doc (it is read-only) and make a copy for yourself. You don’t even need an API key. Enjoy!*

Step One: Use FollowerWonk to gather prospects

Let’s say that we are building links for a poetry site. Go to FollowerWonk.com and search for “poetry”. You will receive a long list of Twitter names that include the word “poetry” in their biographies. FollowerWonk provides the ability to sort by relevance, follower count, or friend count.

I usually sort by follower count, in order to use the accounts with the largest numbers of followers so that we can gather as many link prospects as possible. We can always narrow down the list later using some Excel wizardry (I realize I’m using “wizardry” frequently. I think I have Harry Potter on my mind…)

Now use MultiLinks in Firefox (a free extension that I have not found a comparable Chrome extension for yet. If you know of one, please leave it in the comments). You have the ability to set MultiLinks to allow you to highlight all of the links on one page via right-click click-and-drag. Use this capability to gather all of the Twitter profiles returned to you with FollowerWonk. Paste the gathered links into Excel, where you will now have the Twitter handle and Twitter site of each account.

Step Two: Use FriendOrFollow.com to download all of their followers

Now run the top Twitter accounts through FriendorFollow.com. I recommend running the top 10 or so because these are the accounts with the most followers. You’ll get some junk followers, such as bots, in your list, but we’ll sort those out later in Excel. I also recommend only running the top 10 or so because this will give you a large set of prospects and will also not take up your whole day!

FriendorFollow allows you to sort by “following” (people followed by the account, but who are not following it back), “fans” (people who follow the account, but are not followed back), and “friends” (people who both follow and are followed by the account). I recommend downloading all three lists and combining them into one Excel sheet. Do this for 3-5 large accounts, and now we have a huge list of potential link contacts/targets.

You should now have the Twitter name, location, description, and follower counts of each Twitter profile.

Now we are getting somewhere, but we’re not there yet. We still need websites that are associated with these profiles. This is where Twitter API wizardry comes into play. First though, we want to scrub some data.

Step Three: Scrubbing Data in Excel

You now have a huge Excel spreadsheet of Twitter profiles, including their usernames, biographies, and follower counts. We want to now clean out the profiles that are not relevant to our niche, in this case “poetry”.

First, let’s clean out the sites that do not have “poetry” or “poet” in their biography. We do this by using the Custom Filter function in Excel.

Your spreadsheet needs to be in a table (on a PC, CTRL+L and select if your columns have headers or not). Click the down “Sort” arrow on the column header and choose “Custom.” Here is how I set mine up:

This will filter the list to include only biographies that include

Once you click “Ok”, Excel will sort out any entry that does not have “poetry” or “poet” in the biography.

Now we can proceed to step four, where we will plug the Twitter usernames into a Google Doc that is set up to gather the website associated with the username.

Step Four: Use Twitter’s API to gather associated websites

This is the step where all of our hard work gathering the prospects pays off. Copy and paste the Twitter usernames into a Google Doc spreadsheet. You can either create your own sheet using the following script, or use a copy of the Google Doc I have linked to below:

*Note* You will need to register for your own SEOmoz Free API Access ID and Secret Key. You can do this here. Enter these on the Config sheet in the specified cells. These will copy to the APIdetails sheet.

Learn on your own

If you are interested in learning how to use APIs on your own, here are some good resources. If you are lazy and have a JSON developer friend, buy them dinner in return for a script!

Now all you need to do is copy and paste the websites associated with the Twitter usernames back into your Excel spreadsheet so that we can scrub the clean and sort the data again and end with a quality list of prospects.

Step Five: Clean Your Data to Qualify Potentials

Hopefully now we have a long list of “poetry” prospects in an Excel spreadsheet, but it’s just that…a long list. How do we narrow it down to create an actionable list of Twitter accounts to target for outreach?

We only want to keep the entries that have associated websites. You should go to the Website column and click the Sort arrow in the column header. The easiest way to clean out the accounts without websites is to unselect the “Blanks” option. Presto! You now have a list of Twitter usernames with websites that are affiliated with poetry niche.

Congratulations!

You now have a long list of Twitter accounts that are related to poetry, as well as their follower counts (which can tell you how influential the account is) and websites. All you need to do from this point is plug the website URLs into a bulk URL opener (like URLopener or Ontolo’s URL Reviewer) and set to qualifying them manually.

How can we take this further?

If this tool is popular, I’m going to try to deploy it onto AppEngine, so that it will be more accessible and widely available to people. If you do too many API calls from within GDocs, you may be limited and not able to make API calls for 24 hours. Be careful!

Please leave any comments/suggestions below in the Comments section!

Bon chance! (Good luck!)

*Update* It is also possible to use this tool for competitor analysis, to get a quick overview of your client’s site and their main competitors. This simply involves adding a column after the column pulling the Twitter API info (column C), inputting the URLs into one of the columns (I put them in column D) and updating Column E to pull the Column D cells instead of Column C. Bonus!

John Doherty

I'm the Senior Growth Marketing Manager of Trulia Rentals, based in San Francisco. Previous to Trulia Rentals I ran marketing at HotPads.com. Previous to that I worked at Distilled for 2 years as an online marketing consultant. In my spare time I shoot lifestyle photography, ski, rock climb, and update my Twitter and Google+ accounts.

Hey Tom -
Thanks for the comment! It should not be the same list, because we have gathered the followers of the first set of accounts in Step 2. The reason we sort the row to only keep accounts with “poet” in them in Step 3 is because so many non-related Twitter accounts exist that probably follow the well-known accounts.

Is this not what is happening? I could have missed an important point…

You are so right, there are many different ways this can be useful. For example, mining your Twitter followers for links. If your client has a large following…this is a way to contact some of them to write something about you, write a review, link from their site, etc.

Awesome article. We’ve just developed a scraper that uses followerwonk to pull every twitter profile with a url in their Bio for a given search query. Works particularly well for ‘local’ link prospecting – ie: miami foodie, etc.

We haven’t taken it as far as friendorfollow, but I think we will now. Awesome ideas – I love that we’re on the same page across the country!

Thanks Brett! Great comment, and I’m glad to hear you all are working on similar tools! They make life much much easier. Feel free to use my spreadsheet, etc and make it more awesome! Then share it back so we can all benefit!

I am using Twitter past 2 months to optimize my software company website.
Although i am happy with the output,but want to be serious with twitter to get best from it…As i am newbie,can you please guide me with your ideas and experience,although i lied your ideas and give it a try…Waiting for your response!!

For some reason when I copy your Google Docs spreadsheet the code doesn’t copy. Any clue on what I’m doing wrong? The GetURL code is there in the article but the code for Tweets and Reddit is not. Any help would be appreciated.

Great post. I’m having a problem though. I’ve made a copy of your google docs spreadsheet and entered my API data on the config tab and it shows on the API tab. However the tab websites for users isn’t populating? Any ideas?

For many years, the tiffany name has inspired makers of True Religion Jeans replica
jewelry to copy design and form. The result has been the creation of a profitable business
with a large clientele among those who want the
Tiffany look.

Simply desire to say your article is as astounding.
The clarity in your post is simply spectacular and i could assume you’re an expert on this subject. Well with your permission allow me to grab your feed to keep up to date with forthcoming post. Thanks a million and please keep up the rewarding work.

John, I listened attentively to your comments in the audience at IONsearch (I believe it was you – correct me if I’m wrong, you sparked up quite a debate!) My comment here today related to API tools – I used to use Twiping software a lot. It was fantastic! Did you ever use it? Anyway since twitter changed it’s API calls this software went instantly out of action. Absolutely gutted. I just wanted to ask if you knew of a decent alternative? In my eyes it was the best kit on the market! Kudos to @jdmullin – it was relatively unknown but had so much potential!!!! The only thing that I have found that resembles it slightly is Followerwonk. Do you use any other tools to datamine on Twitter please John besides the above?

I’ve just started using followerwonk after reading this article. It seems to be working and it’s a free tool as I’m already a pro member at MOZ. Panda and penguin have forced me to rethink my strategy and social is at the core of it. Watch this space!

[...] and prospects quicker; Distilled’s SEO John Doherty did a write up of his experiments into link prospecting with twitter tools and APIs. Take a look at the lists of journalists you can search for [...]

[...] and prospects quicker; Distilled’s SEO John Doherty did a write up of his experiments into link prospecting with twitter tools and APIs. Take a look at the lists of journalists you can search for [...]

Social Me!

I'm a San Francisco based growth marketer, currently working for Trulia. Formerly head of marketing at HotPads, former head of Distilled NYC, I have also run Destinee Media in Switzerland and worked in-house in online education.
The thoughts on this site are my own and I founded HireGun.co.