Pivot Tables Made Easy: The Last Demo You'll Need
May 6, 2014
The more PPC managers I’ve gotten to know, the more I realize that all nerds are not created equal. We talk of Analytics and Excel, and we use the term “pivot tables” a lot. And if you’ve ever tried and failed to create a pivot table, this post is for you. Yes, I’m using a Mac and yes, I’m working with Excel 2010. BUT there are valuable takeaways that apply universally, and we’re always happy to help troubleshoot any details with you! So buckle up and get ready to pivot!
The step-by-step will show you how to produce two types of pivot tables:
1) Mobile performance and 2) Display placements.
Spoiler alert: The heart of this article is about creating a pivot table. Feel free to apply this to all your favorite reports!
To evaluate how your campaigns are working by device, you’ll want to first pull a segmented report, which looks like this:
Step 2 is to do away with unnecessary rows of data. It’s likely that these are part of your problems in the past— pivot tables are finicky beasts that don’t want excess info. You should delete your report title and dates,
and the various Totals provided.
Step 4 is to grab that data. My favorite go-to method is to simply place my cursor in the top cell and select Data and then choose Pivot Table. This starts the ball rolling and as you can see below, you’re often given a few metrics to start with. These may not be what you’re looking for, so the next step is to choose what does go in your report.
Step 5: For this report you’ll want your primary columns to reflect your (surprise!) Device segment. Easy. But what data are you interested in examining? The volume of impressions for each device? Perhaps the click-thru-rate for each campaign by device? Let’s walk through how you’d build this out…
Step 6: Pull impressions. You want to always identify the proper time for “Sum of” versus “Count of” your data. The default is typically “Count of” so this is an easy place for you to get flustered. You want all of your mobile impressions to be summed, which means you’ll choose “Sum of Impressions” like this:
Step 7: If you want to pull the CTR for your mobile and non-mobile traffic, should you just pull this column into your table? Nope! Because your CTR is already calculated in your data, pulling the sum or count or even average of this data will inherently be a little wonky. Instead, use this opportunity to calculate your own CPL by device using the Formula option! And the beauty of pivot tables is that you don’t actually need the cost and conversion columns present in the pivot table to do this, you just need them somewhere in the data source. As a side note, if you are creating a new formula, it must have a unique title. I often opt for something like “CPL (calc)” which reminds me that I built the data myself.
As I mentioned, it’s highly, highly recommended that you calculate your own values when able. Your provided CTR, when averaged, can have a stark difference to what it actually calculated manually. On the left side of the table below are the CTR’s that I calculated for each device, based on the clicks and impressions from the report. The right-hand column is an average of the provided CTR. Clearly, they aren’t always adding up.
Ta-dah! You made your first pivot table! Now you can see how your performance fares by device on campaign, ad group, keyword, and account level!
So now that you’ve walked through this process, let’s crank up the velocity and try again with a Placement Report for your Display campaigns!
Step A involves pulling a report for your performance topic of choice. Here we’ll pull a placement report, which we hope will give us insight into profitable placements across various ad groups or campaigns.
Because I want to see what performs across multiple campaigns, I’ll pull every placement I can get my hands on. From this data, I want to see what ranks in the highest impression volume as well as a high CTR. I want to know what will get me results if I invest my time and money in this site.
Step B: Get rid of the junk. Pull the dates, pull the totals. And pull any unnecessary garbage that may have snuck into your report (such as inactive campaigns).
Step C is the big one: start a new Pivot Table! Let’s take a look at what placements are showing the most, receiving the most clicks per impression, generating the highest costs, and which are actually converting.
As you’ll note, I also included the count of ad groups where these placements were found. This gives me an idea of how frequently they appear repeatedly in various parts of my account. If they fall into an appropriate CPL for this account, I think I’ve found myself an opportunity for managing a placement!
There are literally hundreds of ways you can use pivot tables to benefit your account management. We PPC Heroes are constantly pushing the envelope, with exciting heat maps, pulling the curtain back on Average CPC, and a general “Best Of” list.
Whichever way you slice it, pivot tables are beautiful creatures with countless uses in the life of
data nerds PPC Heroes. What are your favorite ways to use pivot tables to dazzle your friends and clients?
How Should You Use Display Advertising?
We hear similar questions from clients on a regular basis. One question we hear often is “How do your other clients use Display Advertising?”
Expanded Text Ads: 7 Million Clicks Say They’re Underperforming
Over the past 6 weeks, we have been lucky enough to experiment with expanded text ads, and frankly, I’m not impressed with their performance.
£300 Off Hero Conf London Ends Tomorrow!
Time is nearly gone to reserve your seat for Hero Conf London, 24-26 October at etc.venues St Paul’s, at a huge discount off the regular conference rate!
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Expanded Text Ads Come To Google AdWords Editor
Advertisers have been able to create expanded text ads, but couldn't utilize AdWords Editor. That has now changed with the release of the latest version of AdWords Editor.
Bidding Farewell to Adwords Converted Clicks: Don't Panic
AdWords will soon be saying goodbye to the "Converted Clicks" metric. Learn more about what this means for your account and how to prepare for the transition.
Olympic PPC Ad Strategies
How spectators consume sporting events and a massive expansion in the digital space will prove to shake up who and how advertisers reach their audiences.
Hanapin's PPC Resources Are Now Ungated!
You can now access Hanapin's whitepapers and toolkits without having to fill out a form!
View Ad Creative Through The Eyes Of Your Searchers
Learn to understand that ads do not live in the vacuum of an excel sheet and common practices should be tested.
[New Webinar!] 11 Red Flags To Watch Out For When Working With An Agency
Leveraging Analytics For Remarketing: Picking The Ripest Fruit
Remarketing prevalence is well founded based on competitive returns in both volume and efficiency. With that said, this "low hanging fruit" world isn't always easy to pick.
How To Analyze Time-Of-Day Performance For Luxury Products
The importance of ad schedules varies across industries, services, and products. For e-commerce companies that sell luxury goods, ad schedules are more crucial.
One Week Left to Save 25% at Hero Conf London!
You still have time to join us for Hero Conf London, 24-26 October at etc.venues St Paul's at 25% off the conference price. But time is running out.
4 Common Questions Prospects Ask Me During The Sales Process
There’s always new, hot topics and questions we get asked by prospects to see what our company’s take is, and how we go about implementing from a services perspective. Read what our most common questions are.