How are they beating you? Quick SEO Analysis Tools – Part 1
At SEER we pride ourselves on our SEO process and with good reason. From audits, to content creation and linking strategies, our step-by-step process for helping our clients grow rankings, traffic and conversions has been tweaked, revised and refined by some very talented individuals – most of whom post to this blog.
However, as with any process, there are times when we need to step outside the proverbial box and alter our strategies for a particular client. Whether it’s because of their own site limitations, the competitiveness in the industry, or our perceived change in the algorithms we often need to adjust our efforts to achieve success.
In this post I am going to outline a (relatively) quick way to analyze the competition to help you determine if you need to adjust your process. This will be the first part in a series of posts that will feature simple checks you can perform to help isolate the areas where your competition has a leg-up, and where you need to focus.
Disclaimer The following analysis uses Open Site Explorer, and is therefore limited in two ways.
First, you will need to be registered as a PRO member to perform the full export of 10k links. If you do not have a PRO account, you will be limited to up to 1k links.
Secondly, this export will be limited to 10k links. So if you are analyzing a competitor who has a significant number of links, please keep in mind that the analysis performed below will only apply to the 10k most valuable links, as determined by OSE.
Ok, now that the disclaimers are out of the way we can dive into the analysis. Given that this is the first tip in the series, I wanted to make sure that it provided significant value which is why we’ll be looking at how your competitors are (or are not) beating you with anchor text targeted links.
Hypothetical – Let’s say you were a search agency located in the great city of Philadelphia naturally you’d probably want to rank for a term like “philadelphia seo company” right? Maybe you’d also consider going after a much broader term like “seo company” too. Now let’s also assume that you are new to the space, and aren’t quite sure what your competitors are doing. One of the first things we do here at SEER is to analyze how the top 10 SERP competitors are performing with targeted anchor text links and here is one way to do it.
First, run a search for your targeted KW for this example I will use “seo company”.
Next, catalog the top 10 SERPs and run each of them in Open Site Explorer. Be sure that you are only exporting followed links, from external sites. If you are out of the top 10, don’t forget to run your site as an additional export.
Once you have the data, the next step will be to copy all of the sheets into one workbook to allow you to view the data together. This is probably the most time consuming part of the process, but it is a good way to keep your research organized especially if you plan on running this type of analysis for multiple KWs where the SERPs vary. I organize the different sheets by naming them in order of appearance in the SERPs.
Now that you have all ten (or eleven) data exports in one workbook, your next step will be to calculate the number of exact and phrase match links that each site has. To do this, simply create a summary worksheet at the beginning of the workbook and paste the following functions to find the number of exact and phrase-match KWs your competitors have. (Note: The figures calculated will only represent the matching links in the first 1k or 10k links, depending on your access level in OSE).
Exact: =COUNTIF(‘SheetName’!C:C, “keyword”)
Phrase: =COUNTIF(‘SheetName’!C:C, “*keyword*”)
Next, I recommend that you plot the results in a graph to give you a good visual representation of where you stand in comparison. This will help you determine if this is an area where you are particularly strong or weak, and it can also help you understand the relationship between exact and phrase match anchor text, and how these impact rankings.
and just like that you have gained significant insight into your competition, and more importantly, determined if anchor text is an area where you are leading (less project time), on par (falls within “standard process”), or below average (need to adjust process!). This type of understanding will help you develop the right approach for each client or site you are working on and will hopefully give you some insight into the impact anchor links have on rankings (amongst hundreds of other factors). This was the first time I’ve looked at the data for ‘seo company’ – and already it looks like if you do not have the KW in your URL, targeted anchor text is likely a key factor for ranking (www.promodo.com looks like an outlier in this set). Time to get at it I suppose :)
To make things a bit easier I have uploaded a sample excel document that will calculate all of this for you! All you need to do is swap out the data for your keyword and be sure to take the data from OSE and paste it correctly in the appropriate sheets the numbers and graph will update automatically!
To download the excel file, please follow this link.
Note: You can also combine this spreadsheet with the link profile sheet posted on the SEOMoz blog. This sheet takes the same OSE data and analyzes the quality of inbound links based on Page Authority – giving you you two powerful data sets to play with.
Another note: While we now tap into the SEOMoz API and look at this data programmatically (huuuge thanks to @andrewburke86) this type of excel work proved to be invaluable when analyzing the competitive landscape and our project plan. In the next posts I will outline more ways to manipulate this data to drive strategy.
If you have any questions, excel tips, or another way to pull this info feel free to drop a line in the comments section below!
Posted: 08.12.10
SEO Doctor:
Great post, gonna have to give that a go…the graph is pretty impresssive. I just wish you could do this straight from Linkscape and skip Excel all together!
anthonydnelson:
Thank-you for this post and download. This looks incredibly useful and I can’t wait to dig-in to some numbers.
Marc Levy:
Great post! We do something similar here in-house, although I maybe optimising it a little based on this post and the excel provided ;)
As well as exact match anchor information we also place additional information into our spreadsheet. Domain Age, PR, DmR, mR, Total inbound links to domain and ranking page etc. This sometimes allows us to spot trends, but more often than not just the area where each competitor is potentially beating us.
I think the team have even added the title tag of the page and a on-page optimisation score to the process. Adding all this obviously takes more time and your post is about ‘quick analysis’. Anyway its worth it! Not only can it help steer your campaign but it also looks mightily impressive to clients.
Great stuff!
Lani Kee:
I stumbled onto your website on youtube. Great information on youtube and obviously here on your website. I will definitely, check out your site and see if there is more valuable tips you got for me. Mahalo, Lani :)
Jesper Nielsen:
Thanks for the read. I have been using Open Site Explorer for some time now, but not in this way. You methods for analyzing sites are much more sophisticated than what I have done. So far that is. I will use your methods (or something similar) from now on. Looking forward to reading part 2.
BRLM the Costa Rica SEO:
Hey Mark,
Killer post buddy. This says Part 1. When can we expect to see a Part II etc.
BRLM
BRLM the Costa Rica SEO:
Hey Mark,
We’ve done this for one our clients and we found that we were crushing the top 5 SERPS for two of our clients most important keywords in Exact and Phrase match links. Our client is floating at 9 and 14 for each target keyword.
We did a quick look at SEOMOZ’s keyword difficulty tool and found that at least the top 2 SERPs for these 2 keywords had 2 – 3 times more total linking root domains. Could this explain our stagnation in the SERPS?
Best,
BRLM
BRLM the Costa Rica SEO:
Hey Mark,
Any update on a part dos?
Mark:
@Costa Rica Glad you asked – been working on a follow-up, which I hope to post up shortly!
BRLM the Costa Rica SEO:
thanks mark! amp’d to see it.
Rodney:
I seriously couldn’t wait to show this spreadsheet to one of my clients today.. So cool! I used this for 3 websites I work on and counting so far (read it about 12 hours ago). You guys rock. Cant wait for part 2 and more super actionable posts like this. thanks!
Portland SEO:
Hey thanks for sharing. This is a great information regarding SEO and the competition b/w sites beating eachother to get on the top.
Mark:
@ Rodney Awesome – glad we could help! Part 2 turned out a bit different than I intended, but you can check it out over here http://bit.ly/gab8q1
@ Portland – glad you found it helpful!
Honolulu SEO:
Is there any reason this excel doc would not work on mac? I’m able to get the ‘link profile sheet’ from SEOmoz you refer to at the end of the post to work, fine, but not this one?
Anyone try on Mac 2011? Tips, please …