This article was penned during the design of GAQL and does not accurately document the syntax. For the full features and correct syntax, visit the GAQL reference pages at http://www.shufflepoint.com/help/Gaql.aspx
The Google Analytics (GA) API Beta allows for the retrieval of one or more analytics metrics organized by dimensions for a given GA profile. ShufflePoint has developed a query language, GAQL, to take advantage of these new capabilities.
GAQL is in the genre of the OLAP query language MDX. MDX stands for MultiDimensional eXpressions, which fits well with the nature of the GA API.
GAQL, like MDX, allows a query to specify both the data to return and the "geometry" of the result by assigning dimensions and metrics to axes. This is implemented in GAQL using the same "ON ROWS" and "ON COLUMS" as in MDX.
An simple example of a GAQL query is:
SELECT
METRICS pageviews ON COLUMNS
DIMENSIONS browser ON ROWS
FROM
www.shufflepoint.com
Keywords are in CAPS. The "ON COLUMNS" and "ON ROWS" follow the MDX grammar for specifying the axis to receive a set of members. As with MDX, a "pivot" can be accomplished by specifying a "metric in the where clause" like so:
SELECT
DIMENSIONS platform ON COLUMNS
DIMENSIONS browser ON ROWS
FROM
www.shufflepoint.com
WHERE
METRICS pageviews
Analytics metrics from several GA profiles can be combined by listing multiple profiles in the FROM clause:
SELECT
METRICS {pageviews,avgPageviews} ON COLUMNS
DIMENSIONS browser ON ROWS
FROM
www.shufflepoint.com, blog.shufflepoint.com
In MDX, sorting and filtering is accomplished with functions. Rather than use a function-based syntax, we opted for a more SQL-like syntax:
SELECT
METRICS pageviews ON COLUMNS
DIMENSIONS browser ON ROWS
FROM
www.shufflepoint.com
WHERE
FILTER pageviews > 10 AND browser != 'IE'
And ordering is specified as part of an axis clause:
SELECT
METRICS {pageviews,avgPageviews} ON COLUMNS
DIMENSIONS browser ON ROWS BY avgPageviews
FROM
www.shufflepoint.com
Finally, the query timeframe can be specified in its own Where clause:
SELECT
METRICS {pageview, avgTime} ON COLUMNS
DIMENSIONS {browser, campaign} ON ROWS BY browser, pageview ASC
FROM
1174
WHERE
TIMEFRAME {2008-07-10 : 2008-08-10}
We have also defined a handful of timeframe literals. For example:
WHERE
TIMEFRAME {Last30Days}
WHERE
TIMEFRAME {LastMonth}
ShufflePoint Studio supports using Google Analytics as a data source for charts and tables. You can see a screenshot of the new ShufflePoint Google Analytics query builder at http://www.shufflepoint.com/demo.aspx
