Latest posts by Shovon Joarder (see all)
- White Hat SEO Techniques and Right tools for Ethical Link Building Strategies – Myth Revealed - November 24, 2016
- Best & Affordable SEO Content writing tools for Professional Freelancers - August 24, 2016
- How to do Organic SEO Keyword Research with Free Keyword Tools - February 12, 2016
How to do SEO keyword research for best organic search result? This is probably the most common question arise in the mind of every SEO freelancer. Not only them, but also in the mind of any website owners having live blogs or business and e-commerce websites. Well, I write this article particularly for the freelancers because business owners might have their own budget to purchase expensive online keyword tools to serve their purpose which is actually not possible for the SEO freelancers.
However, business owners can also use this technique to minimize their cost of investment for their online marketing effort.
Here, I must mention one thing that, free things are always deliver the most low-quality product, but still if you want to use free things but want the best output, then you’ve to prepare yourself for the hardest and longest time-consuming effort that needs huge patience and dedication.
My strategy of organic SEO keyword research mainly use one free desktop software for necessary data collection and extensive work on MS Excel file. So, you must have to have good proficiency in MS Excel to perform organic keyword research.
First, I’m going to give a intro of the free software that used for data collection and then I explain the research step by step process with a practical example which I recently performed for one of my clients.
So, let’s begin our journey to going into deep of the main topic.
Free Keyword Research Tools
SEO freelancers like me usually search around the internet for free tools, especially the keyword research tools. But, believe me, there are not a single free tool available which can give complete information to keyword research.
But, yes if you manage to use any paid tool, you can get thousands and millions of information which make you literally confused. Yet, they’re really very helpful if you understand and use them properly. Spyfu, Semrush, SEO Powersuite (Rank Tracker), Traffic Travis, Web SEO analytic – all these are my favorite online and desktop tools, but they’re really very expensive to use.
Among the free keyword research tools, Google Keyword planner is no doubt the best resource for data mining and more than 90% of experts will recommend using Google Keyword Planner for Keyword Research. Even me too.
And the interesting fact is, every paid and free keyword research tool use the database of Google keyword planner to provide suggestions. So, there is nothing special if any expert suggest you to use the Google keyword planner for the research.
So here I should say or recommend about the free keyword suggestion tools that can help us to perform an effective keyword research.
Rank Tracker of SEO Powersuit (link-assistant) and Traffic Travis is my most favorite keyword research tool and both the desktop software usually pull data from Google Keyword planner once you set it with your Google account.
Here I should mention that I’m going to use Traffic Travis for pulling keywords from Keyword Planner as the free version of Rank Tracker (link-assistant product) will not allow us to download or copy the data from the software.
As my main topic is to show you the step by step keyword research with the free tool and MS Excel, let’s begin with that.
Step by Step Process of Keyword Research for Organic SEO
To make things easy for you, I will use a live example of one of my keyword research that I’ve recently performed for one of my clients. The client has an e-commerce store of ‘Leather Bags’ and he is targeting the market for the UK.
What is seed keywords? It is nothing but the basic idea of your main target niche. Either you can get the idea from the client, or you can get idea from the website itself. Here, you must use your strong observation power to identify the main target words suitable for the site that can really reflect the niche.
Let’s have a close look at the site below:
I surf all through the website pages, links on menu-bar, product categories, offers and, each and every possible area of the site from where I can get the basic idea to identify the possible words that are mostly relevant to the business. And, I identify the below words shown in the image;
Use Free Keyword Tool to Collect Keywords
Once you prepare a list of seed keywords and identify the most potential words for the site, the next step to collect Google recognized keywords from the Keyword Planner through your desktop software i.e. Traffic Travis.
Remember, it would be one of the most time-consuming parts of the whole process.
Open up your Traffic Travis software, go to “Research” Option, input the seed keywords and start fetching data from keyword planner. Below image can help you out with a more clear explanation.
Use your seed keywords wisely. Tag the main keyword group with sub category, promotional and additional word like shown in the image above and then fetch data. It is wise to use one seed keyword phrase to collect Google recognized keyword from the keyword planner as the software with free version will only provide 100 results with one fetch.
And as at this stage, I collect data from Keyword Planner without any filtering and so followed the process to collect for each and every match of seed keywords.
For instance, I make a seed keyword phrase like “Backpacks Men” with the main category of “Backpacks” with one main sub-category “Men” and fetch data first time with “Broad” option, download it and again fetch with the same words with “Phrase” option.
Below picture will show a more clear explanation of my above statement.
Once you run the above process for every seed keyword data collection, you get a huge bunch of relevant keywords with handful information. For this project, I’ve collected around 5.5k keyword phrase from Google Keyword Planner.
Whenever one Fetch completes, I download the data through CSV format and save in a single Excel sheet. For your better understanding, have a look at the below image;
SEO Keyword Research & Analysis on MS Excel
Once you downloaded all the collected possible potential keywords from Traffic Travis and complied in one MS Excel file, it looks like below image.
In this complied data, you can see a handful of very helpful info’s like search volume for both Global and local target market (as per Google Keyword Planner), Keyword Efficiency Index (KEI) and Search Engine Result Page (SERP).
Don’t worry about the number of comprehensively collected info. As I mentioned earlier that, for this Chesterfield project, I collected more than 5.5k keywords from Keyword Planner through Traffic Travis.
And believe me, chances of finding the best keywords will be much higher if you gather a huge bunch of info’s at this level. Now, its time to organize the keywords as per various metrics, filter-out the bad keywords and narrow down the list.
Step One: Break Down Keyword Structure
At first, I must mention that a good keyword must have three major parts and one optional part like below;
- Long Tail (Optional);
Why the Keyword Structure is Important?
Well, you can find the answer to this question if you ask your mind. Let me make the thing clear to you with an example.
For instance, you’re staying in Dhaka and need a private taxi cab service to go to Airport, and you don’t have any idea about the existing taxi cab companies available in the city. But, you need to book a service instantly so that the cab can come to your place to pick you up at the right time.
So, if this is the case and you’re searching on Google, Bing or Yahoo, then what you write in the search box? What search terms or words will give you most accurate result?
If you write “Taxi Service” or “Cab Service” or something like that, then do you think it’ll give you a search result that can really serve your purpose? The answer is, NO.
Search term like “Taxi booking in Dhaka” will give you much accurate result than above and if you use a phrase like “Taxi booking for Airport in Dhaka”, it’ll even give you more accurate result.
Here if the ‘Taxi’ is Head, ‘Booking’ is the body, then ‘Airport’ is the tail and ‘in Dhaka’ is the long tail.
And usual case studies shows that the keyword with these three main basic structure performs much better result than the keywords which has only ‘Head’ or ‘Head’ and ‘Body’. Keywords with only one and two words generally indicates much broader target websites and only very high authority sites are come up with the search and they’re literally impossible to defeat.
How to break keywords with main target words?
This is very easy to break down the keywords with your main target words by using text filter option in MS Excel sheet.
Set the data filter option on the top row of the keywords list, click on the filter option, select text filter and contains, then a dialog box will open. Then I put my main target word group in the first box and sub-category or other target words in the second box. It comes with the list of the keywords that contains my main target words.
To make things clearer, have a look at the below picture.
Step Two: Make Separate Sheet for Each Keyword Group and Remove Duplicates
By using the text filtering formula, I identified the keyword’s list that contains my most targeted words for the site. Then I copy and paste them in a separate sheet give it a name by main keyword group.
While doing the above, it is possible that many duplicate keywords came up in my filtered list, and so I need to figure it out and remove them.
So, I drag the whole list, select the conditional formatting and click on the Duplicate value. It’ll highlight all the keywords, and I can easily remove them one by one.
Have a look at the below pictures to get more clear idea about how to do it properly.
Step Three: Identify the Numbers of Words in Every Keyword Phrase
In this step, I’ll identify the numbers of words in every keyword phrase. As I mentioned earlier, the keywords with more targeted words that has good structure will serve the most to win the battle. So, it’s important to identify the keywords that have only one or two words and remove them.
Before proceeding to further discussion about this step, let me share one important formula that usually not known to a majority of intermediate level excel experts.
If you want to calculate the no. of words in cell f4, then use the formula: =len(f4)-len(substitute(f4,” “,””))+1
To give the credit of my learning about this formula, I must ask you to visit MS Excel expert’s website as it can help you to learn even more formulas that you may need for another purpose too.
Now follow the below picture to learn about the process.
After identifying the numbers in every keyword, use ‘Sort & Filter’ option to organize in “Smallest to Largest’ sequence and remove the keywords which have only one or two words.
Step Four: Determine the Keyword’s Competition Level
In this step, I’ll identify the difficulty level of every keyword. Its a common question for every SEO freelancers that how to identify the difficulty level of a single keyword and base on which metrics it can be measured?
Well, the answer is Search Engine Result Page (SERP) of a keyword. And, based on below formula, it can be easily measured the difficulty level of a keyword.
- 100,000 pages in SERPs*: Easy
- 100K –1 Million pages in SERPs: Relatively Easy
- 1M –10 Million pages in SERPs: Moderately Competitive
- 10M –100 Million pages in SERPs: Competitive
- Over 100 Million pages in SERPs: Extremely Competitive
So, lets see on the below picture, how we can mark the keyword’s difficulty level using this very important information.
Step Five: Finalize the Keywords for Organic SEO
From the above four steps, I got all the required information that can help me to identify the most potential keywords for the site.
I get the search volume for the global market, search volume for the local target market, keyword efficiency index (KEI), SERP and difficulty level and, most importantly the well-structured keywords which have the head, body, tail and long tails.
At this step, I’ve done the color coding the keywords based on difficulty level and organize them in such a way which I feel convenient for me. For your case, you can follow your own style. And, then narrow down the list to pick the best organic keywords for the site based on below metrics;
- Keyword Structure
- Search Volume
- Keyword Efficient Index (KEI)
- Competition Level
In conclusion, I must say that, for anyone the whole process of this SEO keyword research for organic SEO seems a very lengthy process and very much time consuming. Yes, I agree with that. But, when you are looking for any free stuff but need a quality output, then you must have to invest something which is really valuable. And in my point of view, it is the TIME!
I should also mention one more thing that, I surf many blog posts to learn proper technique for keyword research that can bring the effective result for organic SEO.
I learn formulas and take ideas from different blog posts for this purpose. For instance, break down the keywords, the formula for SERP to determine the difficulty level, etc. Then I finalize this technique for myself and now which is followed by other SEO freelancers at Freelancers-HUB. but unfortunately, I only picked these bullet things in my head and forgot to bookmark the posts. Otherwise, I can give credit to those blog owners from where I learn the formula and get ideas.
However, if somehow this post comes in front of those blog owners and contacts me for credit, I’ll be more than glad to give them credit what they deserve![starbox id=”Shovon Joarder”]