Using Pivot Tables to Visualize Search Query Mismatches

“Can’t we just pause anything under our goal CPA?”

Sometimes it’s hard to explain what an ecosystem a paid search account actually is. All these little pieces weave together to create the account and the results that you see, but the most visible ones wind up being the ones that look like the shiny silver bullet to success.

Keywords are one of these things. Frequently, someone will look at the keyword performance, and their initial reaction is to just start pausing all the keywords that aren’t hitting the target. While that may ultimately wind up being the best path, there’s a diagnosis process to go through first before declaring a keyword a total loser. This has also gotten even more important now that Google allows close variant matching for Exact Match types.

It starts with the Almighty Search Query Report (a.k.a. the SQR). The SQR shows you the actual user search queries and which of your bidded-on keywords it was matched to. Frequently, you’ll find some pretty glaring mis-matches!

Most PPCers know of this and use the SQR regularly, but there are some nice visualizations you can do if you export it to Excel – it’s helped me demonstrate how matching works more than once to a client who is new to it. Sometimes it’s one keyword whose poor performance is baffling, and other times you’re just wanting to figure out where to zero in first. If you aren’t overly familiar with SQRs, there’s a wonderful tutorial that was posted on Unbounce a few weeks ago. To read more generally on Google about SQRs, you can do that here.

I’m a very visual person, so looking at tons of rows in AdWords when it comes to search queries doesn’t do much for me. A few years ago, I started figuring out some pivot tables that helped give me a nice visual of the parent/child relationships on keywords and their matched queries, and they made it a whole lot easier for me to spot issues quickly and know where to drill down.

A Specific Keyword Problem Child

If you have a specific keyword you’re concerned about, the first thing to do is filter for it in the search query report, as an exact match:

This shows me not only if that actual search query is a problem, but also if it’s getting matched to the wrong keyword phrase, and therefore the wrong ad group which is showing the wrong ad copy. Especially in the early days of an account when you’re wrangling with broad and phrase match, this can frequently be the cause for lackluster results on a keyword that should probably be killing it for you. This one doesn’t require Excel, but it’s a good place to start for single-keyword diagnosis if performance stinks.


Drilling Down from an Ad Group View with Excel:

You can take this to the next level as an ongoing account clean-up task as well, to catch these buggers before they become an issue. Here’s one way of doing that:

1. Export the SQR to a .csv file, and make sure you have the Keyword column there so it gives you the actual search-term-to-keyword-match aspect.

Now create a pivot table – this will give us a much easier visualization of what’s going on with each keyword and it’s matching, vs. a huge table of data that makes you go cross-eyed.

2. Do a master filter on your pivot table for anything Added/Excluded. You know you’ve already looked at and/or touched those terms, so we don’t want them in our table.

3. Drag the Keyword field into the Rows area first. This gives you the data that you’d basically see in the Keywords tab so you can identify the laggers easier. Add in your metrics to the Values section like Cost, Conversion, and Cost per Conversion.

4. Next, drop in the Search Term field to Values. Excel defaults to telling me the Count of Search Queries, or basically how many search queries that term was matched to. Seeing this can quickly help me figure out where to focus my efforts:

Hmm. That one in red is converting, but at a really high cost. Notice that it’s been matched to over 1,800 terms. Whoa! Surely they’re not all relevant, and that’s part of the reason I love having this in a pivot table view: if I want to focus on that sticking-out-like-a-sore-thumb-keyword, I just drop a few more things in.

1. Filter the keyword column so you just have that one word.

2. Drop in the Search Term field directly under the Keyword field in your Pivot table builder:

3. Boom! You now have a list of all search queries matched to that problem keyword in a handy list. Make sure you have the sum of the cost of each term in there as well, and sort by that. Now you have tidy list of your costliest search terms, all rolled up under the keyword they’ve matched to. It will also show you some winners that have converted so you can highlight those and add them into the most appropriate spot of your account.

As I tell most clients, once an account is launched, I spend far more time in the Search Query Report than I do in the normal keywords tab. The SQR is the best goldmine of information for what’s actually happening in the auction environment, but it can be a lot of data. Using some quick visualizations can help point you in the right direction of where to start so you don’t spend hours scrolling through rows and rows of data in the AdWords interface.