SERP Saturation Report Spreadsheet: The story of how Stephen's widely-used recently-broken tool has been given new life.

Back in May of 2011, SEOMoz user "Stephen" shared a guide for SEO’s who hate link building. This concept sparked a great deal of interest and, just a few days later, Tom Anthony shared a Google Docs-based tool that made many of these processes quick and effortless.  For a year or so, many SEOs found out about this tool and it became widely used and relied upon.

Then, the tool broke...

I’m not sure why it broke or what happened to cause the breakage but it’s obviously been noticed and many people are asking for someone to fix it.  I’ve spent some time digging into the code and I think I’ve found the errors and revived the document.  This article details those fixes.

The Document

For those of you who are impatient, here’s the sharable Google Docs link to the SERP Saturation Report Spreadsheet. Make sure you're logged into your Google account, open this link, click FILE >> Make a copy... and that will make a copy of the doc for you.  Go to the "Config" tab on the spreadsheet, put in your SEOMoz Access ID and SEOMoz Secret Key, and start plugging in keywords in the "Keywords" section.  Be sure to keep all of your keywords on the "Config" sheet and limit it to 50.  Otherwise, things break.  If you do not have an SEOMoz Access ID and Secret Key, you can sign up to get a free one.

The Fix

If you're curious about how I went about fixing the document, read on.  Before we dive in, let me go on the record and say that I'm not a developer or a code jockey.  I have very little formal training in computer programming so, if you see mistakes in what I have done, kindly point them out and we'll work on getting them fixed.  This also means that I'm going to use completely incorrect names for certain things.  Please don’t crucify me.

All of the issues I found with this document were code issues.  Therefore, you have to dig in to the Google Script to fix it.  Luckily, Google Script is just basically Java Script with some additional modules and functions available to it...and Java Script is very well documented across the internet.  To view this code, open the spreadsheet and click on TOOLS >> Script Editor... This is not necessary.  It's just in case you want to take a look under the hood while I'm explaining.

The First Problem

The first problem that I found was that the script was looking in the wrong location on the spreadsheet for the SEOMoz Access ID and Secret Key.  Therefore, when the spreadsheet made a request to the SEOMoz server, the request was being rejected because the spreadsheet was not identifying itself correctly.

The original code looked like this:

params = config_sheet.getRange(3,2,4,2).getValues();

The “getRange” command is requesting all of the data in cells B3 through D7—which, if we look at the original spreadsheet, is not at all the data that we need.

I changed the line of code to be:

params = config_sheet.getRange(“B5:B6”).getValues();

In this way, we are instructing “getRange” to look only in cells B5 and B6 to pick up only the very specific data that we need to populate the API keys variables and successfully authenticate to the server.

The “Free vs. Paid” Problem

SEOMoz offers two different levels of access to their API.  First is the free version which is great for most of us and which is what I imagine most people are using.  They also offer a paid version which offers some additional bits of data not available in the free version.  Additionally, the paid version allows a person to request data much more quickly from the SEOMoz server.  This difference between the free and paid versions actually necessitates two different changes to the code.

Since this is all based on the free version of the API, the first item to tackle was the issue of which pieces of data were being requested from SEOMoz.  There are two adjustments to the code necessary for this.  First, the “&Cols=” request needs to be changed in the line of code that reads:

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

The “&Cols=” value of 85899378688 represented some variables to which we, as free users, do not have access.  The correct value should be 103079247904 therefore making the line of code read:

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

The exacting details of this change are long and complicated and you really need to understand the API itself to understand why this change is necessary.  In very simple terms, though, this “&Cols=” portion of the request defines very specifically what data you wish to receive when you make your request to the SEOMoz servers.  For a full run-down on why and how that number makes a difference, please look at the SEOMoz API documentation.

Additionally, because we modified the data that we’re asking for, we need to modify the way we parse the data that’s returned.  The original version of the spreadsheet was looking for and expecting to be fed data with the names fipl, pda, and fmrp.  While pda and fmrp exist in both the free and paid version, the fipl is a data name that is delivered only from the paid version of the API.  So even though we’ve changed what we’re asking for, we need to make sure to also change what we’re listening for.  The correct names we should be looking for are ueid, upa, pda, and fmrp.  These are now read in to their own variables so that we can deliver them to the front end of the spreadsheet.

The second “free vs. paid” challenge is the speed at which we can request data.  “Free” users can only make one request for data every ten seconds.  Therefore, we have to find a way to slow down these requests or we will get denied very often.

To understand how to slow down the requests, we need to understand what the spreadsheet is doing and how it requests data.  When you refresh the “Report” tab on the spreadsheet, a great number of calls are made to Google which returns data, that data is processed, and the top ten results are placed onto the “Reports” sheet.  At that point, the spreadsheet simultaneously requests SEOMoz data for all ten of those results.  So every time the sheet refreshes, we’re sending ten requests at the same time to SEOMoz.  That’s a lot more than one request every ten seconds.

To try and mitigate this, I have added the following two lines of code.

sleepyTime = (Math.floor(Math.random() * 4000) + 1000);
Utilities.sleep(sleepyTime);

The first line generates a random number between 1000 and 5000 and stores it in a variable called sleepyTime.  The second line reads that sleepyTime variable and then pauses for that specified number of milliseconds.  This effectively makes the function wait for somewhere between one and five seconds before proceeding.

“Why,” you may ask, “do you pause the function for a random time?  Couldn’t you just instruct the function to pause for five seconds and then execute the next command?”  Yes we could but it wouldn’t solve our problem.  Remember that the spreadsheet calls this function simultaneously ten different times.  If we made it pause for a fixed amount of time, we would still be making ten simultaneous calls—it would just be five seconds after you refresh the spreadsheet instead of immediately upon refresh.  Introducing a random wait time causes the requests to stagger themselves so they’re not all sent at once.  Make sense?

Other Changes

During testing, I ran across a few minor things on the front end of the spreadsheet that just didn’t look right or didn’t lay out like I imagined would be optimal.  I made changes mostly to the “Reports” sheet and there might be other changes on the other sheets as well—I don’t recall perfectly.

The last thing I did was to add comments to the Google Script code.  I figure that, if this is going to be a community effort to maintain this tool, we should all be able to read the code and know which piece does what and why it exists.  These comments do not impact the operation of the spreadsheet in any way.  They just allow developers who follow me to understand the code more easily.

Future Plans

I’d like to see this spreadsheet continue to grow and expand.  I think it’s a valuable tool for many SEOs and we can certainly improve the functionality for the benefit of all.  These are some of my ideas for what might be nice.

• Sequential Versus Parallel API Requests:  It would be nice to be able to only make one request of the API at one time.  In doing this, we alleviate the problem of my janky “random wait time” fix and make a more elegant solution.
• Paid Version & Free Version in the Same Document:  I understand that some people are using the paid version of the SEOMoz API.  It would be nice for this document to accommodate both the paid version and the free version at the same time.  This would introduce a little bit of additional complexity but I think it would be manageable.
• Further Cleanup of the Report Sheet:  Somehow I still feel like the Report spreadsheet can use some more cleanup.  I’m not certain what it needs but it still appears less than optimal to me.  Perhaps smaller font sizes, more compact layout, and more concise display is called for.
• User-Configurable sleepyTime:  The variable sleepyTime, as we’ve discussed, controls the random pause which allows us to make several calls at once to the SEOMoz API without receiving errors in return.  I suspect that, depending upon server load, the API may allow you to get away with a little bit faster or force you into a little bit slower wait times.  It would be nice to have this be quickly configurable to accommodate these changes.
• Individual Refresh for Single Lines in the Report:  No matter how much we try to be careful with our random wait times, we still occasionally get an error on one or two lines in the Report spreadsheet.  I would like to see a “refresh” button that works on only one line in the Report.  So, instead of refreshing the entire sheet at once, you would be able to refresh only one line.

If you have your own ideas, let’s hear them in the comments.  Thanks for taking the time to read this article.  Let me know if you have questions or suggestions for improvement.

Once again, here's a link to the SERP Saturation Report Spreadsheet.

• Tweet
• "&title=""">

About Jerod Karam

Jerod Karam is Director of Online Marketing at Netvantage Marketing, an online marketing company specializing in SEO, PPC and social media. Jerod directs a large portion of the organic SEO efforts for Netvantage clients and is responsible for most of the technical work and custom online tools the company uses.

• More Posts (3)

Tags: analytics, api, google, google docs, google search results, lansing seo, link building, link building tips, link building tools, saturation report, search engine optimization, SEO, serp, spreadsheet, teaching seo

This entry was posted on Wednesday, August 15th, 2012 at 2:41 pm and is filed under General, SEO, Web Analytics. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.