Pivot Tables Made Easy: The Last Demo You'll Need
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?
Browse By Category
The New World Of PPC Targeting
As more steps of the customer journey have moved online and with the great shift to mobile, we shouldn’t be stuck in a last click, myopic view of our marketing.
Apple Search Ads: What You Need To Know To Improve Performance
Seven key learnings and optimizations that you can implement to improve your Apple Search Ads performance.
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
You’re Bidding On All the Wrong Keywords! (Here’s How To Fix It)
Keywords are arguably the most important part of a paid search campaign. Make sure you are bidding on the right keywords, not just the ones bringing in traffic.
Your PPC Golden Ticket to Hero Conf LA
The new year is here! And as an avid reader of PPC Hero and friend of Hanapin Marketing, we'd like to say thanks by offering up the chance to win your way to Hero Conf Los Angeles, April 18-20, 2017, on us!
Work Smarter, Not Harder - Using Google Sheets for PPC
Google Sheets is great for reporting, dashboards, and even to-do lists and status docs. Learn how to get your data into Google Sheets and how to manipulate it.