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