How To: Build Open Site Explorer in Excel

Want to Build Opensiteexplorer in Excel?

Back in late July 2012, we launched the SEOgadget Extension for Excel, a free Excel extension for SEOmoz API consumers to quickly and easily pull link data into their reporting.

There’s been a larger volume of requests for the Open Site Explorer template I constructed to demonstrate the power of this extension so, I thought I’d share the download and give a little information on how, exactly it works.

To download the file, follow this link.

How does Open Site Explorer for Excel work?

If you recall from the original extension launch post, Open Site Explorer uses a links API call to show you what sites rae linking to a domain, subdomain or full URL.

The API call in the Excel Extension looks like this:

=SEOMOZ_linksapi_toFit([URL],[SCOPE],[SORT],[FILTER],[TARGETCOLS],[SOURCECOLS],[LINKCOLS],[LIMIT],[CHUNK])

A working API call to fetch link data for say, SEOmoz.org may look like this (give it a try, copy and paste into Excel!).

=SEOMOZ_linksAPI_toFit(seomoz.org,"page_to_page","page_authority","external+follow","TargetCols=8","SourceCols=103079215109","LinkCols=8",100,25)

The resulting data produced would be 100 external, followed pages pointing to the page SEOmoz.org sorted by page authority.

A Slight Limitation with Arrays

The *only* problem you may have encountered with using these functions is that arrays, once created are impossible to edit. That means you have to copy the formula, make whatever change you need to make, paste the formula back into a call and carry on. This is a minor limitation, as most often you really only need one data set – but (and this is where our extension becomes a platform), you can use variables in the API call to get around the problem.

For example, using our OSE  links API call, we could set the URL to be a variable controlled by a value set in cell A1:

=SEOMOZ_linksAPI_toFit($A$1,"page_to_page","page_authority","external+follow","TargetCols=8","SourceCols=103079215109","LinkCols=8",100,25)

Now, you’re able to refresh the data instantly (it really is very fast with SEOmoz’s new API endpoint).

Data Validation in Excel is Awesome

Here’s the Open Site Explorer for Excel query:

=SEOMOZ_linksAPI_toFit($D$11,$H$13,$L$11,CONCATENATE($H$12,"+",$H$11),"TargetCols=8","SourceCols=103079215109","LinkCols=8",$D$12,)

Note that many of the parameters, like “external+follow” have now been replaced with cell references, in this case: CONCATENATE($H$12,”+”,$H$11).

Data validation in “list” mode allows a cell to become a drop down list of options. That drop down list is determined from anotehr cell range in the sheet. Like this:

Our “No. Results” drop down list. Where are those data points coming from?

Head to “Data > Data Validation”. See how “List” has been selected and a cell range highlighted? That’s where the options live! It’s in column Z, so let’s scroll over and take a look:

Roll Your Own

There are a myriad of possibilities for this platform – I truly hope to see examples of quick apps, prototypes and proof of concepts gestating from it. When you realise that you don’t need so much development help to make these things possible, it really gets exciting. If you’ve built anything awesome, do share and we’ll put a link to it on our site.

Don’t want to build your own, that’s fine – To download the file, just follow this link.



Stay Updated: Sign Up for Webinar & New Blog Alerts

13 thoughts on “How To: Build Open Site Explorer in Excel

  1. gcod says:

    Hi,

    Amazing tool but I get a “401 not authorized” error.

    Any idea?

    Thanks!

  2. Jeremy says:

    I’ve been playing around with using this to create something for awhile. But have recently been getting nothing returned error.

    Really like the use of Microformats on this page btw.

  3. Hm, does a basic =SEOMOZ_URLMetrics_toFit(“seogadget.co.uk”) get you any data? If it does, then you need to learn the syntax. If not, uninstall with the setup file and reinstall, hopefully that’ll work!

  4. You might be making an api call that requires a paid account – try the basic command: =SEOMOZ_URLMetrics_toFit(“seogadget.co.uk”) if that works, but your more complex stuff doesn’t then review the post again and work out what’s available in the free api.

  5. Jeremy McDonald says:

    It doesn’t. I’ll try re installing it and see if that works. Thanks!

  6. Mathew Porter says:

    Awesome, will try this our tonight. Will I only get the same amount of request as I would on a free account?

  7. Bill says:

    I’m having a problem with the Links API Excel Extension. I can see the functions, but all I get is errors even when I use you template. I also get an error even when I try a simple call like the one one listed above.
    When I paste this in a cell:
    =SEOMOZ_linksAPI_toFit(seomoz.org,”page_to_page”,”page_authority”,”external+follow”,”TargetCols=8″,”SourceCols=103079215109″,”LinkCols=8″,100,25)

    It returns this:
    ERROR: Nothing returned for via http://lsapi.seomoz.com/linkscape/links/29?Scope=page_to_page&Sort=page_authority&Filter=external+follow&Limit=25&Offset=0&TargetCols=8&SourceCols=103079215109&LinkCols=8&AccessID=member-cfd***&Expires=130984648&Signiture=*** (Note *** indicates value hidden)

    What am I doing wrong?

  8. Jeremy says:

    Bill – try encasing your url in quote marks

  9. Atul says:

    Hi Richard,

    I don’t have any idea as to how to use this template. Need your help. How do i enter data in this template?

    Thanks

  10. Hold fire Atul, we’re about to launch a new version of the Excel plugin.

    Richard

  11. Jim says:

    Hi
    This is going to be one great spreadsheet but when I go to the link it’s all in unicode and opening it does not work. Can you please please forward the file to me email: feltinham@gmail.com.

    Thank you ever so much

    Jim

  12. Hi Jim,

    That spreadsheet is based on an out of date version of the extension – head to: http://seogadget.com/tools/seogadget-for-excel/ – remove all of the old extensions, install this update and refer to the manual for the OpenSiteExplorer query.

    Side note – IMO the Majestic version of the command is a touch more complete. I’d say you should head in that direction.

    Richard

  13. Atul says:

    Hi,

    Have you launched a new version of the Excel plugin. If yes, how can i use that.

    Thanks

Comments are closed.