SEO Competitive Analysis to Go

In some recent client pitches I’ve begun using an excel chart to visualise the client website’s competitive landscape in organic search, making it clear at a glance what type of competition the website is facing if it wants to dominate relevant search results.

This is the type of chart I show as part of our pitch presentation:

seo chart

I’ve found this to be quite useful, as it serves as a starting point for an honest and open discussion about what the client wants to achieve with SEO and how we can best approach the project.

Building Your Own Table

Creating this table is pretty easy and won’t take a lot of time, which makes it an interesting exercise prior to a pitch. In addition to wowing clients, it’s also actually useful as a competitive overview and will help you determine the level of SEO activity that is required to make your project a success.

First off, I use serpIQ and do a number of competition analyses on keywords that apply to the client website’s industry. I usually do about 10-15 keywords and mix up big head and long tail keywords.

analyze new KW

The competitive analysis reports that serpIQ generates is an analysis of the top 10 ranked websites for the given keyword, and provides a competition index number for each.

Gathering Metrics

This competition index is a benchmark figure based on a number of factors, including the domain age and whether or not it’s an exact-match domain, the on-page optimisation and PageRank of the ranked webpage, the site’s Alexa rank, and several other factors.

competition index

The competition index is not intended as a full-proof competitive metric, but in my experience it does give a good indication of the strength of the ranked page for that particular keyword.

These competitive analyses will often feature a very similar set of websites that dominate the industry. My rule of thumb is that if a website features on two or more analysed keywords, I include it in my sheet. I copy the website URL and include the competition index number.

Next up, I take this list of domains and throw them in to Majestic SEO‘s bulk backlinks checker. This gives me an instant overview of the number and quality of backlinks of these competing websites:

backlink checker

I export this to CSV and take a number of key metrics to put in my competitive overview: AC Rank, CitationFlow, TrustFlow, External Backlinks, and Referring Domains.

Now I have all the relevant data, and what remains is to apply a number of conditional formats on the data cells in Excel to give the sheet the appropriate colours:

adding colours

And for the AC Rank column I use an icon set, to give a bit of variation to the sheet. I also highlight the client site’s metrics in this sheet (usually by bolding that particular row).

SEO at a Glance

The end result is a competitive overview that conveys the position of a client website among its competitors at a single glance. In the case of this specific client, it shows that the client site can move up to the middle tiers relatively quickly, but displacing the top sites is going to take a long-term SEO project.

complete chart

It also shows that the top ranked site in this particular industry might at some stage be hit by a Google penalty, as it seems to rely rather heavily on a few site-wide links. (In fact I was surprised the site ranked at all in this post-Penguin world, considering its very one-dimensional link profile.)

All combined this competitive analysis will take you around 15-20 minutes to do, which makes it perfectly feasible to perform as part of a new client pitch. It will both inform your SEO discussions and impress your potential client.

One comment

  • Neat document. 8)

    I do a similar one for ORM showing above the fold, below the fold.. (still valid metric), also showing negative/neutral url movement for negative sentiment, also “cleans”, where the url has been removed from the “top ten” (8-12 range actually across Google and Bing).

    The clients don’t really want to see the details, they like pie charts for each month and historical pivot tables for month to month vs baseline goal achievement.

    So what kind of output can you get out of spreadsheet? We only care that the details are correct in the spreadsheet.. the client just needs to know the facts.. =)

Leave a Reply

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