Posted by Tom Anthony

This post was originally in YOUmoz, and was promoted to the main blog because it provides great value and interest to our community. The author’s views are entirely his or her own and may not reflect the views of SEOmoz, Inc.

Faced with a new client, and having established a list of keywords they need to target, you want to evaluate the competition to find out what sites are dominating the SERPs for these keywords. However… being an SEO you’re a busy guy (or gal), and you need it done right now. I’ve built a Google Docs tool to automagically do exactly that and this post will walk you through it.

The basis for this tool comes from a report in this linkbuilding post on YOUmoz which contained a neat little ‘SERP Saturation’ report. I don’t know how Stephen made his snazzy looking report (he’s now shared a few details in this comment), but in response to a few people asking about his I thought I’d put together a tool. Here is Stephen’s report:

Cool, eh? We are going to produce something very similar, albeit not as pretty. We will automatically pull ranking data and tie into the Linkscape API to pull in some helpful metrics.

1. What does the report show?

So, what’s the report all about? It is a pretty standard report, and most SEOs will have put together similar reports in their time. It shows which domains are dominating the results pages for the specified list of keywords. It is an excellent way to quickly see who the main players are, and see a few metrics for them.

Ours will be sorted by the cumulative number of times a subdomain has appeared in the top 10 of the search results over all the keywords we specify, and will display the mozRank, Domain Authority and Linking Root Domains for each. We’ll show just the top 10 competitors in our report.

You can just duplicate the Google Docs spreadsheet I provide below, and change almost any of this to add, modify or take away as per your needs.

2. How do you configure it?

You must configure it the first time you use it:

1) If you’ve not yet done so, get a SEOmoz API key. Its free!

2) Open the Google Docs spreadsheet. In File menu select ‘Make a copy’ so you have a version you can edit (call it “Report Template” or such).

3) Go to the ‘Config’ sheet at the bottom, and enter your SEOmoz API details.

4) If you’d like to change the template for which Google URL to do (it defaults to UK for me), you can do that here too.

3. How do you use it?

Open your report template spreadsheet you just made.

1) On the config tab, paste up to 50 keywords, one per row, starting at cell B7 (its indicated).

2) Open the ‘Report’ sheet.

3) Now select ‘Make a copy’ and give it a name (“Client X Report” or whatever). This  step is *essential* or the fields will not update properly (I’m working on making this not necessary – any clues?).

4. What should you see?

 You should see a snazzy little report:

It shows everything I promised, and more even:

A colourful and interactive, albeit it slightly wonky, graph! What more could you want?!

5. Under the hood

You don’t need to read this section if you are neither interested in how it works or need to edit it at all. Besides which, I’m mostly just going to refer you elsewhere! A big shout out to Tom Critchlow, whose prior work contributed heavily to this little tool. Firstly, you need to read:

How To Build Agile SEO Tools Using Google Spreadsheets

Which introduces how to scrape the SERPs for ranking data. I modified what Tom did slightly as I wanted a list of subdomains, rather than pages, so there is a bit of string cropping (and fudging!).

Next you need to read Ian Lurie’s post (which Tom also helped with):

Linkscape + Google Spreadsheets. Together, at last.

Again, this I also edited. I changed the code around quite a bit, which you can see in the script editor. You end up with a function you can enter into a cell:

=getLinkscapeData(A1, 1)

The A1 is a cell reference to a URL, and the 1 is a dummy parameter to prevent annoying caching issues.

For a look at the full code for the Linkscape API interface, 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 with.

The rest of the spreadsheet is a few simple bits to filter and cumulate the necessary bits and pieces, along with a few tricks to try to sidestep some bugs in Google Apps. Nothing in the sheet is protected (there are a fed hidden columns) so you can take a look at the workings. If you have specific questions, post them in the comments and I’ll try my best to answer.

This was my first real foray into Google Docs, so it might not be particularly elegant. Also the document seems to have trouble updating sometimes – if anyone has a solution that would be great. In the meantime, if you just ‘Make a copy’ it seems to force an update.

6. Wrap up

Ok, it isn’t in depth analysis, but if you have a keyword list, and want a very quick peek at what domains are players, and their general stats, this tool gives you a quick and dirty look. Most importantly – it is free and open, so you can tweak it to your hearts content.

Questions, comments or suggestions are very welcome – post below and I’ll get back to you.

Do you like this post? Yes No

Posted by adamf

First of all, thanks to everyone who played along with our little April Fool’s prank a couple of weeks back. I apologize if we made our fake Future Rank tool seem a little too real. As penance, I thought I would offer a sneak peek at an actual upcoming feature that is still in the advanced planning stages.

So, What Is It?

The SERP Analysis report is a deep and detailed report that shows the top ten ranking URLs for a particular keyword, retrieves a breadth of potential ranking signals, and presents them in a format that is easy to analyze at a glance. The tool is meant to help SEOs better compare and analyze the factors that may lie behind why particular sites rank in the top ten for certain keywords.

The selection of metrics and format for this report is largely based on the SERPs analysis worksheets that we shared as part of Rand’s December post 6 New Tools Every SEO Should Check Out.

Which Metrics Do We Plan to Include?

At the moment, the plan for V1 is to include the following metrics:

• URL Link Metrics
  • Page Authority
  • mozRank
  • mozTrust
  • mozTrust / mozRank (Low scores can help identify spammy sites)
  • Total Links
  • Internal Links
  • External Links
  • Followed Links
  • Nofollowed Links
  • Linking Root domains
• On-Page Optimization
  • On-Page Analysis Grade (Grade returned after running and on-page keyword analysis report)
  • Broad Keyword Usage In Title
  • Broad Keyword Usage in Document
  • Keyword Used in URL
  • LDA Score (future)
• Domain Name Keyword Match
  • Exact Match Keyword In Domain
  • Broad Match Keyword In Domain
• URL Anchor Text Metrics
  • Links with Exact Anchor Text Match
  • % of Links with Exact Anchor Text Match
  • Linking Root Domains with Exact Anchor Text Match
  • % of Linking Root Domains with Exact Anchor Text Match
  • Links with Partial Anchor Text Match
  • % of Links with Partial Anchor Text Match
  • Linking Root Domains with Partial Anchor Text Match
  • %Linking Root Domains with Partial Anchor Text Match
• Root Domain-Wide Metrics
  • Domain Authority
  • Domain mozRank
  • Domain mozTrust
  • Domain mozTrust / Domain mozRank
  • External Links to the Domain
  • Linking Root Domains Linking to this Domain
• Social Signals
  • Tweets
  • FaceBook Shares
  • Blog Links (Future)

What Will It Look Like?

Well, this isn’t exactly what the full user interface will look like, but this is the format we plan to use within the report. I’ve included a tiny version of the full report at the top (with a link to the complete spreadsheet), and broken it down below. (Note: the data is old, and a bit munged, but accurate enough to offer a flavor for what we plan to build.)

Warning: you may have to pull out your reading glasses to see this onscreen. (I recommend hitting ctrl-+ in your browser window a couple of times.)

First here is the full metrics sheet:

(Ok, I don’t actually expect you to be able to read this, you can download the full spreadsheet here.)

Here is a slightly larger breakdown with a few observations added:

While the metrics for a single section often won’t answer the question “why is that ranking first?”, looking holistically at all of them will do that most of the time. For example, I was looking at the ninth-ranking result, noting that it seemed to dominate most of the link metrics, and wondered why it ranked below its competitors. Looking further to on-page metrics, it was easy to see that this page has not optimized for the keyword, which is probably why it falls where it does (and why it might improve if the site owner decided to focus on the keyword on-page).

Showing Summary Results

Since it will likely take some time to generate a full report, we plan to also show a summary of data as a quick view. The version we have planned at the moment includes the following elements:

• Page Authority
• Total Links
• Linking Root Domains
• Domain Authority
• Percent of Linking Root Domains with Exact Match Anchor Text

It would look something like this:

This does not include all of the metrics that I would want to include, like on-page score or social metrics, but these should all be retrievable real-time and provide some high-level value before the full report is run.

Why Build a Tool To Generate These Reports?

I’ve heard from many SEOs that one of their more time-consuming tasks is generating complex spreadsheets to analyze why certain sites are ranking well for specific SERPs and to discover opportunities to improve a target site’s ranking. For many, this has proven to be a tedious process of data gathering from multiple sources followed by lots of data entry. Our goal is to take the manual work out of creating these reports and provide that data in an easily consumable way.

Why Are Certain Metrics Missing from This Report?

I imagine that while you were looking through these tables, you noted some important metrics that are absent. Some of these metrics are not ours to provide, like Page Rank. Others are just not ready or available for mass consumption yet, such as LDA score.

Where Will These Reports Live?

We plan to offer this report, both as part of the Keyword Difficulty tool and as a replacement for the basic SERP analysis on the Ranking History Reports in your campaigns.

What Can I Do To Make This Feature Better?

Glad you asked! I’m certain that there are many other great metrics that we could or should include. So if you see something that is totally missing, wonder why in the world we included a certain data point, or think that representing anchor metrics in purple is ridiculous, let us know. We’d love to hear your thoughts! 

Do you like this post? Yes No