How to research targeted keywords you'll actually use

Research Targeted Keywords

How to Research Awesome Targeted Keywords You’ll Actually Use


Use The Google AdWords Keyword Tool for Mind-Blowing Results

How to research targeted keywords you'll actually use

I know you don’t need a long explanation about long-tail keywords. You would not have found this article if you didn’t already know what they are. You are here because you want to learn how to find keywords and keyword phrases. Use my method to get:


  • Consolidated list of competitive keywords and phrases
  • Count / Sort by the number of words in the phrase
  • No duplicate entries
  • Count / Sort data based on average monthly searches


I use my process to find all the long tail keywords I need. The words and phrases I discover in my process are the words and phrases people use when they search the Internet. I call this their “natural language.”


There are benefits to using these natural phrases. First, it makes it easier for you to build a rapport with the reader. Using their language helps to develop a bond with your readers.  Second, using their natural language is the best on page SEO. Google refines their algorithms to support this natural language. If you use it, you will get high Search Engine Result Placement (SERP).


How I Use the Google AdWords Keyword Tool


You must have an account to use Google’s AdWords Keyword Planner. If you don’t have one, sign up now. It’s free, and you can use your account for all Google’s free tools. Search for the term “Google keyword planner” and click on the appropriate URL. For your convenience, you can click on the Keyword Planner link provided here.


Locate Google AdWords Keyword Tool and login to your account.

Search for Google Keyword Planner


Since you are starting from scratch, you should go to the “Find new keywords and get search volume data” box.  Select “Search for new keywords using a phrase, website or category.”


Click where the red arrow is pointing to begin your long tail keyword research.

Click on Search for new keywords to begin you long tail keyword research.


When the page opens, enter your keyword or keyword phrase in the “Your product or service” box.  I usually enter a two-word keyword phrase.  In my opinion, I get more long tail keywords this way.  Feel free to experiment.  Let me know what you discover.


If you want to change your targeting data, date range or customize your search you can do it here.  I generally accept the default settings.  Click on the “Get ideas” button.


Click the get ideas button to get long tail keyword suggestions

Click the blue Get Ideas button to get your initial keyword suggestions


If you aren’t already there, click on the “Keyword ideas” tab. A graph illustrating the average monthly searches appears near the top of the page. This is not monthly average on the search term you entered. Rather, it is the average number of monthly searches for your entry and the keywords and phrases in the table below. I usually toggle this feature off to avoid any confusion. To turn it off click on the “L-shaped” icon with the lightning bolt between the “Columns” and “Download” buttons. The vertical red arrow in the picture points to the right spot.


You will notice that your initial search term is in a box by itself. It has columns for average monthly searches, competition, and suggested bid. Depending on your initial term you may have a low number of searches. Don’t worry about that now.


To see a graph on the “Average monthly searches” for your initial search term go to the “L-shaped” icon with the lightning bolt. It is to the left of the number. Hover your cursor over the icon and the graph appears. It stays only as long as you leave your cursor in position. The horizontal red arrow points to the right icon.


View the graphs for overall and idividual long tail keyword phrases

Hover your cursor over the graphs icon to view the overall and individual Avg. monthly searches


Below your initial keyword search term Google has given you a table of keywords. Google’s algorithm sorts them for you by relevance. I generally defer to Google and leave it sorted that way. If you want, you can sort it by the other columns.


Look at the “Suggested bid” column. I use this as a gauge to measure the effectiveness of the keywords. When you see bids of more than a few dollars, you know the keywords are useful. The amount entered is the cost per click they are willing to spend on the keyword phrase. The higher the price, the more useful the expression. Businesses do not make a practice of spending their money on something that does not give them a return on their investment.


The original search term and the table of suggested keywords offered by the Google AdWords Keyword Tool.

The initial Search term and the Keywords offered by the Google AdWords tool


How to Extract Your Data from Google AdWords Keyword Planner


Create a working folder somewhere on your computer to hold the keyword files we are going to create.  For my convenience, I create a folder within the project folder.  I like to keep everything together.


Open a simple text editor such as Notepad.  Go through the list of relevant keywords and pick out keywords and phrases that best match your niche.  Ensure they have a decent number of monthly searches, low to medium competition, and suggested bids of a few dollars or more.  I always save the list.  I use a combination of my project name and append “SeedKeywords” at the end.


Despite extracting the keywords and phrases from the initial list I always save it.  Click on “Download” and save the file as an Excel CSV.  A window opens that gives you the number of keywords on the list.  It also gives you options on how and where to save the data.  Ensure you select “Excel CSV.”  Click on the blue “Download” button.


Download keywords suggested by the Google AdWords Keyword Tool

Download your suggested keywords as an Excel CSV file.


A window opens and tells you your download is available and the option to “Save file.”  Select “Save file.” Next, go to your download folder and open the file.


We are not going to manipulate the data yet.  All we want to do here is to save it as a comma separated value (CSV) file.  Note that the default file is Unicode Text.  You have to open the drop down menu and select CSV.  You can use the default filename or change it to suit your needs.  I usually add the seed keywords as part of the filename so I can track where I am on my text file list of keywords.  You want to create a CSV file for each word or phrase on your seed list.


Save your long tail keywords data in the CSV format, not the TXT format.

Ensure you save your data as a CSV file.


How to Refine Your Long Tail Keyword Search


The data collected so far is impressive.  For example, when I made my long tail keyword search for this blog post I collected 39,289 keywords and keyword phrases.  Now, that is a lot of data.  We need to refine it to make it more useful.  In the steps below I am going to show you how to:


  • Merge all your data
  • Count the number of words in the phrase
  • Remove duplicate entries
  • Collate data based on average monthly searches


Consolidating Your Long Tail Keyword Lists


Now that you have collected the CSV files for each of your seed keywords we need to combine them into one master keyword file.  No, you will not have to cut and paste and jump between CSV files.  You can create a simple batch file or open a command prompt and enter the command.  I prefer using a batch file as it saves a little time and you don’t have to bother with opening a command prompt.


Creating Your Batch File


To create the batch file, open a text editor.  I prefer Notepad.  In your text editor type:


copy *.csv KeywordMasterList.csv


For those who haven’t created or used batch files before I will explain the syntax of the command.


  • “Copy” is the same command we used back in the DOS days. It copies a file.
  • “*.csv” combines a wildcard with the CSV file suffix. It grabs all files ending in CSV and copies them to the specified location.
  • “KeywordMasterList.CSV” is the destination file where the copy command copies the data from the other CSV files.  You do not have to use my filename; it can be anything you like.


To save the batch file click “File/Save as” and give it a name.  I called mine KeywordCSVimport, but you can call it whatever you want.  Do not accept the default suffix of “TXT.”  In the “Save as type” window click on the down arrow and select “All Files.”  At the end of your filename enter .bat.


Running the Batch File


Once you save the batch file move it into the working folder with your CSV files.  Ensure that only CSV files related to your long tail keyword search are in the folder. Click on the batch file you created.  It copies all the CSV files in the working folder into one master CSV file.


To create a consolidated list of long tail keywords move your batch file to the CSV working folder.

Move your new batch file to the CSV working folder.


Working with the Keyword Master File


In its raw state, the consolidated CSV provides a vast amount of information.  The file has thousands of keywords and keyword phrases.  Each of which has the average number of searches suggested bid and competition rating.  The number of keywords and phrases depends on the number of seed keywords you generated when you started.  For example, my keyword research for this article produced 39,289 keywords and phrases.


Keyword Count for Each Phrase


Now we are going to start manipulating the data to serve our purposes better.  One of the items I promised was the ability to count and sort on the number of keywords.  We are going to do that now.


If you want, you can delete the unnecessary columns.  You will need the “Keyword, Avg. Monthly Searches, Competition and Suggested bid” columns.  In my view, don’t bother.  This keeps all the files consistent if I need to combine them again.


Select the column to the right of the keywords column.  In my case, it is column C.  Right-click and select “Insert.”  Label the column with something appropriate.  To keep it simple I named the column “Number of Keywords.”


Add another column so you can sort your long tail keywords by the number of words.

Select the column to the right of the “Keyword” column. Right-Click and select “Insert” to add a column for the number of keywords.


The Number of Keywords Formula


In “Number of Keywords” column you just created insert:

 =IF(LEN(TRIM(A2))=0,0,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,” “,””))+1)


Look carefully, make certain the formula correctly annotates the cell location of your first Keyword.  For example, if your first keyword is in Column B Row 2 you would change all the instances of A2 to B2.  The red arrow in the picture below shows where to make the changes.  For more information, you can search for Microsoft Knowledge Base Article 213889.


Edit the formula in this location in order to count the number of words in your long tail keyword phrase.

The red arrow shows where you edit the Cell address to match your spreadsheet.


Press return and the “Number of Keywords” column has the total number of words in the “Keyword” cell.  Select the cell containing the answer to the formula you entered.


Notice Excel outlines the cell, and there is a small box on the lower right side.  The arrow in the picture below shows where it is.  Hover your cursor over the box and it changes to a plus sign.


Left-click and drag that box down to the bottom of your “Number of Keywords” column.  Let it go and you will notice that each cell now contains the number of keywords in the cell to its left.


Left-Click and hold the small square. Drag it to the bottom of the Number of Keywords Column.

Press Return. Left-Click and hold the small square and drag it to the bottom of your Number of Keywords column to populate the data.


Remove Duplicate Values


The next step is to remove any duplicate values.  I’m sure you have noticed that there are thousands of them.  If you are using a recent version of Excel (2007 or newer), it is a simple process.  For more information, go to Microsoft’s Office Support website.  The website has instructions on how to remove duplicate values from Excel spreadsheets.


In the Office 2016 version of Excel, you click on the “Data” tab.  Move to the “Data Tools” box and click on “Remove Duplicates.”


The Removes Duplicates” tool deletes data according to your criteria.  I suggest that you select all the columns and put a check mark in “My data has headers.”  Click OK to remove the unnecessary data.


Select all columns and check my data has headers to remove duplicate long tail keywords.

Select All columns and check My data has headers. Click OK to remove duplicate data.


Another window pops open informing you of the number of duplicates removed.  I removed 32,583 copies, leaving me with 6,707 unique values.  Click OK and the CSV file opens.  Rows one and two may be duplicates.  If they are, delete row two.  It happened because “My data has headers” was checked.


Window opens to show you how many duplicate long tail keywords were removed and how many remain.

Window illustrating the number of duplicates removed and the number of values that remain.


Sorting by “Avg. Monthly Searches.”


We want to sort the keywords and keyword phrases highest to lowest by the number of monthly searches.  If you are not already on the “Home” tab of the CSV file, click on it to move there.  The steps to sort by “Avg. Monthly Searches” are below.


  • Click on the “Home” tab
  • Click on the triangle shaped icon above column A, cell 1
  • Click on the sort icon
  • Select custom sort.
  • Sort by: Avg. Monthly Search, Sort on Values, Order: Largest to Smallest.
  • Click OK


As a safety measure, I like to preserve my data by saving it with a new filename as I move through the process.  This way, I don’t have to start from scratch if I mess something up.  I append an ordinal number to the end of each filename.  This way I have the initial list and a list for each step in the process.



Filtering the Keywords by Number of Searches


I like to filter my list so that it contains keywords and phrases with 200 or more monthly searches.  Your selection depends on your niche.  Some niches, such as business to business, generate fewer searches.


To filter the data, select the down arrow in “Avg. Monthly Searches” again.  Select “Number Filters.”  You see different ways to filter your data.  Go ahead, test each method.  Don’t forget to save your data.


For my purposes on this blog, I decided to use 200 as my cut-off.  I had no rhyme or reason; it just seemed like a good number.  After I had clicked on the “Number Filters” menu, I selected “Greater Than or Equal to.”  The “Custom AutoFilter” window opened up, and I entered 200.  Notice that you have the option to put in another value to set a range of values.  I left it blank and clicked OK.


Select Number Filters then Greater Than or Equal To. Enter a value for the minimum number of keyword searches you want.

Select Number Filters, then select Greater Than or Equal to. I used 200 as my minimum number of searches but you can enter any value you want.


On the bottom of your CSV spreadsheet, you should see the results of the filter.  I have 414 records with 200 or more Avg. Monthly searches.  This is much more manageable than our initial file.  However, we have a little more work to do.


Filter by Number of Keywords


We are at the final step.  Keyword gold is only a few clicks away.


Select the down arrow in the “Number of Keywords” column.  This opens a window where the values for the number of keywords are listed.  For this blog article, I decided that I want keyword phrases with three or more words. The final step is to select the filter icon in the “Number of Keywords” column and sort the data from largest to smallest.


You can filter the list of long tail keywords by the number of keywords in each phrase.

Filter your long tail keywords by the number of words in each phrase.


Long Tail Keyword Gold


Follow this process and you will find long tail keyword gold.  Use them to make your content more competitive.  Populate your web pages or blogs with headlines, headers, and copy from the list.  Use your prospect’s “language” and your content will resonate.  You will get higher SERP results and increase conversions.



Optimization WordPress Plugins & Solutions by W3 EDGE