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