This is a guest post by Chad Summerhill, author of the blog PPC Prospector, provider of PPC AdWords advice , and in-house PPC specialist  at Moving Solutions, Inc. (UPack.com  and MoveBuilder.com ).
As with any PPC analysis , you must get the right data to answer your questions. Here are some of the questions our data will need to be able to answer easily:
- 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/Conv?
- What search queries are duplicates of existing exact match keywords?
In order to answer questions about a search query’s performance we need the Search Query Report, for questions about comparison metrics we will need an Ad-Group Report, and for questions about duplication we will need a Keyword Account Structure Report.
This tutorial does require a basic understanding of how to use the AdWords interface, Microsoft Excel, and AdWords Editor.
I will try to be as detailed as possible without bogging the article down. If you need further instructions you can always ask for help in the comments section of this post and I will be happy to answer your questions.
Let’s get started!
Search Query Report
Either through the MMC Reporting Center or your AdWords interface, pull a 90-day Search Query Report for the campaigns you want to use for this query mining  exercise.
Choose the columns I have displayed below.
Next, download the report as a .csv and open it in Excel. We will come back later to and format this report for our analysis.
Some of you may be asking why we are about to pull an ad-group report for our search query mining exercise. A big part of data-analysis is applying the proper context for PPC peer comparisons . For this analysis, I want to be able to compare search query performance against the ad-group that it matched to.
For example: Search query CTR is 30% below that of its peers
If you are wondering why I don’t just use the SQR data for this comparison, it’s because the SQR only reports on search queries that received a click. If I aggregated the SQR by ad-group it would show a much inflated CTR compared to the CTR that is reported in the ad-group report that contains all impressions.
Now, pull your ad-group report for the same time-period and campaigns as the SQR you just downloaded.
Download and open the ad-group report in Excel and we will come back to it later.
Account Structure – Exact Match Keywords
Next, copy your exact match keywords for the campaigns you are analyzing from AdWords Editor and paste it into Excel. Again, this keyword data will be used for context during our analysis of your SQR.
We will use this data to find duplicate search queries (search queries that are already in your account as an exact match keyword) and for comparing the individual words that make up your search queries as we mine for campaign negative keywords .
The reason we use this data source instead of the exact match search queries found in the SQR is because we want all keywords that are currently in your account not just keywords with impressions and the SQR is a performance report and will not show the current state of your account like AdWords Editor will.
Put it all in Excel
You should now have three sets of data (1. SQR, 2. Ad Group Report, and 3. Keyword Data from Editor). Next get them into an Excel workbook together as shown below:
In the next post I will show you how to prepare your data for search query mining. You will learn how to:
- Format your data as a table in Excel
- Create derived fields in Excel (DUP_FLAG, PEER_CTR, ETC.)
- Create a WORD COUNT formula in Excel
- Use the Excel VLOOKUP FORMULA
As with most data-mining exercises, data preparation will be a large part of the work and is crucial for being able to answer all of the questions we will have concerning our search queries' performance.