Search Query Mining for Campaign Negatives

Query mining for campaign negative keywords can be tedious if you have a large data-set. One of the best ways I’ve found to filter through the data is to compare the words you are intentionally targeting to the words that show up in your search queries in your Google AdWords or MSN AdCenter Search Query Report.

The video below walks you through the entire process. You can use this technique to quickly filter through a SQR that’s thousands of rows long–especially if you automate the process with Excel macros. I hope you enjoy the tutorial and please leave your comments and feedback.

Special thanks to eBuySigns.com for providing us with the data-set used in the example. If you are looking for vinyl lettering in the near future they would love to help you out.

And if you are interested in having me analyze some of your Google AdWords or MSN AdCenter data for a tutorial video please contact me.

Here is a list of the steps that are detailed in the video:

1.  Pull your AdWords Search Query Report, summarized at the AdGroup level, into Excel.

2.  Format you data as a table in Excel.

3.  Filter for ‘other unique’ in the Search Query column and delete.

4.  Filter for Broad & Phrase match search queries that had conversions and delete.

5.  Filter for your Exact Match search queries and copy and paste into a new worksheet.  Repeat for Broad & Phrase Match search queries.

6.  Use Excel’s Text To Columns feature to parse out the individual words into separate columns and resort all words into one column.  Do this for both Exact and Broad match.

7.  Add column headers for you Broad Match list (search query, count)

8.  Under the ‘count’ column add the number 1 all the way down the list of words and format your data as a table.

9.  Create a Pivot Table for your Broad Match words and copy the results back to your Broad Match worksheet.

10.  Remove the duplicate words from your Exact Match list using Excel’s Remove Duplicates function.

11.  Add a vlookup formula to your Broad Match list.

12.  Format your Broad Match list as a table again and filter for #N/A.

13. Look for campaign negatives.

Tags: , ,

5 Responses to “Search Query Mining for Campaign Negatives”

  1. victor ed. November 11, 2010 at 3:15 am #

    Thank you. I found your tutorial very helpful. I look forward to sharing my best practices in return.

  2. Chad Summerhill November 11, 2010 at 8:09 am #

    You are welcome, Victor. I look forward to hearing about your techniques!

Trackbacks/Pingbacks

  1. The Best PPC Blogs – The Definitive List of Pay-Per Click Blogs - February 2, 2011

    [...] Search Query Mining for Campaign Negatives [...]

  2. Ad Text Optimization with the Pros: A Q and A with Chad Summerhill - March 30, 2011

    [...] One of the things I find really cool about Chad’s blog posts and videos – both on his own PPC Prospector blog and other places around the Web – is the depth of detail he goes into. You find a lot of blogs (in PPC as well as other disciplines) where the smartest people seem to talk more about theory and industry trends than actual day-to-day management tasks they’re doing to get results. Chad’s videos are great because they outline an actual process for things like mining search queries for campaign negatives. [...]

  3. eMentorMarketing » Use PPC Negatives And Save Money - October 26, 2011

    [...] A helpful resource to working with that data can be found in this helpful step-by-step guide, Search Query Mining for Campaign Negatives on PPC [...]

Leave a Reply