Many marketing gurus and job ads mention pivot tables as a 'must have' skill.  

But guides on how to use them are usually too general. Here's a specific example of how - and why - a digital marketer would use pivot tables.

As a digital marketer you are often faced with the task of making sense of log files. But log files are a blessing and a curse.

A blessing in the sense that they capture everything, but a curse in the sense that we are then expected to turn hard-to-read data into organized reports.

One type of log file which we often find ourselves converting is the campaign data from a social media platform

Typically, these are a organized with each row being a single date - and all of the campaign data as columns.

But that is often not the way you want to see things. It is much easier to read the report when the campaign is the left-most column and the dates are on the top. Then we can easily see how our data has changed daily for a particular campaign.

Of course this is possible with some fancy transposing with Excel, but thankfully there is another, simpler way to do it.  And that is precisely what pivot tables are for.

What I'm using

To make this tutorial clear, I'll stick to using the following: 

  • Log file: The file I'm working with is from the Facebook ad campaign report - which you can find at Ads Manager / Reports. Any daily ad report will work, though.
  • Data: I'm only going to use dates, campaigns, ad impressions, and click-through rates (CTRs) - so those will be the only columns.
  • Spreadsheet: I'm using Excel, but you could equally use the excellent - and free - LibreOffice.

Getting started

First off, export your data from Facebook and load it into a spreadsheet.

Now when you're about to work with your data, it's good to strip it down to just what you need. You can always go back to the source later and add more.

Okay, so your data should be in the format date, campaign, impressions, and CTRs - and it's helpful to put headers on the columns.

What we're going to do

We are going to:  

  1. Change dates from being all in one column to being all the columns.
  2. Change campaign name from being all in one column to being one campaign per row.
  3. Move impression numbers to the corresponding date column and campaign row.

 And here is what will happen to our data:

  1. The dates will become columns. 
  2. The campaigns will become rows.
  3. The impressions will correspond to the date they are under and campaign row they are in.

That is the essence of the 'pivot' - and all you need to know for this tutorial.

How to do it

Select the whole table of data you are going to work with

And then select 'Insert/Pivot Table'.

Choose a new worksheet as it will be clearer when we do the manipulation.

Now you should be presented with a somewhat confusing page which tells you to build a report. If you don't see Excel as below, then make sure you're on the right tab.

Luckily we already have the model that we need to follow - and it is almost as simple as just following the instructions:

1. Click on 'Date' (the word, not tickbox) and drag it into COLUMNS. You now have all the dates in your table as columns.

2. Click on 'Campaign' and drag it into ROWS. There we go - our campaigns are now the rows.

3. And finally drag 'Impressions' into VALUES.  

  

Voilà - you can now see your campaigns lined up on the left and see the impression values as columns. Much nicer than sorting by campaign and then by date, right? 

And so...

Pivot tables offer you a great way to change long and hard-to-read log files into a nice report suitable for managment consumption. Of course there are many other things you can do with the pivot table - including interactive graphs - but we'll leave that for a future post.

In the meantime, please let me know if you have any other insights in the comments - any other typical uses, or tips and tricks. 

And finally, pivot tables are an incredibly useful tool for performance marketing - so, like it or not, they are here to stay!

If you're looking to improve your analytics skills, book yourself onto Econsultancy's Web Measurement & Analytics Training Course.

Jeff Rajeck

Published 11 September, 2014 by Jeff Rajeck

Jeff Rajeck is the APAC Research Analyst for Econsultancy . You can follow him on Twitter or connect via LinkedIn.  

237 more posts from this author

You might be interested in

Comments (6)

Comment
No-profile-pic
Save or Cancel
Avatar-blank-50x50

Scott

wrong wrong WRONG!!! ugh........

AVERAGE still summs data cells then divides by the number of entries. You need to add a calculated cell and include clicks in your data set.

PivotTable Tools > Calculations > Fields, Items & Sets > Calculated Field.

Also worth pointing out you can't SUM Reach.. I see this all too often especially with Social reporting.

almost 4 years ago

Jeff Rajeck

Jeff Rajeck, Research Analyst at EconsultancySmall Business

Ack you're right - you should never average CTRs. Thanks for pointing that out.

And agree about reach, though impressions should be fine.

almost 4 years ago

Avatar-blank-50x50

Scott Spooner

Thank you for this clear and concise walk-through of the pivot table. They have baffled me to the point where I avoid them. No longer!

Now bring on those log files...

almost 4 years ago

Jeff Rajeck

Jeff Rajeck, Research Analyst at EconsultancySmall Business

Glad to hear it!

I think once you get over the initial hurdle, they can really help with presenting useful information to people outside of marketing.

almost 4 years ago

Avatar-blank-50x50

John

Great analysis Jeff and I have to say that Pivot Tables is my favourite Excel tool and very very easy to use, its just that lots of people havent had the right training.

I have some free videos that your readers can view that expands on your blog, there are so many things that you can do with a Pivot Table that will astound you and give you the power to become a better informed analyst.

Here are a couple of free videos that I have put together:
https://www.youtube.com/user/LearnExcelPivotTable/videos

Cheers,

John

almost 4 years ago

Avatar-blank-50x50

digital marketing agency India

Nicely explain about pivot table.the content is fascinating, topical and useful. Thanks for sharing.

almost 4 years ago

Comment
No-profile-pic
Save or Cancel
Daily_pulse_signup_wide

Enjoying this article?

Get more just like this, delivered to your inbox.

Keep up to date with the latest analysis, inspiration and learning from the Econsultancy blog with our free Digital Pulse newsletter. You will receive a hand-picked digest of the latest and greatest articles, as well as snippets of new market data, best practice guides and trends research.