3 Essential Excel Formulas to Automate PPC Reports
April 3, 2018
This guest post is brought to you by Jason Pellissier, Paid Media Manager at Nebo. See Jason live at Hero Conf Austin, April 16-18.
The daily life of a PPC manager can be summed up with one word: Reports. Day of week reports, hour of day reports, keyword reports, the number seems to always be increasing with less and less time in the day to actually do them. Because time spent pulling reports gets longer, the time spent analyzing them gets shorter.
While working 12 hours a day may seem ideal for some people, most of us would just prefer for these reports to create themselves. That’s where automating Excel reports can help. With a few simple formulas, the data can be pulled and organized, ready for you to do your analyzation magic.
While there are multiple tools to create automated reports, such as Supermetrics and Tableau, many of them have a learning curve that most of us don’t have time to learn. In this blog post, I’ll show you 3 easy to learn Excel formulas that can help automate almost any report. The best part is that you can start implementing them into your daily routine immediately.
One of the most useful formulas in organizing and automating reports is the text formula. Not only does it allow you to reformat data, it allows you to extract only the information you need from a cell.
Take the following scenario. You have your campaign data segmented by day of the month and you want to perform a day of week analysis.
First set up a new column for Day of Week, then just enter in the Text formula using “dddd” as the format value.
Now the data can be quickly and easily pivoted out to create a Day of Week analysis and next time you need to create this report all you have to do is paste in the new data.
A more powerful version of a Sumif, a Sumifs allows you to sum data based on multiple criteria.
Let’s take the previous example a step further. Let’s say we now need to know how much the campaigns spent on Tuesdays each month.
To set up the formula, first choose the sum range, followed by the first criteria range, then the first criteria, and so on for as many criteria as you have. In this case, our criteria will be the campaign name and Tuesday.
Now you have a quick report you can send to the client each month.
Now that we know how much each campaign spent, what if we want to know how much a group of campaigns spent? That’s where wildcard characters come in.
Not technically a formula, wildcard characters are special characters that can stand in for unknown characters. While there are multiple different wildcard characters, the most useful is the “*”.
Let’s say we have our campaigns broken out by region and we want to know how much the Texas region spent on Tuesdays.
Since there are multiple campaigns in the Texas region, we need to sum every campaign that contains TX. In order to do this, we can set up the same Sumifs equation as before, but instead of using a campaign name, we can substitute in wildcards.
By inserting “*TX*”, Excel will count any campaign that contains TX with any number of characters coming before or after.
There’s an unlimited supply of uses for these formulas that can be applied to almost any report you want to create. Not only can they save you time but they can also be used to look at data in different ways and help find new optimization opportunities.
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 »