SEO

SEOmoz Mozscape Add-In For Excel

by Jason Green on September 5, 2012 · 36 comments

Powered by Mozscape

With the updates just added to the Mozscape API (previously Linkscape), I decided to update the Excel Macro and turn it into a full-fledged Excel Add-In.

Features:
Select any number of cells containing URL’s.
Right-click and select “Run Mozscape on Selection”
Enter your Access ID and Secret Key (aka Private Key) from the api page.
http://www.seomoz.org/api/keys
A big improvement is that you only need to do this once. :-)
Mozscape API Credentials
If your credentials are accepted, a new sheet named “Mozscape” will be created and all url-metrics will be added for your chosen URL’s.

Warnings:
If you run this twice, or if you already have a sheet named “Mozscape” it will be deleted and replaced. Rename it before running again if you want to keep the data.
The free Mozscape API is limited to 1 request every 10 seconds.
100 URL’s would take about 17 minutes. 1,000 would take 3 hours.

Full details will be posted soon. For now, I’m just looking for a few beta testers. ***Please bear with me as this is very much a draft version. There are many features I intend to add, but since the latest release of the Mozscape API broke our previous macro, I’m releasing this untested in case anyone really needs the data. Your support and patience is very much appreciated.

[Update 9/8/2012 : If you're not seeing any data, it's because of a change to the Mozscape API code. They now require that we explicitly list which url-metrics we want to return by using bit flags. I'm expecting to release a new version today that will fix this as well as a few of the other bugs we've seen. More info here: http://apiwiki.seomoz.org/deprecated#default-metrics ]

[Update 9/10/2012 : We've updated the code to use bit flags, fixing the previous issue. Individual bit flag selection has been added as a todo item, as it's best to only retrieve the minimum amount of information you need.
Automatically trims access id and secret key to remove extra spaces.
Installs prerequisite software, such as .NET 4.0 and VSTO Runtime. ]

*** Download the latest version here. ***
[[ This version has only been verified to work with Excel 2010 on a computer running Windows 7. If you get it to work on any other setup, please let us know. ]]
Mozscape Add-in : Beta 1 (9/4/2012)
MozscapeAdd-in : Beta 2 (9/10/2012)
MozscapeAdd-in : Beta 2.2 (9/10/2012)
MozscapeAdd-in : Beta 2.3 (9/11/2012)
*****************************************

Please post any questions or suggestions in the comments below. To receive a notice of each release, enter your name and email in the box below. Thanks again for your support!

I’m very happy to announce an updated version of our Excel Interface to the seoMoz LinkScape / Mozscape API. Your suggestions have been heeded and the new version is a great improvement over the first.

What’s new?

  • Run On-Demand: No more auto-updating. Now, just press Alt+F8 to run when YOU want.
  • Rate Limiter: Helps to keep us out of the seoMoz doghouse. Allows 2 URLs to be processed per second. As of early 2012, the rate limit has been changed to 1 URL per 10 seconds. (for free accounts)
  • Cleaner Spreadsheet: Removed the “raw” seoMoz response and handles all parsing within the macro.
  • Conditional Formatting: Automatically compares scores against all others on the sheet. Highlighting opportunities FTW!
  • Stand-Alone Macro: Save the macro to your personal workbook and you can run it against any list of URLs. No need to use *this* spreadsheet every time. (minor tweaks required)

BusinessHut seoMoz LinkScape API Excel Spreadsheet

Please visit this page to download the latest version of the Excel spreadsheet: Excel Interface to the seoMoz API

— Make it more awesome with this post by John Doherty
— And for even more info, check out John’s post on SEOmoz

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

********* UPDATE 9/5/2012 **********
Version 3.0 is now in beta testing.
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/
**************************************

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

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.