Using Searchmetrics And VLookup For A Competitor Rankings Comparison Report.

July 18, 2013

Searchmetrics is a brilliant SEO tool, the amount of insight that it gives on client and competitor sites is incredibly useful. One of my favourite reports, along with some manipulation in Excel is to run a quick rankings comparison report on your competitors so you can gain insight into what they’re ranking for, more importantly what they’re ranking for and you’re not, and also how your site matches-up a full range of industry keywords.

For this sample report I’m going to take a look at some of the bigger sites in the insurance sector.

http://www.aviva.co.uk
http://www.churchill.com
http://www.lv.com
http://www.morethan.com

Other good insurance companies do exist, along with quite a few terrible ones.

Run each domain through Searchmetrics and run a long-tail keyword report on each of the sites that you wish to compare.

Organic Rankings aviva.co.uk Weekly Searchmetrics Essentials

Export and download each of these reports.

Organic Rankings aviva.co.uk Weekly Searchmetrics Essentials2

 

In Excel create different sheets for each of the exports along with the first sheet which should be named ‘comparison’ this is where all of the magic happens and your data will be pulled-in.

sheets

Paste each sites data into onto it’s own sheet, as well as cumulatively into the ‘comparison’ sheet.

Then under Data > Remove Duplicates remove duplicated keywords on the ‘comparison’ sheet.

remove-duplicates

Then delete the  following columns in the ‘comparison’ sheet  – URL, Pos, Title, and Traffic Index. This should leave just Keyword, Search Volume and CPC.

Next add columns for each of the sites that you wish to compare. This should leave you with a sheet that looks something like this.

sheet

Then, using VLOOKUP you’ll need to pull the ranking data from the other sheets into the comparison sheet. So for example into Column C all of the rankings for Aviva will appear.

The formula you’ll need is =VLOOKUP(A:A,Aviva!A:G,3,0) The easiest way to generate this is to use the insert formula function,

function

Lookup value – Is the value that you’re looking up, in this case is column A, the keyword.
Table array – is the table you’re finding the value in, which is the Aviva sheet, so click in the table array entry field, then go to the Aviva sheet and highlight all of the columns.
Col index num – is the column with the data in that you wish to import, so column 3, the ranking position.
Range lookup – Enter FALSE or 0 here to find an exact match. This will cause #N/A to be returned if the site isn’t ranking for the keyword.

Repeat this for each site. And then expand the selection by dragging the corner of the box down to apply to each of the cells in the sheet.

expand

Tidy the sheet up by formatting as a table, and (hopefully) you should have something that looks like this.

final

If the #N/A results are annoying you can easily remove them by modifying the VLOOKUP formular from

=VLOOKUP(A:A,Aviva!A:G,3,0)

to

=IFERROR(VLOOKUP(A:A,Aviva!A:G,3,0),”-“)

You can also colour-code the rankings using conditional formatting.

endsheet

 

If you would like to download this example sheet I have added it here – CompetitorReport