Announcing: The Links API Extension for Excel for Mozscape

For a long, long time I’ve used 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.

Last year, we announced the SEOgadget Adwords API Extension for Excel. It was (and remains) a huge hit with search marketers wishing to quickly fetch keyword search volume data as part of their market research process.

Today, I’m absolutely delighted to announce we’re releasing the SEOgadget Links API Extension for Excel. With it, you can build reports and cobble together quick applications to fetch valuable link data from all 4 Mozscape API flavours.

>>>>>>>  DOWNLOAD HERE  <<<<<<

A Platform to Open New Possibilities

This post will show you how to use it and provide some insight into how applications and reports like this are now possible:

Yes folks, that’s a working Open Site Explorer report in Excel. It updates automatically, filters for nofollowed, external and internal links and in fact has almost all of the features the real Open Site Explorer has.

Consider our new extension a platform upon which you can build reports, tools and perhaps most importantly, combine Mozscape data with data collected via SEO Tools for Excel, your own custom hacks and of course, keyword search volumes!

About the Mozscape APIs

For almost all of us, the URL metrics API is our everyday friend. The SEOmoz toolbar relies on the service, for example. We’ve been fetching URL metrics in Excel (with this hack) using simple http authentication for a while – but this use case could never exploit the power of the entire family.

API Name              Description

Open Site Explorer is a fantastic demonstration of the real power of the Mozscape API family. The OSE web application is making a call to the Links API, and in turn, the Links API responds with a list of linking URLs to a page or domain. The “scope” of the call can be set to parameters such as “page_to_page”, which would return a set of “source” pages linking to the specified “target” page. Data about the source of the link (“SourceCols=”), the target of the link (“TargetCols=”) and the link itself (“LinkCols=”) is also provided.

OSE performs a separate URL Metrics API call made to fetch the metrics for the target site, too:

The two remaining API calls, Anchor Text and Top Pages can also be seen in action on Open Site Explorer. Anchor Text will return a number of the terms (or phrases) aggregated across links to a page or domain. Top Pages returns a list of the “top pages” on a domain, historically one of the most exciting capabilities of the early Linkscape API and (as long time SEOmoz fans should know) my favourite SEO tool at the time.

Getting Data, Politely and Quickly

Getting to API data is made quite easy by most API developers. Put simply, data is requested via a URL, sometimes without authentication and returned in an ordinary http response. Take Sharedcount, for example:

Request URI: http://api.sharedcount.com/?url=http://seogadget.com/conversion-rate-optimisation/

Response:

While for now, it’s ok to request Mozscape URL metrics data in single API calls with simple http authentication, the approach taken in methods using SEO Tools for Excel simply don’t scale. I’ve spent many a moment feeling burdened with that “oh ^%$£!, I’ve crashed Excel…” feeling. Let’s also remember that the previous methods make single API calls – thousands of them, putting the Mozscape API under a lot of unnecessary load. It’s rude and inefficient.

If you want to make how you’re working with Mozscape data in Excel, it’s time to do it properly.

The Links API Extension for Excel

Our new extension solves all of the problems associated with fetching large data from Mozscape. No more lengthy CSV export waits; you can stay inside Excel and build impressive reports around the API output. The JSON output from the Mozscape API is parsed into rows and columns for you (as an array) and there are queries for each of the main API calls (URL Metrics, Top Pages, Anchor Text, and the incredible Links API).

Here’s a short video tour of the capabilities of our new extension:

Feature Time! SEOmoz Pro Members Can:

• Extract large volumes of link data from the Mozscape API
• Fetch anchor text links to your domain
• Identify the top pages on you domain (with Paid API)
• Capture URL metrics data for groups of URLs
• Plays nicely with the SEOmoz API
• Plays nicely with the Adwords API extension and SEO Tools for Excel

A Note on API Usage

Almost all of the features we’ve constructed will work with PRO Member API limits, though generally the number of results and rate at which you’re allowed to make requests is reduced unless you have a paid API account. Our tool respects the load limits by “chunking” batches of data into single API calls. Regardless of this fact, one of the best things you could do is get a higher rate API account. SEOgadget subscribes to the full API.

The Top Pages API only works with “low volume” paid API accounts or higher.

What are the Queries?

In this section we’ll work though a break down of each query, how arguments should be presented and show some example queries you can use to get started.

URL Metrics

=SEOMOZ_URLMetrics([URL],[BIT])

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.

Example: =SEOMOZ_URLMetrics_toFit(A1,"Cols=103079215109")

Will return: Title (ut), URL (uu), Page Authority (upa), Domain Authority (pda)

Key Areas to Understand to Make this Query Work

To get the most out of this query, there are really only two key points to understand. It’s much simpler than the others in that regard and is therefore a good place to start!

1) The “Cols=” argument uses bit flag values. You can calculate these from this URL Metrics bit flag list. If, for example you wanted to fetch the title (1), and url (4) from the URL Metrics API your bit flag value should be 5. This principle applies to every [BIT] argument required in each of the queries.

2) You can fetch data from a column / list of URLs by specifying the range, for example, if a column of URLs exists between A1 and A6, then A1:A6

3) It’s important to understand the response fields (for example: pda is Domain Authority). Learn the response fields via the response field columns in the URL Metrics bit flag list.

Anchor Text

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

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] eg: “page_to_page” returns a set of Source pages linking to the specified Target page – for a full list visit the Links API documentation.

The 3rd argument [SORT] eg: “page_authority” sort results by Page Authority – for a full list visit the Links API documentation.

The 4th argument [BIT] The bit flag to indicate which columns to return. Leave blank for this version of the release.

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] Send request in batches – for example, fetch 100 results requesting in batches of 10 URLs per API call – this is the friendly way to do  it and we recommend you experiment by requesting in batches of 10 or 25 results.

Example: =SEOMOZ_anchorTextAPI_toFit(A2,"phrase_to_domain","domains_linking_page",,100,)

Will return: Internal Subdomains Linking (appif), Term or Phrase (appt), Internal Pages Linking (appef), External Subdomains Linking (appimp), Internal mozRank passed (appimp), External Pages Linking (appeu), External mozRankPassed (appemp), ExternalRoot DomainsLinking (appep), External Subdomains Linking (appf).

Key Areas to Understand to Make this Query Work

To get the most out of this query you should take note of the following points:

1) It helps to have a low, medium or high volume API account. The first few rows are accessible via an ordinary Pro level account API, so you can still test this API out.

2) The Anchor Text API call introduces the “scope” argument. See the Anchor Text API call format for a full list of settings. You will most usually need to understand how people are linking to your domain (in which case; “phrase_to_domain”) or how people are linking to a particular page (in which case; “phrase_to_page”) would be optimal parameters.

Top Pages

=SEOMOZ_TopPages_toFit([URL],[BIT],[LIMIT],[CHUNK])

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] the bit flag to indicate which columns to return – refer to this list of bit fields http://bit.ly/Oe9C2F

The 3rd argument [LIMIT] how many results do we require? Paid API will allow more results!

The 4th argument [CHUNK] send request in batches – for example, fetch 100 results requesting in batches of 10 URLs per API call

Example: =SEOMOZ_TopPages_toFit(A2,"Cols=103616137253",100,20)

Will return linking title (ut), URL (uu), external links (ueid), links (uid), mozRank (umrp), mozRank raw (umrr), Subdomain mozRank (fmrp) + (fmrr), http status (us), Page Authority (upa) and Domain Authority (pda)

Key Areas to Understand to Make this Query Work

1) This API is only available if your API account is paid access – see the price list and request access to the Top Pages API in any pricing enquiry.

Links API

The mother of all Excel functions – if you can master this query, you’ll be able to build some seriously impressive Excel apps!

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

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] eg: “page_to_page” Returns a set of Source pages linking to the specified Target page – for a full list visit the Links API documentation.

The 3rd argument [SORT] eg: “page_authority” Sort results by Page Authority – for a full list visit the Sort section in the Links API documentation.

The 4th argument [FILTER] apply a filter to the call, eg: “external+follow” for external, followed links – for a full list visit the Filter section in the Links API documentation.

The 5th argument [TARGETCOLS] specifies data about the target of the link is included eg: “TargetCols=8” would give the linked to subdomain

The 6th argument [SOURCECOLS] specifies data about the source page of the link eg: “SourceCols=103079215109” would give OSE results!

The 7th argument [LINKCOLS] specifies data about the link itself, eg “LinkCols=8” would give normalised anchor text. For a full list visit the Link Metric Bit Flags  section in the Links API documentation.

The 8th argument [LIMIT] how many results do we require? Paid API will allow more results. 100 results should be possible in batches of 25

The 9th argument [CHUNK] send request in batches – for example, fetch 100 results requesting in batches of 25 URLs per API call

Example: =SEOMOZ_linksAPI_toFit(A1,"page_to_page","page_authority","external+follow",

"TargetCols=8","SourceCols=103079215109","LinkCols=8",100,25)

 

Key Areas to Understand to Make this Query Work

1) This API query introduces 3 new arguments: “TargetCols=”, “SourceCols=” and “LinkCols=” – understanding of these settings are critical to the operation of this query.

About LinkCols, SourceCols and TargetCols

Understanding LinkCols=, SourceCols= and TargetCols= is extremely important – but actually very simple. SourceCols= is a bit flag aggregate based on the URL Metrics request flags. Adding 1 (URL) to 4 (Title) would mean your SourceCols= request would be 5. The important number is 103079215109, which is all of the SEOmoz metrics available via the free API.

LinkCols are also bit flags, found in the “Link Metric bit flags” section in the documentation. The LinkCols request determines what information you’d like to receive about the link itself. I prefer to use “normalised anchor text”, bit flag 8.

TargetCols is a URL Metric bit flag to request information about the target URL (the linked-to page). I tend to request 8 – to confirm the linked to subdomain, but it’s really up to you. Have a play!

How to Install

1) Firstly, you’ll need to download the file:

DOWNLOAD THE MOZSCAPE API EXTENSION FOR EXCEL

2) Save the distribution folder on your desktop and open the setup.xlsx file. When it opens, click “Enable Content”:

3) Click “Add API Credentials” and enter your Access ID and SEOmoz Secret Key in the dialogue box. You can get your API credentials from http://www.seomoz.org/api.

4) Click “Install Addin” – when the process has successfully completed, you’ll get confirmation.

Enter Your Reports and Dashboards to Our New Application Gallery

If your *super* proud of what you’ve built with our platform, why not submit and share with the community? You’ll get your name up in lights, and of course, a link to your website.

Here’s where to submit your dashboards and reports:

http://seogadget.com/submit-app-gallery/

And here are a few I’ve made for you to try out:

     

Open Site Explorer for Excel                                       Anchor Text Tool for Excel

Thank you

Developing this tool has been a thoroughly enjoyable experience though extremely resource heavy process to all of those involved. I’d like to thank my team who have put up with me at my most wired while I’ve been preparing this for Mozcon, and to our very own Country Datasmith, Tom Gleeson who is always able to make my crazy ideas possible through his incredible development and Excel skills. This is such an incredibly powerful platform, and well worth learning thoroughly.

Get the Download:

This download is free for general use, if you make use of the tool we ask that where possible you provide attribution to SEOgadget for publishing the tool and SEOmoz under the normal terms of use of their API. A simple “Powered by Mozscape” & “Built with the SEOgadget API Extension for Mozscape” would be gratefully received.

Simply download and extract the zip file and follow the installation instructions above.

>>>>>>>  DOWNLOAD HERE  <<<<<<

Donations – to Charity, Please

Finally, if you make use of the tool and feel you’d like to contribute, I have set up a donations page for Starlight, a Children’s Charity here to brighten the lives of seriously and terminally ill children by granting their wishes and providing entertainment in hospitals and hospices throughout the UK.
Donate here: https://www.justgiving.com/SEOgadget

Image Credit: zteamie

Announcing: The Links API Extension for Excel for Mozscape, 5.0 out of 5 based on 2 ratings