SEOgadget for Excel

Unlock the incredible capabilities of tool provider data from Majestic SEO, Moz and Grepwords within Excel

About SEOGadget for Excel

For a long, long time we’ve used Microsoft Excel to gain insight, make calculations, create reports and solve problems. While perhaps, a full time developer might see some limitation in the platform, Excel is a godsend to almost the entire search marketing ecosystem.

For most of us, working with data from 3rd party marketing tools tends to begin with a CSV export. If you think about how much time we’ve all spent waiting for a CSV file to download, and how much time we’ve spent merging the data into Excel after the event, you might agree it’s a time sink, and not a terribly productive part of your analysis.

SEOgadget for Excel addresses that problem by connecting directly to services such as Majestic SEO and Moz via their API services.

This guide teaches you, from the very beginning, how to use SEOgadget for Excel covering each of its main functions.

SYSTEM REQUIREMENTS

Well, this is awkward. Excel DNA (Excel-DNA is an independent project to integrate .NET into Excel) only works with Windows based installations of Office. Specifically, Windows 7,8 or 8.1 with Office 2010 or 2013. 32 bit is still supported in Windows 7, but ideally you should be using the 64 bit installation.

If you’re not a subscriber to any of these services, leave the token field blank – everything else will work.

Click the “Add Credentials” button and you’ll be given a dialog box. Paste your API tokens in here. You can leave any fields blank, just re-run setup if you acquire new API tokens.

Add credentials, and click “Install Add-in”

Working with Links Data APIs

Learn Seogadget For Excel With The Moz Url Metrics API

With SEOgadget for Excel, you can get data and results very quickly. The start of our tutorial focuses on the simplest API call, the Moz URL Metrics API. Spend a little time playing with this function, and the rest of the Moz API calls and you’ll soon be ready to go and quickly master Majestic, SEMrush and Grepwords too.

The URL Metrics API should be very familiar to us marketers. It powers the Moz toolbar, and gives us familiar metrics like Page Authority and Domain Authority. It’s also free.Get your API key from Moz, here: http://moz.com/products/api

LET’S TRY A FEW SIMPLE COPY AND PASTE QUERIES

Let’s start with a really simple query, requesting data from Moz’s URL metrics API for a URL in cell A3. Put a URL in Cell A3, (like http://builtvisible.com) and paste this query anywhere you like:

1

=MOZ_URLMetrics_toFit(A3)

If you’d like to, you can create a list of URLs, and use a range in your query, like this:

1

=MOZ_URLMetrics_toFit(A3:A4)

Here’s what you’ll see:

Take a look at the output – you’ll see “ut”, “uu”, “ueid” in the top of your array.

They’re called response field names, and they’re unique to different types of data.

In this case, “uu” Is the “Canonical URL” and “ut” is the Title of the URL.

To get a full understanding of these field names, head to the URL metrics API documentation. In the API documentation, you’ll see a table that details what each field name represents and outlines the corresponding bit flag number, explained below.

Let’s imagine you only wanted to retrieve our two data points, Title and Canonical URL with “Cols=5”, but write them to a table or a range instead of fitting to an array.So far, you’ve seen the function:

=MOZ_URLMetrics_toFit()

There are several “helper” functions, each designed to fit the data received from the API output.

They’re: “_toFit”, “_toSheet”, and “_toRange”:

1

=MOZ_URLMetrics_toFit("builtvisible.com")

1

=MOZ_URLMetrics_toSheet("builtvisible.com")

1

=MOZ_URLMetrics_toRange("Sheet2","builtvisible.com")

1

=MOZ_URLMetrics_toRange("myTable","www.yahoo.com")

Try copying and pasting each of these queries into Excel to see what they do! Don’t forget to create a table (of any size) called “myTable” – one of these queries will send the data to an entirely new sheet, so be ready to hunt around for your data!

_ToFit

With _toFit, Excel simply creates an array in the correct size, fitting the data it receives into an appropriate space for you.

_ToRange

Using _toRange with a table or cell reference or cell range is very powerful and you’re more able to edit the original formula (which is impossible using arrays offered by the _toFit function.For example, let’s say you’d like to fetch the Canonical URL (uu) and Title (ut) and write the data to a table called: myTable. Create a table (instructions) and name it “myTable”. Use “[#All]” in the table name to stop the table moving down 1 row every time you refresh the data: “myTable[#All]”

The query will look like this:

1

=MOZ_URLMetrics_toRange("myTable[#All]",A3:A4,"Cols=5")

WHICH HELPER SHOULD I CHOOSE?

Most of the time, “_toRange” is the best helper to use because it’s very dynamic in nature, you specify a table, the function populates that table. Amazing.

FILTERING THE DATA YOU NEED

What if you only want a certain type of data from a Moz API call, say the Titles and Canonical URLs for each URL?

Uniquely, the Moz URL Metrics API uses “Bit Flags”, integer values that can be specified in a “Cols=” argument to request only the data that’s needed. So, if you only wanted the Titles and Canonical URLs for each URL, you’d add the “Cols=” argument into the query:

1

=MOZ_URLMetrics_toRange("mynewtable[#All]",A3,"Cols=5")

The value, “Cols=5” is the sum of the “ut” bit flag value of 1 plus the “uu” bit flag value of 4. The sum value, 3 is unique, no other combination of values will result in a sum of 5. “Cols=5” returns the URL title and canonical URL.

EXAMPLE – URL METRICS API

1

=MOZ_URLMetrics_toRange("mynewTable[#All]",A3:A4,"Cols=133714411517")

This query makes a request of the Moz URL Metrics API, outputting the data to range: “mynewtable” (you need to create a small table and name it “mynewtable” first) for a row of URLs found in cell range A3:A4.

Using “Cols=133714411517”, we’ve requested every output field available in the paid API.

If you only have access to the free API, use:

1

=MOZ_URLMetrics_toRange("mynewTable[#All]",A3:A4,"Cols=103616137253")

OUTPUT:

Your output table will look like this:

Url Metrics API Recap

Now we’ve covered the fundamentals of the extension, we’ll use this format to explain each new feature in the SEOgadget for Excel extension:

=MOZ_URLMetrics([URL],[BIT])

Where: The 1st argument [URL] can be either a single URL (without the http://) or a list of URLS (via a vertical or horizontal range).

The 2nd argument [BIT] is the bit flag to indicate which columns to return. Bit flag values can be added together at the user’s discretion:

1

=MOZ_URLMetrics_toRange("mynewTable[#All]",A1,"Cols=103079215109")

Will give you data from the (free) URL metrics API including Page Authority.

Example Query:

1

=MOZ_URLMetrics_toRange("mynewTable[#All]",A3:A4,"Cols=133714411517")

OUTPUT:

If you’re a paid subscriber to Moz’s API service, this query will give you all the data available via the URL metrics endpoint. In this case, the data will be for the URLs found in range A3:A4 – your data will be outputted to a table. Create that table first, and name it “mynewTable” (or anything else you’d like).

Read the API documentation, paying particular attention to bit flag values, and response field names. Don’t worry; they all start to make sense after a while: http://apiwiki.moz.com/url-metrics.

Moz Anchor Text API

Understanding how a page links to another is one of the most important aspects of SEO today. As marketers, we’re often on the lookout for suspicious links that use “exact match” anchor text – with a view to removing them, realigning them, or whatever our SEO strategy dictates.

The Moz Anchor Text API allows us to fetch the anchor text data for a root domain, subdomain, or page. In the Anchor text API calls, we can analyse phrases or terms linking to a page.

ANCHOR TEXT API QUERY CONSTRUCTION

As you read this section, open up Moz’s API documentation for the anchor text API: http://apiwiki.moz.com/anchor-text

=MOZ_anchorTextAPI([URL],[SCOPE],[SORT],[BIT],[LIMIT],[CHUNK])

Where: The 1st argument [URL] can be either a single URL (without the http://) or a list of URLS (via a vertical or horizontal range).

The 2nd argument [SCOPE] e.g. “page_to_page” – Returns a set of Source pages linking to the specified Target page.

The 4th argument [BIT] – The bit flag to indicate which columns to return.

The 5th argument [LIMIT] – How many results do we require? This is limited based on the level of API service you subscribe to – see pricing here.

The 6th argument [CHUNK] – request in batches – e.g., fetch 100 results requesting in batches of 10 URLs per API call – you can usually ask for 1000 results in a single batch

UNDERSTANDING “SCOPE” IN THE ANCHOR TEXT API

For the Anchor Text API, Scope is an argument used to return phrases found in links to the target URL (“phrase_to_page”), phrases found in links to the subdomain of the target URL (“phrase_to_subdomain”) and phrases found in links to the root domain of the target URL (“phrase_to_domain”).

The API will also return terms found simply by swapping “phrase” to “term”.

UNDERSTANDING “SORT” IN THE ANCHOR TEXT API

In Moz’s Anchor Text API, the only available sort function appears to be “domains_linking_page” – sort by the number of domains that link to our page with this anchor text.The idea of scope and sort reappears later in the Moz Links. For now, let’s look at some examples.

ANCHOR TEXT API – COPY / PASTE EXAMPLE

This query will give you the first 100 anchor text phrases (anchor phrases to your page) linking to your URL (a URL in cell A2). The data would be requested via a single batch of 1000 rows and placed in table “yourtable”. All of the anchor text values are being requested in “Cols=2048” (see: anchor text values in the Moz API documentation)

The Moz Links API

The Links API powers Open Site Explorer and allows you to see the links pointing to a URL, root domain or subdomain.

Data can be returned sorted by Domain or Page Authority. This query is the mother of all Excel functions – if you can master this query, you’ll be able to build some seriously impressive Excel apps based on the Moz API!

LINKS API QUERY CONSTRUCTION

Before reading this, take a look at Moz’s Links API documentation. Skim through it, read the API call scopes, sorts and filter construction. Next, gain an appreciation for the meaning of “TargetCols”, “SourceCols” and “LinkCols”. Now read the API documentation again.

The 1st argument [URL] can be either a single URL (without the http://) or a list of URLS (via a vertical or horizontal range).

The 2nd argument [SCOPE] e.g. “page_to_page” Returns a set of Source domains, root domains or URLs linking to the specified Target URL, domain or root domain – for a full list visit the Links API documentation.

In this call, we’ve asked for the filters “external+follow” to be applied – showing only external links that do not use “rel=”nofollow” in the href link.

“TargetCols” is set to 8 – showing the subdomain (“luufq”) of the URL the links are pointing to. “SourceCols” is set to 103079215109 which will give every freely available URL metrics data point on the linking URLs.

“LinkCols” is set to 8, which represents normalised anchor text.

OUTPUT:

This API call will give the first 100 linking domains to your page, sorted by domain authority, the output will look like this (check the field names against the URL metrics values to understand what data you’re looking at!)

If you have a paid Moz API subscription, you could adapt “SourceCols” to the full API output to gather all the Metrics data for your URLs. That query would look like this:

This query outputs the same URL metrics data per link as our first call, but this time we’re showing “single links per set of root domains linking to any page on the specified target’s root domain” via the “domain_to_domain” scope.

We’re filtering for “nonequity” – “links with any of these attributes specified: nofollow, meta-nofollow, offscreen, 302 or an RSS feed”.

Majestic API

QUERY THE FRESH AND HISTORIC LINK INDICES

Majestic’s API is a powerful, fast endpoint to request data from their fresh and historic indices. We use this API heavily in our own software and internal Excel / data analysis work.

WHAT MAJESTIC API CALLS SEOGADGET FOR EXCEL SUPPORT?

SEOgadget for Excel supports the 12 main functions of the API, for example: “GetIndexItemInfo” – a call that returns key statistics for index items (domain/subdomain/URL) such as: number of external backlinks and referring domains pointing to index item.

=MAJESTICAPI_ AS A WRAPPER FUNCTION

There are too many separate functions in the Majestic API to justify separate function names in Excel. So, rather than creating separate functions for each API call, the =MajesticAPI() function acts as a wrapper for all other API calls.

For example, to get the data on external back links and referring domains pointing to a URL, we use the “GetBackLinkData” command as the first argument in the =MajesticAPI() function.

The following formula would populate a table called “majestictable” with data for the domain builtvisible.com:

This function can handle all API commands as published (and any new ones, as long as the response format remains the same).

The 1st argument [ARG1] is the command to execute. For example, “GetAnchorText”, “GetBackLinkData”, “GetBackLinksHistory” or “GetHostedDomains”.

The 2nd argument [ARG2] is the value to assign to the index “datasource” API parameter – “fresh” or “historic”

The 3rd argument [ARG3] handles the URL(s) passed to the API. Many accept a list of URLs in the format: “item0,item1,item3” others a single “item” or “Query” parameter. Read the individual Command Name pages top get a feel for what’s possible.

The 4th argument [ARG4] handles any other API parameters not covered by 2 & 3 above. The name-value pairs may be supplied as a CSV list or via a range pointing at a two column table holding such. You could ignore the 2nd & 3rd arguments and simply use this mechanism for all parameters if preferred.

As the API can return 1 or more tables of data use the 5th argument [ARG5] to specify which table. If not specified 1st table is returned.

The 6th argument [ARG6] if set to TRUE will ignore the API’s cache. In general, using the cache is a good idea as Excel can call a formula multiple times even if that’s not the intention.

The (separate) cache associated with the Majestic API is even more useful due to the multiple tables sometimes returned. Without the cache, each table fetch would make a resource consuming call back to Majestic.

The 7th argument [ARG7] is the API call http timeout (in seconds), which defaults to a long 60 seconds if left un-configured.

MAJESTIC GETBACKLINKDATA API: GET DATA ON YOUR BACK LINKS TO A URL

Let’s make a start with some actionable queries. “GetBackLinkData” is probably the most powerful links analysis function on the planet – allowing access to Majestic’s huge database of links.

GETBACKLINKDATA API – COPY / PASTE EXAMPLES

Example Query:

This query will get the top 1000 links from the fresh index to the root domain, builtvisible.com, writing the data to a table called “majestictable”:

You’ll see a large amount of data outputted (this is only a small section of the first 10 columns! A full breakdown of the meaning of all of the API’s output headers can be found at the bottom of this page.

MAJESTIC GETANCHORTEXTAPI: GET THE ANCHOR TEXT LINKS TO A URL

This powerful API function returns Anchor text for a given domain, subdomain or URL, from Majestic’s Fresh or Historic index as well as allowing you to filter the results by a keyword.

GETANCHORTEXTAPI – COPY / PASTE EXAMPLES

Example Query:

Get the top 10 anchors to the domain builtvisible.com from the historic index and write those results to a table called “majesticanchor”:

Working with Keyword Data APIs

GREPWORDS API

The Grepwords API is a powerful, fast endpoint for keyword search volume collection.

For a long time, its output was for US and “global” search volumes only, but that all changed thanks to Russ and his team. Now we have search volumes from Google in around 32 countries. SEOgadget for Excel supports these new locations with just a simple change to your query syntax.

The 4th Argument [ARG4],” results” is a request for the number of records to return – defaults to 10. Not a mandatory argument.

The 5th Argument [ARG5], “Location” respects Grepqord’s current US defaults, but will work for new locations as they are released into the Grepwords index. Not a mandatory argument.

The 6th Argument [ARG6], “Regex” the SEOgadget for Excel extension will accept regex if set to Y. Not a mandatory argument.

The 7th Argument [ARG7], “ignoreCache” – calls are normally cached, this reduces resource usage and allows for the return of multiple tables in a single call, set to “true” if ignore this cache. Not a mandatory argument.

The 8th Argument [ARG8], “timeout” = sets the web request timeout in seconds, defaults to 30. Not a mandatory argument.

GREPWORDS API: GET “RELATED” AND “TOP” KEYWORDS FROM GOOGLE SEARCH

This function returns keyword data related to the term or phrase specified.

RELATED – COPY / PASTE EXAMPLES

Example Query:

Supply 100 keywords related to the term “cats” and write the data to a table called “grepwordsrelated”.

Richard founded Builtvisible in September 2009. Since then he has grown a one man consulting startup agency into a 40 strong, industry accredited digital agency based in the heart of trendy Shoreditch, London.

More about Richard

Richard brings extensive experience in digital marketing, having begun his career in search in 2003. Having worked in highly competitive verticals including travel, finance and retail throughout his career, Richard understands what is required to perform well in a crowded, competitive marketplace.

Away from Builtvisible, Richard is an investor in several technology agency and retail orientated startup companies.

With his remaining time, Richard races sports prototype cars, competing in the 2017 Radical Challenge Championship. If you want to follow his racing career, find @richBracing on Twitter.