AdWords Tips

Analyzing Your AdWords Geographic Report Using Tableau

By Chad Summerhill January 25, 2011 Posted In: AdWords Tips Comments: 1

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

Geographic Report

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

Format as Table

  1. Add a City&State Column using Concatenate formula: =CONCATENATE([@City],", ",[@Region])

  2. Save as an Excel file.

Getting Your Data into Tableau

  1. Open Tableau.

  2. Click on either the "Connect to Data" icon or link.

Connect to Data

  1. Choose "Microsoft Excel" and click OK.

Microsoft Excel

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

Geographic Report

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

Create Calculated Field

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

Calcuated Field

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

Field Properties

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.

Tableau Public Book 1

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…"

Edit Colors

  1. Choose "Red-Green Diverging" from the "Palette."

  2. Then click on the "Advanced" button and set the "Center" to your target CPA or an acceptable CPA.

Edit Colors

  1. 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."

Quick Filter

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

AGG(CPA)

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.

    Filtered Data

  1. Click on "Create Set…"

Create Set

  1. Name your set something meaningful. In my example I filtered for "High CPA cities."

Name Set

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

AdWords Performance Grader




If you enjoyed this post, please consider leaving a comment.

Comments

Wednesday January 25, 2012

Daniel (not verified) Said:

Interesting tool! I'm excited to try out the public version!

Leave a Comment

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.
 
Free Keyword Tool

Get thousands of relevent keyword suggestions - more,
faster, free!

Free Keyword Niche Finder

Discover profitable pockets of keywords for your
business.

Free Negative Keyword Suggestion Tool

Identify wasted spend before it happens and increase
your paid search ROI.

Contact Us | Company | Support | Site Map | Trademarks | Privacy Policy © 2007-2014 WordStream, Inc. All rights reserved.