Advanced Excel Tips for PPC Managers

Today’s post is by 3Q Digital Search Account Coordinator (and Excel whiz) Spencer Fair. You can catch 3Q Digital’s expert search marketers on the Bruce Clay, Inc. blog every other month, and we on theirs, in our ongoing search blog partnership. Now, let’s get to some advice on advanced Excel reporting.

There are many useful Excel tips for PPC out there and even more general Excel help forums offering all sorts of suggestions for formulas, reference sites, etc. It’s all great stuff, but one of the things I’ve had trouble finding lots of info on is reporting templates for pay per click campaigns.

For those savvy in Excel, it’s fairly easy to use the formulas and tips you find online to build yourself a clean and nice-looking report. Building out sheets for each variation and request and manipulating the data for each report, though? That’s a major time sink. But rather than pay a third party to make your reports for you, which costs lots of money and adds a potentially clumsy middleman, you can learn to do automated reports yourself and become the office hero.

Learn what a good PPC report looks like and discover formulas that will unlock a whole new world of Excel wizardry.

Reporting Templates for PPC

Very often I see reports with long scrolling sheets full of data tables from past months/weeks/days:

This never-ending sheet of past data feels messy and unprofessional to me, and I always want to provide the client with something cleaner and more presentable. Why not build out a template that can be used universally on most client accounts with a vast range of data that a) looks nice and b) condenses the data in a much more digestible manner, both for the ease of use for the client as well as for internal account monitoring?

The report should be easy to update and still look nice with little work needed on upkeep:

For this report, you dump your data into a hidden raw data sheet that is leveraged onto a table with drop-down menu choices that will adjust the tables’ data accordingly. In this case, there is just one table with multiple week choices, removing the need for a long sheet of messy tables.

Formulas for PPC

I’ve always felt macros can scare away a lot of people from Excel once you start needing to use visual basic, so my goal was to create a universal (with some rare exceptions) report that could easily be adjusted to each client but, once set up, would offer a quick and painless way to generate the weekly/daily/monthly reports that usually take hours of time each time you update. Internally, we’ve recognized the need for some time; one of my colleagues, Jay Stampfl, has also been hard at work making reports better, faster, and more digestible (for our company and the industry at large).

Most people with experience in Excel and PPC have the formula knowledge to build and maintain a report template. There is a fairly steep learning curve, but once you get past that initial step, you have some very powerful formulas at your fingertips that can do a lot more than many think they can do.

One formula that is undervalued/underused is the =if(_,_,_) formula. When it is used, it is usually in its most basic form (a yes or no formula). It is commonly used as a checker formula – but why not use it for more with the help of some other formulas?

=IF(ISNUMBER(SEARCH(“Brand”,campaign cell)),”Brand”,”Non-Brand”)

This searches in the campaign name for brand; if it finds it, it will return Brand, and if it doesn’t, it will return Non-Brand. This still only returns two values (yes or no), so why not use it for more?

For example, let’s pull product type out of a campaign name:

=IF(ISNUMBER(SEARCH(“product A”,campaign cell)),”Product A”, IF(ISNUMBER(SEARCH(“product B”,campaign cell)),”Product B”, IF(ISNUMBER(SEARCH(“product C”,campaign cell)),”Product C”,……)))

This formula wraps “if” statements inside “if” statements to allow you to get more than a yes or no response. It allows you to fully parse out data you have embedded in campaign names, etc. (using naming conventions), which is a very powerful tool if you are trying to build out an automated report that just uses data you downloaded from Google.

Leveraging these formulas in a raw data sheet, you can parse out a vast number of funnels for you to filter the data, allowing for a much better view into the account for both you and your client.

Understanding that you can put formulas inside other formulas you use on a regular basis opens a whole new world of data manipulation. For example, you can use a vlookup inside a vlookup to dynamically/automatically adjust which column of data is pulled.

Now for that steep learning curve I mentioned earlier:

How do you leverage all these formulas parsing out segments and calculating data on a raw data sheet into something professional with drop-down menus that adjust the data?

The core Excel functions used to do this are:

• A table (for the raw data)
• Name manager (automatically adjust the data ranges in the raw data table)
• Data Validation (the drop-down menus to filter the table of data in different ways) and
• The Sumproduct formula (the formula that pulls the data together into on clean table)

Name manager allows you to name cell ranges and refer to them by the name; this helps you manage the data and easily refer to those ranges in formulas:

If you set a Named range to a table, the range will auto-adjust as you add or remove data from the table. This is why the raw sheet is built in a table.

Data Validation is used with a hidden List sheet that allows you to have drop-down menus with each filter option available (parsed using the if statements):

You can use some formulas in the data validation option to even have the drop-down lists auto-adjust as you add or remove from the lists sheet:

=OFFSET(Lists!$C$2,0,0,COUNTA(Lists!$C:$C)-1,1).

This formula helps to prevent large amounts of blanks from appearing in the data validation (drop-down) menu.

These formulas are a good start when it comes to building report templates that can be used across clients/accounts; they will definitely help in making production and reporting more efficient, manageable, and consistent on a larger scale. For even more Excel tips and tricks, I recommend Chandoo, a site that provides tips, tutorials, examples, and downloads for Excel.

Have questions? Leave them in the comments. Or, call BCI for PPC services to help manage your ad campaigns.

Spencer Fair is a search account coordinator at 3QDigital and an Excel whiz that saves his colleagues many (many) hours of Excel-wrangling time.

See Spencer's author page for links to connect on social media.