March Madness Continues: What Excel Tool/Function Rules Them All?

In light of the March Madness season it was thought that we could have some fun and review some of the previous PPC Hero articles on the best ways to utilize Excel in order to more efficiently and effectively manage PPC accounts.

First, let’s choose the seeds for the respective functions. There are four regions just like in the real tournament for the Excel Tools/Functions tournament.

Regions

• Data Tools
• Advanced Functions & Formatting
• Simple Functions
• IF Functions

Data Tools Seeding

1. Pivot Tables – Seemingly the king of Excel, Pivot Tables had to get the one seed here. Reporting is probably the top reason to utilize a Pivot Table, but there are many different ways a Pivot Table comes into play when dealing with PPC data. Even in 2012 – the value of Pivot Tables couldn’t be denied as almost every reporting touchpoint could be done utilizing a Pivot Table. More and more reasons have risen to the forefront on why Pivot Tables are a key to successful PPC management. From advanced SQR tactics to finding unprofitable themes in your account, Pivot Tables have been the top dog for reporting for a long time.
2. Solver – The solver tools is becoming used more often and is an amazing tool for projections. The Solver can be used to project based on ROAS Goals, Budgets, CPL Goals, etc – and also can be broken down by month, by category, etc in order to budget and project out accordingly to availability using impression share metrics. Macros can also be utilized with the Solver to find multiple solutions. Using Solver to set campaign budgets has been a common tactic for some time – but using advanced tricks to set locational, network or any other categorical budget is something the Solver is starting to be used to do more and more often.
3. Text-to-Columns – The use of Text-to-Columns can be overlooked in the PPC world, but it really illustrates why a consistent campaign-level naming convention is needed. Using a campaign convention such as: Network_Category_Location, allows for you to download campaign-level data, use a text-to-columns and then create a pivot to go through your reporting accordingly to how you want to visualize the data. Text-to-Columns is a very underrated tool for reporting purposes.
4. Remove Duplicates – This tool is another overlooked tool in Excel – a lot of users in Excel utilize this tool often, but don’t even realize how often due to the ease of use. An example as to when this tool is used is: downloading a list of locations, placements, SQRs, etc – and then doing a remove duplicates before performing a ‘sumif’ to retrieve data and align all the data into one spot.

IF Function Seeding

1. Regular IF Statement – Ah, the normal IF statement. These are great for finding areas over or under certain performance targets you are looking to hit in your account. Fantastic for finding themes within keywords or queries that work well or do not work well at all, but doing an if contains statement and pivoting off the new column. Also – this is helpful to set maximums and minimums with your bidding strategies to write in: if the formula causes the bid to go above the maximum then use the maximum, etc. Though this is on IF statements within Excel sheets specifically – Adwords now uses the simple methodology of IF statements to allow users to include them in their ads.
2. IF AND Functions – The next step to an IF Function is the IF AND function. This has been a go-to function for setting up bidding strategies in the past because it helps you utilize a performance metric (CPL, ROAS, etc.) with a growth availability metric (impression share, average position, etc.) and set bid rules accordingly. IF AND allows you to go one step farther in finding themes in queries or keywords as well, and themes in ads that perform well or poorly.
3. COUNT IF – The count if function makes things much easier to visualize in terms of how many of your keywords are at certain quality scores, performing well, contain certain words, etc.
4. SUM IF – SUM IF does the same as a COUNT IF function; however, the objects are then summed up rather than counted – and you can sum other columns based on rules on columns within the same row. This can be extremely useful for reporting on all levels. From SQR data to campaign-level reporting the SUM IFs have almost overtaken Pivot Tables in a sense that they can be automated much easier. If you utilize Google Sheets and Supermetrics – the SUM IFs become that much more important – as they allow you to automate almost anything as data gets pulled into Google Sheets from a reporting standpoint.

Advanced Functions And Formatting

1. VLookUps – Another one of the most commonly used Excel functions in the PPC world is a VLookUp. VLookUps are fantastic for a multitude of tasks. Having a reach per location chart handy is an example where you can use that chart and simply use a VLookUp side by side with a list of locations in order to find the reach for each location you’re looking at.
2. Index Match – One step above VLookUps in terms of finding values in a table is the Index Match. Index Match is great for finding information about a specific item in a list just as VLookUps are – but Index Match helps for you to have flexibility in the direction on the sheet your looking to pull data from. Index Match is also used on tables that are built for different bidding methods with performance and availability both listed on a chart and different bid adjustments listed accordingly to the two number. Index match allows you to read through columns and adjust your bid accordingly.
3. Heat Maps/Conditional Formatting – Heat maps and/or conditional formatting helps to create a fantastic visible view of statistics and pacing. Conditional formatting can be used to show which categories or areas of your account are pacing below or under goals with the use of color in cells. Heat mapping takes that to the next level, and is best used when aligning hour of day and day of week data into one table and using a Heat Map to pinpoint top performing hours throughout a week and bottom performing hour throughout a week based on color within the table.
4. Significance Testing – Really a fantastic advanced formula for testing Ads or Landing Pages – using significance levels will help guide you on when to shut down an ad tests or landing pages tests when CTRs, Conversion Rates or a combination of the two is showing to be significantly better or worse in one ad vs another in an A/B test.

Simple Functions Seeding

1. LEN Function – Maybe the first function we all learn when beginning PPC is the LEN function. This function is best for counting letters within your ads to assure you don’t exceed the limit. Combining the LEN function with conditional formatting helps further the use of the LEN function and the ease of visibility when your ad has too many letters.
2. TRIM Function – In order to clean up a sheet and the spaces that might occur when using tools such as text-to-columns, the TRIM function is extremely helpful. A lot of times when we combined all of these functions, the TRIM is needed to assure that a Pivot Table or VLookUp is functional across the column.
3. Correlation Formula – Correlation formulas are fantastic in the PPC world and seem to be underutilized overall. The best times to use a correlation formula is when you are trying to prove out the top of funnel efforts helping lower funnel efforts. Some examples of this include: showing correlation between Display Impressions vs. Branded Potential Impressions or YouTube Views with Direct Conversions. This helps to show how much one metric is effected by another metric or tactic statistically.
4. Wild Cards – The use of what Excel calls “Wild Cards” can be an extremely helpful tactic for reporting on any level as well. Looking to sum campaigns that contain a certain word? Use the “*” wild cards. Looking to count queries that begin with X and end with Y? Use the “?” wild card. This functionality helps you to put phrases within on cell into segments.

Tournament Time

In the first round, Pivot Tables beat out Remove Duplicates, Solver beat out Text-to-Columns in the Data Tools region. The Pivot Table as stated is the most utilized tool for PPC and moves on easily over Remove Duplicates. And though Text-to-Columns is needed in more situations the predicting that the Solver is capable of is too important to pass up in the first round.

The SUM IF beat out the original IF STATEMENT due to the reporting it allows online advertisers to pull in, while the IF And Function moved on over Count IF due to the ability to dive deeper into Search Queries AND provide help with bidding techniques.

VLookUps, maybe the most utilized tool in Excel by advertisers, beat Significance Testing – which obviously is only used for testing of copy or landing pages typically. Index Match beat out Heat Maps by a hair – as the ability to help with driving results from a bidding perspective allowed it to edge out Conditional Formatting. This set up the great Elite 8 matchup Excel Nerds have been waiting for: VLookups vs. Index Match.

Finally – the original function that we all learned, the LEN Function, moved on over Wild Cards – though Wild Cards provide a huge edge from a reporting standpoint. The LEN function has been handy from the day you started PPC to now. The Correlation formulas knocked off the TRIM function in the first round – as providing data to back the value of top of funnel efforts has become largely important in the PPC space, especially when pulling in more and more Social Media efforts.

Pivot Tables, Sum IF, Index Match and Correlation Formulas round out the Final Four – with Pivot Tables (heading up the reporting capabilities of Excel) battling Index Match (heading up the deeper analysis side of Excel) for the championship. To no surprise, Pivot Tables won the championship – as this tool can not be beat from a perspective of helping PPCers with combining data on queries, display placements, categorical data, locational data, campaign-level segmenting, etc. The Pivot Table is here to stay as the champion of all Excel Tools.

Conclusion And Final Words

There are a ton of Excel tools and features that weren’t mentioned in this tournament. Did the committee of one leave something on the bubble that deserved to get in? Use Twitter to get your thoughts out on what Excel feature deserved a shot at the title for best tool for us PPCers to use.