Subscribers

I love Pivot tables, yes, love them. They really are a marketer’s best friend – once you know how to use them. They make a great tool that will help you organize and then slice and dice data in several different ways for analysis.

Sure you can use traditional methods and sort or filter data, but if you have a lot of data, this can be cumbersome and confusing. A pivot table is a great visual refernce that can be created quickly and most importantly very easily if you know what you’re doing.

Many companies use pivot tables, but I’m specifiaclly talking about how to create pivot tables using Microsoft Excel. Pivot Tables aren’t something often promoted and aren’t the easiest things to figure out initially. But once you know how to use them, you’ll look like a pro.

Once you have your data in a spreadsheet (presumably exported from a tracking system) with your data in columns, with appropriate named headers you can begin. Preferably, it’s best not have any other data in the document that you don’t want in the pivot table. Don’t worry about any formulas like CTR that you might need, you can insert those later in an instant.

Click into the top left hand of the file (i.e A1). Now from the data menu, find and select your pivot table option:

You will next be presented with the format screen. Standard options are ok, so just click Next. You will now be asked for your data range. Ideally if you’ve followed these steps, all you need to do is click Next. However if you have other data you don’t want in the table in the document. Reselect your povot table range. Note: If you are not in the uppermost left corner to start, the program often can’t recognize what you’re trying to do and may give you an error message.

Now you’re asked where you want to put this file – in the same sheet, or a new one. Make sure you choose the new sheet, otherwise as you play with the data, you may run into issues with your original data. Don’t click Next just yet! You’ll want to set up (or partialy set up) your layout. Once you clicked the layout option you’ll be presented with a screen that looks like this:

On the right-hand side you will see your column headers, these are fields that can be dragged and dropped into various field layouts. I’ve used an example for online advertising here. Ideally if I want to look at how my ads did per site, I will drag and drop the “website” field into the “Row” area. From there, I’ll be choosing the data I want to see in the data column. I’ve chosen and dragged impressions there now. Now I click OK and then finish. And voila, I get something that looks like this:

I can now see how many impressions I’ve had by website. But what if I want to see impressions and clicks? Simply drag the clicks box over to the data area. Hmmm This doesn’t look right does it? It’s stacked it on top of one another. How do I lay it out in columns? You will need to click on the “name” of the column header (in this case, “Sum of Clicks”) until you see the “movable” four-way arrow. Drag and drop. As you pick up the column, you will notice a little “ghost” box appear that looks like your current pivot table layout. The blue area shows where you are proposing to move your field to. You want to move it so that it is in “subhead” box on the right hand side.

Now that looks better. Any other fields you drag and drop over will follow that order/layout so you won’t have to do that each and every time.

What if I need to determine what my CTR was and I don’t trust my own math/cutting/pasting/manual function skills? Never fear, you can add formulas here, that will repeat for all the data in your column without having to manually do anything once you’ve entered it in the first place.

Using your Pivot Table Tool Bar, click the Pivot Table drop down menu until you see the Formula option, from there, choose the “calculated” option on the flyout menu.

Follow the instructions, give your formula a name – like CTR, then choose the metrics and formula you need – for example clicks/impressions. Click ok and your new column should appear with all data calculated for you. If it doesn’t appear, you’ll notice that it is in your pivot table field chooser box and you can simply drag it over.

Ok – great, so what you say? I can do this with a data sort and manul adds. Of course, but what you can do now – especially if you had a lot more data to work with – placement on page, targeting options, etc etc – is drag and drop various fields to show the data’s performance. For example, maybe I don’t want the “row” area to have “websites” as the filter option. Perhaps I want to see which ad size did best? And Maybe I want to view which ad size did best by which vendor -well I’ll now add vendor as a second option to the “row” area and voila, I have each ad size showing which publisher ran what and how it performed.

I can now start to optimize my media plans with my agencies/media teams/publisher based on how something is performing pretty quickly and efficiently. I can mix and match different combinations without having to change formulas or layouts.

What I can’t do is format the pivot table itself – otherwise the formatting would mess up every time I wanted to change it. So you’ll need to get the combination you want, and then do a simple copy and paste in another worksheet and format for size, colour, shading etc as you see fit.

In terms of the column names, the default is “sum of” so that you will get totals, you can change the “field name” to remove sum within the pivot table, but I prefer to do it in the worksheet as I format other areas. The reason is that I can also choose my field settings, in case I need to show averages of my data, counts or other functions for those numbers.

As you can see, I’ve barely touched the basics here on how to create Pivot Tables, there’s all kinds of customization and tips/tricks you can learn. I suggest watching the Demo from Microsoft itself.

Pivot tables are a wonderful way to sort and filter your data easily which allows you to look at your data in many different ways. You can really use it to start looking deeper and trying to find patterns or trends, even seasonality comparisons.

Put simply, pivot tables hep make the world of data analysis a lot less scary.