Analytics Query Language (AQL) Samples

The ShufflePoint Analytics Query Language (AQL) is used to create Excel® documents with refreshable digital analytics data.

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"

Dynamic 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

Custom Dimensions

Visits by traffic source

Custom Dimensions are very handy when you wish to “multi-segment” or mix metrics and dimensions which would normally get you the “incompatible” message

CUSTOMDIM sp:visitorsource LABEL "Source"
  MEMBER "Search Traffic" AS SEGMENT ga:medium == "cpa" or ga:medium == "cpc" or ga:medium == "cpm" or ga:medium == "cpp" or ga:medium == "cpv" or ga:medium == "organic" or ga:medium == "ppc"
  MEMBER "Direct Traffic" AS SEGMENT ga:medium == "(none)"
  MEMBER "Referral Traffic" AS SEGMENT ga:medium == "referral"
SELECT
  METRICS ga:visitors ON COLUMNS
  DIMENSIONS sp:visitorsource ON ROWS BY ga:visitors
FROM default
WHERE timeframe default

The use of named segments is also supported:

CUSTOMDIM sp:visitorsource LABEL "Source"
  MEMBER "Paid Traffic" AS SEGMENT "Paid Search Traffic"
  MEMBER "Non-paid Traffic" AS SEGMENT "Non-paid Search Traffic"
  MEMBER "Search Traffic" AS SEGMENT "Search Traffic"
  MEMBER "Direct Traffic" AS SEGMENT "Direct Traffic"
SELECT
  METRICS ga:visitors ON COLUMNS
  DIMENSIONS sp:visitorsource ON ROWS BY ga:visitors
FROM default
WHERE timeframe default

Pageviews by Author

Since custom variables are a limited commodity, once can conserve by placing several values into a single variable. Assume that the GA markup has tagged article downloads with a custom variable containing Article Topic, Author, Date, and Length. A specific custom variable value may look like this:

      /Finance/Bill Gates/2010-10-29/4325

To turn the "Author" component of the custom var into dimension, and get sorted downloads by author:

CUSTOMDIM sp:author LABEL "Author"
  MEMBER "Bill Gates" AS FILTER ga:customVarValue1 =@ "/Bill%20Gates"
  MEMBER "Larry Ellison" AS FILTER ga:customVarValue1 =@ "/Larry%20Ellison"
  MEMBER "Steve Jobs" AS FILTER ga:customVarValue1 =@ "/Steve%20Jobs"
  MEMBER "Michael Dell" AS FILTER ga:customVarValue1 =@ "/Michael%20Dell"
SELECT
  METRICS ga:pageviews ON COLUMNS
  DIMENSIONS sp:author ON ROWS BY ga:pageviews
FROM default
WHERE timeframe default

Keyword theme by month

Custom dimensions effectively allow you to create custom groupings. This use useful on dimensions such as ga:keyword as demonstrated in the following query. Note that this is a pivot query with a metric in the where clause.

CUSTOMDIM sp:keywordTheme LABEL "Keyword Theme"
  MEMBER "Trains" AS SEGMENT ga:keyword =~ "train"
  MEMBER "Planes" AS SEGMENT ga:keyword =~ "plane"
  MEMBER "Boats" AS SEGMENT ga:keyword =~ "boat"
  MEMBER "Automobiles" AS SEGMENT ga:keyword =~ "(automobile|car)"
SELECT
  DIMENSIONS ga:month ON COLUMNS
  DIMENSIONS sp:keywordTheme ON ROWS
FROM deault
WHERE 
  METRICS ga:visitors
  TIMEFRAME fullMonths:6