davidbotella/aw-reporting

AdWords Reports to DB

AwReporting

Overview

AwReporting is an open-source Java framework for large scale AdWords API reporting.

• You can use this project to generate reports for all the AdWords accounts managed by an MCC.

• 15 common reports are included in the reference implementation. You can easily follow the code examples to implement more.

• Reports are stored in your relational database, so you can integrate them with your existing systems.

Quick Start

Prerequisites

You will need Java, Maven and MySQL installed before configuring the project.

Build the project using Maven

$ git clone https://github.com/googleads/aw-reporting

$ cd aw-reporting

$ cd aw-report-model

$ mvn clean install eclipse:eclipse

$ cd ../aw-reporting

$ mvn eclipse:eclipse

$ mvn compile dependency:copy-dependencies package

Configure your MySQL database

CREATE DATABASE AwReporting DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

CREATE USER 'reportuser'@'localhost' IDENTIFIED BY 'SOME_PASSWORD';

GRANT ALL PRIVILEGES ON AwReporting.* TO 'reportuser'@'localhost' WITH GRANT OPTION;

Configure AwReporting

Now we'll create a properties file to specify your MCC, developer token, OAuth, and database credentials.

$ vi ../aw-reporting/src/main/resources/aw-report-sample.properties

Fill in the following fields with your MCC acount ID, and developer token.

mccAccountId=

developerToken=

Fill in your OAuth credentials. If you need to create them, visit: https://code.google.com/apis/console#access

clientId=

clientSecret=

Fill in the following with your database connection.

aw.report.model.db.sql.url=jdbc:mysql://localhost:3306/AWReporting?rewriteBatchedStatements=true

aw.report.model.db.sql.username=reportuser

aw.report.model.db.sql.password=SOME_PASSWORD

Import the project into Eclipse (optional)

To import the project into Eclipse, first import the model:

File -> Import -> General -> Existing projects into workspace.

aw-reporting/aw-report-model

Next import the database code:

File -> Import -> General -> Existing projects into workspace.

aw-reporting/aw-reporting

Command line options

Set the following command line options before running the project:

java -Xmx1G -jar aw-reporting.jar -startDate YYYYMMDD -endDate YYYYMMDD -file <file>

java -Xmx1G -jar aw-reporting.jar -generatePdf <htmlTemplateFile> <outputDirectory> -startDate YYYYMMDD -endDate YYYYMMDD -file <file>


Arguments:

   -dateRange              ReportDefinitionDateRangeType.

   -debug                                 Will display all the debug information. If the option 'verbose' is
                                          activated, all the information will be displayed on the console as
                                          well

   -endDate <YYYMMDD>      End date for CUSTOM_DATE Reports (YYYYMMDD)

   -file <file>            aw-report-sample.properties file.

   -generatePdf <htmlTemplateFile> <outputDirectory>
                           
                           Generate Monthly Account Reports for all Accounts in PDF
   
                           NOTE: For PDF use aw-report-sample-for-pdf.properties instead, the fields need to be different.

   -help                   Print this message.

   -startDate <YYYYMMDD>   Start date for CUSTOM_DATE Reports (YYYYMMDD).

   -verbose                The application will print all the tracing on the console

Run the project and verify it's working

It's possible to run the project using either Eclipse or the command line. If using Eclipse, open and run:

aw-reporting/src/main/java/com/google/api/ads/adwords/jaxws/extensions/AwReporting.java

Be sure to specify the properties file you edited above on the command line.

As it's running, the project will provide status messages about the reports it's downloading on the command line.

Check your database when the run finishes to be sure it's been populated with the reporting data, e.g.:

SELECT * FROM AwReporting.AW_ReportAd limit 1;

Details about the code

For better organization and encapsulation, the project groups the reporting workflow into two parts: Aw-Report-Model for the logic (API services, downloader and processors) and Aw-Reporting for persistence, entities and the CSV mapping to AdWords information.

Aw-Report-Model

Provides all the necessary classes to persist data and the entities’ mapping to AdWords report data.

• Entities: these POJOs define all the available fields for each report kind as java fields, by using annotations. The Entities contain the information to link the java fields to the report fields definition, the csv display name header fields and the datastore fields.

• CSV: The CSV classes use the OpenCSV library to convert CSV files into Java beans using annotations. The package also contains two new annotations to define the Report Definition Type and the mapping between java field, report’s Column Name and Display Name headers. For example:

  • Annotation @CsvReport at the Report class level, for example for ReportAccount: @CsvReport(value= ReportDefinitionReportType.ACCOUNT_PERFORMANCE_REPORT) public class ReportAccount extends Report {...

  • Annotation @CsvField at the java field level, for example for avgCpm: @CsvField (value = "Avg. CPM", reportField = "AverageCpm") public BigDecimal avgCpm;

• Persistence: The persistence layer uses Spring for bean management, injection and in class annotations, this helps to clearly demarcate the application layers. AuthTokenPersister: is the interface for the authorization token storage, we have implemented it for Mysql and a MongoDB. ReportEntitiesPersister is the interface for the report entities storage, we have implemented it for Mysql and a MongoDB.

Aw-Reporting

Provides the logic (API services, downloader and processors)

• Downloader: Based on MultipleClientReportDownloader java example (it uses the Library ReportDownloader) the Downloader is on charge of downloading all the report files using multiple threads.

• Processors: The ReportProcessor is the class with the main logic, it is responsible for calling the downloader, use the CVS classes for the parsing and call the Persistence helpers for the storage. This class can be replace by a custom processor by changing the bean component in the projects xml configuration files.

• API Services: Beside the report Downloader calls to AdHoc Reports, the ManagedCustomerDelegate is the only class talking to the AdWords API, it is on charge of getting all the account ids in the MCC tree.

• AwReporting main: The AwReporting main class is on charge of printing the help information, the properties file example and of passing the command line parameters to the processor for execution.

PDF Generation

PDF generation works monthly and also needs the use of a HTML template like ACCOUNT_PERFORMANCE_REPORT.tmpl

First run the the date range without the -generatePdf to download the data needed to generate them.

Here's an example properties file for PDF generation:

aw-report-sample-for-pdf.properties

Fine print

Pull requests are very much appreciated. Please sign the Google Code contributor license agreement (There is a convenient online form) before submitting.

Authors

Julian Toledo (Google Inc.)

Gustavo Menezes (Google Inc.)

Copyright

Copyright © 2013 Google, Inc.

License

Apache 2.0

Limitations

This is example software, use with caution under your own risk.