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

pivot 1

, 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:

pivot 2

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.

pivot 3

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.

pivot 4

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 5

Pivot table created. Now what?

pivot 6

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.

pivot 7

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:

pivot 8

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

pivot 9

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.

pivot 10

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

pivot 11

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

pivot 12

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’.

pivot 13

Uncheck the ‘Autofit column widths on update’ checkbox.

pivot 14

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.

pivot 15

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

pivot 16

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

pivot 17

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’.

pivot 18

On the ‘Subtotals & Filters’ tab, select ‘None’. On the ‘Layout & Print’ screen, select ‘Repeat item labels’ checkbox.

pivot 19

Now our pivot table is looking more like a summarized table of data that we can make sense of.

pivot 20

What if you wanted to expand or collapse the Device column? Just right-click anywhere in the device column and go to Expand/Collapse and select Collapse Entire Field.

pivot 21

This is useful to do when you have a lot of subfields that are expanded and you want to collapse them without having to click the tiny collapse buttons over and over again.

pivot 22

Well, that covers a large majority of how to use a pivot table. The next time you have questions about data, use a pivot table to summarize it to get the answers you need quickly.