The 10 Reports that Made Me Fall in Love with Pivot Tables
December 10, 2012
Lots of crazy stuff has happened in PPC during 2012. Even though that’s been true pretty much every year since its launch, we at PPC Hero felt that it was a big enough year to justify its very own series. That’s why for this month’s series we’re taking on the Biggest Thing in My PPC Year. Whether it’s tools, product releases or changes to our way of thinking, we’ll be covering the biggest thing that’s changed our work lives/brains this year.
Dr. Seuess said that “you know you’re in love when you can’t fall asleep because reality is finally better than your dreams.” So what that it doesn’t rhyme? It’s nice, right? It’s also true. I lost a lot of sleep this year, lying awake with a smile on my face. Reality was better than my dreams. I’d found pivot tables and my world was rocked. It’s a pretty straightforward tool within Excel that allows you to transform mounds and mounds of data into something comprehensible and actionable with almost no time at all.
In a video blog that marked my first ever post to PPC Hero, I talked about the basics of pivot tables and how you can use them in your accounts. In the months that followed, I frolicked with pivot tables in sun dappled prairies and learned a thing or two in the process.
With a special thanks to Brad “PPC Jesus” Geddes, I’d like to present a top ten list of my favorite analyses to perform with pivot tables.
1. Quality Score
This has been covered before by people far more qualified than I. It’s what’s started my mythical journey down the golden road to unlimited pivoting, so it has to be included. How does your performance vary by quality score? What percentage of your traffic is heading to what quality score? These are some huge questions, and you can answer them pretty quickly.
2. Best average position for your account
We all know that CTR and conversion rate will vary by which position you’re in. But using pivot tables you can get a much higher level of insight into what position is really working best for you. Segment your data to give yourself lots of granularity, and then use pivot tables to aggregate it to give yourself actionable lessons. I have the steps here. If your campaigns are a lot like mine, you may have an average position in the neighborhood of 3.2, but even though it converts regularly at that position at the campaign level, your keywords may be behaving very differently. Take a closer look.
This one has the added bonus of including another development from the year in PPC. If you download your search query performance, it will then show you the match type for each of the searches. SQRs aren’t the best to look at, but you can throw a quick pivot table into them and take a look at cost, conversions, CPL and whatever else you may need by match type, which will break out Phrase and Exact (close variants) for you. Google’s all about growing your volume, but through a quick pivot you can decide if you want that added volume. You also have the option to add in ad group/campaign to see where the biggest gains are coming from.
4. Ad reviews
Yet another topic that I’ve covered in a video blog. I use pivot tables to break out performance of headlines across networks, as performance can vary wildly across campaigns types or networks. It you take the extra step of labeling your type of ad (such as which benefit you’re touting) in addition to your campaign type (branded, non-branded search, display, remarketing, etc.) within your original set of data, you can see not only what messaging works, but where it works. This style of analysis can give you insights into consumer behavior that you can even apply to non-PPC consumers.
5. Landing page reviews
While some of the minor stuff in this point may be obsolete with Google’s recent incorporation of Analytics data into AdWords, there’s still a lot that can be done with landing page analysis with pivot tables. For non-Google engines you can check out statistics on user behavior via Analytics and then add it into your data set from Bing or Facebook or wherever else. You don’t even have to vlookup. Just add your relevant campaign/ad group/landing page info into the same columns as your report from the interface and then add new columns for the Analytics data. That way you can segment and manipulate your data however you see fit. (Make sure to remove spaces from your interface data so that stuff matches up exactly.) And now that Google has great new features with Analytics integration, you don’t have to manipulate your data much at all. Add in your columns, download and start pivoting.
6. Return on Ad Spend
All of my clients are lead generation so I need some time to pass before I can effectively measure how profitable those leads are. A few months down the road, however long the sales cycle may be, my clients can provide me with their backend data that I can easily sync up with costs across the engines. It changes a report that used to take full days, requiring all sorts of vlookups and sums and subtotals, and reduces it to one step. Similar to landing page reviews, I line up the campaign/ad group info for both sets of data on one spreadsheet. Then all I have to do is add extra columns with number of closed leads and their associated revenue. There are a lot of blank cells on my worksheets, but when it comes time to pivot they all go away, vanishing into the ether of progress. Add in one column for cost, one for revenue and then a calculated field to get what I’m after. For an added benefit I can also segment my report by month to see trends and also get a sense of the length of the sales cycle.
Dayparting can be an essential tool for getting the most out of limited budgets. At times, though, seeing what effect those changes have had can be a bit tricky. If you download a report segmented by both day of week and hour of day you’ll get a whole lot of data that would be tough to make anything of on its own. By adding day of week as the row and hour of day as the column, you can put CPL as a calculated field for your value. It’s a super quick report that lets you know if your dayparting is having the desired effect.
8. Comparing data across accounts
I’m on a handful of clients that have different sites that advertise slightly different products or to different geographic locations. As a result, there’s some overlap across keywords. I like to put all of my overlapping data into one big spreadsheet and then run a pivot off of it. What are my best keywords across all accounts? What’s working well for some but not others? It’s always a good idea to compare accounts when you have the ability, and pivot tables take it a step further by allowing you to aggregate performance across all sites, but then easily letting you add in location/site to your data to get a clearer picture of performance.
9. Placement performance on the display network
There are a ton of sites out there. Some of them may not pop up on your radar since they’re only getting a handful of clicks per month on the placement reports that you pull from AdWords. If you download all placements for a certain date range, you can run a pivot to see which ones could be leaking clicks and money. They don’t look insidious one at a time, but you can use pivots to add them up and save some serious money.
10. Search partner performance
You can segment by search partners in the interface, but Google doesn’t do the job of summing that up for you so you can see totals. With a quick pivot you can take a look at that in no time. It will be able to provide you with a macro-level of info, that you will then have the ability to dive in deeper with should you desire to do so. If you want to go next level, you can sync up your search partner performance with your backend data (assuming that you capture referring URLs in your systems) and then make another pivot off of what you’ve already pivoted. Russian nesting pivots!
This is a pretty long-winded article, but that’s how much there is to say about pivot tables, and you should know that I could keep going. I think you get the point, though. They are tremendous tools that have allowed me to think about my data and my analysis in a whole new light. They’ve definitely changed my PPC year.
Stay tuned the rest of the week for the biggest things the rest of our staff experienced this year! Anything you like to pivot? Anything that you have questions about? Let me know in the comments below.
Expanded Text Ads And Other Bing Ads Editor Updates
Pilot access for Expanded Text Ads is now available to Bing Ads users. Get access to the link and learn about other new features in the latest Bing Ads Editor update.
Keynotes from Bing & Google + the Hero Conf Advanced Track
Where do you go to get the most relevant information in PPC, plus the opportunity to network with the best and brightest the industry has to offer? Why Hero Conf, of course.
3 Questions You Need To Answer Before Beginning Paid Advertising
So you’ve you decided you want to start PPC advertising, but now you are asking, “Where do I start?” We've listed and answered 3 questions you should be asking.
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
Road Trip - 5 Tips For Creating International Campaigns
International markets can add complexity to any account, but here are a few settings and opportunities to make your global ad strategy stand out in non-US markets.
Discover 3 Levels Of Geo Targeting For Beginners, Intermediates, & Experts
Setting geographic targets is day one. Whether it’s to exclude places you cannot sell or serve or to specifically target your primary customer base, the implementation of geo-targets is on the must-do list.
[New Webinar!] What You Need To Know About New AdWords Features
In this new live webinar, Hanapin Associate Director of Services Jeff Baum and Optmyzr’s CEO Frederick Vallaeys will walk you through the new AdWords tools and show how you can use them to optimize your accounts.
The Social Strategies You Need To Know For App Promotion
Create social strategies to promote app awareness and leverage geographic and demographic data reported by these platforms to refine audiences.
Guide the Industry and Take the 2016 State of PPC Survey!
Each year, Hanapin Marketing manages extensive research into the State of PPC Report, which provides a statistical breakdown of the digital ad industry and guides expectations.
What's In A Name? Building Powerful Product Titles For Ecommerce
Over the years, we've tested a variety of different product title alterations in hopes of finding the perfect title. Did we? Yes, and no. The fact of the matter is, it depends.
What Upgraded Bing URLs Mean For You
Bing is continuing its effort to catch up with Adwords. Upgraded URLs, which isolate tracking parameters from final URLs, are now available to Bing advertisers.
Where Does Your Hero Conf Dollar (or Pound) Go?
5 Common Excel Issues And How To Fix
Excel, while a fabulous tool for PPC, is not without its occasional hang-ups. In this post, we’ll go through some common problems, and how to fix them.
[New Whitepaper!] Guide to Google AdWords: Advanced Edition
In this whitepaper, you will gain a greater understanding of the distinction between e-commerce and lead generation PPC strategies, learn the effective use of Remarketing, how to utilize advanced segmentation for improved targeting, cross-device attribution, and how to use automated rules in your accounts.