You’ve created a killer AdWords campaign. Your research was thorough, there’s not a broad keyword in sight and you’ve used every negative keyword resource ever created to pre-empt those pesky off-target searches.
But what do you know; you review your campaign’s search query report after a day/week/fortnight and queries that don’t quite hit the mark are still slipping through.
Excel or Google Sheets is about to become your best friend. Trawling through hundreds of search queries in the AdWords interface and adding the irrelevant search terms as negatives individually is not only going to be a huge drain on your time, it’s also going to be pretty pointless.
If your client sells trampolines, adding “used trampolines for sale on gumtree in bury st edmunds” as a negative keyword is going to get you nowhere. However adding ‘used’ and ‘gumtree’ as phrase match negatives to your whole account is likely to eradicate a whole load of irrelevant searches. And it’s finding those negative keyword gems (which won’t always be that obvious), that an export is going to help you with.
Let’s pivot.
Using pivot tables to mine highest and lowest performing terms.
Pivot tables are designed to summarise data and reveal patterns and trends, they were basically made for search query report mining.
When you export your search query report include the columns containing the KPI(s) most relevant to your account, be that number of contact form submissions, conversion value or ROAS. If your account contains a large number of campaigns covering a range of products or services, think about conducting your analysis on a per-campaign basis. Also, consider filtering out branded searches as these will skew your results.
PPC Hero, one of our faves here at Impression, have this great step-by-step guide on using pivot tables, allowing you to find the good, the bad and the downright ugly from your report.
Then pivot again…
Eliminating keyword crossover.
And that’s not all, pivot tables can also be used for finding keywords that allow search terms to crossover campaigns. Simply include a column for campaign and ad group in your table, then sort by term and identify duplicates.
By adding negatives at individual campaign level to eliminate these crossovers, you can ensure that users only see the ad and landing page that is most relevant to their search, maximising the opportunity for conversion.
So, in summary…
Analyse by match type, then action!
Our super handy permutation tool…
In a similar way to segmenting your keywords by campaign and ad group in a whole account export, you can also include a column for match type in your table. This will allow you to compare and identify which match types are top performers in each of your campaigns.
When a campaign is in it’s infancy you’ll want to use modified broad keywords to capture that all important initial traffic. Because, analysing your own search query report is actually the best keyword research you can do, right?
But as time goes on, maturing your keywords into a carefully planned and analysed list of exact and phrase match keywords (using the insights from those search query reports, of course), is the key to increasing quality score and a healthy ROI. It’s easy to see how marketers may be wary of this; what if I miss out because the user used a slight variation on my phrase?
Using a PPC keyword permutation tool like Kombinator will do they hard work for you.
Take it to the next level…
Using word count on your search query report.
I know we keeping harping on about these pivot tables but there’s another super useful use for them! Word counting.
They’ll always be big budget advertisers using broad match keywords to capture all traffic for a particular term, especially in competitive markets. To get ahead, without paying a cost-per-click to make your accountant wince, use long-tail keywords. Plus, with segmentation you’ll be able to serve highly relevant ad copy to increase click through rate too.
Add a column to your pivot table with a word count formula (the one below works for Google Sheets) for each individual search query. Add the word count column to the ‘columns’ section of your pivot table and voila!
=len(C19)-len(substitute(C19,” “,””))+1
When researching this method I came across a blog by @RealSecretJake on filtering search queries by word count, definitely worth a look if you think this method is suitable for your account (which it is, by the way).
Wondering what fruits you might see from all this word counting, pivoting labour? The below is real-life metrics from an ecommerce client of ours in the interiors business. Take a look at how clickthrough rate and conversion rates increase and cost-per-click decreases as search queries get longer.
This isn’t to say we’ll be limiting the account to only keywords with a word count of 7+, but we certainly took a look at those 4-word search queries a little more closely, using them to build new ad groups, influence strategy and have a re-think on our user’s intent when they visit the site.
Oh, and those negative keyword resources we mentioned right at the start?
Sometimes, excel skills are no replacement for simple old common sense. When you create a campaign, put yourself in the mind of a user.
If you or your client sells front doors and you’re running a geo-targeted campaign with the keyword +doors, ‘car doors’ is a negative keyword you would naturally want to add straight away.
Need a little negative keyword inspiration? As a reward for getting this far, here are some of our favourite resources…
Ask the seeker at answerthepublic.com
Get bespoke results from Wordstream’s negative keyword tool
Check out these ‘Big Daddy’ thematic lists of negatives
Here at Impression, our in-house development team have built an automated search query crawler, allowing the PPC team to generate in-depth search query data, aimed at achieving our clients target ROI (and beyond!)
Find out more about our services and how we can apply our PPC knowledge and tools to your campaigns here.
