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.
Text Formula
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.
Sumifs
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.
Wildcard Characters
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.
Wrapping Up
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.