New! Local SEM Competitive Intelligence
Discover Your Competitor’s PPC Keywords & Ad Copy
Enter your #1 competitor's URL

Using Search Query Reports to Maintain Your AdWords Keywords

Like most Search Engine Marketers, I believe one of the best places to find new keywords for an account is the Google Search Query Report (SQR). Staying informed about what people are searching for helps you know your account and understand better the search trends that pertain to it. It also helps assure you that you have all bases covered in your keyword list. This post is somewhat detailed and explores an advanced, important Adwords account management practice.

Report Frequency

How often you should check the SQR for new keywords really depends on the volume of the account and how long it has been running. However, a somewhat safe bet is once every two weeks. If your account sees fewer than 30 conversions a month, it’s probably safe to perform this task once every three to four weeks.

If your account was recently launched and you see over 150K impressions a day, it wouldn’t hurt to perform this task once a week. You may also want to adjust the parameters to pick new keywords based on click volume and click-through rates, as opposed to conversions. But more on that later.

How to Run a Search Query Report

Step 1: Configure Adwords SQR Correctly

You can start this task by going to the dimensions tab in the Google Adwords User Interface. Make sure search terms are selected from the drop-down on the left-hand side. Also, make sure your date range is set to the ideal time frame. As I said, I would recommend performing this task every two weeks. I would also recommend making sure the date ranges overlap slightly, so as not to miss any valuable conversion data. For example, if the last time I performed this task, I used the date range 02/16/13-03/01/13; this time, I’d want to start with 03/01/13 so as not to miss any partial data from one day.

Also, make sure you have all the appropriate columns selected. This can be done from the column drop-down, mid-screen. Which columns you should use really depends on the metrics that are most important to you and the objectives of your account. At a minimum, I’d recommend downloading clicks, impressions, cost, and conversions. Other metrics such as Avg. CPC, Avg. Pos., CTR, conversion rate, and Cost/Conversion are also good ideas.

Step 2: Download Data

After selecting the important metrics, download the data into Microsoft Excel, which can be done by clicking on the downward pointing arrow. You will then be prompted by another screen to download the report – click “Create.” Your report should download as an Excel file.

Step 3: Set Up Excel Correctly

The first thing you should do after opening the Excel file is add a filter to the second row, where all the column headers are. The filter feature can be found on the top right-hand side of the home tab of Microsoft Excel. I also deleting the bottom total row, so it won’t get in the way of the data.

From here, de-dupe against the current keywords you have in the account. For me, the easiest way to do this is by going into AdWords Editor and setting a custom view to only show active keywords in active campaigns.

Step 4: Incorporate Positive Keywords

Then, go to the keywords tab – making sure that you’re on the positive keywords – and select all and copy. Go back to your main Excel spreadsheet and, on a second sheet, paste these keywords. To keep things simple, delete all columns (Campaign, Ad Group, Max CPC, etc.) except for the keyword column. Double-check to make sure there are no extra spaces in front of the keywords from AdWords Editor. In the instance there are, I suggest using Microsoft Excel’s ASAP Utilities Text9 to remove leading and trailing spaces. If you haven’t downloaded ASAP Utilities, I suggest doing it now. It’s a super-helpful tool. You can also use the trim function (=trim(cell)).

Step 5: Clean Up Data

Then go back to the initial sheet, the search term report. Right next to the search terms in column A, create a brand-new column. Run a v-lookup to de-dupe these search terms against the current keywords. In cell B3, go up to the function bar and click on it. (at the top of Excel, with the fx next to it). Select ‘vlookup’, and press ok.

Then select cell A3 for the lookup value. For the table array, go to the second tab of keywords, select the entire area, and press F4. For column index number, select 1, because there is only one column of data we are looking at.

For Range Lookup, type in ‘false’, because we are looking for exact match data. Press enter. Then, go to the bottom right corner of the cell we just populated (B3) and double-click it to populate all the way down.

For the cells in the vlookup column that returned text, those are keywords that currently exist. For those that returned the #N/A value, those do not exist as keywords, so deselect everything but the #N/A value in the filter to isolate all of the potential new terms.

Step 6: Choose Metrics to Determine Keywords

From here, you need to decide metrics for choosing new keywords. Again, this is something that will vary by account, but for most accounts, it’s a good bet that you want search terms that lead to a conversion.
From here, sort largest to smallest for conversions, then isolate the converting terms by deselecting 0.

Then, for this account, you might filter for 10+ impressions. I would do this because, in the instance there is only one impression, the term may end up being low search volume, or it may end up being something obscure that will never get queried again.

If you are working with a very large volume account, it doesn’t hurt to use additional thresholds, such as a minimal number of clicks, or a higher minimum number of conversions.

Step 7: Click Your Heels Together and Smile

Give the remaining terms a quick once-over to make sure they are all relevant and appropriate to the account.

If they are, you have successfully identified some important new keywords which you should add to your account!


Guest Blogger Jaime Sikora
Jaime Sikora joined PPC Associates in July 2011. Jaime is certified in Google Search and Google Display advertising. She graduated from the University of Illinois at Urbana-Champaign with a degree in Advertising. Prior to joining PPC Associates, she worked in the newspaper industry at the Chicago Sun-Times. In her spare time, Jaime enjoys reading, cooking, traveling, and spending time at the Chicago lakefront.