One of the projects I am working on is to make a template to help standardize the Search Query Report (SQR) process for adding positive and negative keywords. I’ve been racking my brain for the last two days on how to automate some aspects of the SQR from Adwords so that we can more easily identify words to add as negatives.
I kept on getting stuck on something fairly simple: how to remove the match type identifiers from the keyword column. (ie “, +, [,]). I knew that i could use a substitute function to remove the identifiers, but as I embedded the functions together I kept on receiving an Error value.
So before i get started into the solutions, I want to start with what is the substitute function?
Excel Substitute Function
The excel substitute function is a simple function that allows you to identify a cell and find -something-, replace it with something else, and specify if you want it to happen a certain number of times or in every instance.
=substitute(text, old_text, new_text, [instance number])
So in my example, I want to use substitutes on a phrase match keyword to find the quotation marks surrounding the keyword and replace them with “” (NOTHING.) For broad modified keywords, i.e. +keyword, i want to find the + symbol and again replace it with “” nothing. For exact match keywords i want to identify the brackets  and again replace them with “” nothing. This will give me just the keyword.
The next step is how to use the substitute function to do this, and where I went wrong…
Remove PPC Keyword Match Type Identifiers from the Keyword Column using Embedded Substitute Functions
A really really smart co-worker, Sam James, came up with the following solution that involves embedded substitutes with right and left trim functions. This works and is 100% functional and it removes all of the match type identifiers from the keyword column.
In this example the KEYOWORD column is B. So we’re going to be referencing cell B2 because it is the first row in column B with a keyword.
As I mentioned this is 100% accurate. However, my brain has a really hard time processing the logic because of its complexity. I knew that there had to be an easier more straight forward method. I also decided that instead of hardcoding in the characters like quotation mark, i.e. “”””” four quotes in a row identifies a single text quotation mark, “[” identifies the left bracket in text, i would use the ASCII character identifier so I would be less likely of screwing up the substitutes.
My original embedded substitute was failing because I kept on leaving a additional comma after the new_text. i.e. =SUBSTITUTE(SUBSTITUTE(B2,CHAR(34),””,),CHAR(91),””,)). My logic was that I wanted to substitute for all instances, however it was expecting a value after the comma so it caused my formula to give me the dreaded #VALUE symbol.
After pulling my hair, screaming bloody murder and wanting to cry. I used Sam’s logic above and removed the extra comma.
Trim removes extra spaces including a proceeding space incase your first word in the keyword phrase was broad modified.
This works! Wohoo. Now on to creating a formula to idenifty when a search query term matches exactly to a exact match term so I know where to add NEGATIVES. :)
When I’m done with all of this goodness it’ll be posted to the PointIt.com blog.
I’m humbled to be included in Search Engine People’s list of individuals nominated by the SEM community of PPC Experts you should follow on Twitter!
If you’re looking for a list of individuals I follow and regularly ask questions to here are some of my most often looked to individuals:
Ginny Marvin – @GinnyMarvin
Kirk Williams – @PPCKirk
James Svoboda – @Realicity
Bryant Garvin – @BryantGarvin
John Lee – @John_A_Lee
David Szetela – @szetela
John Gagnon – @jmgagnon
Marty Weintraub – @martyweintraub
Matt Van Wager – @mvanwagner
Aaron Levy – @bigalittlea
Julie Bacchini – @neptunemoon
Lisa Sanner – @lisasanner
Luke Alley – @lukealley
Rae Hoffman – @sugarrae
Katy Tonkin – @katytonkin
Brad Geddes – @bgtheory
Elizabeth Marsten – @ebkendo
Sam Owen – @samowenppc
Lisa Raehsler – @lisarocksSEM
Michelle Moorehouse – @michellemsem
Steve Hammer – @armondhammer
Christine Churchill – @chrischurch
Ruth Burr-Reedy – @ruthburr
Duane Forrester – @DuaneForrester
John Ellis – @johnwellis
Amy Hoffman – @hoffman8
Jennifer Sleg – @jenstar
Purna Virji – @purnavirji
Bill Sebald – @billsebald
Annie Cushing – @AnnieCushing
Matt Cuts – @mattcutts
Mark Traphagen – @marktraphagen
Jennifer Sable Lopez – @jennita
Eric Enge – @stonetemple
Brittan Bright – @BrittanBright
Matt Decuir - @mattbasically
Rick Galan – @RickGalan
Jonathon Colman - @jcolman
Greg Gifford – @greggifford
Jabez Lebret – @jabezlabret
Shah Menz – @michellemsem
Mike Arnesen – @Mike_Arnesen
Ian Lurie – @portentint
Pete Meyers @dr_pete
John Doherty – @dohertyjf
Rand Fishkin – @randfish
Danny Sullivan - @dannysullivan
Okay, so it’s not a comprehensive list, and I know that I still missed some awesome digital marketers on this list, but these are a handful of individuals I’d highly recommend you follow to get some wonderful tidbits on PPC, SEO and a few social people.
I love what I do! I love working in Search engine marketing however it has a down side –it’s an always on space and there is ALWAYS something more we can/could do. I’m finding that some people feel like they need to work extra hours to stay ahead or just keep up to meet expectations. I’ve been digging in this last week to work on processes to share with my amazing PPC team members at Point It. Not because anyone is doing anything wrong or because I’m finding issues in our accounts, but more so as a tool to help people prioritize their day to day work schedules so they can leave work at the office at the end of the day.
I’ve made it one of my goals for this quarter to automated some of the PPC audit that we do regularly, and to establish processes to help with prioritizing work so they can challenge requests that are busy work and won’t move the needle. My end goals: reduce burn out and keep people happy.
What do I mean by establish PPC processes?
I’m working through a PPC audit checklist for weekly, monthly, and quarterly PPC Audits and tasks. Each of the audits will help uncover areas of opportunity that the account managers know where to focus to improve the performance of the PPC Campaigns that they are managing. Will it be more work? Initially yes, because whenever you start using new processes it takes a while to get used to them. However, if I can audit the PPC Audit process for Adwords via the use of Scripts it will save quite a bit of time.
Here is what I’ve pulled together so far. I’d love your thoughts and opinions:
Weekly PPC Checklist:
- Campaign Budget Monitoring
- Performance audit to prioritize which campaigns/ad groups need work
- Bid Optimization
Monthly PPC Checklist:
- Search Query Report Analysis (Bi-Weekly to Monthly depending on the amount of ad spend)
- Quality Score Audit & Pruning
- Ad Extensions Refresh / Audit
- Ad Copy Audit
- Landing Page Audit
- Remarketing List Audit
Quarterly PPC Checklist:
- Comprehensive PPC Audit
- Goals & Targets Analysis
- Campaign Settings
- Ad Extensions Audit
- Bid Modifiers: Device/Location/Demographic/Ad Scheduling
- Competitive Analysis & Audit
- Specific Audits surrounding new features that were launched/released in the previous quarter
- Develop quarterly test plans
- Planning for upcoming launches & new campaigns
Goodbye Exact Match, Hello Exact-ish Match
In August the Inside Adwords blog made a quiet announcement of Close Match Variants rolling out for Exact and Phrase match types by the end of September. So what does that mean? According to Google, “Close variations include misspellings, singular and plural forms, acronyms, stemmings (such as floor and flooring), abbreviations, and accents.” Here it is, the quiet and slow good bye to Exact Match. Here are some examples of close match variants:
- clothes / cloths
- Kids / Childrens
- Baby / Babies
- Java / Coffee
- Cardiology / Cardio
- bar / barre
A few of these things aren’t like the other. Some of them make sense, some of them do not.
I’ll keep my rant to a minimum… I’m not a fan of Google continuing to take control away from paid search marketers in order to help
grow their revenue reduce the amount of work to maintain large keyword lists. I understand that Google is creating a product for the lowest common denominator — small businesses who might not have resources to manage their campaigns. I get it. I really do. What I don’t understand is why Google is choosing to remove the ability to opt out. It makes me sad. Not as sad as Google forced enhanced campaigns upon all adverisers, but still sad. Google is slowly moving the control away from advertisers and professionals who know what they are doing. My solution, give the sophisticated adverisers (aka the Professionals) the ability to opt in or opt out of feature. Okay, end rant because I could write for hours. Let’s get to the meat of what this means for you.
Analyzing Close Match Variants Performance
Okay. So it’s coming. How will you know if you need to make any changes to your accounts? Simple. Analyze the performance by match type and then dig in to your top exact and phrase match terms to determine if you need to take action.
- Login to Adwords
- Pull a Search Query Report: In the Keywords Tab, click on details and select all search terms. (Minimum 30 days of Performance Data)
- Put the data into a Pivot Table and segment by Match Type. Create calculations for the CPA and ROAS.
- If the close match variants match type performance is substantially different then I’d recommend digging in by ad group and adding positive and negative keywords.
Example: For this client, based on the Phrase and Exact performance I need to go in and add some negative keywords into the account because the ROAS for Exact (Close Variants) is -50% or more of the Exact match type.
Work Arounds for Close Match Variants
Unlike Enhanced Campaigns there is a work around for the launch of close match variants that will enable you to maintain some control for your best performing exact keywords. Introducing the worlds most complex Negative keyword strategy. Just kidding, it’s not that complex, but if you would like to keep your top performing Exact Keywords as exact as possible what you can do is this:
For your top performing Exact Keyword…
- Create a Unique Ad Group for Each Exact Match Keyword
- Add all misspellings and variations as exact negative keywords
- In the rest of your ad groups add your top exact match keywords as “exact negative”
Here is a quick and easy visual guide for the work around for utilizing search query reports to get back to “Almost Exact” match.
So I’ve got some exciting news…. I’ve taken a job with PointIt as their Director of Search Engine Marketing! I’m very excited to be joining such a great team of Digital Marketing professionals, including some very savvy women in Paid Search! You’ll be hearing more about what I’m up to and learning as the year progresses!
Learn about the New Google+ Interactive posts. They allow you to enhance your existing sharing by embedding a clickable Call to Action in your post. http://ow.ly/qWpUz