My approach to PPC involves analysis. My career began when I realized that optimizations were possible if you focus on your ability to manipulate and interpret performance information.
Excel skills can help you become more efficient and improve the quality your analyses.
Excel is one of my favorite things. I find new ways to make it more efficient. Many functions and formulas are available to speed up PPC tasks.
Here are seven Excel tips that will help you quickly identify high impact PPC optimizations that will make a difference for your client or brand.
Excel 101: Foundational formulas
These are the Excel skills that I first teach to new hires, who will be expected to perform bulk sheet and performance reporting.
You may find yourself in a similar position or feel that these tasks are too cumbersome.
Delta
A delta is simply a rate of change. This is half of the battle for performance marketers. To develop efficient optimizations, we need to be able to see the changes in real time.
In the example calculation, notice how large the Click Conversion Delta. A drop of 5% to 3% could easily be overlooked. However, the delta calculation shows that click conversion must improve to increase campaign CPA.
Incremental impact
The incremental impact calculation will provide insights that will be compatible with the insights from performance deltas. Big deltas will have large incremental impacts. These formulas should not be used together to generate new insights.
This formula is useful when creating a performance narrative. This formula helps to answer the “so how” question that all stakeholders can understand. To add more color to your narrative, incorporate callouts like “anX% drop in clicks results in Y less clicks” or “anX% increase in CPCs led $Y more spending”.
This formula assumes that everything else is constant. These metrics are interconnected, and marketers know this.
CTR will decrease if impressions rise by 50% Deltas are the best way to fully understand performance changes. Only use this calculation when it makes sense.
Concatenate
This article will have the greatest impact on your day-to-day tasks if CONCATENATE does not become part of your bulk sheet creation process. This function allows users combine the contents from multiple cells into a single text string. It can be used to join cells.
This function has many applications. Important note: The function is not limited only to cells. You can include text/characters as fields in the formula by using quotation marks. Example 2 shows how text can be incorporated into a CONCATENATE formula.
Get the daily newsletter search marketers rely on.
Please refer to the terms.
Excel 201: Preparing data for analysis
Pivot tables can be a PPC marketer’s best friend. To get the most out of pivot tables, it is important to take the time to verify the data’s integrity and granularity.
These functions/formulas are most useful when preparing a dataset to be analysed, but the applications are extensive.
VLOOKUP
This function is often argued to be the most important for search marketers. I wouldn’t argue with them. VLOOKUP is an important function search marketers must master. It’s common for datasets to need to be augmented, modified, or merged with data from other platforms.
If you have “source of truth” reporting that is not available on the PPC platforms, then you will likely be familiar with the difficulties of combining data sets. VLOOKUP allows you to add macro-level filters and filters to a granular data set.
My best advice is to pull data at the highest level possible. VLOOKUP can be used to quickly add filters. Start at the highest levels of analysis and then drill down to the most important drivers of change.
If stakeholders request a breakdown of geographical performance, pull at the local level, create a table associating cities to states or regions, and then add two columns for State/Region to the dataset using VLOOKUP.
Transform the date into a week
This IF statement is especially useful for PPC as the weekly reporting options are limited when pulling reports directly through the platform.
If you don’t have a Monday-Sunday schedule for your clients or brand, you can pull data at the date/day level. This formula will add a column to your dataset for Week.
Excel’s standard settings indicate that 1=Sunday and 7=Saturday. Example 2: Tuesday is used as the start date of the week.
You should update the day-of-week indicator in both the IF logic and the returned value if it is false.
Excel 301: Tools for large-scale datasets
Text-based categorizing
Although it may seem complicated at first, this formula is extremely useful for search marketers. It allows users search text cells for a specific word or phrase.
If the cell meets the criteria, then the formula will return the text entered in the ‘text is true’ field. Based on the formula I have just shown, you can probably guess what happens if it doesn’t match.
This formula can be used in many areas of search marketing. Categorization is not always binary, so you don’t have to categorize entities into one of two options.
Example 2 illustrates how multiple logical test can be stacked on top of each other. This is called a nested IF sentence. You can create a nested IF by repeating the IF statement logic in ‘text if true’ with different search criteria.
This tells Excel to search the first phrase/text. If Excel fails to find the first phrase/text, it will search for the second. Advertisers can quickly categorize data at scale by using nested IF statement.
Combining complex data
Let me conclude by highlighting how the techniques I have already mentioned can be combined to improve the quality of analysis.
If you follow the best practice of pulling platform information at the most granular level, it is common for data to contain multiple segments (a.k.a. attribute columns in the data).
Data must be able to be combined between two platforms at the same level. If not, data pulled from your original dataset may be incorrect.
CONCATENATE can be used to join multiple datasets. It creates an additional column that joins all segments in the original dataset. This should be repeated in the second dataset using the same order of the concatenation. For an example, see Step 1.
The connector between the two datasets is now the newly formed column. Use the VLOOKUP function to search for the newly created column in the second data set and select the column of data that you would like to add to dataset 1. Double-check that the VLOOKUP function worked by comparing the summation of the newly imported data with the original table.
Happy number crunching
There is a lot of information here. We’re only scratching the surface of the iceberg.
If you are having trouble with Excel, don’t lose heart. It takes time to master the functions and their capabilities. You can find videos that show you live examples. These were especially helpful for me.
Search Engine Land’s first post, 7 Excel formulas and functions that can be used for PPC, appeared first on Search Engine Land.
