Creating Better Client Facing Reports With Excel
November 16, 2015
One of the most effective ways to relay information to your client is via Excel. The plus side is that it’s easy to pull data from most PPC platforms into Excel. The negative side is that it’s not so easy to make this data easy to digest.
By taking the time to create a template that you can plug data into, you can save precious time that you can spend on analysis and strategy. As a bonus, you’ll get some nice looking graphics that you can send along to your client or boss.
Auction Insights data from AdWords is a great resource for determining who your competitors are. Unfortunately, it doesn’t download into Excel is an easy-to-graph format.
Create a template that uses VLOOKUP and OFFSET functions to map out impression share for yourself and your top competitors. Combine this data with an average CPC data from the dimensions tab to map out how the competition is impacting your costs.
Once you’ve got the data to auto-populate into the correct format for graphing, you can create your graphs (which will also auto-populate when new data is pasted into the workbook).
Consider using line graphs or area charts (shown below) to illustrate impression share amongst yourself and your competitors.
Or combine both graph types!
In this particular example, we see a competitor (pictured in orange on the impression share graph) enter the space and take up a large chunk of the impression share. At the same time, as our own impression share decreases, average cost-per-click rises.
The graphs provide a nice visual for the client to look a, while allowing you to formulate a strategy to become more competitive.
When running an ad test, it’s important to reach statistical significance before concluding the test. This simple template allows you to copy and paste data from the dimensions > labels > ads tab, and calculates statistical significance for you. It also automatically updates a set of graphs to show impressions, clicks, and CTR for both variations.
Now you’ve got a nicely laid out snapshot of the ad test, and you can easily include the actual ad copy if you’d like. In this example, you can clearly see that the control is the winner with 96% significance, resulting in a 41% increase in CTR.
An ad writing template that includes length counters and potentially conditional formatting is pretty common, but it isn’t quite so easy on the eyes.
If your client likes to review ad copy, some simple OFFSET functions on the next sheet will format your ad copy to mirror what it’ll potentially look like on the SERP.
Here’s some detail behind what’s going on in those cells:
Time Of Day Analysis
Time of day analysis can be great for bid adjustments, and can also provide great information for your client. While a heatmap is a great way to visualize performance across days and times, a scatterplot works nicely.
Create a pivot of conversions by day and time of day, then highlight the data and created a smooth marked scatter plot:
Once you have the chart formatting to your liking, simply copy and paste a different pivot for your respective accounts/campaigns/etc.
Using Excel templates is not only a big time saver in your analysis, but also can provide great visuals for your clients and colleagues. It’s worth it to take the time to create a good template, rather than to do the same charts/graphs over and over again for all of your different accounts.
Browse By Category
Personalized Training to Become a PPC Superhero
If you could divulge your biggest challenges in paid search advertising to an expert who is highly revered by industry peers, approved by 15 US & UK agency leaders, and a driving force in PPC innovation, would you?
Leveraging Custom Intent Audiences on YouTube
Google recently announced the addition of custom intent audiences for YouTube. Structure audiences & content to build goodwill with your prospects!
My Top Failures as a PPC Account Manager
Explore Will Larcom's 5 biggest shortcomings as an Account Manager and the takeaways unearthed by being able to identify them!
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
Why You Should Test Target ROAS Bidding for Shopping Campaigns
The case for testing Target ROAS bidding for AdWords shopping campaigns, plus tips on how to successfully set up such a test.
10 New Ways to Optimize Paid Search with Call Intelligence
Mobile search drives billions of calls to business each year, and calls convert at a higher rate than digital leads. When properly optimized, calls can have a transformational impact on your bottom line. Join this webinar to learn tactical tips and smart strategies to boost...
Google Adds New Tools to Highlight Physical Store Locations
Buying in-store is still a big deal for shoppers, and Google has announced a few new tools to let physical retailers target those shoppers heading in stores.
Load More »