Recently one of our clients wanted to find out where in the United States they were getting the most conversions, and which of those places were providing the most (and least) effective returns. This might be something you want to know too, so here’s how you can build a great report that shows you where your campaigns are the most productive geographically.

First, you’ll want to go to the ‘Dimensions’ tab in the Adwords interface and create a report that extracts the info you want.  First, you’ll need to specify your date range.  One thing to note here is that when making reports like these in accounts with many campaigns and large amounts of data, you’ll want to be careful about choosing too long of a date range and overloading the dimensions report maker.  When I tried to run one for a large account for a whole year, I kept getting error messages.  No fun.  So I decided to pull out the info for the past six months.

Screen Shot 2014-04-17 at 1.25.28 PM

 

In the ‘View’ pull-down tab, select ‘User Locations’.

 

*The ‘User Locations’ selection is slightly different than ‘Geographic’ in that User Locations will show you the exact location of the user who received an impression of your ad, whereas Geographic will show customers Physical location (where the user received the ad impression) and their Location of interest (the location a user searched for on Google, a location related to content the user viewed and more).

 

I have a couple more columns that I want to see, so I selected ‘Columns’ and added Conversions and Cost/Conversion to my report.

Download the report.

Google will export a .csv file that you can then open with Excel.  We’re working in Office for Mac 2011, fyi.  If you’re on a PC version of excel, don’t worry, the process is very similar.

First, apply a filter to the data set (Data > Filter) so that your data can be easily organized.

Next, select Data from the menu bar, then choose PivotTable.  Excel should automatically select the data range.  I prefer creating the table on a new worksheet, so thats what I’ll choose.

Screen Shot 2014-04-17 at 2.00.05 PM

Now, in your new sheet you can use the PivotTable Builder to break down your data in to something thats a little easier to understand.

Heres what you’ll see.

Screen Shot 2014-04-17 at 2.03.51 PM

 

First, drag the Country/Territory field down into the Report Filter box.

Next, we’ll add Region to the Row Labels box, since we’re trying to see all this data organized by state.  While we’re at it, I’ll also add Metro Area, just for kicks.

Screen Shot 2014-04-17 at 3.47.52 PM

 

 

I also would like to find out what the CPA is for each state, so I’ll need to create a formula for that in my Pivot Table.

Screen Shot 2014-04-18 at 12.53.09 PM

 

Click on ‘Formulas’ in the PivotTable menu and select ‘Calculated Fields’.

Screen Shot 2014-04-17 at 3.43.25 PM

 

 

I’’ll name it “CPA Calc”.  I then insert the ‘Cost’ field, divide it by the ‘Conversions’ field, and click ‘OK’.  We can then add that field to the Values column as well.

Screen Shot 2014-04-17 at 2.03.21 PM

 

As you drag and drop these metrics, you’ll start to see the PivotTable being created.

Screen Shot 2014-04-17 at 2.26.23 PM

 

Now, since I’m only interested in performance in the United States, I’ll click on the arrow next to ‘Country/Territory’ in cell A1/B1, and de-select all, then re-select United States so that I only see the rows with info from the US.

 

Screen Shot 2014-04-17 at 2.29.01 PM

 

To clean this up a bit, I’ll select ‘Collapse’ from the PivotTable Menu.

Screen Shot 2014-04-18 at 3.00.12 PM

 

Now we’re getting something we can understand.

Whenever I have a PivotTable like this with expansion arrows in the rows, I’ll usually add a space between each row to clean up the look of things.  That’s easy to do in the PivotTable menu, too.

Screen Shot 2014-04-18 at 2.38.20 PM

 

Select ‘Blanks’ and then ‘Insert Blank Line After Each Item’.

Screen Shot 2014-04-17 at 2.43.39 PM

 

Perfect.  Now, to give the client an easier way of seeing what is performing well and what isn’t, I’ll apply some color scales to a few of the columns.

Screen Shot 2014-04-18 at 2.38.54 PM

 

Screen Shot 2014-04-17 at 2.48.33 PM

 

First, highlight the column you’d like to format.  Then apply the color scales.  I’m going apply them to my ‘Sum of Conversions’ and ‘Sum of CPA Calc’ columns.

Screen Shot 2014-04-17 at 4.04.42 PM

 

Everything looks good here, so for my final cleanup step, I’m going to adjust the way these numbers appear on the table.  To do that, I’ll go back to my PivotTable Builder and click the ‘i’next to the field i want to adjust, which in this case is ‘Sum of CPA Calc’.

Screen Shot 2014-04-17 at 4.06.00 PM

 

Then click ‘Number’ and choose currency.  I’ll then go through and clean up the other ‘Values’ fields and clean the other columns up in the same way.

Screen Shot 2014-04-17 at 4.27.37 PM

 

Now we have our table, which is clean and concise enough to easily interpret your campaign’s performance with regard to user location. We can be confident in any geotargeting changes that we make.  One strategy would be to segment out the top performing state(s) in attempt to maximize performance in that specific area.  You may also decide to exclude certain states or regions that have historically underperformed in your AdWords account.  Either way, you have a nice report to show to your client how their nationally targeted PPC campaigns perform at the individual state level.

Recent Posts
Showing 2 comments
  • James Harrison
    Reply

    Do you know which states are typically the cheapest (per click). Does cost go hand and hand with population/search volume?

  • Brad Merkel
    Reply

    For this particular report we were unable to find any correlation between location and average cost-per-click. Oddly enough, Mississippi had the highest CPC at $0.99 and Maryland was the cheapest at $0.48. California was in the middle of the pack at around $0.60 per click.

Leave a Comment