Keyword Research and Analysis in Excel: Keyword Grouping (+ free Template)
In this post we are going to cover one of the basic concepts of keyword research – keyword grouping.
Why is keyword grouping important?
Think of the following grouping examples: branded keywords, locality (city, region), year, transactional, informational.
Preparing and cleaning the data.
1. Make a “smart table” out of this list by clicking on any cell in your list and pressing CTRL+T on your keyboard (or go to the top menu Insert → Table).
2. Remove duplicate keywords from your table. Click on any cell in your table and go to the top menu Data → Remove duplicates.
In the new popup window unselect all columns by pressing the “Unselect All” button and then select only the Keyword column and press “OK”.
Basic grouping: word count and search volumes.
Now you are ready to start grouping your keywords. Let’s do some basic grouping: by the number of words in a phrase and by search volumes.
As there is no default formula to count words in a single cell, what we are going to do now is to calculate the number of spaces between words (if any).
Here’s a formula to do that:
Grouping by search volume is possible with a number of nested IF statements. We just need to define the bucket size. Let’s use the following buckets / groups:
- Below 100
Here’s a formula for this kind of grouping:
=IF([@[Search Volume]]<100,”0-100″,IF([@[Search Volume]]<500,”100-499″,IF([@[Search Volume]]<1000,”500-999″,”1000+”)))
Advanced grouping: using additional tables to automate formulas.
The example with nested IF statements is actually the one we are going to pimp up now in order to do the advanced grouping. We are also going to utilize the Total Row functions of our “smart tables”.
Let’s have a brief look at what a Total Row of a “smart table” is.
Total Row, when enabled, offers a number of calculations that it can make for each column: count rows, sum numbers, show minimum or maximum value, etc. It is also possible to use other built-in Excel functions. The best thing about the Total Row is that it works independently of the “smart table” size, i.e. no matter if there are 5 rows or 5000 rows it will execute the same calculation. So, knowing all that we are going to use the Total Row to quickly create multiple IF conditions for our advanced grouping with the REPT (repeat) function.
Take a look at the example below.
Here you see a table with two columns: Lookup and Formula. First column is for manual input only. The Formula column automatically creates an IF condition that we’re going to use further. It just takes the value of the Lookup column and concatenates it with other text elements to make a formula string out of it. In other words, the results of the formulas in this column are other formulas (perceived by Excel as text strings for now).
Here’s the formula that takes values and concatenates them:
And here’s the result of that formula:
You can now see a number of IF conditions in that column for each row. And yes, there is no error – the closing parentheses were skipped on purpose. But how do we combine all of that in a single formula? Well, the magic happens in the Total Row where we use the following formula to concatenate all IF conditions in a single long string:
What this formula does is that it takes all values from the Formula column and concatenates them into a single string. It also adds a “=” symbol and adds closing parentheses, so that the resulting string looks exactly like an Excel formula should look like.
There are two limitations, however, in Excel, that you have to know about.
The first one is that the maximum number of nested IF statements in a formula is 64 which means that for a single column with keyword groupings we may use not more than 64 different keywords. That’s why, for example, in a keyword analysis with geographical grouping by city one would require two such columns for cities in Germany with a population of 100.000+ people.
The second limitation is Excel’s formula length. It may not exceed 8.192 symbols. This limitation never affected my most complex formulas, but still it exists.
Implementing grouping rules.
Ok, so what do we do now with all these columns, rows and formulas?
- Copy the resulting cell of the Formula column in our Total Row.
- Switch back to our Keyword table and paste copied data as “Values only” (to do so press CTRL+ALT+V and choose “values” in the menu).
- Press F2 to enter the cell and then press ENTER.
After that the formula will no longer be perceived as a text string, but as an actual formula. That’s why it will start working immediately and you will see the results of grouping defined by the conditions we used.
To make your life even easier we created a downloadable Excel file with grouping table templates that you can use for your keyword research.
The file contains an improved grouping table with an additional Topic column, that helps defining several related subtopics in a single grouping rule (for example, if you want to group different BMW car models in a single group with subgroups like “X3”, “X5”, “i3” and so on).
About the author
Yury has 10 years experience in BI, Data Analytics & Business Development. He is a Power BI & Excel Expert and also the Analytics Team lead in Digital Loop.
He and his team is ready to support you in Data analytics area!