Google Analytics

Combining the Google Analytics and Google AdWords APIs for New Insights

Alexander Lucas, Google Analytics API Team – April 2010


  1. Overview
  2. Retrieving Data from the Google Analytics API
    1. Prerequisites
    2. Authenticate to Google Analytics Export API
    3. Define the request
  1. Set Up Your Filters
  1. Retrieving Data from the Google AdWords API
    1. Prerequisites
    2. Define the AdWords Request
    3. Give it a date range
    4. Send the Request
  1. Putting It All Together
    1. Print out the data
    2. What now?

Introduction

This article shows you how to use and combine data from both the Analytics Export API and the AdWords API in order to gain some key insights from the combination of the two.

Instead of digging through web interfaces for both of these services and manually copy/pasting one data point at a time, you'll be able to write an application that pulls exactly what data you need from both services, and combines it for you!

Why go after this big-picture data? Because context is king. By looking at Analytics and AdWords data side-by-side, you can gain a big-picture perspective that wouldn't be available to you by just looking at one data-set at a time. You'll be able to see the following relationships:

  • Total amount spent on a keyword (cost) vs transaction revenue — Immediately see return on investment for your keywords.
  • Quality score and average position of your ad vs Goals Completed — Is the quality score low for that keyword? Is that worth investing resources on improving?
  • Clicks vs bounces — Is this keyword targeting the right people?

This article assumes that you already have the following items ready:

  • Your Analytics and Adwords accounts are already linked, and autotagging is enabled in Analytics
  • API access and a developer token for the AdWords API.
  • Downloaded copies of the Java Client Libraries for both the Analytics Export API and the AdWords API.

You'll get the most out of this article if you download the source code, and see how the finished product fits together.

Overview

The code you're going to write will do the following:

  • Pull a month's worth of data from Google Analytics, including IDs for campaigns, adgroups, and keywords.
  • Using the IDs received from Analytics, retrieve specific keyword data from the AdWords API
  • Combine related Analytics and AdWords data together, using the IDs as joining points
  • Output all the data in a CSV format for examination and manipulation in your spreadsheet application.

Retrieving Data from the Google Analytics Export API

Prerequisites

To pull data from Google Analytics, you'll need the following pieces of information:

  • Your username/password information for a Google account with at least read-access to the view (profile) you wish to pull data from.
  • The view (profile) ID of the Google Analytics view (profile) you wish to pull data from. The easiest way for you to get the view (profile) ID is to visit the "View (Profile) Settings" page in Google Analytics, as described here.

To access the API, you'll first need to authenticate, and then define the query.

Authenticate

AnalyticsService analyticsService = new AnalyticsService("Super-Awesome Sample App");
analyticsService.setUserCredentials("username", "password");

For the purposes of this article, the Client Login method is being used, since that's the simplest way to access the Data Export API. You also have the option of using AuthSub or oAuth, but those are both outside the scope of this article. To authenticate, create a new AnalyticsService object, passing a swanky name for your client as the parameter. This object will do all the communication with the Analytics Export API for you. Pass it the username and password using the setUserCredentials method, and you're done!

Define the request

Next, we define exactly what data we want from the Export API. To do this, create a DataQuery object, and tell it exactly what data we want, and what date range we're requesting it for.

DataQuery dataQuery = new DataQuery(new URL("https://www.google.com/analytics/feeds/data"));
dataQuery.setIds("ga:1234");
dataQuery.setStartDate("2010-03-01");
dataQuery.setEndDate("2010-03-31");
dataQuery.setDimensions("ga:year,ga:month,ga:adwordsCampaignID," +
     "ga:adwordsAdGroupID,ga:adwordsCriteriaID");
dataQuery.setMetrics("ga:sessions,ga:entrances,ga:bounces,ga:transactions," +
    "ga:transactionRevenue,ga:goalCompletionsAll,ga:goalValueAll");
dataQuery.setSort("-ga:transactionRevenue,-ga:entrances");
dataQuery.setFilters("ga:adwordsCriteriaID!=3000000");
dataQuery.setMaxResults(100);

This code defines our request to the export API. Here are some things to keep in mind:

  • Dates are sent as strings in the form YYYY-MM-DD.
  • Sort occurs on transaction revenue and entrances.
    This sort occurs in descending order (highest value first) by using the minus character (-) in front of the metric to sort by. Without this character, sorting occurs in ascending order.
  • Only traffic from AdWords is being retrieved.
    • Because AdWords-specific dimensions are being used in the query, only AdWords traffic is returned. No organic search traffic or links from third-party websites are included.
    • Any traffic originating from a Display Network click on a third-party website will have a criteria ID of 3000000. Since this example is geared towards AdWords keyword data only, Display Network traffic is removed from our results using the filter ga:adwordsCriteriaID!=3000000.

Note: The dimensions and metrics used in this example are a small subset of those available via the Export API. For detailed list, visit the Dimensions and Metrics Reference.

When you're done, just send the request using:

DataFeed dataFeed = analyticsService.getFeed(dataQuery.getUrl(), DataFeed.class);

You'll get a DataFeed object containing all the Analytics data you requested, organized by dimension into elements called DataEntry objects. A DataFeed object is just a collection of DataEntry objects. Each of these represents values for all the metrics we requested. Also, the values of the requested metrics are further broken into each possible combination values across all dimensions. For instance, if our query looked like the following:

  • Date Range: Jan 1 to March 31, 2010
  • Dimensions: ga:month,ga:landingPagePath
  • Metrics: ga:sessions,ga:pageviews

The DataFeed object would have the following entries in it:

Data Entry # Month Landing Page Path Sessions Pageviews
1 1 /landing1/ 14 30
2 1 /landing2/ 20 80
3 2 /landing1/ 10 29
4 2 /landing2/ 15 64
5 3 /landing1/ 4 10
6 3 /landing2/ 10 42
7 3 /landing3/ 10 42

Assuming the first row of this table represents a single DataEntry, the data it held, translated to English, would mean "In January, there were 14 sessions and 30 pageviews from users who entered your site starting at the URL yoursite.com/landing1/." Notice how no two DataEntry rows have the same values for all their dimensions (no two have the same month AND the same landing page), but rows 6 and 7 have identical values for both sessions and metrics. When you request metrics from the API, the values returned will be segmented by the dimensions applied to that request.

Set Up Your Filters

This is the crossover point between the two APIs. The data we pulled from Analytics includes a series of keyword IDs that are used in a AdWords API call to specify which keywords we want data for. This is done using the AdGroupCriterionIdFilter. Each AdGroupCriterionIdFilter takes the IDs of campaigns, adgroups or criterion you want data on. You pass the AdWords API an array of these filters representing a collection of different items the client is requesting data for, instead of spreading them out over many different requests

In the Analytics request, the data returned is grouped by ad group ID and criterion ID. Now those two pieces of information will be retrieved from the data received from Google Analytics, and placed into the filters that will be part of the request sent to the AdWords API. Since the filters are meant to look up specific keywords across multiple AdGroups, the filters need to include both the AdGroup and Criterion IDs. If you just wanted a list of all criterion within an AdGroup, you could include only the AdGroupID.

int numFilters = analyticsData.getEntries().size();
AdGroupCriterionIdFilter[] critFilters = new AdGroupCriterionIdFilter[numFilters];
for (int i = 0; i < numFilters; i++) {
  DataEntry entry = analyticsData.getEntries().get(i);

  // All dimensions are returned from the API as Strings, but in the case of groupID and
  // criterionID, they're really longs.  Cast as long using Long.parseLong(str).
  Long groupID = Long.parseLong(entry.stringValueOf("ga:adwordsAdGroupID"));
  Long critID = Long.parseLong(entry.stringValueOf("ga:adwordsCriteriaID"));

  // Add AdWords IDs from Google Analytics to criteria filter array.
  AdGroupCriterionIdFilter critFilter = new AdGroupCriterionIdFilter();
  critFilter.setAdGroupId(groupID);
  critFilter.setCriterionId(critID);
  critFilters[i] = critFilter;
}

Here we're creating the array of filters, and populating the array one element at a time. Pulling the CriterionID and AdGroupID out of Analytics is done using the stringValueOf method. This method pulls data out of DataEntry objects, similar to the way you would get data out of a hashtable — just pass it the name of a dimension or metric, and it will pass back the value in the form of a string.

Now, onto AdWords!

Retrieving Data from the Google AdWords API

Prerequisites

In order to connect to the AdWords API, you will need several pieces of information.

  • Login/Password
  • ClientID (visible in top nav when you log into your AdWords MMC account and click on the client via the web interface)
  • User Agent
  • Developer Token

The steps to pulling data out of the AdWords API are similar to the steps for Analytics:

  • Authenticate
  • Define the request
  • Define the date range
  • Send the request

Note: Your use of the AdWords API is governed by the AdWords API terms and conditions.

Authenticate

AdWordsUser user = new AdWordsUser();

The Java API wrapper for the AdWords API lets you store all your authentication data in an adwords.properties file that sits in your home directory. For details, check the "Basic Usage" section of the README file bundled with the Java library.

If you choose not to go this route and want all the login/auth information explicitly placed in your code, the constructor looks like this:

AdWordsUser user = new AdWordsUser(USER, PASS, CLIENTID, USERAGENT, DEVTOKEN, USE_SANDBOX);

Define the AdWords request

Next create an AdGroupCriterionSelector object. This object collects and organizes all the information we're sending to the AdWords API for our request.

AdGroupCriterionSelector agcSelector = new AdGroupCriterionSelector();
agcSelector.setCriterionUse(CriterionUse.BIDDABLE);
agcSelector.setUserStatuses(new UserStatus[ {UserStatus.ACTIVE, UserStatus.DELETED,
    UserStatus.PAUSED});
agcSelector.setPaging(new Paging(0, 100));
agcSelector.setIdFilters(criterionFilters);
  • CriterionUse.BIDDABLE is an enum representing keywords which are bid on, as opposed to "Negative" keywords, which are keywords that your ad does not show up for.
  • setUserStatuses — A keyword can be active, deleted, or paused. For the purposes if this exercise, information on all three types is being requested.
  • Paging objects hold two pieces of information — The "starting" index for the query, and the size of the page in items. So 0,100 means return the first 100 items.
  • setIdFilters — Adds the filters we created earlier to the selector.

Give it a date range

The date range is going to specified by a DateRange object, passed to a StatsSelector object, passed to the AdGroupCriterionSelector object. The code for that looks like this.

DateRange dateRange = new DateRange("20100301", a"20100331");
StatsSelector statsSelector = new StatsSelector();
statsSelector.setDateRange(dateRange);
agcSelector.setStatsSelector(statsSelector);

The DateRange object takes two parameters, start date and end date, each in the form YYYYMMDD. Attentive readers will notice that this is nearly identical to the format which Google Analytics takes, but without the hyphens.

Send the request

Now that the selector is completed, create the service interface that will handle the request, and make the request using the selector as a parameter. In this case the request specifically involves criterion, so the line of code defining this service will be creating an AdGroupCriterionServiceInterface.

AdGroupCriterionServiceInterface agcService = user.getService(
    AdWordsService.V200909.ADGROUP_CRITERION_SERVICE);
AdGroupCriterionPage criterionPage = agcService.get(agcSelector);

That criterionPage variable now holds all the information the AdWords API has sent you about the criterion you specified in your filters.

Putting It All Together

Print out the data

You have a DataFeed object, containing a lot of Analytics data. You have a AdGroupCriterionPage object, containing a lot of AdWords data. These two sets of data have a deep, meaningful relationship. How do we best express matters of the heart? With a spreadsheet, of course.

In this section, we'll output all this information in CSV format, for easily displaying, pruning, and/or graphing in the spreadsheet application of your choice.

Remember in the beginning, our Analytics data was sorted in descending order by transaction revenue and entrances, both highest-value-first, so that the most important data was at the top. The AdWords API doesn't doesn't take a sorting parameter like that, so it's helpful for us to create a small HashMap. We'll use this to easily access keyword info based off its ID, without looking through all the AdGroupCriterion objects in the page returned from AdWords every time we need to find that one ID.

HashMap critDict = new HashMap();
for (AdGroupCriterion criterion : criterionPage.getEntries()) {
  critDict.put(criterion.getCriterion().getId().toString(), criterion);
}

Good! Now every time we want to pull all the data for a specific AdGroupCriterion object, we just send an ID to the critDict (criterion dictionary). An alternative would be to simply dump the objects in that page into an array and sort that to match the Analytics data ordering; however, this is much less flexible. You'd have to account for the possibility of missing AdWords entries and make sure the right slots were empty. This way, you know immediately, on each lookup, that either that entry exists or it doesn't in O(1) time.

Now that all the data is in order, it's time to print.

Start by printing out the column names. There's no especially "elegant" way to do this. You're just going to have to buckle down and print out one giant string.

System.out.println("Year,Month,Campaign ID,Ad Group ID,Criteria ID,Sessions," +
    "Entrances,Bounces,Transactions,Transaction Revenue,Goal Completions,Goal Value," +
    "Keyword,Quality Score,Impressions,Clicks,Average Position,Cost,Average CPC, Max CPC");

To print out the actual data corresponding to these column names, write a for-each loop that iterates over the List you access with the getEntries method.

The for loop is broken into two pieces. The first piece prints all the data from the Analytics data entry. The second piece prints the corresponding AdWords data. The Analytics piece looks like the following:

for (DataEntry entry : dataFeed.getEntries()) {
  StringBuffer buffer = new StringBuffer();
  for (Dimension dimension : entry.getDimensions()) {
    buffer.append(entry.stringValueOf(dimension.getName()) + ",");
  }

  for (Metric metric : entry.getMetrics()) {
    buffer.append(entry.stringValueOf(metric.getName()) + ",");
  }

Notice none of the dimensions or metrics need to be explicitly named. The names of each are available via the getDimensions() and getMetrics() methods, and are ordered identical to the original request. Pulling the value is just a matter of sending that dimension or metric name to the stringValueOf method we used when creating filters. So all you have to do is loop through the available dimensions, and then through the available metrics, and you're done.

Grab the AdGroupCriterion object holding data for this specific criterion by pulling the ID from the DataEntry used earlier in this iteration of the loop and using it to look up the criterion in the HashMap we set up.

  String criterionID = entry.stringValueOf("ga:adwordsCriteriaID");
  BiddableAdGroupCriterion bagc = (BiddableAdGroupCriterion) critDict.get(criterionID);

Note the explicit cast to BiddableAdGroupCriterion. This is a safe cast, since earlier in the code we set the type of criterion to query for as CriterionUse.BIDDABLE.

  if (bagc != null) {
    Stats stats = bagc.getStats();
    double maxCPC = 0.0;
    if (bagc.getBids() instanceof ManualCPCAdGroupCriterionBids) {
      ManualCPCAdGroupCriterionBids bids = (ManualCPCAdGroupCriterionBids) bagc.getBids();
      maxCPC = moneyInDollars(bids.getMaxCpc().getAmount());
    }
    if (bagc.getCriterion() instanceof Keyword) {
      Keyword keyword = (Keyword) (bagc.getCriterion());
      buffer.append(keyword.getText());
      buffer.append("," + bagc.getQualityInfo().getQualityScore().toString());
      buffer.append("," + stats.getImpressions());
      buffer.append("," + stats.getClicks());
      buffer.append("," + stats.getAveragePosition());
      buffer.append("," + moneyInDollars(stats.getCost()));
      buffer.append("," + moneyInDollars(stats.getAverageCpc()));
      buffer.append("," + maxCPC);
    }
  }
  System.out.println(buffer.toString());
}

// Helper method, just converts a Money Object to its value in US Dollars
public double moneyInDollars(Money money) {
  return money.getMicroAmount() / 1000000.0;
}

  • You'll notice that most of the data being printed out comes from the Stats object. This object contains much more data, and you can learn about it by reading the Stats API Reference.
  • Max CPC is pulled in a different manner than average CPC. That's because there's different ways to bid in AdWords, and max CPC is only relevant when you're manually setting that number yourself - In which case, the method we're using to get bids, BiddableAdGroupCriterion.getBids(), will return an object of type ManualCPCAdGroupCriterionBids. This is the object that can be mined for a Max CPC.
  • Average CPC, max CPC, and total cost for any given keyword are all returned as Money objects. The Money object contains the value in "micros," which are millionths of the fundamental currency unit. It's advantageous to use such a helper method no matter what currency you are working with, as it clears up what's going on in code much better than "getCost() / 1000000" would.

What Now?

At this point, when you run the program, there should be several comma-separated lines of information in front of you. Save that data to a .CSV file, and open it in your spreadsheet application. Now, in front of you, is the finished result: keyword data from AdWords, cross-referenced with what users did on your site after clicking those keywords. A few interesting things you can easily cross-reference now, that before would have required plenty of switching between AdWords and Analytics browser tabs:

  • Cost VS Revenue - Oh Look! ROI!
  • Quality Score VS Average Position VS Goals Completed - What keywords are worth increasing investment in?
  • Clicks VS Bounces - Is this keyword targeting the right demographic? Could "Chicago Bears" be getting sports fans instead of Illinois-based conservationists?

Of course, this is only a tiny subset of the data relationships you now have programmatic access to. For more ideas on what kind of data you can analyze, try the Google Analytics Dimensions & Metrics Reference and the Google AdWords Getting Started Guide. Remember, the Google Analytics Export API and the AdWords API can do much more for you together than either piece can do alone. Leveraging these two datasets together can expose untapped opportunities in optimizing your web traffic and ad spend. Dig in and tell us what you come up with!

Back to Top

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.