Analyzing Your AdWords Geographic Report Using Tableau

One of my PPC New Year’s resolutions was to start taking advantage of geo-targeting more in AdWords. Analyzing your AdWords Geographic Report is a good place to start with developing your strategy for setting up geo-targeted campaigns.

Everyone knows about the power of Excel for PPC data analysis, but not too many people are taking advantage of the power of data visualization using Tableau.

Download Tableau Public

Tableau is an easy-to-use business intelligence and data visualization software. You can download a copy of Tableau Public for testing. Tableau is like a visual pivot table and really shines at displaying multi-dimensional views of data. Don’t be intimidated, Tableau has lots of training material on their website, so it won’t take you long to start slicing and dicing your data.

Get Your AdWords Geographic Report

In order to do this analysis you will have to download an AdWords Geographic Report from the “Dimension” tab inside the AdWords web interface.

1. Select the campaign you want to analyze.
2. Select a large date range (like a year).
3. From the “Dimension” tab, select the “Geographic” view.
4. Adjust your columns as shown below (you don’t have to select the calculated metrics like CTR because we will use Tableau to generate these metrics).

5. Download your report.

Prepare Your Data

Where Tableau shines at data visualization, Excel shines at data preparation.

1. Delete the last row of data (the Totals row).
2. I delete the country column (I only advertise in the United States).
3. Format your data as a table by selecting the entire data set and clicking on “Format as Table” from the “Home” ribbon.

4. Add a City&State Column using Concatenate formula: =CONCATENATE([@City],”, “,[@Region])
5. Save as an Excel file.

Getting Your Data into Tableau

1. Open Tableau.
2. Click on either the “Connect to Data” icon or link.

3. Choose “Microsoft Excel” and click OK.

If everything went well your data should show up like this in Tableau:

4. Create Calculated Fields for CTR, CVR, and CPA by right-clicking in the “Measures” area and selecting “Create Calculated Field.”

5. Use the following formulas:

• CTR = sum([Clicks])/sum([Impressions])
• CVR = sum([Conv# (1-per-click)])/sum([Clicks])
• CPA = sum([Cost])/sum([Conv# (1-per-click)])

6. Format your measures appropriately by right-clicking on the metric you are formatting then > “Field Properties” > “Number Format…”

Start Visualizing Your Data for Insights

1. Drag the “CVR” measure to the “Columns” shelf by clicking on “CVR” and dragging.
2. Drag the “Cost” measure to the “Rows” shelf.
3. Drag the “City&State” dimension to the “Label” shelf.
4. Drag “Impressions” and “Clicks” to the “Level of Detail” shelf.
5. Drag “CPA” to the “Color” shelf.

You should now have a scatter plot visualization to play with. Try moving measures and dimensions to different shelves. Maybe drag the “Impressions” measure to the size shelf, etc.

Getting All Ninja in Tableau

Let’s make our “CPA” metric work harder for us.

1. Click on the top right of the “AGG(CPA) box and click “Edit Colors…”

2. Choose “Red-Green Diverging” from the “Palette.”
3. Then click on the “Advanced” button and set the “Center” to your target CPA or an acceptable CPA.

4. Now let’s add a “Quick Filter” by clicking the dropdown arrow on the “AGG(CPA)” in the “Color” shelf and choosing “Show Quick Filter.”

5. Now you can zoom in on the good CPA’s or bad CPA’s very easily.

We can see which cities have a good CPA just by the color, and you can zoom in and out to display more or less data on your scatter plot with your quick filter.

Go ahead and add more “Quick Filters” and start experimenting with the power of Tableau. You can add “Region” to the “Filters” shelf and then make it a “Quick Filter” to easily choose between states, etc.

Another great feature of Tableau is the ability to create “sets.” After you have an interesting view of you data in the scatter plot:

1. Select your filtered data on the scatter plot by holding down the left-click button on your mouse and dragging over the data.
   

2. Click on “Create Set…”

3. Name your set something meaningful. In my example I filtered for “High CPA cities.”

You can treat a set just like any other dimension which is very powerful.

The scatter plot in the example above is a very powerful data visualization technique for comparing highly dimensional data. We can quickly identify cities with below average CTR or CVR. Because we used “CPA” on the color shelf and took the time to format it appropriately we can easily see cities with high or low CPA’s.

I barely scratched the surface of the power of Tableau. In the hands of a knowledgeable PPC analyst it is the perfect tool for slicing through your AdWords geographic data.

Take it for a test drive and consider adding it to your PPC tool box. Once you get your head wrapped around dimensions, metrics, and the various shelves and filters you may find yourself using Excel pivot tables less and less.

Chad Summerhill is the author of the blog PPC Prospector, provider of PPC tutorials, and in-house AdWords Specialist at Moving Solutions, Inc. (UPack.com and MoveBuilder.com).