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.
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.
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.
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.
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.
Click on ‘Formulas’ in the PivotTable menu and select ‘Calculated Fields’.
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.
As you drag and drop these metrics, you’ll start to see the PivotTable being created.
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.
To clean this up a bit, I’ll select ‘Collapse’ from the PivotTable Menu.
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.
Select ‘Blanks’ and then ‘Insert Blank Line After Each Item’.
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.
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.
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’.
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.
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.