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.
https://www.aviva.co.uk
https://www.churchill.com
https://www.lv.com
https://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.
Export and download each of these reports.
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.
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.
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.
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,
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.
Tidy the sheet up by formatting as a table, and (hopefully) you should have something that looks like this.
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.
If you would like to download this example sheet I have added it here – CompetitorReport
