Have you discovered the magic of Excel pivot tables? As PPC managers, we are up to our elbows in data, and pivot tables are an excellent tool for evaluating large amounts of information and reviewing campaign performance. Using pivot tables can and will save you time, but there are a few quirks that will affect how data is calculated or displayed that you should be aware of.
Here are a few of my favorite pivot table tips and quick reports that will help to evaluate campaigns and help them to be efficient:
Remove Total Rows
Most reports pulled from Bing, AdWords or Analytics will have a total row that sums up the data, and you don’t need it; so get rid of it.
Instead of scrolling through thousands and thousands of rows to find the total, simply press Ctrl + End and Excel will take you to the last filled cell in the spreadsheet. Next, delete the row, then use Ctrl + Home to jump to the top of the spreadsheet. BAM! It might sound like a no-brainer, but sometimes when you are in the deep end of a data pool, quick tricks will help to keep your mind focused on what you are trying to learn from the data, instead of how to get there.
Return on Ad Spend Report
I have a mix of e-commerce and lead generation clients, so I need to measure return on ad spend based on actual revenue, as well as closed leads based on varying associated values.
Using a pivot table, it’s easy to look at spend on channel, campaign and ad groups levels, then add in columns with the sales amounts and closed leads, and finally a calculated field for return on ad spend. Since this is an ongoing report, I also segment the data by day or month to see trends. Check out this helpful post with tips to segment pivot table data by month.
Day Parting Report
Along with return on ad spend reports, day parting is essential for maximizing clicks on a limited budget as well as meeting cost per conversion goals.
There is a lot of insight to be gained from reports segmented by both day of the week and hour of the day, and by adding a calculated field with the average cost per conversion you will be able to make informed adjustments to both bids and schedules.
I would love to hear about your PPC pivot table adventures! Share below in the comments or tweet us @ClixMarketing!
