Analyzing Google AdWords Data - Getting Started

By Guest (not verified) March 30, 2008

Simply put, Google is the juggernaut of the online marketing world. With a 25% market share of all online advertising and 75% of paid search revenues, they generate over $3 billion in revenue each quarter!

Yet, despite their ubiquity, often as a business's primary advertising vehicle, there is no great way to analyze the dollars being spent on Google advertising. With so many factors contributing to a campaign's success (external and advertiser controlled), it is often a crapshoot on how best to understand, improve, and communicate advertising results.

Problems with Google Adwords Reporting Tools
Because Google is the largest advertising platform, it is often assumed their reporting tools will help answer questions about advertising campaigns, copy, and keywords. While Google does have many packaged reports and the ability to create additional reports, they are not meant for in-depth analysis. Existing third party applications built to add more flexibility to Google reporting fall into two buckets:

1. Excel and reporting tools are the de facto standard for analyzing Google AdWords reports. Row after row of text and numbers discourage users from doing much more than asking the most basic questions. Analysis runs the gamut from simple sorting and filtering to more advanced Pivot tables, graphing wizards, and other Excel analytics. Pivot tables can be effective for specific and well known analysis paths, but typically users get stuck with no opportunity to ask additional questions or drill further into their data.

2. Dashboard Gauges that provide graphical displays of key performance indicators. These tools assume that managing an AdWords campaign is simple enough to be captured by a fuel gauge, a few connected chart types, and a fancy blinking light. In reality, even sophisticated metrics can’t fully capture the stories that AdWords data can tell. It is the stories in the details that guide intuition and make it possible to manage an AdWords campaign effectively. Rich graphical campaign summaries linked to visual “details-on-demand” deliver the broad strokes and the decision altering nuances.

Instead of these tools, I use my company’s software application, Tableau Desktop, to help me analyze and understand our AdWords campaigns. Yes, we do eat our own dog food. By connecting Tableau to data exported from AdWords and then simply dragging variables onto columns, rows, filters and other “shelves”, I am able to visually analyze my data rapidly. (Note that at the end of this post, I’ve listed the necessary steps in Google AdWords to export your data and a link to a free trial of Tableau so you can create your own AdWords analysis.)

One caveat: although these data are based on Tableau’s actual campaigns, all elements about the data have been changed for confidentiality reasons. So, instead of seeing keywords and real costs associated with campaigns for visual analysis software, you’ll see campaigns and theoretical costs about car dealership ads.

Answering Google Adwords Questions with Tableau
There is no one best way to analyze AdWords data with Tableau. Tableau lets you take an infinite number of analysis paths depending on the depth of data available, the questions you are trying to answer, or trends, outliers, and phenomena you discover along your journey. With this in mind, below is a question I answer all the time – what is generating our leads? This question is followed by a complete analysis path I often take when investigating our AdWords data.

What is generating my Leads?
Usually a good starting point is just understanding which ads and keywords are generating clicks and conversions. By dragging “clicks” to columns and "conversions" to the rows, I see a scatter plot instantly. This is a pretty straight forward question that typically leads to many more questions. I answered a couple obvious follow-up questions by adding additional dimensions to this view.

Google AdWords Conversion Analysis

Figure 1: In this view each keyword/ad combo is represented by a symbol. It's easy to see the top lead-producing combos on the graph's right hand side. I've sized the keyword/ad combos by impressions, colored them by their avg. position (high positions typically perform better but cost more), and labeled them with cost/conversion (for this case study, I’ve disguised our real goal and set it arbitrarily at $30/lead). From this view I can quickly see where I should change or test ad copy (lots of impressions with an OK position, but not many clicks), landing pages (lots of clicks but not many conversions), and bid strategy (a combination of position and cost/conversion goals).

The following questions are a likely scenario. This whole analysis, from connecting to my AdWords report to making rapid, informed campaign decisions, took about 20 minutes.

What are my most successful campaigns?

Google AdWords Campaign Analysis

Figure 2: In this view I can see how my top campaigns performed in Q1 and Q2. The conversion bars contain keywords colored by their cost/conversion. I can quickly identify keywords (red marks = high cost) that may need further analysis. I can also see how my leads and cost-per-lead for each campaign are changing from one time period to another.

Which ad groups are contributing to the success or failure of a my campaigns?

Google AdWords Ad Group Analysis Link
Google AdWords Ad Group Analysis

Figure 3: I jumped to this ad group analysis from Figure 2 via the pictured “sheet link” (sheet links in Tableau link you from one worksheet to another in just one click). This link filters my ad group analysis so that it only displays the selected campaign. We can see that the Ford campaign has a couple main lead producing ad groups - F-150 and Ranger. With this view it's easy to see each ad group’s overall performance and decide if further analysis is needed. A good health indicator for campaigns, ad groups, and your entire AdWords system is an improving conversion/impression ratio.

Which keywords are contributing to the success or failure of my ad groups?

Google AdWords Keyword Analysis Link
Google AdWords Keyword Analysis

Figure 4: I jumped to this keyword analysis from Figure 3 via the pictured sheet link. This link filters my view so it only displays the ad groups I select. In this example, I chose the F-150 ad group. We can see this ad group has several lead generating keywords. This view includes a bunch of measurements (columns) for each keyword. With these metrics I can make quick decisions about what actions are needed for each keyword.

How can I make a keyword live up to its potential?

Google AdWords Ad Copy Analysis Link

Google AdWords Ad Analysis

Figure 5: I jumped to this keyword ad analysis from Figure 4 via the pictured sheet link. The link filters this view so it only displays the selected keywords. We can see that the ford 150 engine keyword advertisement at the top of the graph is performing very well – even from the third position. This analysis path delivered a series of key insights that led to these actions:

  • invest more in ford 150 engine keyword – see if we can get a top 2 position (we’re still under our cost/lead goal)
  • work on ford 150 engine CTR by developing some ads based on {insert keyword}
  • work on ford 150 engine conversion rate with at least an A/B landing page test synced with new ad copy
  • Download a pdf of these views

Connecting Tableau to your Adwords Data
The easiest way to start analyzing Google campaigns is to log into your Google AdWords account and go to the Reports tab. Here are the settings I'd recommend to get started:

In Google:

  • Ad Performance Report
  • Text Ad
  • Daily
  • Appropriate date range
  • All Campaigns and all their Ad Groups
  • Select all relevant columns that are not ratios or calculations. So include Keyword, Conversions, and so forth, but not CTR, Avg CPC, etc. The only exception being Avg Position. We'll use Tableau to do all the calculations.
  • Set this template up to run every month and have it emailed to you.

Open the exported csv file in Excel:

  • Delete any extra rows above the column names as well as any summary statistics rows at the bottom.
  • Save the file as an Excel file.
  • Drag and drop the Excel file onto your Tableau Desktop shortcut (if you don't have Tableau, download a 2 week trial).
  • Start using fast analytics to explore and understand your Google AdWords campaigns.

Getting Started tips:

  • If your AdWords report is a large file, extract your Excel data into a Tableau Extract database.
  • Define your primary calculated fields (CTR, CPC, etc). Click here to download the basic formulas.
  • Scatter plots are a clever way to analyze AdWords data.
  • Build a 360 degree view of your campaigns by adding additional data sources to your Tableau dashboards. At any time you can update your worksheets, views, dashboards, and reports by editing your data connections and browsing to the most up-to-date files. This is a great way of reusing work product for reporting and dashboards. However, interactive, dynamic analysis is where you'll make your most profound business changing discoveries.

Be sure to watch our ondemand video "Analyzing Adwords." The video shows nearly everything above!

By Guest (not verified)

Comments

Submitted by Jock M. on

Wade - great post. I hope people will tell us about connecting Tableau to their own AdWords data.

Submitted by Andy C. on

Wow - that's a fantastic example of dogfooding. Great post, and bound to convince more people of the value of Tableau!

Submitted by . J. on

This is off-topic -- but using "dogfood" as a verb -- i.e. "dogfooding" is absolutely HILARIOUS -- you made my day. I am going to use the daylights out of it for awhile.

\jm

Submitted by Chris (not verified) on

Question - why not download the ratios that Google already provides? What's the advantage of calculating them in Tableau?

Thanks for the great example and I hope there are more coming.

Dogfood has been a verb for a while!

Submitted by Wade Tibke (not verified) on

The attached workbook has the explanation inside. Let me know if you need more details. Basically Tableau needs to know how many impressions (and clicks and conversions) the ratios are based on to do further calcs.

Submitted by RGA (not verified) on

Do you have any other visualization techniques? For data exploration, correspondence analysis would be really helpful, since it groups disparate items that have similar characteristics.

Do you support more advanced quant analysis--e.g., multivariate regression?

Submitted by Barb (not verified) on

Interesting tool. my clients aren't doing e-commerce at their sites yet, so I technically don't have any "conversions". We have KPIs, but those are not captured in AdWords. how can I connect these 2 things for my clients that are not using Omniture?

Submitted by Wade Tibke (not verified) on

Hi Barb - you could connect to whatever data files you have that contain the KPI data. So maybe some adwords click/cost data csv, website traffic excel, CRM, etc and combine them all into a single dashboard.

Submitted by Adeel (not verified) on

Great post, but has any one tried using Google Analytics custom reports. These reports are very flexible and can provide useful insight to your adwords advertising and above all they are FREE!!!!

Submitted by Rob (not verified) on

Hey Wade -- very informative post that is still relevant a year and half later!

Would you be willing to post the packaged workbook so that we could see the same datasource layout? We're having issues reproducing the reports.

Rob

Submitted by Wade Tibke (not verified) on

Hi Rob - feel free to shoot me an email at wtibke @ tableausoftware.com. I'd be happy to do a goto meeting and dicuss your data.

Wade

Submitted by briab (not verified) on

Hey will this pick up repeat and fraudulant clickers? I am looking for something that flags when the same ip has clicked 5 times and more