Using the SEOmoz Free API with Excel

by Jason Green on April 19, 2011 · 85 comments

Powered by Linkscape


At one time or another, we’ve all come across a “juicy” list of directories, blogs, or other websites that we might be able to use for some link building. I recently came across such a list and was quickly annoyed at how low the quality of some of the sites was. I wanted a quick and dirty way to rate all of the sites so I could start my linkbuilding process with the most promising websites. That’s when I found the SEOmoz Free API. With my mad Excel skills and lots of help from Leith Ross with some Excel code, I was able to create what I think is the fastest and easiest implementation of the SEOmoz API out there. It’s also easy to customize if you want to hack it into something that suits your needs.

This is a self-service spreadsheet, so you’ll need to have your own API credentials. (Get credentials here) If there’s enough demand, I’d be happy to publish a more polished version for mass consumption. (leave a comment)

I wrote a custom function in Excel that queries the SEOmoz API URL with the various parameters pulled from the spreadsheet. You just need to download the spreadsheet and insert your API credentials.
Press Alt+F8 and run the GetURLMetrics macro to begin processing. Be patient with large lists of URL’s. Currently, it takes about a half-second per URL to process. LinkScape has been renamed Mozscape API and new throttling limits have been added. For the free version, we’re limited to 1 request every 10 seconds. Using that version, please don’t try to run huge lists of URLs. 1000 URLs would take about 3 hours.

To follow along, download the latest Excel macro-enabled spreadsheet below: (Enable macros after opening.)
Excel Spreadsheet (Beta1) 4/19/2011 (Original Version)
Excel Spreadsheet (Beta 2) 9/22/2011
*** 7/23/2012 – Issues with V2 have been resolved. Please download version 3 below. ***
Excel Spreadsheet (Beta 3) 7/23/2012
***[UPDATE: 8/14/2012: I joined the Mozscape API beta testing group today. Expect a new and hugely improved version of this spreadsheet very soon!!!]
***[UPDATE: 9/1/2012: The Mozscape API beta testing is complete and has been released to production. Unfortunately, the release broke our macro. Luckily, during beta testing we were working on an add-in / plugin version. See below…
********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing. (very very beta)
Rather than a vba macro, this new version is an installable Excel Add-in.
Check it out here: http://www.businesshut.com/seo/seomoz-mozscape-add-in-for-excel/
This also renders most of the instructions on THIS page, null and void. The new page above will contain the latest information for this project.
**************************************

The function, named GetURLMetrics, takes a specified URL and whether or not to follow redirects as parameters. [eg. =GetURLmetrics("google.com",TRUE) ]

It returns the entire response of the Linkscape url-metrics API call, and the cells to the right parse that into usable fields. If you’re looking for a way to assess many websites quickly, I think this will work for most applications.

This is very much a beta version and I appreciate anyone giving it a try. It worked fine for me, but others have not had the same success. Your questions and suggestions are very much appreciated.

*** Update: If you receive an “invalid signature” error, please refresh the API page on SEOmoz to get new credentials. Make sure to re-copy all of the information needed, and that you don’t accidentally copy line breaks or other hidden characters. It takes a few tries for some, but usually works.

*** Update: Mac users: If anyone was able to get this working on their Mac, or with OpenOffice on any platform, we’d love to hear from you. Several users have tried, but weren’t able to get it working. If you figured it out, please let us know.

Download the spreadsheet here.
(right-click and Save Target As)
The macro/function is saved within and can be viewed by pressing Alt+F11 once in Excel.
Press Alt_F8 to Run.

You can download the text of the macro below. You’ll need all of the included functions for everything to work properly. To run as a stand alone macro, just insert your credentials directly into the code and uncomment the input boxes. (There are notes included in the macro.)



********* UPDATE 12/13/2011 **********
Rolling out December, 16th, 2011, the SEOmoz Free API will be severely limited.
The new rate limit for the Free API is 1 request every 10 seconds.
(Compared to 2 or 3 per second that we’ve been running.)
And batch requests for the URL Metrics Call has been limited to 10 URLs for every POST request.
You can read the official SEOmoz API update here.
I’m currently looking into alternatives, and am entertaining the idea of building my own API.
**************************************
********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing. (very very beta)
Rather than a vba macro, this new version is an installable Excel Add-in.
Check it out here: http://www.businesshut.com/seo/seomoz-mozscape-add-in-for-excel/
This also renders most of the instructions on THIS page, null and void. The new page above will contain the latest information for this project.
**************************************


Suggestions and support are welcome. Thank you to everyone who has given feedback thus far.


Previous post:

Next post: