SpreadsheetExporter.java - ga-api-java-samples - Project Hosting on Google Code

Source path: svn/ trunk/ src/ v1/ SpreadsheetExporter.java

// Copyright 2009 Google Inc. All Rights Reserved.
 
/*
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.  
 * You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.analytics.frontend.feed.examples;

import com.google.gdata.client.analytics.AnalyticsService;
import com.google.gdata.client.analytics.DataQuery;
import com.google.gdata.client.spreadsheet.CellQuery;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.Link;
import com.google.gdata.data.analytics.DataEntry;
import com.google.gdata.data.analytics.DataFeed;
import com.google.gdata.data.batch.BatchOperationType;
import com.google.gdata.data.batch.BatchStatus;
import com.google.gdata.data.batch.BatchUtils;
import com.google.gdata.data.spreadsheet.Cell;
import com.google.gdata.data.spreadsheet.CellEntry;
import com.google.gdata.data.spreadsheet.CellFeed;
import com.google.gdata.data.spreadsheet.SpreadsheetEntry;
import com.google.gdata.data.spreadsheet.SpreadsheetFeed;
import com.google.gdata.data.spreadsheet.WorksheetEntry;
import com.google.gdata.util.ServiceException;

import java.io.IOException;
import java.net.URL;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * Demonstrates using the Analytics Data Export API along with the Google
 * Spreadsheets API to retrieve Analytics data in spreadsheet form. To use it,
 * create a Google Spreadsheet. In the header columns, type in the names of
 * some Analytics dimensions and metrics, using the names from the API (e.g.,
 * ga:browser, ga:city, ga:bounces, etc). Then run this program to populate
 * the spreadsheet columns with those headers with Analytics data.
 */
public class SpreadsheetExporter {

  public static void main(String[] args) throws ServiceException, IOException {

    if (args.length != 5) {
      System.err.println("Usage: SpreadsheetExporter "
      + "[username] "
      + "[spreadsheet key to update] "
      + "[Analytics table ID to draw data from] "
      + "[start of date range to draw data from, in format YYYY-MM-DD] "
      + "[end of date range to draw data from, in format YYYY-MM-DD] ");
      return;
    }

    // The user's Google user name and password. This account must have
    // permission to view the data in the Analytics profile
    // and must have full permissions on the spreadsheet to update.
    String username = args[0];
    String password
        = String.valueOf(System.console().readPassword("Enter password for user %1$s: ", username));

    // The key of the spreadsheet to update. For a spreadsheet with URL
    //   http://spreadsheets.google.com/ccc?key=[...]&hl=en
    // the part marked [...] is the spreadsheet's key.
    String spreadsheetKey = args[1];

    // The table ID of the Analytics data to populate the spreadsheet with.
    // Table IDs can be retrieved separately from the Analytics Data API
    // accounts feed. In the Java client library, you can get a table ID
    // for the profile represented by a particular AccountEntry as follows:
    //   String tableId = entry.getTableId();
    String tableId = args[2];

    // The start and end dates for data to retrieve. These should be of the
    // form YYYY-MM-DD.
    String startDate = args[3];
    String endDate = args[4];

    SpreadsheetService spreadsheetService = new SpreadsheetService(APP_NAME);
    spreadsheetService.setUserCredentials(username, password);
    // Workaround for Google Data APIs Java client issue #103
    // (http://code.google.com/p/gdata-java-client/issues/detail?id=103)
    spreadsheetService.setProtocolVersion(SpreadsheetService.Versions.V1);

    AnalyticsService analyticsService = new AnalyticsService(APP_NAME);
    analyticsService.setUserCredentials(username, password);

    SpreadsheetExporter exporter = new SpreadsheetExporter(spreadsheetService, analyticsService);
    exporter.exportToSpreadsheet(spreadsheetKey, tableId, startDate, endDate);
  }

  /** The application name to report to the Google Data APIs */
  private static final String APP_NAME = "google-analytics-spreadsheet-example-1";

  /** The service to use for interacting with Google Spreadsheets */
  private final SpreadsheetService spreadsheetService;

  /** The service to use for interacting with Google Analytics */
  private final AnalyticsService analyticsService;

  /**
   * Constructor.
   *
   * @param spreadsheetService The service to use for interacting with Google
   *     Spreadsheets.
   * @param analyticsService The service to use for interacting with Google
   *     Analytics.
   */
  public SpreadsheetExporter(SpreadsheetService spreadsheetService,
                             AnalyticsService analyticsService) {

    this.spreadsheetService = spreadsheetService;
    this.analyticsService = analyticsService;
  }

  /**
   * Populates the default worksheet of the spreadsheet with the given key
   * with Analytics data named by the header columns of that spreadsheet.
   *
   * @param spreadsheetKey The key of the spreadsheet to populate with
   *     Analytics data.
   * @param tableId The Analytics table ID to pull data from
   * @param startDate The starting date to retrieve data from
   * @param endDate The ending date to retrieve data from
   * @throws IOException If a network error occurs while trying to communicate
   *     with Spreadsheets or Analytics
   * @throws ServiceException If an application-level protocol error occurs
   *     while trying to communicate with Spreadsheets or Analytics
   */
  public void exportToSpreadsheet(
      String spreadsheetKey, String tableId, String startDate, String endDate)
      throws IOException, ServiceException {

    SpreadsheetEntry spreadsheet = getSpreadsheetWithKey(spreadsheetKey);
    Worksheet worksheet = new Worksheet(spreadsheet.getDefaultWorksheet(), spreadsheetService);

    ColumnTypeMap columnTypeMap = readAnalyticsColumns(worksheet);
    List<DataEntry> dataToUpload = getAnalyticsData(tableId, columnTypeMap, startDate, endDate);
    addDataToWorksheet(worksheet, columnTypeMap, dataToUpload);
  }

  /**
   * Returns the SpreadsheetEntry for the spreadsheet with the given key.
   *
   * @throws IOException If a network error occurs while trying to communicate
   *     with Spreadsheets
   * @throws ServiceException If an application-level protocol error occurs
   *     while trying to communicate with Spreadsheets
   */
  private SpreadsheetEntry getSpreadsheetWithKey(String key) throws IOException, ServiceException {
    URL metafeedUrl = new URL("http://spreadsheets.google.com/feeds/spreadsheets/private/full");
    SpreadsheetFeed spreadsheetFeed = spreadsheetService.getFeed(
        metafeedUrl,
        SpreadsheetFeed.class);

    List<SpreadsheetEntry> spreadsheets = spreadsheetFeed.getEntries();
    for (SpreadsheetEntry spreadsheet : spreadsheets) {
      if (spreadsheet.getKey().equals(key)) {
        return spreadsheet;
      }
    }
    throw new IllegalStateException("You don't have access to a spreadsheet with key " + key);    
  }

  /**
   * Determines which columns in the given worksheet should be populated with
   * Analytics data.
   *
   * @param worksheet The worksheet whose columns should be populated with data
   * @return A column type map representing the columns to populate and what
   *     Analytics data to populate those columns with
   */
  private ColumnTypeMap readAnalyticsColumns(Worksheet worksheet) {
    ColumnTypeMap columnTypeMap = new ColumnTypeMap();
    for (int columnIndex = 1; columnIndex < worksheet.getColCount(); columnIndex++) {
      // read the headers, which are all in the first row
      CellEntry entry = worksheet.getCell(1, columnIndex);
      Cell cell = entry.getCell();
      String value = cell.getValue();
      if (value != null) {
        value = value.trim();
        if (ANALYTICS_COLUMNS.containsKey(value)) {
          columnTypeMap.put(columnIndex, ANALYTICS_COLUMNS.get(value));
        }
      }
    }
    return columnTypeMap;
  }


  /**
   * Retrieves the requested Analytics data from the Analytics Data Export API.
   *
   * @param tableId The table ID to request data from
   * @param columnTypeMap The column type map representing the columns to
   *     retrieve
   * @param startDate The start date for the range to retrieve
   * @param endDate The end date for the range to retrieve
   * @return A list of DataEntries containing the requested information
   *
   * @throws IOException If a network error occurs while trying to communicate
   *     with Analytics
   * @throws ServiceException If an application-level protocol error occurs
   *     while trying to communicate with Analytics
   */
  private List<DataEntry> getAnalyticsData(
      String tableId,
      ColumnTypeMap columnTypeMap,
      String startDate,
      String endDate) throws IOException, ServiceException {

    DataQuery query = new DataQuery(new URL("https://www.google.com/analytics/feeds/data"));
    query.setIds(tableId);
    query.setDimensions(columnTypeMap.getColumnString(ColumnType.DIMENSION));
    query.setMetrics(columnTypeMap.getColumnString(ColumnType.METRIC));
    query.setStartDate(startDate);
    query.setEndDate(endDate);
    DataFeed feed = analyticsService.getFeed(query, DataFeed.class);
    return feed.getEntries();
  }

  /**
   * Adds the given Analytics data to the given worksheet.
   *
   * @param worksheet The worksheet to update
   * @param columnTypeMap The columns within that worksheet to update
   * @param dataToUpload The Analytics data to upload
   *
   * @throws IOException If a network error occurs while trying to communicate
   *     with Spreadsheets
   * @throws ServiceException If an application-level protocol error occurs
   *     while trying to communicate with Spreadsheets
   */
  private void addDataToWorksheet(
      Worksheet worksheet,
      ColumnTypeMap columnTypeMap,
      List<DataEntry> dataToUpload) throws IOException, ServiceException {

    // It's possible that the worksheet doesn't yet have enough rows to hold
    // all the data we want to upload to it. We can only bulk-UPDATE cells,
    // we can't bulk-ADD them with pre-populated date, so if we don't already
    // have enough rows we first resize the worksheet, then update our cell
    // entry map with the newly-created cells. (We add 1 to the size of the
    // data to upload because our data starts on row 2. Row 1 is for column
    // headers.)
    if (worksheet.getRowCount() < (dataToUpload.size() + 1)) {
      worksheet.setRowCount(dataToUpload.size() + 1);
    }

    // modify all cells that need to be updated (start with 2; 1 is for column
    // headers)
    List<CellEntry> updatedCells = new LinkedList<CellEntry>();
    int rowNumber = 2;
    for (DataEntry dataEntry : dataToUpload) {
      for (Map.Entry<Integer, AnalyticsColumn> column : columnTypeMap.entrySet()) {
        int columnNumber = column.getKey();
        String dimensionName = column.getValue().getName();
        CellEntry cellEntry = worksheet.getCell(rowNumber, columnNumber);
        String value = dataEntry.stringValueOf(dimensionName);
        cellEntry.changeInputValueLocal(value);
        updatedCells.add(cellEntry);
      }
      rowNumber++;
    }

    // send the updates in chunks of 1000 to ensure we don't send too much
    // data in one batch
    List<List<CellEntry>> batches = chunkList(updatedCells, 1000);
    for (List<CellEntry> batch : batches) {
      CellFeed batchFeed = new CellFeed();
      for (CellEntry cellEntry : batch) {
        Cell cell = cellEntry.getCell();
        BatchUtils.setBatchId(cellEntry, "R" + cell.getRow() + "C" + cell.getCol());
        BatchUtils.setBatchOperationType(cellEntry, BatchOperationType.UPDATE);
        batchFeed.getEntries().add(cellEntry);
      }

      Link batchLink = worksheet.getBatchUpdateLink();
      CellFeed batchResultFeed = spreadsheetService.batch(new URL(batchLink.getHref()), batchFeed);
      // Make sure all the operations were successful.
      for (CellEntry entry : batchResultFeed.getEntries()) {
        if (!BatchUtils.isSuccess(entry)) {
          String batchId = BatchUtils.getBatchId(entry);
          BatchStatus status = BatchUtils.getBatchStatus(entry);
          System.err.println("Failed entry");
          System.err.println("\t" + batchId + " failed (" + status.getReason() + ") ");
          return;
        }
      }
    }
  }

  /****************************************************************************
   * HELPER CLASSES
   *
   * These classes are slightly smarter versions of more standard classes,
   * equipped with little bits of extra functionality that are useful for our
   * purposes.
   ****************************************************************************/

  /**
   * Wrapper around Spreadsheets Worksheet entries that adds some utility
   * methods useful for our purposes.
   */
  private static class Worksheet {
    private final SpreadsheetService spreadsheetService;
    private WorksheetEntry backingEntry;
    private CellFeed cellFeed;
    private int rows;
    private int columns;
    private CellEntry[][] cellEntries;

    Worksheet(WorksheetEntry backingEntry, SpreadsheetService spreadsheetService)
        throws IOException, ServiceException {

      this.backingEntry = backingEntry;
      this.spreadsheetService = spreadsheetService;
      this.rows = backingEntry.getRowCount();
      this.columns = backingEntry.getColCount();
      refreshCachedData();
    }

    /**
     * Presents the given cell feed as a map from row, column pair to CellEntry.
     */
    private void refreshCachedData() throws IOException, ServiceException {

      CellQuery cellQuery = new CellQuery(backingEntry.getCellFeedUrl());
      cellQuery.setReturnEmpty(true);
      this.cellFeed = spreadsheetService.getFeed(cellQuery, CellFeed.class);

      // A subtlety: Spreadsheets row,col numbers are 1-based whereas the
      // cellEntries array is 0-based. Rather than wasting an extra row and
      // column worth of cells in memory, we adjust accesses by subtracting
      // 1 from each row or column number.
      cellEntries = new CellEntry[rows][columns];
      for (CellEntry cellEntry : cellFeed.getEntries()) {
        Cell cell = cellEntry.getCell();
        cellEntries[cell.getRow() - 1][cell.getCol() - 1] = cellEntry;
      }
    }

    /**
     * Gets the cell entry corresponding to the given row and column.
     */
    CellEntry getCell(int row, int column) {
      return cellEntries[row - 1][column - 1];
    }

    /** Returns this worksheet's column count. */
    int getColCount() {
      return columns;
    }

    /** Returns this worksheet's row count. */
    int getRowCount() {
      return rows;
    }

    /** Sets this worksheets's row count. */
    void setRowCount(int newRowCount) throws IOException, ServiceException {
      rows = newRowCount;
      backingEntry.setRowCount(newRowCount);
      backingEntry = backingEntry.update();
      refreshCachedData();
    }

    /** Gets a link to the batch update URL for this worksheet. */
    Link getBatchUpdateLink() {
      return cellFeed.getLink(Link.Rel.FEED_BATCH, Link.Type.ATOM);
    }
  }

  private static enum ColumnType { DIMENSION, METRIC }

  /**
   * Simple container class that represents an Analytics column by pairing its
   * name with its column type.
   */
  private static class AnalyticsColumn {
    private final String name;
    private final ColumnType type;

    AnalyticsColumn(String name, ColumnType type) {
      this.name = name;
      this.type = type;
    }

    public String getName() {
      return name;
    }


    public ColumnType getType() {
      return type;
    }
  }

  /**
   * ColumnTypeMap instances represent a group of spreadsheet columns that
   * should be populated with Analytics data. Each key is a spreadsheet column
   * index, and each value is the Analytics dimension or metric that the
   * spreadsheet column should be populated with.
   */
  private static class ColumnTypeMap extends LinkedHashMap<Integer, AnalyticsColumn> {

    /**
     * Gets a string representing the columns of a particular Analytics column
     * type represented as values in this map. The returned string is suitable
     * for supplying to the Analytics API as a dimension or metric specifier.
     */
    String getColumnString(ColumnType columnType) {
      List<String> columnNames = new LinkedList<String>();
      for (AnalyticsColumn c : this.values()) {
        if (c.getType() == columnType) {
          columnNames.add(c.getName());
        }
      }
      return join(columnNames);
    }

    /**
     * Small utility method that joins the list of strings, separating with a
     * comma between each pair.
     */
    private static String join(List<String> strings) {
      Iterator<String> stringIterator = strings.iterator();
      if (!stringIterator.hasNext()) {
        return "";
      }

      StringBuilder s = new StringBuilder(stringIterator.next());
      while (stringIterator.hasNext()) {
        s.append(",").append(stringIterator.next());
      }
      return s.toString();
    }
  }

  /****************************************************************************
   * MISCELLANEOUS HELPERS
   ***************************************************************************/

  /**
   * A set naming every column that should be considered a dimension or metric.
   */
  private static final Map<String, AnalyticsColumn> ANALYTICS_COLUMNS;
  static {
    String[] dimensions = new String[] {
        "ga:date",
        "ga:hour",
        "ga:month",
        "ga:week",
        "ga:year",
        "ga:adGroup",
        "ga:adSlot",
        "ga:adSlotPosition",
        "ga:affiliation",
        "ga:browser",
        "ga:browserVersion",
        "ga:campaign",
        "ga:city",
        "ga:connectionSpeed",
        "ga:content",
        "ga:continent",
        "ga:country",
        "ga:day",
        "ga:daysSinceLastVisit",
        "ga:daysToTransaction",
        "ga:networkDomain",
        "ga:flashVersion",
        "ga:searchUsed",
        "ga:hostname",
        "ga:searchKeyword",
        "ga:searchKeywordRefinement",
        "ga:searchCategory",
        "ga:javaSupport",
        "ga:keyword",
        "ga:language",
        "ga:latitude",
        "ga:longitude",
        "ga:medium",
        "ga:networkLocation",
        "ga:pageDepth",
        "ga:pageTitle",
        "ga:operatingSystem",
        "ga:operatingSystemVersion",
        "ga:productSku",
        "ga:productName",
        "ga:productCategory",
        "ga:referralPath",
        "ga:region",
        "ga:pagePath",
        "ga:landingPagePath",
        "ga:searchStartPage",
        "ga:exitPagePath",
        "ga:searchDestinationPage",
        "ga:screenColors",
        "ga:screenResolution",
        "ga:source",
        "ga:sourceMedium",
        "ga:subContinent",
        "ga:transactionId",
        "ga:userDefinedValue",
        "ga:visitorType"
    };

    String[] metrics = new String[] {
        "ga:totalAbandonmentRate",
        "ga:abandonedFunnels",
        "ga:goal1AbandonmentRate",
        "ga:goal2AbandonmentRate",
        "ga:goal3AbandonmentRate",
        "ga:goal4AbandonmentRate",
        "ga:costPerGoalConversion",
        "ga:costPerTransaction",
        "ga:costPerConversion",
        "ga:goalConversionRate",
        "ga:goal1AbandonedFunnels",
        "ga:goal2AbandonedFunnels",
        "ga:goal3AbandonedFunnels",
        "ga:goal4AbandonedFunnels",
        "ga:perVisitGoalValue",
        "ga:visitors",
        "ga:rpc",
        "ga:bounces",
        "ga:bounceRate",
        "ga:adClicks",
        "ga:CTR",
        "ga:adCost",
        "ga:CPM",
        "ga:CPC",
        "ga:entrances",
        "ga:exits",
        "ga:percentExit",
        "ga:totalGoalCompletions",
        "ga:goal1Completions",
        "ga:goal1ConversionRate",
        "ga:goal2Completions",
        "ga:goal2ConversionRate",
        "ga:goal3Completions",
        "ga:goal3ConversionRate",
        "ga:goal4Completions",
        "ga:goal4ConversionRate",
        "ga:totalGoalStarts",
        "ga:goal1Starts",
        "ga:goal2Starts",
        "ga:goal3Starts",
        "ga:goal4Starts",
        "ga:totalGoalValue",
        "ga:goal1Value",
        "ga:goal2Value",
        "ga:goal3Value",
        "ga:goal4Value",
        "ga:impressions",
        "ga:newVisits",
        "ga:percentNewVisits",
        "ga:timeOnPage",
        "ga:avgTimeOnPage",
        "ga:pageviews",
        "ga:pagesPerVisit",
        "ga:itemQuantity",
        "ga:itemRevenue",
        "ga:uniquePurchases",
        "ga:transactionRevenue",
        "ga:averageValue",
        "ga:perVisitValue",
        "ga:searchDepth",
        "ga:searchDuration",
        "ga:searchExits",
        "ga:searchRefinements",
        "ga:transactionShipping",
        "ga:transactionTax",
        "ga:transactions",
        "ga:searchUniques",
        "ga:uniquePageviews",
        "ga:timeOnSite",
        "ga:avgTimeOnSite",
        "ga:visits",
        "ga:searchVisits"
    };

    ANALYTICS_COLUMNS = new HashMap<String, AnalyticsColumn>();
    for (String dimensionName : dimensions) {
      ANALYTICS_COLUMNS.put(
          dimensionName,
          new AnalyticsColumn(dimensionName, ColumnType.DIMENSION));
    }
    for (String metricName : metrics) {
      ANALYTICS_COLUMNS.put(
          metricName,
          new AnalyticsColumn(metricName, ColumnType.METRIC));
    }
  }


  /**
   * Chunks a list of items into sublists where each sublist contains at most
   * the specified maximum number of items.
   *
   * @param ts The list of elements to chunk
   * @param chunkSize The maximum number of elements per sublist
   * @return A list of sublists, where each sublist has chunkSize or fewer elements
   *     and all elements from ts are present, in order, in some sublist
   */
  private static <T> List<List<T>> chunkList(List<? extends T> ts, int chunkSize) {
    Iterator<? extends T> iterator = ts.iterator();
    List<List<T>> returnList = new LinkedList<List<T>>();
    while (iterator.hasNext()) {
      List<T> sublist = new LinkedList<T>();
      for (int i = 0; i < chunkSize && iterator.hasNext(); i++) {
        sublist.add(iterator.next());
      }
      returnList.add(sublist);
    }
    return returnList;
  }
}

Show details Hide details

Change log

r5 by api.nickm on May 08, 2009 Diff

GA API and Google Spreadsheet integration

Go to:

Project members, sign in to write a code review

Older revisions

All revisions of this file

File info

Size: 22891 bytes, 627 lines

View raw file

File properties

svn:executable: *