08 May 2011

By - 20 Comments

SEOmoz Linkscape API is an extremely powerful tool for SEOs, but SEOs often do not come from a development background, and might find the API inaccessible to them. Whilst I believe SEOs should learn to code somewhat, I know not everyone is from that school of thought. If you are unfamiliar with the API, then you can see the SEOmoz API wiki; we’re going to be looking at the URL Metrics API.

Luckily, a recent blog post from Ian Lurie details using Linkscape API with Google Spreadsheets. Ian’s post is great, and probably does exactly what you are looking for. What I post here is a modified version the code from his post, which I used to form the basis of a competitive analysis tool detailed in my recent SEOmoz post: Competitive Analysis in under 60 Seconds using Google Docs.

You can see the code in action in my example spreadsheet (you’ll need to copy if and add your SEOmoz API key), or take a peek here:

  1. var AccessID;
  2. var secret;
  3.  
  4. function readLinkscapeConfig() {
  5.  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  6.  var config_sheet = active_spreadsheet.getSheetByName("Config");
  7.  params = config_sheet.getRange(1,2,2,2).getValues();
  8.  AccessID = params[0][0];
  9.  secret = params[1][0];
  10. }
  11.  
  12. function generateExpiry() {
  13.  method = "HMAC_SHA_1";
  14.  uDate = new Date().getTime();
  15.  uDate = Math.round(uDate/1000);
  16.  Expires = uDate + 1200;
  17.  
  18.  return Expires;
  19. }
  20.  
  21. function generateSignature(Expires) {
  22.  theString = AccessID + "\n" + Expires;
  23.  signature = Utilities.computeHmacSignature(Utilities.MacAlgorithm.HMAC_SHA_1,theString,secret);
  24.  signature64 = Utilities.base64Encode(signature);
  25.  signature64 = encodeURIComponent(signature64);
  26.  
  27.  return signature64;
  28. }
  29.  
  30. function getLinkscapeData(url, dummy) {
  31.  readLinkscapeConfig();
  32.  
  33.  Expires = generateExpiry();
  34.  signature64 = generateSignature(Expires);
  35.  inV="http://lsapi.seomoz.com/linkscape/url-metrics/" + url + "?AccessID=" + AccessID + "&Expires=" + Expires + "&Signature=" + signature64 + "&Cols=85899378688";
  36.  jsonStringResponse = UrlFetchApp.fetch(inV);
  37.  
  38.  var data1 = jsonStringResponse.getContentText();
  39.  data2 = Utilities.jsonParse(data1);
  40.  IDomains = data2["fipl"];
  41.  Dauth = data2["pda"];
  42.  mzRnk = data2["fmrp"];
  43.  
  44.  
  45.  var returnData = new Array(3);
  46.  returnData[0] = mzRnk.toFixed(2);
  47.  returnData[1] = Dauth.toFixed(2);;
  48.  returnData[2] = IDomains;
  49.  
  50.  return returnData;
  51. }

In Google Spreadsheets, you can select “Make a copy” in the File menu to create your own private copy of the spreadsheet to work on. You’ll need to add your SEOmoz API Access ID and secret key (from SEOmoz API page) to the ‘Config’ sheet. You can select Tools -> Script Editor in order to see and edit the code to your own purposes, if you’d like to return a different combination of metrics.

Editing the Code

I return 3 pieces of information about a URL: mozRank, Domain Authority, and linking root domains to the subdomain of the URL.
Results from Linkscape API pulled into Google Docs

However, you can change that my modifying this section of the code:

  1.  var data1 = jsonStringResponse.getContentText();
  2.  data2 = Utilities.jsonParse(data1);
  3.  IDomains = data2["fipl"];
  4.  Dauth = data2["pda"];
  5.  mzRnk = data2["fmrp"];

The codes fipl, pda, and fmrp refer to pieces of information the API is able to give you; you can find a full list of them on the URL Metrics API wiki page. Be sure to check the right hand column to see if they are included in the free API if that is all you have access to. You can edit the above code to include those you want, but that isn’t the end of the story, you also need to edit a couple of other pieces:

  1.  inV="http://lsapi.seomoz.com/linkscape/url-metrics/" + url + "?AccessID=" + AccessID + "&Expires=" + Expires + "&Signature=" + signature64 + "&Cols=85899378688";

The number 85899378688 is a mask which tells the API which columns to passback (you could always just ask for them all, but that puts more load on the API servers, and isn’t polite!). It is based around how computers store numbers, but luckily all you need to know is that you select which metrics you want from the API and then URL Metrics API page you add up the corresponding rows in the “Bit Flags” column. For example we used fipl (17179869184), pda (68719476736), and fmrp (32768), so we add 17179869184 + 68719476736 + 32768 = 85899378688. Easy when you know how!

Lastly, you need to make the code pass back the fields you’ve selected, so need to update:

  1.  var returnData = new Array(3);
  2.  returnData[0] = mzRnk.toFixed(2);
  3.  returnData[1] = Dauth.toFixed(2);;
  4.  returnData[2] = IDomains;

to the fields you created above. The ‘toFixed’ function rounds off numbers to the second decimal place, which will make things much neater and tidier for you.

Using the Code in a Tool

Hopefully, this will have given you some insight into how to go about using the Linkscape API and modifying it your needs. I used this to create a tool for Competitive Analysis which you can read about in my SEOmoz post: Competitive Analysis in under 60 Seconds using Google Docs.

Please post a comment to let me know if you are using this and what awesome tools you’ve come up with. :)

UPDATE: Chris Le over at SEER Interactive has just released a post with an updated version of this code, which accounts for the new rate limits on the Moz API. I recommend you use his version. Thanks Chris!

20 comments on “Using SEOmoz’s Linkscape API with Google Docs
  1. Competitive Analysis in Under 60 Seconds Using Google Docs : india sem

    [...] and some pointers on how to modify it to suit your needs I’ve put up a separate post on Using the Linkscape API with Google Docs, which includes a simpler example spreadsheet to try the code out [...]

  2. Maelle

    This is fantastic explanation, thank you very much!

    I am having some issue however: the mozRank values are all returning 0.00 and the DA values 1.00 … There doesn’t seem to be any problem with callig the API, but the values are obviosly wrong… Do you have any idea why this is happening?
    Sorry if I am asking about something blindingly obvious :-s

    Thanks again for this tutorial, it made a whole bunch of things a lot clearer!

  3. Maelle

    I have figured it out, sorry…

    I had “http://www.(…)” URLs instead of “www.(…)” ones.

    Duh.

  4. Tom

    Hi Maelle,

    I’m glad you got it worked out. Let me know if you have any other problems!

    -Tom

  5. jason

    Hi Tom,

    I’ve been using the Google docs competitor analysis that you shared on SEOmoz not long ago and it was working great, but then i went to use it today and no data is showing. Could this be a problem with the API? or is this working fine for you?

    Thanks

  6. Tom

    Hi Jason,

    I’ve been having problems, and others have too I think. What is happening is that now lots of people are using these tools and all of them are hitting Google from the same IP addresses (those belonging to the Google Docs) – so some of the time the Google Search servers are blocking the Google Docs servers because they can see they are being used for automated requests.

    Unfortunately, there isn’t much we can do about this at the moment, but I am looking into solutions and will post anything I find. I have a couple of ideas to look into.

    • Mr PB

      Did you manage to find a solutions? It randomly works or doesn’t work for me!

  7. jason

    Great – thanks for your reply Tom (and good luck).

  8. jayson

    Im wondering why linking root domains column does not show any details? :)

    • Mike Belasco

      Jayson,
      I am having the same issue. Did you figure it out?

      Thanks
      Mike

  9. Jason Hotham

    Link Root domains is not working for me either. Is there an update needed for the script? Also could the metrics be expanded to include Page Authority?
    Thanks
    Jason

  10. Bruce

    Since december this is no longer working that good for me. I used to be able to check 300-500 URL’s with no problems, today I can probably check 15 links and then need to wait 30 seconds. Getting error 503 throttled.

    Working with big lists (1000+) this is almost impossible.

    I wish to find an alternative to this.
    Is it somehow possible to run it somewhere else than on Google docs?
    Or maybe queue the script to check 1 line every 2 seconds (like ToS says we SHOULD use it)

    I really need an answer on this one!

  11. Jerry

    Love the article! But wondering if anybody has found any solutions for working with large lists. (i.e. Bruce’s suggestion to “maybe queue the script to check 1 line every 2 seconds”)

  12. Ari

    I think this has to do with the new throttling requirements. I built a tool that uses the free API and basically just added a delay between API calls for 2 seconds between each one. Now it works fine (albeit much slower).

    Please release a new script!

  13. Tom

    Hi everyone,

    I’ve just updated the post with a link to an update that Chris Le over at SEER Interactive has put together that deals with the new rate limit requirements. :)

  14. Eacsoft-Web design

    Hey guys Noob here just wondering about adding the key. I’m getting missing ; before statement ?

  15. Joe Robison

    Hey Tom,

    Any advice on where to start if I want to learn how this API is working, line by line, so I can implement similar things with other APIs (such as Raven Tools)? Thanks!

  16. The Ultimate SEO Timesaver: SEOmoz Linkscape API and Google Docs | Search Engine People | Toronto

    [...] then input the following script with only a few changes from the one that Tom Anthony graciously provided on his [...]

  17. SEOmoz Data for Google Docs | SEER Interactive

    [...] recently updated Tom Anthony‘s original tool to get SEOmoz data into Google Docs. My update includes a simpler setup and [...]

  18. Rank Checking with Google Docs ImportXML Function | Christopher Yee

    […] using Google Docs for SEO by Richard Baxter. Tom Critchlow followed suit a couple months later but Tom Anthony’s post illustrated how powerful it can be for SEO when you used it in conjunction with the SEOmoz […]

Leave A Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>