Retrieving Google Analytics Data
Google Analytics data is organized into various metrics (Sessions, Impressions, AdClicks, etc.), which can be queried over various dimensions (Country, Month, etc.). There are many valid combinations of metrics and dimensions. The adapter surfaces some of the most commonly used combinations as tables for ease of use.
Additionally, the adapter allows you to query all valid combinations, even those not included in the predefined tables, using two schemes: by using the Dimensions and Metrics columns and by defining custom schemas. Refer to Advanced Queries for more information. Below is a guide to getting started with the default tables.
Selecting Dimensions and Measures
The dimension and metrics are clearly defined for each table and can be seen in the Data Model: Simply select the metrics and the dimensions you are interested in.
For example, to find the number of sessions in each month, query the Session metric over the Month dimension. This would return 12 rows: one for each month.
SELECT Sessions, Month FROM TrafficTo separate out the months in each year, include both the month and the year dimensions in the query:
SELECT Sessions, Month, Year FROM Traffic
All Google Analytics reports cover a specific date range. The default behavior is to pull the last month of data if the StartDate and EndDate inputs are left unset. To override this behavior, the values can be set directly in the query. For example:
SELECT Sessions, Month, Year FROM Traffic WHERE StartDate = '90daysAgo' AND EndDate = 'Today'
The supported inputs for StartDate and EndDate in the Google Analytics API are 'today', 'yesterday', 'NdaysAgo' (where N is some number), and an exact date. Starting in the v4 API, up to two separate date ranges can be set in the filter.
SELECT * Query
Unlike most database tables, it is not very helpful to select all metrics and dimensions in a given table.
In some cases, it is not even possible to do this since Google Analytics allows a maximum of 7 dimensions and 10 metrics in a single query.
The adapter thus interprets the SELECT * query to mean a default set of metrics and dimensions are requested.
This includes queries that explicitly select all columns. For schemas with less than 10 metrics, all metrics will be returned.
Refer to the individual view's documentation in Data Model to see which fields are the default for each schema.