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!*

Tools You Need

The Process

There are five steps to this strategy:

  1. Use Followerwonk to find initial Twitter accounts for your niche
  2. Use FriendorFollow to download all of their followers
  3. Clean your data so you have a quality preliminary list.
  4. Use the free Twitter API to pull the websites (or make a copy of my Google Doc)
  5. Clean your data and actionize!

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:

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:

function getURL(url) {
var jsondata = UrlFetchApp.fetch(“http://api.twitter.com/1/users/show.json?screen_name=”+url);
var object = Utilities.jsonParse(jsondata.getContentText());
return object.url;}

Basically, the above script is named “getURL” and is saying “Fetch the URL for the given screenname from the Twitter API and return the URL”.

Here is a screenshot of websites that I have gathered for some of my followers:

You can also use this spreadsheet (create a copy):

Make a copy of this Google Doc to use it.

*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!

Official Twitter API documentation
PrototypeJS intro to JSON (pretty technical)
Tom Critchlow’s “Using APIs to Track Your Social Media Strategy” post
Building Agile Tools using Google Docs (Distilled blog)

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 QuickURLOpener) 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!

57 thoughts on “Link Prospecting with Twitter Tools and APIs

  1. Cool post – really like it.

    One question – in step 3 when we clean the excel sheet to only include the list of people who have ‘poet’ in their biography are we not ending up with the same list as we saw in step 1?

    1. 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…

    2. Would imagine that Step 3 is to clear out any bots or irrelevant accounts etc. who might have been found (in Step 2)following the ‘poets’ you found in Step 1 🙂

      Great idea John, and good comprehensive post, will definitely give this a go, cheers!

  2. Hey John!

    This is really cool, great explanation of the process. Tried playing with your ideas and found a huge number of great link prospects 🙂

    This combined with custom search terms, local directories means loads of link targets!

    Jon

    1. Awesome! So glad you found it useful!

      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.

      Thanks for letting me know!

  3. John,

    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!

    ps – just tweeted @ you too..

    Brett

    1. 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!

      Cheers!

    1. Boom! Glad it was useful, Dan. I’m sure you had to hack the sheet a little bit to make it work exactly as you wanted, but all the API legwork was done for you.

  4. Pingback: Social is about Traffic and Conversions, NOT Rankings | John Doherty

  5. Pingback: Five Ways to Find Link Prospects | rashidgoogly.com

  6. Pingback: Five Ways to Find Link Prospects | rashidgoogly.com

  7. Pingback: Most Epic Distilled Content of 2011 | distilled

  8. Pingback: - eat.sleep.search.

  9. Pingback: Link Building Resources – A Curated List | Point Blank SEO

  10. 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!!

  11. Pingback: Linklove Boston 2012 Recap | distilled

  12. Pingback: Linklove Boston 2012 Recap | Reputation Expert

  13. Pingback: 4 Steps To Executing A CRO Approved Link Building Campaign

  14. Hi John

    wow this is frickin awesome man, step by step explanation is great. I was watching you Link Love 2012 presentation its amazing, loved it….

    another thing is spreadsheet is not working even thought save a copy but still not showing websites instead giving “error” message.

    wait to hear from you

    1. 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.

  15. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | Complete SEO Marketing News

  16. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | Buypappa blog

  17. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | SEO And Web Company

  18. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | Online SEO Company

  19. Pingback: On The Last Turn Of The Universe – Top Ducks Secret » SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces)

  20. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | Clixto7

  21. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | Ranking Maine

  22. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces)

  23. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we’ve joined forces) | The best SEO Articale on the web

  24. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we've joined forces) « paygseo.co.uk

  25. Pingback: SEOmoz PRO Member? You Now Get Followerwonk Free! (because we've joined forces) - Seo Company Cape Town

  26. Hey John,

    I loving this article. However l am stuck on the google doc step.

    l had no problem inputing the id and key and I left twitter names you had but the websites are not showing up for these twitter names.

    Trying all morning but not sure what l am doing wrong.

  27. Hi John

    Thanks for this post which I still refer back to all this time on 😉

    There’s a chrome extension called linkclump which does the same job as multilinks in chrome.

  28. What’s up it’s me, I am also visiting this web site daily, this website is actually pleasant and the people are genuinely sharing fastidious thoughts.

  29. Pingback: Link Building Is Like...

  30. Hi Tom,

    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?

    Sam.

    1. Same thing here.

      I know it’s a 2-yr-old post but would love to know if you’ve ever added it to AppEngine and if so, where…

      Good strategy overall. Thx for the tut.

  31. Pingback: Link Builder’s Guide to SEER Interactive’s SEO Toolbox | serfofthesoil

  32. 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.

  33. Pingback: Mike King’s SEO and Social Media Tools – SMX London 2012 | Hallam Internet

  34. Pingback: How To Effecitvely Perform Link Prospecting & Outreach via BuzzStream - STRYDE

  35. Pingback: 26 Creative and Innovative Link Building Experts and Their Strategies | Kaiserthesage

  36. 永遠の連続を家族これらを使用して素晴らしい時計します。過去にグリーに関するファッション御堂筋によって 1918
    年 11 月 11 日に始まったヘンリーG.
    女性のペンダント時計はほとんどの場合かかわらず、いくつかのハンターの時計の完全ダウン手は顔を開いて。

  37. 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.

    My weblog – Handymen (Anja)

  38. 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?

  39. 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!

Comments are closed.