3 Excel Exercises to Tone and Tighten Your Campaigns for Summer
Published: June 11, 2014
Author: Molly Shotwell
Summer is near and your campaign structure may be floundering in the kiddie pool or just simply bloated with irrelevant keywords. With the hot days approaching, it’s time to work out your campaigns into something clean and lean and rocking to a tune other than Summertime Sadness (no offense to Miss Del Rey).
What needs to be done depends on the unique nature of the account. The scale, whether the account is large or small, will also determine the need for the following Excel methods. From my production experience with a large and complicated e-commerce account, I learned about some common issues and discovered creative uses of Excel functions and features to work through enormous sets of data quickly and efficiently. I trimmed these down to my top three issues and how to tackle them:
1. Disorganized or irrelevant keywords
2. Missing Keywords or Ads in Ad Groups
3. Missing Ad Type in Ad Set (ex: Sale ad)
Keywords Aren’t Themed or Organized in Ad Groups
Close-knit keywords grouped by themes make it easier for advertisers to write effective relevant ads. Relevancy and quality of both ads and landing pages help improve CTR and attract converting customers. As a result, high-quality ads and a strong set of tightly themed keywords are rewarded with a high Quality Score, which in turn can lower costs to achieve better visibility and improve ROI.
This method won’t knock out 100% of badly grouped keywords, but it simplifies the steps for finding keywords that do not share similar intent or meaning as others in their group. The ultimate goal is to delete the bad keywords and leave a manageable remainder of keywords that need a manual review.
I use the ISNUMBER function to weed out irrelevant or very broad keywords. Read my colleague Spencer’s post to learn more about ISNUMBER.
Start by finding each keyword’s word count, parse out the keywords with Text to Columns, and then apply this formula to each token in the parsed columns and the ad group. You can follow the picture to see the referenced cells: =IF(ISNUMBER(SEARCH(Cell,$Cell)),”true”,”false”). If the keyword’s token length is 5 and there are 4 “True” results that match text contained in the Ad Group, it is likely that the keyword is relevant. If the keyword is three tokens long and there are two “False” results, the keyword is probably not relevant. Use conditional formatting to make the “False” results stand out. This method is not perfect, but it helps direct you to keywords that need review.
Keywords without Ads. Ads without Keywords.
Soon, summer will rush in with blockbuster movies, beach parties, barbeque, and beer pong. What is beer pong with just a pong ball and no beer or vice versa? Exactly – it is meaningless. Such is the case for Ad Groups with ads but no keywords, or Ad Groups with keywords and missing ads. Google’s systems will alert advertisers of this issue, but it isn’t always accurate. With a large account to go through, this is when the VLOOKUP function becomes your friend.
In AdWords Editor, copy all active Ad Group shells or download a list in the UI. Insert a column for keyword referencing, “Has Keywords?” Create a new column and concatenate the Campaign name and Ad Group name. Populate this down the list of Ad Groups. Next, download a keyword report and move it to the current workbook. Create a column and concatenate the Campaign and Ad Group names also. In the Ad Group sheet, in the column “Has Keywords?”, VLOOKUP the Ad Group reference column against the Keyword reference column, which should be on a second tab. Populate the formula down the list. The formula will be similar to this: =VLOOKUP(Cell#,Sheet1!A:A,1,FALSE).
Do the same against an Ad Report list. If there are any #N/A results, you will be alerted to the missing elements with little effort in execution.
Missing Ad Types in an Ad Set
Every beach-goer has an essential beach kit comprised of many items; the most important are sun block, a towel, and flip-flops. For many marketers, their campaigns may include an ad set that includes a generic ad, a sales ad, and perhaps a DKI ad. A fast way for checking for Ad Groups that do not have a complete set of ads is by using a pivot table.
Download an Ad Report and create two columns that will mirror each other. One is for the ad type label, and the other is for the ad type count. For Ads that contain a Sale, label these Ads as a “Sale” type. Copy that over into the second column. For Generic ads, label as “Generic” and copy into the second column as well. Populate down the remaining ads. Finally, create a pivot table. The first column is the Ad field and it is placed under Row Labels. The second column is placed under Values and set to Count.
In this case, each Ad Group should have a total of three ads and contain one of each. Ad Groups that are missing one or more ad types will show in the Count of Ad Type column in the pivot table below.
A few weeks remain until June 21st. Begin cleaning your campaigns now to make them scalable as they grow. And since you might see a nice boost in performance for all your hard work, you’ll no doubt find yourself spending more time sitting by the pool than cramping away at the keyboard.