SEO How-to, Part 5: Analyzing Keyword Data

Keyword research is a search engine optimization tactic to identify the words and phrases of consumers who are looking for the types of products and services that your company sells.

This post is the fourth installment in my “SEO How-to” series, following:

Think of keyword research in terms of supply and demand. Every word on your site represents keyword supply. The words that people input into Google and other search engines represent the demand. Keyword research is the process of determining the demand to adjust your supply.

In this post, I’ll cover four steps: seeding keyword research, collecting the data, organizing the data in my free keyword analysis template, and analyzing it to understand intent and demand.

Seeding Keyword Research

The tools for collecting keyword research rely heavily on a seed list — your choice of words that consumers search on. A comprehensive list of seeds yields the most data. A weak list generates fewer keyword themes and often leads to poor optimization decisions.

To brainstorm a seed list, start with your site’s navigation. An ecommerce site that’s organized in a logical, hierarchical manner will contain navigational options that cover all of its products. Your keyword research will tell you whether those products coincide with what people search for.

List of all of the product categories — copy and paste directly from your menus. Then add synonyms and the types and styles of those items.

For example, a site that prints pictures on mugs, wall art, and greeting cards would include synonyms such as “photos” and “images” as well as popular product attributes such a size, occasion, and style.

Don’t be afraid to generate a long keyword seed list. It may look daunting, but the process of assembling the keywords can go surprisingly quickly once you get started, and the resulting data is pure gold.

You can also enter the URL for any page in Google Keyword Planner, and it will fetch the relevant keywords. Hence, identify pages that rank well for the keywords you need to rank for, and then note those URLs in your seed list.

Collecting Keyword Data

Next, push the sets of seed words into the input field of your keyword tool to understand the search demand of each. The process is simple and tedious.

Copy a set of seeds from your list and paste them into the tool. Wait for it to finish processing, export the resulting data, and repeat until you’ve entered all of the seeds.

When you finish passing the seeds through the tool and exporting the data, you’ll have a mass of .csv or Excel files. Merging these manually would take forever. On a PC, use the command line in your operating system to merge .csv files into a single file. Do the same thing on a Mac in Terminal.

Populate the Keyword Template

Now you have a lot of raw keyword data. But it has little value unless you organize it for analysis.

That’s where my free keyword analysis template comes into play. It aggregates in Google Sheets the keyword demand for one-, two-, and three-word combinations, and displays that demand alongside data from:

Google Search Console for an approximation of organic search volume by keyword combination.
Google Ads for estimates on conversion potential by keyword combination.

To use the template, copy the Google Sheet, or download it as an Excel file. (Excel is your best bet if you have more than 20,000 rows of data.)

Paste your keyword data into the “Google Keyword Planner” tab. For a different keyword tool, just make sure that the keywords are in column A and the number of monthly searches ends up in column C. Both columns are referenced from the primary “Keyword Analysis” tab.

Next, remove the duplicate rows in the keyword data. In Excel on a PC or Mac, go to the “Data” tab and choose “Remove Duplicates.” You can do the same thing using Numbers on a Mac.

Your keyword data will likely contain many irrelevant words and phrases. Sort the keyword data by the number of searches, from highest to lowest. Review the top 300 or so (or until the number of searches drops below a level you find valuable) and remove the unrelated rows.

The tool may identify keywords that drive many relevant searches but weren’t in your original seed list. If so, consider running those back through the tool, as well.

Also, download data sets from Google Search Console and Google Ads. Keyword volume is calculated as a monthly number. Thus the other three tabs should each contain data for a full month.

For reference, I’ve included a tab for Google Analytics data for traffic from organic search (Acquisition > All Traffic > Channels > Organic Search), though I’ve excluded that data in the “Keyword Analysis” tab since Google Analytics reports traffic for URLs but not for organic keywords. It consequently can’t be aggregated with the other sources.

Analyzing Keyword Data

As you prune the irrelevant keywords from your dataset, you will likely notice patterns in individual keywords. Now it’s time to quantify those broad patterns and compare them to understand the value of the overall keyword theme.

Start entering seeds into columns A through C. As you enter words in those columns, the formulas in the template calculate the keyword demand (Keyword Planner), current organic search performance (Search Console), and potential conversions (Ads).

Scan the words to find patterns in keyword usage. Notice how the demand and performance can be radically different for slight variations when aggregated. For example, the monthly search volume for keywords containing both “gift” and “picture” is 121,460 whereas the volume for keywords containing both “present” and “picture” is just 19,280.

Think of the analysis as an exploratory process. You can always add words or remove them if the ones you start with don’t work out. You’ll have it right when the amount of keyword demand is consistent with the total demand in the “Google Keyword Planner” tab.

Above all, listen to the data and look for trends with an open mind. Don’t attempt to prove the validity of your site’s existing structure.

See “Part 6: Optimizing On-page Elements.”