Keyword Categorization

What is Keyword Categorization?

Simply put keyword categorization is the process in which you organize search phrases by common words. Organizing keywords helps structure your website capturing all phases of the buying process.

As mentioned in my post in 2013 "Categorizing Keywords" I cover the importance of categorizing keywords, but also a quick way to do it in Excel. This is a great strategy when working with a smaller set of terms, but what happens if you need to focus on larger sets including millions of terms to categorize that will most likely break you excel document?

There are tools like Conductor's Searchlight and Keylime that are great for dynamically tagging your terms with common words so that you can quickly categorize them. As more terms are added to your searches the dynamic tagging will add them without any additional effort from you. So easy!

Not every business has the thousands per month budget to shell out for these awesome tools so manually or setting up your own means of tagging is going to have to be done. Using Excel you can export your terms from Google Search Console and a simple sumif formula will pull in the totals for your terms.

For this lesson I am going to use my keyword list around auto make, models and year make model searches to categorize with the three distinct categories, and additional tagging to understand how many terms are reviews or the long tail and "for sale" that would be a high intent to convert.

We'll start with our exported CSV file that you'll pull from Google Search Console, Keylime, or or Keyword Planner. It should look something like this when you first open it up:

Downloaded CSV File from Google Search Console

Downloaded CSV File from Keyword Planner

 As you can see you will have three or 5 important columns. The Keyword column holds the list of terms you're going to set your lookup against. The data following falls under your Impressions, Clicks and additionally Click Through Rate and Average Position. We're going to focus on Impressions and Clicks for this exercise and use a formula to calculate the CTR from there.

Start by saving your document as an Excel Workbook. Formulas won't work in CSV.

Saving CSV as Excel WOrkbook

 Next up you'll want to create a new tab. I usually name the tab "Lookup" so that I know that this is where all my criteria I want to lookup is located.

Adding a new tab in Excel called "Lookup"

To make it easier to type in formulas and manage then I like to name the criteria that I will be using. In this case we are naming A2 through A55 "Make", doing the same with "MakeModel" and "YearMakeModel". I additionally will name the "All" tab columns as "Term" for the Keyword list, "Impressions", and "Clicks".

Excel naming feature

How to Sum Cells by List of Criteria

Using this formula and method only looks for exact match phrases to the keyword phrases in the cell and adds up all into a total. 

The formula will look like this:

=SUMPRODUCT(SUMIF(Term, Make, Impressions))

How the formula works is by using the first array which contains the TRUE / FALSE values that result from the expression (SUMPRODUCT) then using the SUMIF criteria to lookup the range (Term), Criteria (Make) and finally Sum Range (Impressions). Only the Terms that include the list of the criteria will be added up, or "summed".

Excel Sum cells by list of criteria

Excel Sum Cells by List of Criteria and is Included (not Exact)

In this case we want to add up all the keywords that include a few specific words within them. So we need to use the SUM formula to add up each of the words we have in our Lookup. In this case we have set three terms that we believe have a high user intent to purchase that are "for sale", "used" and "priced under".

A normal SUMIF sets the Range we want to lookup, the criteria of what to look for, and then what we want totaled. In this case the criteria involves a word that is located somewhere in the phrase so we will be using a wild card before and after. Ex: SUMIF(Term,"*for sale*",Impressions).

Because we are using criteria in a cell we need to concatenate the star to complete the formula. Ex:"*"&Lookups!E2&"*"

Since the Intent list just includes three words we'll SUM all three as a total.

=SUM((SUMIF(Term,"*"&Lookups!E2&"*",Impressions)),(SUMIF(Term,"*"&Lookups!E3&"*",Impressions)),(SUMIF(Term,"*"&Lookups!E4&"*",Impressions)))

Excel Sumif list within terms in cell

In order to calculate the Click Through Rate we'll just divide the Clicks total by the Impressions and format at a percentage.

=C3/B3

I have saved the excel document I used for this example in my Google Drive so you can download and play with. The numbers for the terms are completely falsified, so don't be using them to optimize your auto site. 

Depending on what data you need to look at for your terms, you can use these formulas to quickly add up totals to help you understand how your SEO is working, and determine what you will need to build out to capture new opportunities.