SEO

Update: Version 2.0 of the Excel Interface to the seoMoz API

by Jason Green on September 22, 2011 · 6 comments

I’m very happy to announce an updated version of our Excel Interface to the seoMoz LinkScape 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.
  • 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

– Download Version 2.0 of the Macro-Enabled Excel Workbook –
– Download the Text-Only Version of the Macro –

Prior supporting information is available here: 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 and becomes almost unusable for this macro.
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.
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 · 59 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 credentials, and the function should start pulling data. (Press Ctrl+Alt+F9 to refresh formulas.)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.

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


View the Updates in Version 2

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

– Download Version 2.0 of the Macro-Enabled Excel Workbook –
– Download the Text-Only Version of the Macro –



********* UPDATE 12/13/2011 **********
Rolling out December, 16th, 2011, the SEOmoz Free API will be severely limited and becomes almost unusable for this macro.
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.
Suggestions and support are welcome. Thank you to everyone who has given feedback thus far.
**************************************

I Love Google But…

by Jason Green on August 27, 2010 · 0 comments

I love Google! They provide incredible services to the online community for free! Plus, they’re giving away Gmail stickers now too!

However, I agree with Brian Clark at CopyBlogger in that we shouldn’t use so much energy to game the system. We need to concentrate on excellent content that is useful to many people. Writing a bunch of blog posts only to fill your site with keywords might get you some random search traffic, but it’s not even close to having loyal subscribers.

Simple comparison:
1. You get 1000 visitors in a month thanks to the 200 posts you wrote that were strategically written to include buzzwords from the long tail and the short head.
- Super~ These people will give you about 10 seconds of their time before they see what you’ve done. Real people can easily recognize a post that’s stuffed with keywords or that was purely written to attract the search engines. You got a visitor, but they’ll never be back.

2. You have 100 subscribers that read every one of your posts. They visit your site and occasionally leave comments. Maybe a few of them have blogs of their own and like to comment about your posts from there. Yes, that’s an inbound link that will help your search rank. You didn’t get it by spamming the Internet with garbage. You got it by writing content that someone found useful. They found it so useful that they took time out of their day to write about it and give you a link. Now the readers of that blog, who trust what the writer has to say, click over to your blog. Snap! More subscribers. And the cycle continues.

A little more math to back up #2: If you write 10 high-quality posts per month and all 100 subscribers visit, that’s 1000 visits right there. Add in the link juice and the fact that it will naturally contain good keywords, and you can expect a ton more traffic than #1.

Moral of the Story:
Everyone please stop writing posts solely for the search engines! Yes, I’m guilty too, but I’m starting to come around. When writing a post pretend that you’re writing a letter to a friend. If you wouldn’t send this to a friend, then why would anyone else care to read it? If you don’t pass that qualifier, don’t write the post.