As a recent convert to pivot tables for analysis of your PPC Account and the fact that it has been the most requested guide after the training course at Biddable World, I thought I’d write a post to explain what they are, how they work and how to use them to analyse your PPC Data.
I will happily admit that when things get a bit ‘too maths’ I run to the hills (or start crying), but stick with me and I assure you that you’ll get the hang of pivot tables in no time!
What are Pivot Tables?
According to Wikipedia, a Pivot Table is as follows:
Still with me? Great!
Take a deep breath, and let’s extract some data from our Adwords account and manipulate it in excel to help us optimise our spend.
What you’ll need?
- Data from your PPC Account
- A copy of Excel
- This post open next to your excel spreadsheet 🙂
Pivot Table to Analyse Quality Score
There is no point in re-making something that is already a great resource so if you haven’t already done so, check out this fantastic Brad Geddes video showing you how to look at ‘pockets of opportunity’, how to segment out relevant data, download, and manipulate data to help you gain insights using columns, filters and pivot tables.
The ‘Sweet Spot’ – finding the most profitable position
You don’t have to be in the top slot to get a decent return on investment. In some cases, being at the top can cause too many impressions and still not enough clicks so could also damage your CTR and your overall quality score.
So if you want to save some money or make a case for spending more – the ‘Gerald B Watson Memorial Guide to finding the sweet spot’ video from Sean Quadlin at PPC Hero is a good place to start.
Other Great Pivot Table Resources
The most comprehensive guides to pivot tables that I have found are as follows:
- http://www.ppchero.com/how-to-use-pivot-tables-for-ppc-data/
- http://www.marketmotive.com/training/tutorials/ppc-and-paid-search/pivot-tables-and-quality-score.html
- http://www.getfoundfirst.com/blog/ultimate-visual-guide-pivot-tables-ppc-data/
- http://www.beyondthepaid.com/5-ways-use-pivot-tables-ppc/
- http://searchenginewatch.com/article/2180415/Downloads-Pivot-Tables-for-AdWords-Analysis
- http://searchengineland.com/how-to-excel-at-excel-for-sem-applications-part-5-master-pivot-tables-22684
Takeaways
There are unlimited amounts of data to crunch so start downloading and practise pivot tables every chance you get. Not only will it impress your boss (or your potential new boss!), it will help you get the most out of your PPC data with minimal effort. It certainly beats trawling through screens and screens at a glance, and the great news is, you do not have to re-make the pivot table each time – just replace the data and re run!
When you are using pivot table data – remember:
- This is an analysis of data so keep things simple
- Use different Segments of data to see the overall picture (eg weekly vs monthly)
- Use filters in the Adwords Data download to get more precise analysis
- Practice – it took me several goes before I could do it off by heart
- Re-use the table – exchange data and re-run – so set up a table for every type of data you want to run in advance
If you don’t have any data but would like to figure out the tables – contact me and I will pass you a copy of dummy data that you can use to try crunching