Competitor Keyword Frequency - Assessing a New Search Scape

When you take on a new project trying to assess the search scape for a particular industry can be a daunting task. I found myself in this situation today. Thrown right in at the deep end, I've got a seriously competitive market to get to grips with, and not so much time to do it in.

There are a few different methods I'll use to find my way around (many of which you'll know about already), but there's on I thought I'd share as I developed it myself and you might find it useful.

Competitor Keyword Frequency

The competitor keyword frequency tool is a simple spreadsheet which I use to identify the leading websites across a competing set of keywords (the same keyword set I intend to target myself) in a particular industry.

Once identified, I can take the top 10-15 sites and dissect them bit by bit to get a clearer picture of how the particular search scape is working.

This is how it works:

1 - Open up your favourite spreadsheet package and divide like so:

COLUMN A - sites
This is where you'll list the domains of the top 20 sites for each keyword.

COLUMN B onwards

In the following columns you'll need 2 columns per keyword. One entitled 'position' and one entitled 'points'. It should look something like the picture below.

2 - Decide on your keywords (I tend to go with five or six, one & two word umbrella key words) and then transfer the top 20 results into the spreadsheet.

3 - Against each result state it's position (1-20) and distribute the points (position 1 scores 20 points, position 2 scores 19 points, position 3 scores 18 points and so on)

4 - Once you've assigned points and positions by keyword, sort the spreadsheet alphabetically by site and transfer all the position & point details so that any pages with listings across multiple key words are copied onto the same row in the spreadsheet.

5 - After sorting the rows so there is only one row per page, add up all the points for each page across the whole keyword set.

6 - Those with the highest scores are the sites who are most successful across your chosen keyword set, and the ones you probably want to take an extra special look at.

I hope that all makes sense, it's really more for my own records than anyone else's, but if you can use it and you find it useful, please feel free to try it :)