Paid Search Marketing

Advanced Search Query Mining Part 2: Getting the Right Data

By Chad Summerhill November 17, 2010 Posted In: Paid Search Marketing Comments: 2

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.

Ad-Group Report

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.

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).

AdWords Performance Grader




If you enjoyed this post, please consider leaving a comment.

Comments

Saturday August 20, 2011

college papers (not verified) Said:

I am impressed by the quality of information on this website. There are a lot of good resources here. I am sure I will visit this place again soon.

Thursday June 06, 2013

Tee (not verified) Said:

In regards to the step where you ask us to pull an  "Account Structure – Exact Match Keywords report", what if you have a campaign where you are only bidding on broad match keywords?

Cheers,

-T

 

 

 

Leave a Comment

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.