How to Analyze Search Terms Using Pivot Tables

Basic Search Terms Analysis Using Pivot Tables

Pivot tables can sound scary and complex. You don’t know what they are, what they do, why they’re useful, when to use them, and how to make one. In this post, I’ve distilled the only things you need to know about pivot tables to start using them right away. We’re going to do this tutorial-style. The best way to learn anything is to do it so

, follow along, and by the end of it, you’ll know everything you need to know about pivot tables.

Before we start, let me first answer

“what is a pivot table?”

It’s a tool that rolls up data with the same values onto a single row.

Here’s an example:

Here’s what this data looks like in a pivot table. All we’re doing is grouping the data based on the type of fruit first, then by color. The first thing you need to know about pivot tables is that there’s a hierarchy. The hierarchy below is first Fruit, then Color.

We can easily change the pivot table to show Color, then Fruit. That’s why pivot tables are called ‘pivot tables’ - you can rearrange the column fields around in different ways to help you answer different questions.

Have a look below. We grouped first by color of the fruit, then by the fruit. It’s all the same data, just arranged differently.

Now that you have a general idea of what a pivot table does, it’s time to learn why they are useful!

Open the workbook to the Sample Data tab.

In this example, we are going to analyze search terms performance by Device, Network, the term itself, and match type.

If you’re familiar with paid search search terms, you know that that can include unwanted punctuation such as * { // ^%. The first thing I like to do is normalize this text data. In column I of the Excel file, I’ve included a formula that removes common punctuation from the search terms. Drop that formula down, copy cell from I2 to I22604, go to cell C2 and right-click and paste as values. Then delete column I (or it will slow you down significantly from here on out!).

Let’s take a step back and ask “why did we do this?” We want to aggregate (roll up) duplicate search terms data into one unique row so we can see how it performs as a whole. If we didn’t do this, we would have one row with punctuation and one row without punctuation for the same keyword. In my opinion, punctuation doesn’t add a lot of meaning to the keyword so I like to normalize it all only allowing for & (ampersands) and ‘ (apostrophes).

Now that our data is clean and normalized, select any cell within the data table, then in the ribbon of Excel, go to INSERT > Pivot Table, when the ‘Create PivotTable’ box pops up, click ‘OK’.

Pivot table created. Now what?

Knowing what to do next all starts with knowing your data. For example, I know my ‘Device’ column has three types of devices: [Computers, Mobile Devices, Tablets]. I might ask something like “What device gets the most conversions?” Let’s find out!

Click the following checkboxes in the right-hand pivot table pane: ‘Device’, ‘Impressions’, ‘Clicks’, ‘Costs’, ‘Conversions’. See what it did? It summed up all 22,603 rows into just 5 total rows! Now we can answer our question quickly and easily.

What if we wanted to know the top converting search terms?

Simply uncheck the ‘Device’ checkbox in the pivot table pane and check ‘Search Term’. You can sort this pivot table by selecting cell E4 and going to the DATA tab in the ribbon and sorting Z-A descending like so:

It looks like our top converting search term is ‘elevation deciding’ (obviously this is dummy search term data J ).

What if you want to add a calculated column?

Select any cell within the pivot table and in the ribbon, look for an ‘Insert’ feature. Depending on what version of Excel you have, it is usually on the HOME > Insert > Insert Calculated Field OR you might see an ANALYZE tab appear and it’s at ANALYZE > Fields, Items & Sets > Calculated Field.

Let’s add Cost per conversion (CPA) to our pivot table.

Let’s format the column by selecting column F and formatting as Currency.

You might have noticed that our pivot table re-sizes itself every time you change it. This can be annoying. Right click anywhere in the pivot table and select ‘PivotTable Options’.

Uncheck the ‘Autofit column widths on update’ checkbox.

While we’re here, it’s worth pointing out two other features I use all the time. If you go to the Display tab, there’s a checkbox to use the ‘Classic PivotTable layout’ which allows you to see text field data more easily.

For a comparison, here’s what Device by network looks like in a standard pivot table.

If we check the ‘Classic PivotTable layout’, here’s what the same pivot table looks like below.

Do you see how the text fields have subtotals in the classic version? Let’s say we want to get rid of the subtotals, and repeat the text fields so there’s no blank cells. Right-click any of the Device column cells (column A) and select ‘Field Settings’.