October 8, 2021
The Ideal QA Process in Digital Advertising
For the most part, advanced proficiency in Excel is not a requirement for advanced proficiency in paid search. That said, Excel offers a number of tools that can make a PPC account manager more efficient in his or her daily work. Below are three Excel functions with specific use cases for PPC that make them valuable tools in your account management arsenal.
1. SUBSTITUTE for Modified Broad Match Keywords
The Substitute function in Excel performs a find and replace operation on the selected cell.
Use Case
Useful in a number of situations, I most often use this formula to add “+” to the front of each word in a bulk list of keywords. When building out keywords in an AdWords Editor Excel template, this formula instantly converts any number of keywords to modified broad match.
Inputs
− Text: the cell(s) on which you want to perform the operation
− Old_text: the characters you want to replace
− New_text: the characters you want to put in place of old_text
− Instance_num: which occurrence of old_text you want to replace (if omitted, all instances are replaced)
How to Use It
When creating keyword lists in Excel, the following variation of the Substitute function can be used to convert keywords into modified broad match:
=”+”&SUBSTITUTE(A1,” “,” +”)
The initial “+” places the “+” in front of the first word of the keyword. The Substitute function then replaces each instance of a space with a space followed by “+”.
2. LEN and SUBSTITUTE to Count Words
Although it seems like there should be a simple formula to count the number of words in Excel, the most efficient way to do this is with a combination of the LEN and SUBSTITUTE functions.
Use Case
I most frequently use this when doing keyword research for competitive terms. While Google’s Keyword Planner has some great recommendations, you must spend time sifting through the less useful one- and two-word recommendations to find suitable keywords. After downloading suggestions from the Keyword Planner into Excel, I use this function to calculate the number of words in each keyword. I then filter for longer-tail terms (e.g., 3+ words) to quickly find the most valuable recommendations.
Inputs
− LEN – Text: the cell that contains the characters you want to count
− SUBSTITUTE – See above
How to Use It
The idea here is that a space designates a break between words. By counting the number of spaces, we can essentially count the number of words. In a blank column, use the below formula to count the number of words in the selected cell(s):
=LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)+1
The first expression counts the number of characters in the selected cell. The SUBSTITUTE function in the second expression replaces all instances of a space (“ “) with nothing (“”), so we can then count the number of characters without spaces in the selected cell. Taking the difference of the two expressions yields the number of spaces. The final +1 is for the first word, which has no space before it.
Note that this function will not accurately count the number of words if there are extra spaces within the selected cell(s). You can use the TRIM function to remove excess spaces before using the above formula.
3. SUBTOTAL & Filters for Quick SQR Analysis
Excel filters are a powerful way to quickly drill down into search queries that contain certain words, do not contain certain words, etc. Evaluating performance for certain types of search queries can inform negative keywords, new ad group breakouts, and other optimizations. SUBTOTAL can be used to view performance metrics only for the queries you’re filtering on.
Use Case
Traditional SUM formulas calculate on the full range of data and will not update as you add and remove filters. The SUBTOTAL function can be used to perform a number of computations, including COUNT, SUM, AVERAGE, and more. For SQR analysis, I most often use SUM. This allows me to quickly compare performance for search queries containing different modifiers (e.g., “modern” versus “stylish”).
Inputs
− Function_Num: the number that corresponds to the computation you want to perform; for SUM, this is 9
− Ref1: the array on which you want to perform the computation; in an SQR, this is typically a full column
How to Use It
After downloading an SQR, use the SUBTOTAL function in a row above each additive metric (impressions, clicks, cost, conversions, revenue). Then use basic formulas (e.g., = clicks / impressions for click through rate) to calculate the rate metrics based off of the SUBTOTAL cells.
Consider a situation where you want to see how queries containing “cheap” perform compared to queries containing “trendy”. Apply a filter to your ‘Search Term’ column for queries containing “cheap”. Your SUBTOTAL row along the top will recalculate to show performance for these queries. Now change your filter to show queries containing “trendy”. Your SUBTOTAL row will again recalculate, and now you can see that “trendy” search terms perform at a higher ROI, but at a fraction of the conversion volume. This comparison helps you determine the prioritization of new ad groups and bid strategies for these keywords.
While advanced Excel techniques may seem largely unnecessary for day to day paid search analysis, clever application of certain features can go a long way in increasing efficiency. As a starting point, familiarizing yourself with the above tools will make common PPC tasks quicker, leaving you more time for the intuitive account analysis an Excel spreadsheet can’t do.
