Have you ever used a technique for years, only to find out that it blows people away? For SEO purposes I use a very quick and simple system to find the right keywords, that factors in how popular a keyword is with the level of competition. It only takes a few minutes and helps you tap into the gold that lies buried  in the rubble that Google give you.

I really recommend that you watch this video. If you choose the wrong keywords for your SEO efforts, not only are you wasting your time, but you’re also missing out on a lot of people who want to buy what you sell.

The text below is a transcript of the video – at risk of sounding like an SEO, it is good for Google’s spiders…

Choosing the right keywords

Hello SEO people! One of the challenges that we face when we’re grappling with SEO for our businesses is choosing the right keywords. Without going too deeply into it the problem is we want to target keywords that are obviously relevant to our business, that a lot of people are searching for, and that there aren’t too many other websites competing with.

So, how do you strike that balance? How do you choose keywords that are relevant, sought after, and not too competitive? The good news is it’s really easy. The even better news is I’m going to show you how to do it.

Tools: AdWords account and Excel

The two things that you’re going to need to carry out this remarkably effective method of keyword research is first of all Microsoft Excel, which I assume that you have, and an AdWords account. If you don’t have an AdWords account (I’m baffled) but get one because they’re very useful. The other you need is Google’s keyword tool that they’re now calling the keyword planner. So go to Google and search for “keyword planner”, and if you’re not logged into your AdWords account you’ll then be gently prompted to do so and we’re going to click this first option – search for new keyword and ad group ideas. So for the sake of an example we’re going to imagine you’re selling high quality dog food, it’s as good example as any.

So for the product, or service, we’re going to enter exactly that. Keep an eye on the targeting options over here, right now it set as all locations, or languages. Sometimes it defaults to your local version, so for instance if you’re based in the U.K. you might see Google U.K., it’s worth checking on that. So put your keyword in there, I recommend one main keyword initially, and then click on get ideas. Once the data starts to come in we’re not interested in this tab; the ad group ideas, but we are interested in the tab next to it; the keyword ideas.

This is what you typically see – that Google gives you 801 keywords, 800 of them are related and associated keywords, and the one is your original. You can see there’s a lot of information here, but it’s not in a particularly good format, it’s not very easy to read, and actually Google are giving you more information than it at first appears. So what we’re going to do is download this spreadsheet, click on the download button, save it as an Excel .csv file, click on download, get the prompt, save the file, and then we open it.

Export the data

And when you open the spreadsheet it’s going to look a little bit like this. Now, it’s a whole lot of different information here, let’s just open up the column so you can see everything. We’re not interested in most of these. We’re not interested in the ad group. We’re not interested in the currency. We are interested in the average monthly searches and the competition. We’re not interested in any of the other columns to the right, so you can get rid of all those. So what we have here, let me just tidy this up, sorry, I’m a little anal with these things.

What we have here are Google’s suggestions for keywords that they feel are relevant to that initial keyword that you put in. So we’ve got the keyword, for instance; high quality dog food, we’ve got an indication of the average monthly searches, and we’ve got competition . . . bear in mind this is ad data. This is primarily, in fact totally, based on out data, but it doesn’t matter. I’d rather get some data from the source than no data. So we’ve got the keyword, high quality dog food, we’ve got the average monthly searches, and we’ve got competition. So the dilemma that some SEOs have is which is more important?

We obviously want to be targeting keywords that have a lot of searches, in other words we don’t want pages set up to target keywords that one or two people are searching for every month. However, we also want to factor in how many people we’re competing with, how do we do it? And the answer is we use both. We set up a fourth column in D that I call KPI, it’s a little tongue in cheek, keyword performance indicator, and all I do is apply a very simple formula. So put equals to tell Excel it’s a formula, and what I do is I’m taking the monthly searches, see, if I click on it, it shows it’s B2 squared, so B2 multiplied by itself, and then divided by the competition.

So we’ve got average monthly searches times monthly searches divided by competition, and we get that figure there. Now, you’ll see why in just a moment, I’m going to tidy this up by right clicking here. I’m going to tell Excel here that this is a number (with no decimal places) for ease of reading we’ll use the thousand separator, the comma. What I’m then going to do is copy that formula all the way down by double-clicking that little square, and I get this, let’s open it up a little bit. What we have here is a very simple formula for factoring in monthly searches and competition, and in a way here, the higher the figure, the higher the KPI, the better in terms of the most searches and the least competition.

So I’m going to hit windows control shift L, and then go and sort down from the largest to the smallest, and then we’re getting this. Let’s delete that first irrelevant one, and we have some that look like this. Now, this is where you have to be a little bit careful, we don’t need to work all the way through all 800 entries, don’t worry about that! But we want to find the best keywords that are right at the top. So just take a moment to remind yourself what we’re doing here; we’re choosing the keywords that have a healthy volume of monthly searches with a reasonable level of competition.

Cleaning up

So the process is we start to work our way down the list and get rid of the keywords that are not relevant. So for instance, dog food is definitely too general, pet supplies too general, and so on. You yourself will be able to pick out the correct keywords for your products, because obviously you know your keywords, you know your products, you know who you’re targeting and so on. You want a really good match here. You don’t want keywords that are loosely related to what you sell, you want keywords that are very, very closely bound to what you sell; that’s a really important point.

So what we have here, take this first example on the second row, best dog food, this is according to the model that we’ve used here, the best keyword. Yes, the competition is high, competition is graded from a zero all the way up to 1, so a .99 is very, very high competition, 0.01 is a very low level of competition. So we do have a high level of competition here, however the reason why this phrase “best dog foods” is in first place is because of the number of monthly searches.

So using this formula there’s a kind of trade off, a kind of balance, between searches and competition, but we see the score is considerably higher. This is a considerably more valuable keyword than number two in the list, which is the “science diet”. Again, this is all dependent on what it is that you’re selling. These keywords have to be a really good perfect fit. If they are, then you’ve just found the perfect way to strike the right balance between number of searches and the level of competition. Go out there and conquer.