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.
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
4 Tips on Generating Leads for SaaS Clients
SaaS clients have a stereotype of moving really fast and want quick results. Here are 4 of my top tips on how to generate paid search leads through AdWords.
Why You Should Be Using Facebook Canvas Ads
Explore four reasons why you should test canvas ads on Facebook.
The Future of Ad Copy in an AI World
In this webinar, AdFury’s Christopher Hoover and Hanapin’s Lauren Rosner will discuss the hot topic of AI and what the future could look like when automation becomes more and more popular in our industry – the good, the bad, and the challenging.
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
Reasons to Not Use an Automated PPC Bidding Strategy
There are so many situations when it is a good idea to test out PPC Bidding Strategies on campaigns. In this article, we are going to discuss different reasons why you might want to avoid using automated bid strategies.
Take the 2018 State of PPC Survey!
We're looking for marketers to take our State of PPC survey! The survey takes about 5 minutes to complete and establishes benchmarks, identify trends, and helps marketers around the world understand what's working and what's not in PPC.
How To Avoid Overlap Between Multiple Similar PPC Accounts
Get tips on how to avoid overlap between multiple similar PPC accounts and start becoming more effective at running ads for multiple or related brands.