Analytics anyone can use.
Data prep anyone can use.
Analytics for organizations.
Cloud analytics for organizations.
Despite Adwords' ubiquity, often as a business's primary advertising vehicle, there is no great way to analyze Adwords data and determine campaign efficiency and ROI. 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. Google has added tracking and reporting abilities to their interface, but these tools lack the deep analysis capabilities that one would want for thorough reporting and a deeper understanding of campaign performance.
1. Excel is often 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 Adwords data rapidly. (Note that at the end of this post, I’ve listed the necessary steps to export your Adwords 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.
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.
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.
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.
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.
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.
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:
The easiest way to analyze Adwords data is to log into your Google AdWords account and go to the Reports tab. Here are the settings I'd recommend to get started:
Open the exported csv file in Excel:
Be sure to watch our ondemand video "Analyzing Adwords." The video shows nearly everything above!
Want to do more with your Google data? Learn how to optimize your digital marketing and website analytics with Google products and Tableau.