Creating a Usable PPC AdWords Geographic Report

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.

AdWords Report Center

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.

AdWords Report Center Geo Report

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.

AdWords Report Center Options

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

Exporting AdWords Geo Report

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.

Creating Excel Pivot Table

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.

Selecting Pivot Table Data

 

In the Pivot Table Field List select Country/Territory, Region, Metro and City and add to the Row Labels.

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.

  • Brandy,

    Great post! Wish you would contribute to Vizion's blog more with your useful PPC account managing insights!

    I am subscribed!

    Cheers,

    Mike Stewart

  • Hi Brandy,

    I was about to say "Great Post!", but there's something that doesn't work.

    Changing SUM for AVERAGE when it comes to CTR, $/CONV. and %CONV. will not give you the correct average…

    Or there's something I don't understand…?

    • John Carruthers

      Hi Eric-

      Good catch. I looked into this issue and have altered the blog to display the correct information. I hope that it's still useful for you.

      Thanks

  • John Carter

    One of the best Google Adwords tips I ever got, from a guy named Simon, was to include a solid mix of the three (four including modified) match types, with the correct proportions of broad, phrase, and exact within each ad group. Most people that get started on PPC don’t think to do things that way. If you want to talk to Simon, you can call him at 219-733-4687.