AwReporting is an open-source Java framework for large scale AdWords API reporting.
- Support full suite of AdWords API reports with full list of fields.
- Report data are stored in your local database, so you can integrate it with your existing systems.
You will need Java 8 (9 is not supported), Maven and MySQL installed before configuring this project.
$ git clone https://github.com/googleads/aw-reporting
$ mvn clean install
CREATE DATABASE AWAPIReports DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
CREATE USER 'reportuser'@'localhost' IDENTIFIED BY 'SOME_PASSWORD';
GRANT ALL PRIVILEGES ON AWAPIReports.\* TO 'reportuser'@'localhost' WITH GRANT OPTION;
Now you should create a properties file to specify your manager account ID, developer token, OAuth credentials and database login.
$ vi aw-reporting/src/main/resources/aw-report-sample.properties
Fill in the following fields with your manager account ID and developer token.
managerAccountId=
developerToken=
Fill in your OAuth credentials. If you need to create them, visit Google API Console Credentials page.
Note that you don't have to enter RefreshToken, as AwReporting takes care of getting a new one when it runs for the first time.
clientId=
clientSecret=
Fill in the following with the number of rows that will be parsed in batch from report data before persisting to the database. The bigger the number, the bigger the memory usage, but it might also results an improvement in performance.
aw.report.processor.rows.size=1000
Fill in the following to set the number of threads for report data processing and database insertion.
aw.report.processor.threads=20
Fill in the following with your database connection.
aw.report.model.db.sql.url=jdbc:mysql://localhost:3306/AWReports?rewriteBatchedStatements=true
aw.report.model.db.sql.username=reportuser
aw.report.model.db.sql.password=<SOME_PASSWORD>
Specify report types and fields you want to get data.
ACCOUNT_PERFORMANCE_REPORT=ExternalCustomerId,AccountDescriptiveName,Date,Impressions,Clicks,Cost,...
Now, you are ready to run AwReporting with the following command.
$ java -Xmx1G -jar aw-reporting/target/aw-reporting.jar -startDate YYYYMMDD -endDate YYYYMMDD \
-file aw-reporting/src/main/resources/aw-report-sample.properties
Be sure to specify the properties file you edited above on the command line.
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/awreporting/AwReporting.java
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 AWReports.AW_AccountPerformanceReport limit 1;
Set the following command line options before running the project:
Note: aw-reporting.jar is in the aw-reporting/aw-reporting/target/ directory.
java -Xmx1G -jar aw-reporting.jar -startDate YYYYMMDD -endDate YYYYMMDD -file <file>
Arguments:
-accountIdsFile <accountIdsFile>
Consider ONLY the account IDs specified on the file to run the report.
-csvReportFile <csvReportFile>
This is the path to the CSV report file that will be used to import data directly
into AwReporting.
-dateRange <dateRange>
ReportDefinitionDateRangeType.
-endDate <endDate>
End date for CUSTOM_DATE Reports (YYYYMMDD)
-file <file>
aw-report-sample.properties file.
-help
Print this message.
-onFileReport <onFileReport>
This is an experimental argument, where you can specify the report type, and the
processor will read the data directly from the CSV file passed on the
'csvReportFile' argument. It's mandatory to pass the 'csvReportFile' argument if you
pass this argument.
-startDate <startDate>
Start date for CUSTOM_DATE Reports (YYYYMMDD).
File -> Import -> General -> Existing projects into workspace.
Import both modules:
aw-reporting/aw-reporting-model
aw-reporting/aw-reporting
For better organization and encapsulation, the project groups the reporting workflow into two parts:
- aw-reporting-model for database persistence, entities and the CSV mapping to AdWords report information.
- aw-reporting for the logic (API services, downloader and processors).
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 type as java fields, by using annotations. The entities contain information to link the java fields to the report fields definition, the csv display name 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 three new annotations:
-
Annotation @CsvReport at the report's Java class level, to define the report definition type. For example for AccountPerformanceReport:
@CsvReport(value= ReportDefinitionReportType.ACCOUNT_PERFORMANCE_REPORT) public class AccountPerformanceReport extends Report {... -
Annotation @CsvField at the report's Java field level, to define the mapping between java field, report's field name and display name. For example for avgCpm:
@CsvField (value = "Avg. CPM", reportField = "AverageCpm") public BigDecimal avgCpm; -
Annotation @MoneyField at the report's Java field level, to convert the field's values from micro amount to normal currency. For example for cost:
@MoneyField public BigDecimal cost;
-
- 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, where we provided implementation for MySQL and MongoDB. "EntityPersister" is the interface for the report entities storage, where we provided implementation for MySQL and a MongoDB.
Provides the logic (API services, downloader and processors)
-
downloader: Based on MultipleClientReportDownloader (which uses client library's ReportDownloader) for downloading all the report files with multiple threads.
-
processors: The ReportProcessor is the class with the main logic, it is responsible for calling the downloader, parsing the CSV reports and persisting report data into storage. It would be replaced by a custom processor by changing the bean component in the xml configuration file.
-
API Services: Besides calling AdWords API to download report data, the ManagedCustomerDelegate is the only class using AdWords API to get all the AdWords account IDs under the top manager account.
-
AwReporting main: The AwReporting main class is the entry point for passing command line arguments to downloader / processor for execution.
In case some report types are not yet exposed in AdWords API, but are already available in the AdWords User Interface, we introduced the feature of importing data to the database directly from CSV files that were downloaded from the interface.
The offline data import works just as the online mode (where the data is downloaded from the API), but skips the download step. All the field mappings and report types supported are still the same, but keep in mind that most of the entity IDs are not available in the reports downloaded from the interface.
IMPORTANT NOTE: Before importing the CSV with AwReporting, you must edit the file and make sure that it's in the same format as the CSV file downloaded from the API:
- First line must contain the name or description of the report;
- Second line must contain the column names/headers;
- Following lines must contain the data.
Usually when you download a report from the interface, the CSV file will contain some additional lines in the beginning of the file. You have to remove those lines before importing it into AwReporting.
To use the offline import data, you just need to specify in the command line the report type that you will import, and the local file that you will use as an addition to the other arguments:
$ java -Xmx1G -jar aw-reporting/target/aw-reporting.jar -startDate YYYYMMDD -endDate YYYYMMDD \
-file aw-reporting/src/main/resources/aw-report-sample.properties \
-onFileReport CAMPAIGN_PERFORMANCE_REPORT -csvReportFile <CSV FILE LOCATION>
IMPORTANT NOTE: The dates specified are very import, because they will be used to populate the database following the same format as the data downloaded from the API. Date periods are not supported.
We will keep migrating AwReporting to the latest AdWords API version. In order to migrate your version of AwReporting, please follow these steps:
- run
git pullto get the latest code. - run
mvn clean installto build it. - Check change log to see what AdWords API version is being used and refer to the AdWords API's release notes for changes on reports.
- Create a new database, update properties file to make AwReporting persist report data into the new database.
- Create database scripts to import data from old database to new one, or just run AwReporting to pull all historical data you need.
The MySql version supported by default is v5.6. If you encounter any database
issues, specifically on schema creation, and you are using a higher version of
MySql, the suggested fix is to change the hibernate.dialect property in
src/main/resources/data-source-mysql.xml so that it refers to a higher
version dialect, e.g. org.hibernate.dialect.MySQL8Dialect.