Advanced Search Query Mining Part 4: Mining Your Data for Insights

In the last post in this series, I showed you how to prepare your search query data for analysis. We had some specific questions that needed to be answered and that shaped how we transformed our data. These questions included the following:

• What search queries have high impressions but no clicks?
• What search queries have resulted in a conversion?
• What search queries have a below-average CTR for the ad group?
• What search queries have an above-average cost/conversion?
• Do I have a problem with ad poaching and duplication?

For the most part, these questions are focused around search queries that may need to be added as negative keywords and search queries that need to be a part of a keyword expansion strategy.

I like to start my query mining analysis with a quick survey of my data.

Survey your data

The really bad and obvious waste can sometimes be seen easily in your data by just sorting by cost and filtering for zero conversions, etc., so a quick survey of your data is always advised.

I encourage you to get to know your data. Use a pivot table, sort, and filter. The more you learn about your data, the better equipped you will be for the next part of our analysis process.

A good example of this would be creating a pivot table to see how much duplication is happening.

1. Select a cell in your ‘sqr_data’ worksheet
2. Insert a Pivot Table
3. Add ‘DUP_CK’ to the Row Label
4. Add ‘Search Term’ to the Values and Count
5. Add ‘Match Type’ to the Report Filter and filter out the exact match search queries (they definitely will already be in the account)
6. See if you have an issue with duplication

Depending on your results, you may need to invest some time to reduce the amount of ad-poaching that is occurring by the creative use of negative keywords (i.e., add the exact keyword negative to the broad and phrase match ad group where the duplication is occurring).

While you are surveying your data, go ahead and filter for zero conversion search queries as well as search queries with zero clicks. Look for the easy stuff before we jump into the more advanced analysis.

Create your negative keyword candidates list

One of the main reasons for search query mining is to find negative keyword candidates, or words and phrases that might need to be added as a negative keyword inside your ad groups or campaigns. Because of the time we spent preparing our data, creating this list will be much easier.

In this tutorial I will be focusing on ad group level negatives. Look here for more information on finding campaign negative keywords. Ad group level negatives will be based primarily on the search query’s performance when compared to its peers in the ad group. If it’s performing badly, most likely you will need to add it as a negative and let another ad group worry about it. If you think it’s relevant you may need to move it to another ad group (as a keyword) with better aligned ad copy or landing page.

Here are the questions we will ask of our data in order to create our negative candidates list:

• What search queries have a below average CTR for the ad group?
• What search queries have a below average CVR for the ad group?
• What search queries have an above average cost/conversion?

This is how you create your list of negative candidates:

1. Filter for ‘DUP_CK’. Choose ‘#N/A’…this will leave only non-duplicates.
2. Filter for ‘LOW_CTR’
3. Filter for ‘zero conversions’ (optional)
4. Filter for ‘ENOUGH_IMP’ (this is optional and may not be necessary with small datasets)
5. Copy the resulting data into another worksheet and rename it ‘negative candidates’
6. Repeat this process for your ‘LOW_CVR’ and ‘HIGH_CPA’ search queries.

You now have a dataset that is a fraction of the size of your original search query report. Because these search queries are below average in some way, they are much more likely to need to be added as a negative search query.

Of course, you can and should play around with your comparison thresholds. In this example we used ad 30%+ below average comparison. Depending on your situation you may need to be more or less aggressive. For example if you are analyzing 300K rows of data you might want to start with an 80%+ below average comparison for CVR or CTR and drill right to the horrible stuff. Search query mining is part art and part science.

Regardless of the thresholds you choose, you now should have a much more manageable list of search queries to work through.

Create your keyword expansion list

Your raw search query data most likely consists of thousands of rows of data, so we need a good way to pull the good performing search queries out and create a keyword expansion list.

Some PPCers are advocates for adding any search query with a conversion to your account as a keyword. I can understand the reasoning behind this, but I would like to suggest an alternative strategy.

I don’t like to add new keywords to my account unless they have enough activity (clicks) for me to potentially need to treat them differently (bids, ads, etc.)

An easy way for us to do this is to:

1. Filter for search queries that have resulted in a conversion.
2. Filter for ‘ENOUGH_CLICKS’
3. Filter for ‘N’ on ‘HIGH_CPA’ (might be a good idea to do a quick pivot and find out how many of your converting search queries have a high CPS). NOTE: I took a fairly non-scientific approach in creating the ‘ENOUGH_CLICKS’ flag in my previous post, but it does help limit the list to search queries that have enough clicks to care about. You could also throw a little more science at it and look for a “true CVR” using the formulas in this peer comparisons spreadsheet.
4. Copy and paste this data into another worksheet and rename the worksheet to keyword_expansion.

You should now have your negative candidates and keyword expansion list ready to be worked. In my experience these lists will be around 1-3% of your original search query report, making them much more focused and easier to work with.

In my next and final post on advanced search query mining, I will walk you through the process of taking action on your negative candidates and keyword expansion list. We will be using Excel again and the AdWords Editor. I hope you have found some valuable insights and are ready to take action.

This is a guest post by Chad Summerhill, author of the blog PPC Prospector, provider of PPC tutorials, and in-house AdWords specialist at Moving Solutions, Inc. (UPack.com and MoveBuilder.com).