Keyword research is the bedrock of search engine optimization (SEO). The question is, how thorough should that keyword research be? By taking your time and doing in-depth keyword research, your SEO strategy will be built upon hard data and will have the best chance of success. Here is our detailed seven-step process for SEO keyword research.
1. Understand the Business: First, you need to understand the business and the industry it’s in from an insider’s perspective. Who are the biggest competitors? Which products are critical to success? What trophy keywords does management aspire to rank for?
2. Analyze the Site: Take some time to thoroughly analyze the site as well as the competitors’ sites. Check out the wording used and how it compares to competitors’ phrasing.
3. Create a Seed List: This keyword seed list is a starting point for your keyword research. Basically, a keyword seed is a single word that can be found within a larger keyword phrase. For example, for a perfume company, “floral” could be a keyword seed for the phrase “women’s floral perfume.” The seed list of keyword ideas will be your concrete for the foundation you will base your SEO strategy on.
Start the seed list off strong by typing every combination of terms you may want to rank for. Include phrases from your notes on the competitive analysis and the deep dive into your site. For ecommerce sites, it’s a great idea to jot down all the navigation links and some descriptors. These words will be your seeds, as shown below. Put all of your seeds into a spreadsheet organized by categories in different columns.
4. Combine Seeds for Potential Keyword Phrases: Once your seeds are organized, and you’ve exhausted the initial brainstorming session and analysis, use a tool such as Mergewords to combine columns every which way (that makes sense). Different merged column variations will return phrases that most likely have keyword value. Decide which columns to merge together by writing them down or typing them out.
Combine at least two columns together, but combining three columns will be useful to gather long-tail keywords. Copy all row combinations from your spreadsheet into each of the three boxes in Mergewords, and click “Merge” to return a lovely list of merged phrases. In the example below, column A and column B combine to create 35 phrases that may or may not have keyword value.
Combining these columns, as shown below, can quickly produce hundreds, even thousands, of keyword possibilities.
Copy merged phrases into a new column in the seeds spreadsheet. Once all possible column combinations have been merged, as shown below, you’re ready to move to the next step.
5. Input Merged Seeds into Google Keyword Planner: Use Google Ads to turn your merged keyword seeds into numerical keyword data.
- Click the “Tools and Settings” wrench symbol.
- Under “Planning,” select “Keyword Planner.”
- Once there, choose “Discover New Keywords” to navigate to the tool into which you can transfer your merged seeds into keyword data.
- Now, have both your spreadsheet seed list and Google’s Keyword Planner open — ideally on two screens.
- You’ll be copying 10 of your merged seeds (the new column you added to your seed list after you merged your columns of seeds) at a time into the Google Keyword Planner. That’s the maximum amount of keyword ideas you can enter. Click “Get Results,” and Google Keyword Planner will show you the data in a graph trend line for the past year. Below that, you can get a glimpse of the data and the keywords that turned up based on your seeds.
- You can broaden your keyword query by adding any of the suggestions at the top of the graph. Use the filters provided to you in the right-hand panel to include or exclude certain keyword ideas based on the 10 you have put into the tool. These selected filters will ensure that what Google Keyword Planner turns up is more closely aligned with your targeted SEO keywords. The results will automatically refresh based on those inclusions or exclusions you selected.
- In order to make use of this data, you’ll want to find the blue arrow at the top right corner of the screen and click to download the keyword ideas as a .csv file.
Once you’ve downloaded the data, you’ll see that the Google Keyword Planner is “preparing report.” Then it says “downloading report” at the bottom of your screen. The download will go to your download folder.
- Rinse and repeat for the rest of your seed list. Select 10 seeds at a time (Excel gives you a nice count on the bottom of your screen). Once you highlight down the merged column in the seed list, you can easily see how many cells you’ve selected and copy 10 every time. I personally bold these 10 merged seeds after I copy them into Google Keyword Planner, with the handy shortcut ctrl B after I copy, get results, and download the .csv file.
This process can get tedious with thousands of seeds, and the worst thing would be losing your place. Don’t panic; keep going. I promise the results will be worth it. Put on some calming music, or listen to a chill podcast, and plug away. Once you get into the swing of it, you should be able to input more than 2,000 per hour.
Yes, you may notice that it takes a while to complete this critical step, but the resulting data is immeasurably valuable to your SEO strategy. Once you complete this fifth vital step in the SEO keyword research process, you’ll be that much closer to having every possible keyword at your disposal with rich data behind them to support your organic search strategy.
6. Merge All .csv Files into One Spreadsheet: You’ve almost made it! Now, you potentially have hundreds of .csv files in your download folder. Use this PC trick to merge all of your files into one.
Go to the file explorer and copy all of those downloads. They all start with “Keyword Stats.”
Create a new folder on your C drive called “merge” and paste all of the .csv files into it.
Rather than manually copying and merging each spreadsheet into one — you’d be doing that for hours with a high potential for mistakes — use this old-school command line merge hack, a time-saving tip to the rescue.
- Go to the Windows start menu, type “run,” and hit enter to launch the command prompt.
- c:\Windows\System32\cmd.exe will appear. Click ok. A black box will pop up on your screen.
- Enter the following after C:\Windows\System32>CD \merge Hit enter. This changes the directory — CD for “change directory” — to the new “merge” directory you just created.
- Then C:\merge>copy *.csv merge.csv. Press enter. This tells the system to copy all .csv files in the “merge” folder into a single new file called merge.csv.
You can find the new merged file in the merge folder. Click to open this .csv file. This, at last, is your keyword research spreadsheet.
7. Clean up the Keyword Research Excel Spreadsheet: Now that you have your keyword research, isn’t it beautiful?
Well, let’s quickly clean it up. Save the .csv file as an Excel workbook. I highly recommend keeping it in Excel format for fewer headaches and hassle.
- Start cleaning up the data by going to cell B4 and freeze the panes.
- Then add a filter to row 3. Select the entire row, and click “Sort & Filter” and then “Filter.”
- Sort the spreadsheet by “Avg. monthly searches” in column C, choosing “Largest to Smallest.”
- Delete keyword separators — these are the header rows from all the merged .csv files. Filter column A by selecting “Text Filters,” then “Contains,” and typing in “keyword.” Delete these rows. Clear the filter from column A.
- Repeat this process by typing in the name of the “month” you downloaded the keyword data. Delete these rows too, and then take the filter off column A.
- Because you merged so many .csv files, there are probably many duplicate keywords that appeared in multiple files. Deduplicate column A by going to “Data” and then “Remove Duplicates.” Unselect all besides column A. This step will remove all the duplicate keywords.
- Set an additional filter in column C to remove the “blank” and “0” monthly searches by going to the filter on Avg. monthly searches and unchecking these boxes.
- Lastly, for an even more thorough batch of keyword data, add another column to the left of column A where your keywords are. You can manually go row by row and mark the relevance of each keyword by labeling them with either yes, no, or maybe. Filter out the no’s from this column, and you’ll be left with relevant keywords.
After completing these seven steps, you now have oodles of SEO keyword data at your fingertips. This keyword research will be useful for optimizing all the essential pieces to the SEO puzzle, including title tags, meta descriptions, H1 Headings, content on each page, and blog optimization. It becomes the basis for all that you’ll do for your SEO strategy.
But keyword research can also be used by so many more teams to strengthen their strategies, as well. After all, keyword research is a peek into the things that real people care enough about to search for. You’ll want to share the data with your paid search counterparts who manage Google ads, the social media team, your writers, and just about everyone else in marketing. You’ve spent a lot of time compiling this treasure trove of customer research gold — share the wealth.