SEO Keyword Research + Excel Functions

Keyword Visual from Wordle

Introduction

There are many reasons as to why people write hubs and other types of content. The most popular reasons being people are passionate about writing.

Others do it to make a few extra bob. Others actually make a living out of it, I am not one of those people! however, I have made a career out of on line marketing and content writing plays a big part in that.

Computers and the internet have changed the world dramatically over the last 20 odd years, mostly for the better.

Take me for instance, being dyslexic I did not do very well at School due to my atrocious spelling .

After I left school came the .com boom. I got a computer, found the spelling function and that is the reason I am able to bring you this Hub about SEO Keyword Research and Excel.

Most tutorials have a habit of explaining everything as red widgets or blue widgets but I am going to give you guys something a little more tangible and transparent.

Please note you do not have to be an excel guru to to do this. A basic understanding of excel should do the trick.

Enjoy

Finding the keywords that will make you money

So lets say you have a new (no Google analytic's data) franchise that specialises in providing a teeth whitening service in 20 of the biggest cities in the UK.

You have whatever resources and budgets you need (you wish!) to compete in all 20 cities in the UK.

For example lets use cities by population. They are...

  • London
  • Birmingham
  • Leeds
  • Glasgow
  • Sheffield
  • Bradford
  • Edinburgh
  • Liverpool
  • Manchester
  • Bristol
  • Wakefield
  • Cardiff
  • Coventry
  • Nottingham
  • Leicester
  • Sunderland
  • Belfast
  • Newcastle Brighton
  • Hull

Now we have the locations, we need to think of 4 or 5 keyword modifiers we think people would use in search engines to find our services.

If you are new to the market like I am with teeth whitening services its a good idea to start in Google's keyword research tool. For people who are familiar with there market areas I am sure you guys will have a fair idea on what keywords to use.

Google's Keyword Research Tool

There are many keyword research tools out there, some free, some cheap, some expensive, some good, some OK and some bad. None are excellent!

My argument on using Google's keyword research tool as to others it that most other keyword research tools use Google's API to power there own KWR tools. So why pay a 3rd party when you can get your data straight from the horses mouth for free?

Moving on... Firstly, if you do not have a Google account please sign up and create an account. Google will give you data if you are not signed however, it is limited.

Once you have signed in to Google double check your settings are OK.

For instance I am targeting the UK market so I will set my advanced filter to united Kingdom and language spoken to English.

Advanced Filter in Google's KWR tool.
Advanced Filter in Google's KWR tool.

Add Keywords to Tool

Once we have checked the settings are OK its time to add some obvious keywords to the tool.

They are...

  • Teeth whitening services
  • Teeth whitening
  • Teeth whitening service

Some other things to keep in mind is to make sure you set match type to exact match.

The radio button above the advanced filters link that reads (only show ideas closely related to my search terms) is optional. Some times leaving this blank works good however, some times it pollutes your results with not so relevant keywords.

In this case I have left the radio button blank to give me the widest berth of possible keyword targets.


Data on your chosen keywords

Keyword Ideas from Google

Keyword Elimination

Google returned a total of 803 keywords that they think may be relevant to your site. Now we need to go through them all and segment the wheat from chaff. Its a little time consuming but this is where an SEO strategy begins.

If you dive into a campaign without having a direction it could cost you big time.

The Google keyword tool will show you the most relevant keywords from top to bottom so analysing the first page results will be sufficient for this test however, for mature campaigns you will need to dig deeper.

Step...

  1. Is to drill through the first page results, ticking the radio box for any keywords that you find may lead to sales (leave location based keywords out at this stage)
  2. Click the more like these box highlighted below

This narrows down and groups the list. How far you want drill down is totally up to yourself but for this exercise we will stick to the 23 keywords.

Grouped keywords below...

Excel

Now we have our keyword group its time to extract these from Google's keyword tool to CSV file. Simply click the download button and download all.

Once you have the spreadsheet open we want to delete the brackets wrapped around the keywords. To do this...

  • hit "control F" on your keyboard
  • hit replace
  • enter [ into the "find what" row
  • leave the "replace with" row blank
  • hit the "replace all" button and viola

Repeat for the bracket facing the opposite way.

Now we want to delete column A,B & E so we are left with the "local monthly searches" column alongside the keyword column.

I am going to use the top 4 keywords based on relevance and search volume for this tutorial however, mature campaigns will need a lot more focus and elbow grease (work).

In saying that, remember you cant possibly manage all possible keywords. With a modest budget sticking to a small group of 3 - 4 keywords pre page will keep you in a good place.

When shifting data around in excel the software works better when a header is used along the top of the columns. Now we should have something that looks like this...

Now create a new sheet (tab).

So we will use the most popular generic term "teeth whitening" for example. In the new sheet insert the list of locations into column B. In column A have the key phrase present next to the location terms.

  1. In cell C2 hit the function button, search and select the CONCATENATE function.
  2. In the function argument box row "text1" select cell A2
  3. In row "text2" select cell B2

As you can see this joins up the texts in cell A2 and B2. You may notice there is a space missing. If so....click in cell A2 and add a space at the end of the text string and then select the cell, double tap the bottom corner so it changes all other cells below.

Now select cell C2 and double tap the bottom right so all cells below are filled with location based phrases like the screen grab below.

CONCATANATE

The pleasure with adding the CONCATANATE function is that you can simply change the text in cell A2, select and double tap the bottom rite corner of the cell and you have a whole load of new keywords to check with minimal fuss.

For example I changed the keyword "teeth whitening" text in cell A2 to "teeth whitening in ". Selected the cell and double tapped the bottom rite corner. This changed all cells in column C to my desired keywords.

Now its just a case of repeating the above process.

  1. Insert keywords into Google's KWR tool
  2. Tick all keywords you would like to add to your campaign
  3. Click the "more like these" button to find more suitable keywords
  4. Change to another keyword in cell A2
  5. Copy keywords and paste into Google KWR tool (Make sure to copy and paste the keywords below the keywords already in the tool box)
  6. Keep repeating process until you are happy with your selection keywords.
  7. Once you are happy with your keywords - download and easily sort using a pivot table.

Thanks, I hope you found this useful. This is a really quick way of efficiently carrying out keyword research.

There was so much more little details I would have liked to share but time is of the essence. Hopefully these instructions will be enough to get you started or shave some time of your usual way of doing it.

I will do a hub on how to create a pivot table to easily sort your final list per keyword group or location.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working