Analytics Query Language (AQL) Samples

The ShufflePoint Analytics Query Language (AQL) is used to create Excel® and PowerPoint® documents with refreshable Google™ Analytics data. It is also the basis of ShufflePoint's Google Analytics with Google Gadgets integration.

Note that AQL is case-insensitive. Samples here have main keywords in caps for clarity.

Standard table

SELECT
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:pagePath ON ROWS
FROM default
WHERE TIMEFRAME default

Sideways Table

SELECT
  DIMENSIONS ga:continent ON COLUMNS
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON ROWS
FROM default
WHERE TIMEFRAME default

Pivot Table

SELECT
  DIMENSIONS ga:campaign ON COLUMNS
  DIMENSIONS ga:medium ON ROWS
FROM default
WHERE
  METRICS ga:transactionRevenue
  TIMEFRAME yearToDate

Timeframe

Given

SELECT
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:date ON ROWS
FROM default
WHERE TIMEFRAME 2009-01-01:2009-06-15

Named

SELECT
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:date ON ROWS
FROM default
WHERE TIMEFRAME LAST30DAYS

Shifted

SELECT
  METRICS ga:visitors ON COLUMNS
  DIMENSIONS ga:month:2 ON ROWS
FROM default
WHERE TIMEFRAME yearToDate

Last four Months

SELECT
  METRICS ga:visitors ON COLUMNS
  DIMENSIONS ga:month ON ROWS
FROM default
WHERE TIMEFRAME months:4

Sorting and Limiting Rows

With Other Row

SELECT
  METRICS ga:avgPageDuration ON COLUMNS
  DIMENSIONS ga:continent ON ROWS TOP 4 by ga:avgPageDuration
FROM default
WHERE TIMEFRAME default

Without Other Row

SELECT
  METRICS ga:avgPageDuration ON COLUMNS
  DIMENSIONS ga:continent ON ROWS
     TOP 4 noOther by ga:avgPageDuration
FROM default
WHERE TIMEFRAME default

Advanced Segments

Named segments

SELECT
  METRICS ga:visits, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:date ON ROWS
FROM default
WHERE
  TIMEFRAME default
  SEGMENT "New Visitors"

Dyanmic segments

SELECT
  METRICS ga:visits, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:date ON ROWS
FROM default
WHERE
  TIMEFRAME default
  SEGMENT ga:visitorType == "New Visitor"

SELECT
  METRICS ga:visits, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:date ON ROWS
FROM default
WHERE
  TIMEFRAME default
  SEGMENT ga:medium==cpa OR ga:medium==cpc OR ga:medium==cpm

Filters

String Comparison

SELECT
  METRICS ga:visits, ga:visits:{%} ON COLUMNS
  DIMENSIONS ga:visitorType ON ROWS
FROM default
WHERE
  TIMEFRAME default
  FILTER ga:visitorType == "New Visitor"

Multiple String Comparisons

SELECT
  METRICS 
      ga:visits, ga:goal1Completions, ga:goal2Completions ON COLUMNS
  DIMENSIONS ga:source ON ROWS BY  ga:visits DESC
FROM default
WHERE
  TIMEFRAME default
  FILTER ga:source == "google" AND ga:medium == "organic"

Regular Expressions

Regular expression can be a maximum of 64 characters.

SELECT
  METRICS ga:visits ON COLUMNS
  DIMENSIONS ga:source ON ROWS BY  ga:visits DESC
FROM default
WHERE
  TIMEFRAME default
  FILTER ga:source =~ "^(google|google\..*)$"

Formatting

Format Result

SELECT 
  METRICS ga:visits:{#,##0} ON COLUMNS 
  DIMENSIONS ga:source ON ROWS TOP 5 BY ga:visits DESC 
FROM default 
WHERE TIMEFRAME yearToDate

Calculate Percentage

SELECT 
  METRICS ga:visits:{#,##0}, ga:visits:{0.00%} ON COLUMNS 
  DIMENSIONS ga:source ON ROWS TOP 5 BY ga:visits DESC 
FROM default 
WHERE TIMEFRAME yearToDate

Format Function

Single Value

SELECT 
FROM default 
WHERE 
  METRICS format("{0:#} Visits", ga:visits)
  TIMEFRAME yearToDate

Multiple Values

SELECT
FROM default
WHERE
 METRICS format("{0:n0} visits used {1:n0} languages", ga:visits, ga:language)
 TIMEFRAME default

Counting Dimensions

SELECT
FROM default
WHERE
 METRICS format("{0:n0} visits used {1:n0} languages", ga:visits, ga:language)
 TIMEFRAME default

Literals

SELECT 
  METRICS "The site had", ga:visits ON COLUMNS 
  DIMENSIONS ga:continent ON ROWS
FROM default 
WHERE TIMEFRAME default

Row and Column Headers

Suppress Row Headers

SELECT norowheaders
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:country ON ROWS TOP 10 BY ga:pageviews
FROM default
WHERE TIMEFRAME default

Suppress Column Headers

SELECT noColumnHeaders
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:country ON ROWS TOP 10 BY ga:pageviews
FROM default
WHERE TIMEFRAME default

Suppress All Headers

SELECT noRowHeaders noColumnHeaders
  METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS
  DIMENSIONS ga:country ON ROWS TOP 10 BY ga:pageviews
FROM default
WHERE TIMEFRAME default

Pivot Totals

Row Totals

SELECT
  DIMENSIONS ga:campaign ON COLUMNS TOTALS
  DIMENSIONS ga:medium ON ROWS
FROM default
WHERE
  METRICS ga:transactionRevenue
  TIMEFRAME yearToDate

Row Top and Totals

SELECT
  DIMENSIONS ga:campaign ON COLUMNS TOP 3 TOTALS
  DIMENSIONS ga:medium ON ROWS
FROM default
WHERE
  METRICS ga:transactionRevenue
  TIMEFRAME yearToDate

Row Top, Totals, and Sort

SELECT
  DIMENSIONS ga:campaign ON COLUMNS TOP 3 TOTALS BY ga:transactionRevenue
  DIMENSIONS ga:medium ON ROWS
FROM default
WHERE
  METRICS ga:transactionRevenue
  TIMEFRAME yearToDate

Column Top, Totals, and Sort

SELECT
  DIMENSIONS ga:campaign ON COLUMNS
  DIMENSIONS ga:medium ON ROWS TOP 3 TOTALS BY ga:transactionRevenue
FROM default
WHERE
  METRICS ga:transactionRevenue
  TIMEFRAME yearToDate

Column and Column Totals and Sort

SELECT
  DIMENSIONS ga:campaign ON COLUMNS TOTALS BY ga:transactionRevenue
  DIMENSIONS ga:medium ON ROWS TOTALS BY ga:transactionRevenue
FROM default
WHERE
  METRICS ga:transactionRevenue
  TIMEFRAME yearToDate