Keyword Research Tool: Find Out How Many Competitors You Have with Google Spreadsheets

Targeting the right keywords is always the first thing you want to do when you’re coming up with your SEO strategy. The typical process is like this:

1. Check the monthly search volume using Google Adwords. The more monthly searches that a keyword has, the more audience you could capture.
2. Check the current ranking of each keyword which has decent monthly searches. This is to help you know the potential of your website. You always want to leverage any opportunities when your website is already ranked 11th, 21st, etc. Because in this case, your website is ranked at the first position of each page and has the potential to move to the next page.
3. Check how many competitors your website has for each keyword. This is so that you can know your winability potential for each keyword.

This article is meant to introduce an efficient way to determine the number of competitors for each keyword using Google Spreadsheets and its built-in function, ImportXML().

Before I discuss the Google Spreadsheets tool, it’s necessary to know these search queries using Google’s advanced search operators:

• Intitle: “keyword” - provides results which have the keyword in their title tags.
• Inanchor: “keyword” - provides results which have the keyword in their anchor text.
• InURL: “keyword” – provides results which have the keyword in their URLs.
• Intitle: “keyword” AND inanchor:”keyword” - provides results which have the keyword in their title tags and anchor text.
• Intitle: “keyword” AND inURL:”keyword” - provides results which have the keyword in their title tags and URLs.
• Inanchor: “keyword” AND inURL:”keyword” - provides results which have the keyword in their anchor text and URLs.
• Intitle: “keyword” AND inanchor:”keyword” AND inURL:”keyword” - provides results which have the keyword in their title tags and anchor text.

So, how do you use these queries in keyword research, and how do you determine the number of competitors? Let’s take “seo keyword research” as an example.

First, type this search query into Google:

intitle:”seo keyword resarch” AND inanchor:”seo keyword research”

When you get to the search result page, I’d highly recommended adding the phrase &pws=0 at the end of the URL to avoid personalized results.

Next, click to the last page of search result. For this keyword, the last page is page 35. At the bottom of search results, you will see a notice as shown in the screenshot below:

It means there are 350 results in Google’s index which have “seo keyword research” in title tags and anchor text. This is to say there are 350 competitors on this keyword. (Here I define the ones who use the keyword in their title tags and anchor text as serious competitors. This criterion could differ accordingly).

The methodology is very straight forward. However, when you have hundreds or thousands of keywords to research, it can be mean be very time consuming. Here, I’ve used Google Spreadsheets to accomplish this job in a minute. Using this keyword research tool, you can easily send search queries to Google in a batch, and more importantly, you can get the number of competitors directly with one click.

Here is a screenshot from the spreadsheet.

What you need to do, is just input your keywords in Column A, and go to the top navigation, choose the function you want under the dropdown menu of “Get Results Here”. (See screenshot below.)

Keyword Research Tool

Enjoy!