One of the common misconceptions amongst people trying to rank in Google for a given term, is that the sky is the limit. For instance, some might think because they’re in the tourism business that they can rank in the top 10 for “travel.”
With universal search, one day, maybe. But we want to consistently rank for the terms that mean the most to our business that are within reach.
One way to figure out if a keyword is a good opportunity is to see what sites currently rank in Google. That will give you a pretty good idea that if there isn’t a site like yours (i.e. a competitor isn’t ranking), then it might be more difficult to penetrate because Google is essentially saying, they don’t want sites like yours in that search result.
That means we need to do a lot of Googling to see where our competitors rank, right? Well, kind of. Googling hundreds of keywords is incredibly time intensive so why not use a tool that already checks site rankings in Google to speed up the process?
What we’re looking for are keywords where Google says sites like ours belong. To do this, we are going to use a tool called KeywordSpy to identify great opportunities to rank. Just to note before we dive right in: KeywordSpy is meant to give an overview of rankings, not necessarily the exact rankings. Therefore, if KeywordSpy says www.eyeglass.com ranks #1 for “cool glasses” it could actually rank #3 or #10 tomorrow.
For this example we’re going to be using the eyeglass frame space. At the end of the exercise you should have a keyword list where Google is already rewarding your competitors, so why not you?
Let’s say you’re top 5 competitors are:
Here is what our end output will be:
1. Plug the first domain into KeywordSpy.com:
2. Go to the organic keyword tab:
3. Download the keywords in CSV format:
4. After your download is ready, let’s download it!:
5. Repeat this process for all of our keywords and put the data into one Excel sheet with one header. It should look like this:
6. Now, we want to compare our sites. It would be very difficult to do so if we use URLs as an indicator. For instance, if we used www.framesdirect.com/page-1 as the company, www.framesdirect.com/page-2 would be a different company when we know it’s the same.
That means we need to “clean” the URLs. Copy and paste the URLs into a new column (from column I to J):
7. Select our copied URLs by click the letter in the column. So in our case, we’d click “J:”
8. Now, let’s clean the URLs by finding and replacing the http://, https://, www. and everything after the slash.
Find and replace the following with nothing:
So your data should look like this:
9. The end goal is to see how many competitors rank for a given keyword. However, what happens when a competitor has multiple pages ranking for the same keyword? That occurrence will skew the data if we want to see how many competitors, not competitor pages, are ranking for a given keyword.
Therefore, we need to first figure out the highest rank a competitors page has, then eliminate all lower ranking pages. We can do that by first sorting rank smallest to largest.
Go to the data tab and select filter:
10. Sort position by ascending:
11. Select all of your data by clicking on the first column, hold shift and select the last column in our data set:
12. Let’s delete duplicate competitor pages by going to the data tab, select the remove duplicates button. Once you’ve done that, unselect all in the tab that opens so every column will no longer have a checkmark next to it. Select only the first and last column (A and J in our example):
13. It’d be incredibly difficult to find opportunities with over 50,000 rows of data. We need to consolidate it with a pivot table.
While pivot tables can seem intimidating, it really is as simple as selecting data and dragging and dropping. Let’s give it a try.
Grab your data by selecting the first column, hold shift, then select the last column:
14. To create the pivot table go to the data tab and select the pivot table button:
15. Once your pivot table has been created in a new tab, drag and drop the columns (called “field name”) in the following way:
Above you’ll see keywords where all of our competitors rank. That means Google is telling us that it wants to rank sites like ours for the above keywords, therefore, if we can provide more value than our competitors, we could rank higher than them.
Now that we have all of our data available, we can start to answer different questions. For instance, what are my competitor’s most important pages? Or, where do my competitor’s rank in the top 10 and I do not? There are any number of ways to slice and dice the data so feel free to play around with the pivot tables to answer questions you might have.
Keep in mind this is only one way of doing keyword research, and I’d advise it not be the only method.
Have fun and keep analyzing!