Control your Match Types with Search Query Reports and Pivot Tables
Search query mining is probably one of the most important tasks in managing paid search accounts. It tells us what actual search terms triggered our ads and gives us the opportunity to add or exclude those terms to improve our targeting and profitability.
Next to Google’s official explanation of the search terms report (page by Google), a lot has been written about best practices for search query mining and I can highly recommend the following articles if you want to deepen your knowledge about search query mining and match types in AdWords:
- How to Capture & Control Your PPC Keywords to Achieve a Better Account Structure by David Rodnitzky
- The Broad Match Generator by Alan Mitchell
- Your Broad Match Keywords Are Not Converting Higher than Your Exact Match Keywords by Brad Geddes
- Match Type Keyword Trap by Craig Danuloff (written before the release of broad match modifier)
This article however, will not be about regular search query mining or match type organization but will simply show you how to find the answers to the following questions by using Excel Pivot Tables and your search query reports:
1. Are different ad groups triggering the same search query?
If you find that search queries are being triggered by different ad groups or even campaigns, you are competing against yourself. Besides, the same search query sees different ads and possibly even different landing pages, so you’ve lost some control of what you’re showing to whom.
By knowing where this happens, you can fix this by choosing the ad group where the search query belongs and excluding the query in all other ad groups.
2. How are the close variant queries performing in your campaigns?
In April 2012 Google announced a new matching behavior for phrase and exact match keywords, which now also include plurals, misspellings and other close variants, unless you opt-out.
Whether or not this ‘near match’ is actually working for you often depends per campaign. If it’s giving you more volume within your efficiency target, you should leave this option on. And if it’s not, then you should obviously disable it.
By knowing how this keyword matching option is performing in each of your search campaigns, you can selectively opt-out based upon your own data.
Finding different ad groups that target the same search query
To find out if multiple ad groups are triggering the exact same search query, follow these steps:
1. Go to the Keywords tab on the account level and select a significant date range. The past 3 months usually works well, but feel free to use a shorter or longer time range depending on the size of your account.
2. Get the search query report by clicking on the “Keyword details” button and selecting “All”.
3. You only need the following columns, make sure you have these by clicking on the Columns button and selecting “Customize columns” (and feel free to remove the other columns):
4. If you have separate campaigns that target different devices, geographic locations, time of day or day of week, then create a filter to make sure that you’re only viewing queries from campaigns that share the same targeting settings.
You can do this by clicking on the Filter button and selecting “Create filter”. Now you can manually add the campaigns with shared targeting settings one by one, or filter them by campaign name if you’ve clearly labeled your campaigns.
For example, you should exclude campaigns targeting mobile devices when you’re analyzing the queries of your computer (and tablet) campaigns:
5. Download this report in Excel .csv format and open it with Excel
6. Delete the first and last row of this report
7. Insert a Pivot Table based on this report
8. Select the Search term, Campaign and Ad group fields (in this order) in the Pivot Table Field List, so these appear in the Row Labels of your Pivot Table.
9. Drag the Impressions, Clicks and Conversions fields (in this order) to the Values part of your Pivot Table.
10. Click on each of the values and change the “Value Field Settings” to “Sum” (instead of “Count”)
11. Insert a calculated field: CTR (we need Excel to calculate this, inserting the AdWords CTR data directly will not give us the correct numbers):
12. Click on the arrow in the Row Labels field of your Pivot Table and select “More Sort Options”
13. Sort “Descending (Z to A) by”: “Sum of Impressions” to get the search queries with the most impressions on top.
14. Now look for search queries that occurred in different ad groups:
15. Now it’s up to you to decide in which ad group the particular search query belongs and to exclude it as an exact negative in all other ad groups (or campaigns).
Finding out how near phrase and exact is performing per campaign
Follow the same first 6 steps as in the previous part (skipping step 4). Then continue with the following steps:
1. We want to consolidate the data of the following match types: “exact (close variant)” and “phrase (close variant)”, as we can only enable or disable near matching for both match types at once.
One way of doing this is by putting a filter on the first row and selecting just both close variant match types:
Now you can easily replace both match type names by “close variants” by typing this into the first cell and double clicking the fill handle (the skinny plus sign that appears in the bottom right corner of a cell).
2. Insert a Pivot Table based on this report
3. Drag your Campaign and Match type fields (in this order) into the Row Labels of the Pivot Table
4. Drag the fields of your choice into the Values field, as long as they’re ‘raw’ numbers (so no calculated metrics from AdWords like CTR, CPC, CPA and Conv. Rate), for example: Impressions, Clicks, Cost and Conversions
5. Click on each of the values and change the “Value Field Settings” to “Sum” (instead of “Count”)
6. Insert a calculated field: CPA (we need Excel to calculate this, inserting the AdWords CPA data directly will not give us the correct numbers):
7. Now your Pivot Table shows you performance metrics for each match type within each campaign.
8. In the example above we can see that in Campaign 1 the close variant match type has a significantly higher CPA ($101) than the campaign average ($74) and especially than exact ($67) and phrase ($71).
In Campaign 2 however, we see the close variant performing better than the campaign average and even better than the phrase match type.
9. It’s up to you which difference in performance you want to tolerate before opting out of is this keyword matching option, but I wouldn’t advise to opt out by default as it often brings in additional traffic for reasonable CPA’s.
Some have found that singular terms convert better than plurals (based upon last-click attribution) and I also see this happening in our accounts. This could be part of the explanation of the difference in performance of close variants. In those cases you would want a different (bid) strategy for plural and singular keywords, which means you’ll have to opt-out of this keyword matching option.
I hope you will gain new and valuable insights by using your search query reports and Pivot Tables as described in this article.
If anyone has any other creative uses of AdWords reports and Pivot Tables, I would love to hear them!
Opinions expressed in the article are those of the guest author and not necessarily bgTheory. If you would like to write for Certified Knowledge, please let us know.