Getting the right data
- In the AdWords interface, select "Last month" from the date range picker.
- Select columns (Search Term, Impr. Clicks, Cost, Conv., Avg. Pos.).
- Download report and open it in Excel.
- Follow steps 1-3 for the same month last year (use "Custom date range").
- Combine both reports in a single Excel workbook and rename the worksheets "Current" and "Previous."
Preparing the data for analysis
- 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))
- Copy this formula across 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).
- After you’ve copied the formula across, copy and paste the formula down the rest of your dataset.
- 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.
- Add column headers (i.e. Prv_Campaign, Prv_MatchType, etc.).
- Add percentage of change calculations for each metric. For example:(current_impr/prv_impr)-1 = %change in impr
- 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
- On the "Previous" worksheet, sort descending by conversions.
- Use your % of change column that you created earlier to find the insights (Curr_Conv/Prv_Conv-1 = % change).