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:
- Use Followerwonk to find initial Twitter accounts for your niche
- Use FriendorFollow to download all of their followers
- Clean your data so you have a quality preliminary list.
- Use the free Twitter API to pull the websites (or make a copy of my Google Doc)
- 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:
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:
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 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!