Comparing Year-Over-Year Search Query Trends

In the past I’ve written extensively about search query mining for negative keywords and new keyword opportunities, and while search query mining is arguably the best use of the search query report, there are other insights worth exploring.
Have you ever wondered what search queries were driving your business last year and if they are still contributing this year? Are there new search queries emerging in your market? To answer these questions, you will have to do a year-over-year search query analysis.
While the AdWords interface does allow for date range comparisons down to the keyword level, it loses this functionality at the search term level. We are left with analyzing date range comparisons for search queries in Excel.

Getting the right data

The goal of this analysis is to compare our current search query performance to the same timeframe for the previous year and vice-versa. In this example we are going to pull the previous month's performance.
  1. In the AdWords interface, select "Last month" from the date range picker.
  2. Select columns (Search Term, Impr. Clicks, Cost, Conv., Avg. Pos.).
  3. Download reportand open it in Excel.
  4. Follow steps 1-3 for the same month last year(use "Custom date range").
  5. Combine both reports in a single Excel workbook and rename the worksheets "Current" and "Previous."

Preparing the data for analysis

You should now have two datasets (current and previous) to compare in a single Excel workbook. The next step is to prepare your data for analysis using the Excel VLOOKUP formula to pull over all of the corresponding values from the "Previous" worksheet onto the "Current" worksheet and vice-versa.
  1. If you pulled the same columns as I listed above in the same order then: in cell "I3" in the "Current" worksheet use the following formula: =IF(ISNA(VLOOKUP($A3,Previous!$A:$H,2,FALSE)),0,VLOOKUP($A3,Previous!$A:$H,2,FALSE))
  1. Copy this formulaacross your spreadsheet to pull in the rest of the data (all you have to do is copy and paste the original formula and change the return value for the VLOOKUP from a "2" to a "3" and so on).
  1. After you’ve copied the formula across, copy and paste the formula down the rest of your dataset.
  1. Copy all of the cells containing formulas and Copy > Paste Special > Paste Values. This will keep Excel from updating your formulas every time you sort, etc.
  1. Add column headers(i.e. Prv_Campaign, Prv_MatchType, etc.).
  1. Add percentage of change calculations for each metric.For example:(current_impr/prv_impr)-1 = %change in impr
  1. Follow steps 1-6 for your "Previous" worksheet and pull data from the "Current" worksheet using the same VLOOKUP FORMULA.

Analyzing your data for insights

With your data prepared for analysis, you can answer several different interesting questions in regards to what’s changed, what’s new, etc.
One insightful analysis is to look at the search trend queries that were producing high numbers of conversions last year that may not be performing very well this year.
  1. On the "Previous" worksheet, sort descending by conversions.
  2. Use your % of change column that you created earlier to find the insights (Curr_Conv/Prv_Conv-1 = % change).
After doing this very analysis, I found one search query that was in my top ten last year that had fallen to my 677thconversion contributor.
After a little digging, it turns out that it had been bid down due to low profitability. It produced lots of conversions (leads) that didn’t turn into business. Perhaps there is an opportunity here if I focus on post-conversion optimization a little further down the funnel.
These are the types of insights you will find when looking at your search query performance data year-over-year, and I barely scratched the surface. Keep digging and asking questions and I’m sure you will also find some actionable insights.
Chad Summerhillby Chad Summerhill, author of the blog PPC Prospector, provider of free PPC tools and PPC tutorials, and in-house AdWords Specialist at Moving Solutions, Inc. ( and

Find out how you're REALLY doing in AdWords!

Watch the video below on our Free AdWords Grader:

Visit the AdWords Grader.


Richard Kraneis
Apr 06, 2011

Excel VLOOKUPs? I'm outta here...

Chad, I wonder how many people read your article with interest until the phrase VLOOKUP was used. And then the "I do words, not math" people bailed for the exits.

A pity since VLOOKUPs aren't so tough with good training. As we both know, this is what a VLOOKUP does:

There are two main uses of =VLOOKUP: exact matches and approximate matches. An exact match for an =VLOOKUP would be comparing a short list of items with a long list in order to find a match. An example of an approximate match for an =VLOOKUP would be using a tax table. You earn a certain income, but it doesn’t perfectly match the table you are searching. It’s approximate.

Earliar this decade I wrote and marketed an ebook on Excel on the Internet (fun, I sold it in 40 countries using Google PPC). It includes a short useful chapter on VLOOKUP among other things.

For free, I'll give away 10 copies of the ebook (with its practice files) to the first 10 people who follow me this April 6 on Twitter at rkraneis. Just my way of spreading some Excel joy today to WordStream readers.

Thanks for the article Chad.

Friday PPC News Roundup: #PPC Chat Launch Edition
Apr 08, 2011

[...] PPC Hero also had a great post on 8 AdWords automated rules this week, and Bethany Bey made another guest appearance at the WordStream blog with a great post on the difference between Bing vs. Google match types. I also talked about AdWords’ announcement that campaign experiments are being offered in the opportunities tab, and had a post on how to report on how to report on multiple conversion types, and Chad had a great post on analyzing year over year search query trends. [...]

Apr 11, 2011

I thought this was a great post - very useful to know. thanks

Maneet Puri
Apr 18, 2011

Agree with Toby. Nice blog post, indeed!

Aug 08, 2011

I agree, very useful post. I'm glad I stumbled unto this post.

Thanks Chad!

Leave a comment