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.
