Technical

My Favourite Queries:
SEOgadget for Excel

by on 23rd January 2015

SEOgadget for Excel got a little update this week with better documentation to help you learn how to use the extension.

I thought I’d share the few queries that I use in Excel most heavily:

All the URL Metrics from Moz

Sometimes you need all the URL Metrics data, and, if you have a paid API, that’s possible with this call:

Download: Example Spreadsheet

Where “A7:A28” is the cell range to read for URLs to fetch metrics for and “Cols=133714411517” is the parameter used to specify which metrics you’d like returned. More on those here.

all-url-metrics

If you’re not a paid API subscriber, this query will give you most of the URL metrics data you need including Page and Domain authority:

As an aside, I think the Moz API has improved in performance terms recently. The response time is very fast which I think must be caching related. On API best practices, it’s important to know that SEOgadget for Excel batches requests and tries to play nice with all the API endpoints it interacts with. We cache responses so that, if they’re repeated in SEOgadget for Excel, we don’t retry the query.

Links to a Page or Domain

It’s good to see who’s linking to your page or domain! Opensiteexplorer is mostly based on the Links API, and so queries like this can do very Open Site Explorer based things.

Download: Example Spreadsheet

links-api

It’s a powerful API call because there are just so many ways you can request the data:

Show me the top URL on each domain that links to the page builtvisible.com sorted by Domain Authority, filtering for external and equity passing links, showing anchor text used, all free URL metrics on the linking URL and flag for various link attributes (LinkCols=2) such as a 301 redirect (Column If – a 301 = “65536”).

I’ve spent years trying to explain how this endpoint works at conferences. It’s really, really hard to learn. Once you get it, though, it becomes very interesting and very powerful. Just look at the query structure, and then read the relevant section in the API documentation.

Most of what you need to know is documented on our plugin page but is documented fully on Moz’s Links API page. Open it up in a new tab and work through it step by step.

The best piece of advice I could give you on how to learn the call is learn what each individual parameter value can be set to. Analyse each variable in the query, and experiment by making changes in the example Spreadsheet.

Here’s our query structure: click though to the correct documentation on each parameter. For [Limit] and [Chunk] and an overview of the query in full, refer to our documentation.

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

If you really want to have fun with this API endpoint, why not try to build your own Open Site Explorer in Excel?

New Links with Majestic

Majestic’s API can do lots of things, and we’ve talked about a few features before – reviewing a domain’s backlink history, for example.

I regularly monitor new and lost links to our site, and this query can help with precisely that:

Download: Excel Spreadsheet

=majesticAPI_toRange(“majesticlost[#All]”,”GetNewLostBackLinks”,”fresh”,”majestic.com”,”Count=100,Mode=0″)

Return 100 (“count=100”) new links (“mode=0”) from the fresh index for the domain majestic.com.

Again, with all of the Majestic query features, it takes a little time to learn all of the API calls available (documentation). Reading the SEOgadget for Excel documentation will help too.

Majestic’s GetIndexItemInfo

This is the URL Metrics equivalent for Majestic – get all of those useful URL by URL metrics:

majestic-getindexiteminfo

This query will fetch data on the URLs in cells A5 to A10.

Download: Excel Spreadsheet

This Excel Spreadsheet has a number of API call examples, including GetIndexItemInfo, GetAnchorText, GetBackLinksHistory and GetNewLostBackLinks.

Enjoy!

Join the Inner Circle

Stay one step ahead of the competition by staying with us. Join the Inner Circle to receive a monthly update of Organic Digital marketing resources, blogs and industry news.

Get insights straight to your inbox

Stay one step ahead of the competition with our monthly Inner Circle email full of resources, industry developments and opinions from around the web.