When running AdWords reports one of the challenges when looking over geographic performance is that you are limited to only reports on a daily unit of time. I will show you how you can use Excel to group the daily data into a report you can really use to help optimize the geographical areas you are targeting.
Step 1: Create a new report from the reporting tab.
Select Geographic Performance for the report type. Next select the level of detail you want to view under the settings. For this example I’m going to select Account level. Now here is where you will notice the only unit of time you can select is daily.
The date range is up to you, just select the most relevant time period you prefer to see the performance for. I have chosen the previous month for this example.
Add or Remove Columns, here is where you can choose what data you want to include in your report. I suggest you include Country/Territory, Region, Metro and City until you have a better feel for what area you want to narrow your reports down to. Also, select any conversion data you want to view if the account has conversion tracking in place.
Once you have clicked on Create Report and are returned back to the reporting page click on the name of your new report. You will see an Export Report button at the top of the report. Select the option for .csv (for Excel).
When you have the report exported and open you will see what seems to be just a mess of data. Save the messy report as an Excel Workbook so we can have some fun with a Pivot Table. Yes, I said Pivot Table, don’t get worried. I’ll walk you step by step if you have never worked with Pivot Tables before.
First click on Insert from the top menu, next click on PivotTable.
For your data range select all the columns in row 4 with data, then while holding down the shift key press the Ctrl button and the down arrow key. This should select all the data in the report from row 4 down.
Now select Impressions, Clicks, Avg CPC, Cost and the conversion data you want to include if applicable into the Values as the Column Labels.
You can leave Impressions, Clicks, Cost, Conversions and Total Conv. Value as Sum, you want the total tally of these on your report.
You can close the Pivot Table Field List at this point. I find it easier to edit the data and apply additional filtering if I copy the table and paste it into a new sheet.
Now here is a report that makes a little more sense. You can roll-up the geo locations to Country/Territory or Regions. You can also dress up the report a bit more to be more visual friendly.
This is a good geographic performance report you can use to find which areas are performing best for a longer amount of time than daily. This report is also great for comparing PPC marketing efforts to any direct mail marketing campaigns.
This report can help you limit down your campaigns to only areas that generate a higher performance and reduce your spending over night for an almost instant increase in return.