3 Essential Excel Formulas to Automate PPC Reports
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
Moving Pieces: Thinking About PPC Strategy
Strategy. What is your strategy? As an agency, our team responds to that very question on a daily basis. How do we respond?
You can’t have an outstanding team without an outstanding culture
There's a reason why our team thinks we are a great place to work and no, its not because we have a ping pong table set up. See more about Hanapin's latest certification + we'll let you in on a little secret!
Tracking UAC Performance Through Third-Party App Analytics
Explore how to link and import app downloads and in-app events in Google Ads through third-party app analytics providers.
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
The 2019 Budget Season Doesn't Have to Be a Nightmare
You could just go off of a formula and call it a day, but in order to have a solid budget in place you need to take the time to prepare.
How to Build an In-Platform Facebook Ads Funnel
Facebook has many tools to help you build your funnel right in the platform, and to nurture that funnel without taking users off-platform at all.
How We Gained $21,000 More in Revenue by Changing One Color
Testing button color, or the color of any element on your page can be extremely rewarding - if it is done correctly. See how you can turn a simple test into drastic results.
Load More »