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

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

3. After you’ve copied the formula across, copy and paste the formula down the rest of your dataset.

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

5. Add column headers(i.e. Prv_Campaign, Prv_MatchType, etc.).

6. Add percentage of change calculations for each metric.For example:(current_impr/prv_impr)-1 = %change in impr

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

by 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. (UPack.com and MoveBuilder.com).