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.