The 10 Reports that Made Me Fall in Love with Pivot Tables

By ,

154 SHARES

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.

3. How close variants are affecting your volume

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.

7. Dayparting

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.

Get more weekly links with our Fast Five newsletter! Five Fast Links in Your Email Every Friday.

Also send me a daily RSS digest
Twitter Facebook LinkedIn Google+ Email Print More
  • http://twitter.com/erikford Erik Ford

    Sean- Great stuff. I’m sure you’d love tableau and would further automate your workflow here. Have you checked it out?

    • http://www.hanapinmarketing.com/ Sean Quadlin

      It looks pretty cool, but I’ve never used it. Thanks for passing along the tip!

  • Brian Swanick

    Your love of pivot tables is adorable, Sean. Analyzing average position this way seems like a no-brainer now and I wish it was a party trick that I could show off to my friends. They wouldn’t be as impressed, their loss.

    My question is this: do you use a general, minimum number of clicks/impressions for the data to be valid? It’s great to be able to break down the data in such a way, but is there a numerical point where you don’t feel comfortable basing decisions off of such a small data set? My experience with geo-labeled campaigns is that they tend to be at too low of a volume already to make educated decisions when you pivot them by average position.

    • http://www.hanapinmarketing.com/ Sean Quadlin

      Brian, I’m so happy that you appreciate my puppy dog love.

      Statistical validity is absolutely a concern when doing any sort of analysis like this. I use the Teasley test all the time (http://www.splittester.com/) so I can tell when I’ve got it. We’ve also had a friend of PPC Hero send us a pretty cool plug in that does this for you (https://addons.mozilla.org/de/firefox/addon/split-test-calculator/). If you wait too long, though, you’ll run into all sorts of other variables clouding your judgment. In cases where the campaigns are small, I like to clump smaller campaigns/ad groups together to get to statistical validity. It isn’t perfect, but it at least allows me to move my accounts forward. For your geo-labeled campaigns, think of how those geo’s could be grouped together so that your data paints a clearer picture. Regions? Population densities? Something that makes sense for your accounts/users that also allows you to see enough data.

      • Brian Swanick

        Thanks for the tips, Sean. Much appreciated!

      • http://twitter.com/GnosisArts Gnosis Media Group

        That Splittester tool rocks the cabasa. Thanks for a really useful post, Sean!

        • http://www.hanapinmarketing.com/ Sean Quadlin

          Glad you liked it! Thanks for reading.

  • Steve

    Hi Sean,

    Great article, you’ve inspired the love of pivot tables within me.

    My question is can you give some more direction or the exact steps you take for #9.

    I’m not sure how you set up your pivot table with GDN data and then what you look at and (most importantly) the actions you would take to maximize and optimize the display network.

    Thanks!

    Steve

    • http://www.hanapinmarketing.com/ Sean Quadlin

      Hey Steve,

      I’m glad you liked the article. Thanks for reading!

      For #9 I was talking specifically about placement reports. You would download a placement report across all campaigns. Then run a pivot off of that report, adding domain as your row and clicks/cost/conv. as your data. You can then see how placements are doing across your entire account, instead of just ad group by ad group. You could also add in campaign to your rows to add campaign level exclusions.

      Hope that helps!

  • http://twitter.com/DerekOstler Derek Ostler

    Do you have any more reports that you like to run? Or a link to a place with some more that are useful like these ones?

    • http://www.hanapinmarketing.com/ Sean Quadlin

      Hey Derek,

      I’m of the mindset that you can pretty much use a pivot table to end all wars and world hunger, so there are lots of options for you. Basically, if you take a look through the different segmentation options in AdWords (including labels) you can pivot with all of them. There are also a ton of option on the dimensions tab. I don’t know of an exhaustive list, but you should take a look through all of the available segmentation, add multiple segments at one, and then pivot away.

      If you come across anything particularly fun, let us know! Thanks for reading.