7 useful Excel formulas and functions for PPC

My approach to PPC is analytical. Early in my career, I realized that optimizations became apparent when I focused on developing my ability to manipulate and interpret performance data.

Learning Excel can help you become a more efficient worker and improve the quality of your analyses.

One of my favorite parts of Excel is that I’m constantly looking for ways to be more efficient. Several functions and formulas can help speed up PPC tasks.

Here are seven Excel tips to help you quickly identify highly effective PPC optimizations that will move the needle for your brand or client.

Excel 101: Basic Formulas

These are the Excel skills that I first introduce to new hires who are expected to take over performance reporting and loose plates.

If you find yourself in a similar role or find these tasks cumbersome, try incorporating the following into your process.

Delta

Simply put, delta is the rate of change. For performance marketers, this is half the battle associated with our services. To develop effective optimizations, we need to have a good understanding of the changes we see.

Notice how big the click conversion delta is in the calculation example above. Nominally, you could overlook the drop from 5% to 3%. However, using the delta calculation, it becomes clear that the click conversion needs to be improved in order to increase the CPA of the campaign.

Incremental impact

Insights received from calculating incremental impact will be aligned with insights from performance deltas – large deltas will have large incremental impacts. Do not expect new insights when using these formulas together.

This formula is useful in developing a performance narrative. It helps answer the “so what” question that all stakeholders understand. Include speech bubbles such as “X% decrease in CTR results in Y fewer clicks” or “X% increase in CPCs resulted in $Y more spend” to add more color to the narrative.

One important caveat is that this formula assumes everything else is constant. As marketers, we know that all of these metrics are interconnected.

If impressions increase by 50%, CTR is likely to decrease. Use deltas first to fully understand performance changes, and only use this calculation when it makes logical sense.

Combine

In terms of impact on day-to-day tasks, this is probably the most effective takeaway from this article if CONCATENATE is not part of your process for creating bulk sheets. This feature allows users to merge the contents of multiple cells together into a single text string. Think of it as a glue that you can use to connect cells together.

The use of this feature is far-reaching. One important note – the feature is not limited to cells. Text/characters can be included as fields within a formula by using quotation marks around the text. Example 2 illustrates including text in a CONCATENATE formula.

---

---

Excel 201: Preparing data sets for analysis

Pivot tables are a PPC marketer’s best friend. To get the most out of PivotTables, you need to invest time to ensure the integrity and granularity of your data set.

The following functions/formulas included here are most effective in preparing a data set for analysis, but the applications are far-reaching.

VLOOKUP

Some may argue that this is the most useful feature for search marketers, but I wouldn’t argue too hard. VLOOKUP is a critical feature for search marketers to master, as datasets typically need to be augmented or modified with data from other platforms.

If you’re reporting on “sources of truth” outside of PPC platforms, you’re probably familiar with the challenges of aggregating datasets. Additionally, VLOOKUP is an easy way to add macro-level filters to a granular dataset.

The best practice I recommend is to get the data as granular as possible and use the VLOOKUP function to easily add filters. Start your analysis at higher levels and drill down to the biggest drivers of change.

For example, if stakeholders require a breakdown of geographic performance, pull at the city level, build a table linking cities to countries and regions, and add two columns for country and region to the dataset using VLOOKUP.

Convert date to week

This IF statement is especially useful for PPC because weekly reporting options can be limited when pulling reports directly from the platform.

For brands or clients that don’t have a traditional Monday to Sunday schedule, get day/date level data. Then use this formula to add a column to the dataset for the week.

Note that the standard settings in Excel show 1=Sunday, 7=Saturday. In the 2nd example, I used Tuesday as the start of the week.

Note that the day of the week indicator must be updated in both the IF statement logic and the return value if it is false.

Excel 301: Tools for large datasets

Text-based categorization

Although it may seem complicated at first glance, this formula is very practical for search marketers. Allows users to search a cell of text for a specific word or phrase.

If the cell meets the conditions, the formula will return the text entered in the “text if true” field. Based on the previous formula we covered, I bet you can guess what happens if it doesn’t match.

The formula has many applications in search marketing. However, categorization is rarely binary – meaning that you do not categorize entities into one of two possibilities.

Example 2 shows how multiple logic tests can be stacked on top of each other. This is called a nested IF statement. To create a nested IF statement, repeat the IF statement logic in the ‘text if false’ field with different search criteria.

Basically, this tells Excel to find the first sentence/text. If it doesn’t find it, look for another phrase/text. By using nested IF statements, advertisers can quickly categorize data on a large scale.

Combining complex data sets

Finally, I want to emphasize how the techniques described earlier can be used together to improve the quality of the analysis.

If you follow the best practice of extracting platform data at the most granular levels, it is common for the data to contain multiple segments (even attribute columns in the dataset).

When merging data between two platforms, the datasets must have the same level of granularity. Otherwise, the data you drag into the original dataset will be inaccurate.

To join complex data sets, use CONCATENATE to create an additional column that combines all the segments in the original data set. This should be repeated in the second data set using the same chaining order. See step 1 for an example.

The newly created column now serves as a connector between the two sets of data. Use the VLOOKUP function to find this newly created column in another dataset and determine which column of data to add to dataset no. 1. Double-check that VLOOKUP is working correctly by comparing the sum of the newly imported data to the original table from which it was imported.

Happy number crunching!

There is a lot of information here to digest. However, we have only scratched the tip of the iceberg here.

Don’t despair if you have problems with Excel. It takes time to learn the features and their capabilities. Look for some videos that walk you through live examples – they were particularly effective for me.

---

The opinions expressed in this article are those of the guest author and not necessarily those of Search Engine Land. Staff authors are cited here.

---