Analytics Query Language (AQL)

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.

AQL is in the genre of the OLAP query language MDX. AQL, 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 AQL using the same “ON ROWS” and “ON COLUMNS” as in MDX.

Basic Syntax

The basic syntax of AQL is:

SELECT

<metrics or dimensions> ON COLUMNS <top/by>

<metrics or dimensions> ON ROWS <top/by>

FROM

<profile list>

WHERE

<optional metrics list>

<optional timeframe>

<optional filters>

This query will return the tabular result shown below:

SELECT

METRICS ga:visits, ga:pageviewsPerVisit, ga:bounceRate ON COLUMNS

DIMENSIONS ga:continent ON ROWS BY ga:visits

FROM

default

WHERE

TIMEFRAME yearToDate

Continent Visits Pages/Visit Bounce Rate
Americas 143 2 60.14%
Europe 93 2 67.74%
Asia 27 2 44.44%
Oceania 11 2 63.64%
Africa 1 2 .00%

Keywords

The following are reserved keywords in AQL:

SELECT, COLUMNS, ON, ROWS, FROM, FORMAT, WHERE, DIMENSIONS, METRICS, TIMEFRAME, FILTER, BY, AS, ASC, DESC, CUSTOMDIM

AQL keywords are not case sensitive.

String Literals

When profile names and advanced segment names are present in queries, they must be delimited with either double quotes or square brackets. So to specify a profile, you can either write:

FROM [www.shufflepoint.com]

or

FROM "www.shufflepoint.com"

Axis Expressions

Expressions with “ON ROWS” and “ON ROWS” are axis expressions. They are used to specify which dimensions or metrics should become the column and row headings of the result.

Things to note about axis expressions

  1. A given axis may contain a list of metrics or a single dimension
  2. Names within a list are separated by commas
  3. The metric and dimension names must have a “ga:” prefix (in the case of Google names) or an “sp:” prefix (in the case of ShufflePoint names)
  4. A given axis can have either metrics or a dimension
  5. Currently, only one dimension is allowed on a given axis. This limitation will be relaxed at some point.

The dimensions and metrics available witin AQL are based on the ones supported by Google Analytics API. These are documented on this Google reference page .

Timeframe Expressions

An AQL query must contain a timeframe. The timeframe can be specified as a named timeframe, as “DEFAULT”, or by specifying a start and end date.

A timeframe specified with start and end dates takes the following form.

SELECT ...

WHERE

TIMEFRAME 2009-03-10:2009-04-10

Like profiles, “DEFAULT” can be specified to say “use the default I have specified for this report or session”. The named timeframes which AQL recognizes are listed in the table below.

Timeframe nameMeaning
todaydata for the current day
yesterdayYesterday
yesterday2Two days ago
days:NLast N days
thisWeekLast 7 days
lastWeek2Last 7 days, 7 days ago
weeks:NLast N weeks
last10DaysLast 10 days
last30DaysLast 30 days
last60DaysLast 60 days
thisCalendarWeekThis calendar week
lastCalendarWeekLast calendar week
lastCalendarWeek2Two calendar weeks ago
thisMediaWeekThis media week
lastMediaWeekLast media week
lastMediaWeek2Two media weeks ago
thisQuarterThis quarter
lastQuarterLast quarter
thisMonthThis month
lastMonthLast month
lastMonth2Two months ago
lastMonth3Three months ago
last2MonthLast 2 months
last2Month2Last 2 months, back one
months:NLast N months
fullmonths:NLast N full months
yearToDateYear to date
weeksToDateCalendar weeks to date
weeksToDate2Calendar weeks to date, but starts Jan 1 even if gives partial first week
monthsToDateMonths to date
lastYearLast 365 days
lastCalendarYearLast calendar year
lastCalendarYear2Two years ago

Timeframe comparisons

The timeframe clause may now optionally have two timeframes and an optional third parameter to specify how to display timeframe comparisons.

Note that timeframe comparisons are only supported on queries with metrics on columns and dimensions on rows ("flat" queries).

The simplest two-timeframe form is:

SELECT ...

TIMEFRAME lastMonth, default

If the 2nd timeframe is set to “default” then the query logic will replace default with the appropriate previous period. So the above timeframe statement is equivalent to:

SELECT ...

TIMEFRAME lastMonth, lastMonth2

The named timeframes which support “default” for the second timeframe are YESTERDAY, LASTWEEK, THISMONTH, LASTMONTH, LAST12MONTHS, LASTMONTH2, THISCALENDARWEEK, LASTCALENDARWEEK, THISMEDIAWEEK, LASTMEDIAWEEK, LASTCALENDARYEAR, THISQUARTER, LASTQUARTER, and LASTQUARTER2.

The timeframe clause:

SELECT ...

TIMEFRAME default, default

will be correct if your session default timeframe is one of the above listed named timeframes.

As a complete query example, this query:

SELECT

METRICS ga:visits, ga:bounceRate ON COLUMNS

DIMENSIONS ga:continent ON ROWS BY ga:visits

FROM

default

WHERE

TIMEFRAME lastMonth, lastYear

Would produce the following formatted results:

Continent Visits Bounce Rate
curr prev %chg curr prev %chg
Americas 820 851 -0.036428 .414634 .467685 -0.113433
Europe 605 501 0.207585 .469421 .425150 0.10413
Asia 188 192 -0.020833 .462766 .593750 -0.220605
Oceania 81 44 0.840909 .728395 .590909 0.232669
Africa 13 8 0.625 .461538 .375000 0.230768

Special Cases

There are some special cases of two-timeframe queries. For example, this timeframe clause:

SELECT ...

TIMEFRAME yesterday, lastWeek

will compare yesterday to same day of week last week.

The following table lists most of these special cases. Note that if you can think of another usefule case, please share it with is and we will strive to incorporate it.

Timeframe expressionResulting comparison
TIMEFRAME today, lastWeek
TIMEFRAME yesterday, lastWeek
TIMEFRAME yesterday2, lastWeek
Compare yesterday to same day of week last week
TIMEFRAME thisMonth, lastYear
TIMEFRAME lastMonth, lastYear
Compare this month to same days of month last year
TIMEFRAME yearToDate, lastYear Compare last year to date up to same date
TIMEFRAME monthsToDate, lastYear Compare months to date with same months last year
TIMEFRAME lastCalendarWeek, lastYear Compare week to with similar calendar week last year
TIMEFRAME fullMonths:{N}, lastYear
TIMEFRAME last{N}Days, lastYear
Compare first timeperiod dates with same period year

Selecting period-over-period calculations to display

In the query tool, a two-timeframe query will results in each metric column getting split into three: Current, Previous, and % Change. When queried from Excel, you can use a 3rd timeframe argument to control what columns are present. Valid values are:

  • vp - show value and previous value
  • pvc - show previous, value, and %change
  • pvd - show previous, value, and difference
  • vpc - value, previous value, %change (default)
  • vpdc - show previous, value, difference, and %change

Below is an example query with the third TIMEFRAME argument

SELECT ...

TIMEFRAME lastMonth, lastMonth2, pvd

Note that this third argument will not have any effect in the query tool but will effect the results in Excel.

Row-based timeframe comparisons

If you would like the results of a two timeframe query to be formatted as is done in the Google Analytics web interface, then add an "r" prefix to the format style (or just an "r" to use the defaults):

SELECT

METRICS ga:visits, ga:bounceRate ON COLUMNS

DIMENSIONS ga:continent ON ROWS BY ga:visits

FROM

default

WHERE

TIMEFRAME lastMonth, lastYear, r

The results of the query will be as follows:

Continent Visits Bounce Rate
Americas
2011-02-01 - 2011-02-28 820 .414634
2010-02-01 - 2010-02-28 851 .467685
% Change -0.036428 -0.113433
Europe
2011-02-01 - 2011-02-28 605 .469421
2010-02-01 - 2010-02-28 501 .425150
% Change 0.207585 0.10413
Asia
2011-02-01 - 2011-02-28 188 .462766
2010-02-01 - 2010-02-28 192 .593750
% Change -0.020833 -0.220605
Oceania
2011-02-01 - 2011-02-28 81 .728395
2010-02-01 - 2010-02-28 44 .590909
% Change 0.840909 0.232669
Africa
2011-02-01 - 2011-02-28 13 .461538
2010-02-01 - 2010-02-28 8 .375000
% Change 0.625 0.230768

Last N timeframe types

A common timeframe needs is "give me the last N periods". This can be easily done in the AQL TIMEFRAME expression using expressions like:

TIMEFRAME fullmonths:6

This is translated as "Last 6 full months". Other periods which are supported include:

  1. months:N
  2. weeks:N
  3. days:N
  4. years:N

From Expressions

The keyword “FROM” is followed by either the keyword “DEFAULT” or by a list of profiles. “DEFAULT” says “use the default I have specified for this report or session”. Defaults are a powerful feature in ShufflePoint that allow queryies and containing reports to easily be reused.

Profiles may be specified in several formats. The shortest format is to provide the profile ID, as in:

SELECT ...

FROM 43594

This means that the query will get data for the profile with the id “43594”. A profile name may also be specified by name. Named profiles must be wrapped by double quote or square brackets:

SELECT ...

FROM [www.shufflepoint.com]

Since profile names in Google Analytics are NOT guaranteed to be unique, the AQL engine will use the first profile in your account which matches the provided name. If you want to be certain that you are using the correct profile, but also wish to see the name in the query, then use the format:

SELECT ...

FROM [43594].[www.shufflepoint.com]

Dimensions on each axis (pivots)

You can place a dimension the columns axis and a different dimension on the rows axis. In database and Excex speak, this is referred to as a pivot. Pivoting will also soon be available in the Google Analytics portal (but we had it first!). When you have dimensions on both axes, you need to specify a metric in the WHERE clause.

An good example would be to ask for unique visitors by continent for the last ten days:

SELECT

DIMENSIONS ga:continent ON COLUMNS

DIMENSIONS ga:date ON ROWS

FROM

default

WHERE

METRICS ga:uniquePageviews

TIMEFRAME last10days

Row/Column Totals

By customer request, we have added the ability to specify in AQL that row or column totals be computed and displayed when performing pivot queries. The syntax is demonstrated below.

SELECT

DIMENSIONS ga:campaign ON COLUMNS TOP 3 TOTALS

DIMENSIONS ga:medium ON ROWS TOTALS

FROM

default

WHERE

METRICS ga:transactionRevenue

TIMEFRAME default

The TOTALS keyword in optional on both the ROWS and COLUMNS axes. The totals row or column (or both) will be sorted by the specifed metric on whichever (or both) axes contain the sort expression:

DIMENSIONS ga:campaign ON COLUMNS TOP 3 TOTALS by ga:transactionRevenue

DIMENSIONS ga:medium ON ROWS TOTALS

or

DIMENSIONS ga:campaign ON COLUMNS TOP 3 TOTALS

DIMENSIONS ga:medium ON ROWS TOTALS by ga:transactionRevenue

or

DIMENSIONS ga:campaign ON COLUMNS TOP 3 TOTALS by ga:transactionRevenue

DIMENSIONS ga:medium ON ROWS TOTALS by ga:transactionRevenue

Note that currently AQL only supports a single metric in the WHERE clause.

Two or more row axis dimensions

AQL queries in Gadget data feeds and in Excel Web Queries (interactive and IQY) support having more than one row axis dimension.

SELECT

METRICS ga:visits ON COLUMNS

DIMENSIONS ga:pagePath, ga:pageTitle ON ROWS

FROM

[www.shufflepoint.com]

WHERE

TIMEFRAME default

Sorting and TOP

When the ROWS axis contains a dimension, you can sort the rows in the result table by a metric by using a sort expression on the row axis. The full syntax for such an axis is:

<metrics list> ON COLUMNS

<dimension> ON ROWS TOP <n> BY <metric> [ASC|DESC]

This query will return the tabular result shown below:

SELECT

METRICS ga:visits, ga:pageviewsPerVisit, ga:bounceRate ON COLUMNS

DIMENSIONS ga:country ON ROWS TOP 10 BY ga:visits DESC

FROM

default

WHERE

TIMEFRAME yearToDate

Country/TerritoryVisitsPages/VisitBounce Rate
United States2,275439.87%
United Kingdom297350.84%
Netherlands191342.93%
France180350.56%
India170255.29%
Spain156253.85%
Canada149357.72%
Japan144259.72%
Germany142341.55%
Brazil141351.77%

Filter Expressions

The Google Analytics API supports filter expressions which act to limit the data which is returned - either by metric value thresholds or by dimension member values. AQL passes specified filter expressions directly to Google, so they are not documented here. See this Google reference page for details on the supported filters.

An example filter expressed in an AQL query is

SELECT ...

WHERE

FILTER ga:pageviews > 10 AND ga:browser != "Safari"

Note that the right side of the filter expression must be placed within double quotes or square brackets.

SELECT ...

WHERE

FILTER ga:country == "United States"

Advanced Segment Expressions

AQL supports optionally specifying either a named segment of a dynamic segment filter

Named Segments

The AQL syntax to specify a segment is

SELECT ...

SEGMENT "Returning Visitors"

or

SELECT ...

SEGMENT [Returning Visitors]

Notice that the segment name can be enclosed either in double quotes or square brackets.

Dynamic Segments

Segments can be defined dynamically using filter expressions. Multipart expressions can be build by separating comparisons using AND and OR

SELECT ...

SEGMENT ga:visitorType == "Returning Visitor"

Multipart expressions can be built up by separating comparisons using AND and OR:

SELECT ...

SEGMENT ga:medium == "cpa" OR ga:medium == "cpc" OR ga:medium == "cpm"

New feature: Advanced Dynamic Segments

ShufflePoint now supports the new Dynamic Segments feature. For the full documentation, we refer you to the GA documentation at https://developers.google.com/analytics/devguides/reporting/core/v3/segments. and at https://developers.google.com/analytics/devguides/platform/segments.

We did not want to delay making this feature available until a complete AQL grammar was created, so the following syntax will be supported in addition to any future AQL-based syntax:

SEGMENT "<dynamic segment expression>"

For example, long segment definition (this one from the GA documentation) could be expressed as:

SELECT

METRICS ga:visits, ga:pageviewsPerVisit, ga:bounceRate ON COLUMNS

FROM

default

WHERE

TIMEFRAME lastMonth

SEGMENT "users::sequence::ga:deviceCategory==mobile;->>ga:deviceCategory==desktop;sessions::condition::perHit::ga:transactionRevenue>100"

Segments can be defined dynamically using filter expressions. Multipart expressions can be build by separating comparisons using AND and OR

Format Suffix

Measures and dimension have default formats. You may override the default format by specifying a format string as a suffix on the metric or dimension name in the query. Some examples are shown in the following query.

SELECT

METRICS ga:visits, ga:avgTimeOnPage:{mm:ss}, ga:bounceRate:{0.0%} ON COLUMNS

DIMENSIONS ga:month:{mmm-yyyy} ON ROWS BY ga:visits

FROM default

WHERE

TIMEFRAME yearToDate

Format Function

The format function uses a format string and one or more metric parameters.

SELECT ...

FROM default

WHERE

METRICS FORMAT("{0:d} visits so far this year",ga:visits)

TIMEFRAME yearToDate

The format function can also take a dimension name as a third argument. If present, the second placeholder in the format expression will get the value of the count of the returned dimension members. So the following AQL query would return a single cell with the string “5124 visits from 96 countries so far this year.”

SELECT

FROM

default

WHERE

METRICS FORMAT("{0:d} visits from {1:d} countries so far this year",ga:visits,ga:country )

TIMEFRAME yearToDate

NOTE: When running AQL queries using IQY files, format statements using the # character must swap in the URL encoded representation, which is "%23". So the expression
  FORMAT("{0:#.0}",ga:pageviewsPerVisit)
would need to be changed to
  FORMAT("{0:%23.0}",ga:pageviewsPerVisit)
This is true for both queries embedded in IQY files and queries in Excel cells.
Also note that the format() function currently only supports the generation of a single text string result in a WHERE clause.

String literals in metric lists

Lists of metrics in an axis expression may contain string literals. An example of such a list is shown in the following query:

SELECT

METRICS ga:visits, "visits to the site, with an average of", ga:pageviewsPerVisit, "pageviews" ON COLUMNS

FROM

default

WHERE

TIMEFRAME lastWeek

String literals are very useful in the context of populating PowerPoint text placeholders because each cell in the AQL result table will fill the formatted runs you define in ShufflePoint Studio. So the above query might be styled as:

56 visits to the site, with an average of 3 pageviews

Formatting Metrics

The default display formatting of a metric is specified by the Google API or by ShufflePoint if it is a calculated metric. For example, the display format for ga:bounceRate is 0.00%. One of the features of AQL is the ability to specify a format override within the query. An example column expression without an override is:

METRICS ga:visits, ga:bounceRate ON COLUMNS

To format the bounce rate with no decimals, one would change it to:

METRICS ga:visits, ga:bounceRate:{0%} ON COLUMNS

NOTE: When running AQL queries using IQY files, format expressions using the # character must swap in the URL encoded representation, which is "%23".
So the metric expression ga:bounceRate:{#.00%}
would need to be changed to ga:bounceRate:{%23.00%}
This is true for both queries embedded in IQY files and queries in Excel cells.

Special percent formatting

If a count metric is formatted as a percent, then ShufflePoint will interpret this as a request to display the percent of total contributed by the metric for each dimension member.

SELECT

METRICS ga:visits, ga:visits:{%} on columns

DIMENSIONS ga:visitorType ON ROWS

FROM

default

WHERE

TIMEFRAME default

Visitor Type Visits % Visits
New Visitor 1453 .867981
Returning Visitor 221 .132019

Dimension Count

If a dimension is placed in a METRICS expression, then the dimension is converted to a metric of "count of dimension members". For example, the following query returns a value of 44 for languages. This is a count of the number of different languages of the visitors for the designated timeframe.

SELECT

METRICS ga:visits, ga:language ON COLUMNS

FROM

default

WHERE

TIMEFRAME default

Profile Visits Language
70442 1674 44

Multiple Profiles

The query language supports getting data from multiple profiles in a single query. List all the profiles in the FROM clause and use the special sp:profile dimension to specify that you want the list of profiles to be treated as a dimension.

SELECT

METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS

DIMENSIONS sp:profile ON ROWS

FROM

[www.shufflepoint.com], [blog.shufflepoint.com], [dev.shufflepoint.com]

WHERE

TIMEFRAME lastYear

The list of profiles can also be in the for of a comma separate list of profile IDs:

SELECT

METRICS ga:pageviews, ga:timeOnPage, ga:bounceRate ON COLUMNS

DIMENSIONS sp:profile ON ROWS

FROM

2348584, 523477, 182843

WHERE

TIMEFRAME thisMonth

Shifting time periods

When a time period dimension such as ga:month or ga:week is sent to the Google API, the result set is always ordered by calendar and beginning at first day of the year. So if in June, 2009 you query:

SELECT

METRICS ga:visitors ON COLUMNS

DIMENSIONS ga:month ON ROWS

FROM

default

WHERE

TIMEFRAME lastYear

You will find that your months are ordered like so:

Month Visits
Jan 2009 1656
Feb 2009 1871
March 2009 2083
April 2009 1721
May 2009 1505
June 2008 1593
July 2008 1398
Aug 2008 1428
Sept 2008 1410
Oct 2008 1550
Nov 2008 1708
Dec 2008 1501

You may prefer to display the rows starting with June 2008 and ending with May 2009. This can be accomplished by adding a shift argument to the month dimension:

DIMENSIONS ga:month:5 ON ROWS

This shifts the rows to achieve the desired sequence of months.

Last N periods

Another useful AQL feature is to get timeframes such as “last 3 months” or “last 10 weeks”. This is accomplished in AQL using parameterized timeframe names. To get the last three months, for example, one can use:

SELECT

METRICS ga:visitors ON COLUMNS

DIMENSIONS ga:month ON ROWS

FROM

default

WHERE

TIMEFRAME months:3

To get the last three full months, one would use:

WHERE

TIMEFRAME fullmonths:3

Likewise, to get the last ten weeks you can use:

SELECT

METRICS ga:visitors ON COLUMNS

DIMENSIONS ga:week ON ROWS

FROM

default

WHERE

TIMEFRAME weeks:10

And to get the last six full weeks you can use:

SELECT

METRICS ga:visitors ON COLUMNS

DIMENSIONS ga:week ON ROWS

FROM

default

WHERE

TIMEFRAME fullweeks:6

Note that when using the parameterized timeframes months or fullmonths the ga:month dimension must be present on either rows or columns. And when using weeks or fullweeks the ga:week dimension must be present on either rows or columns.

Transform clause

The new TRANSFORMS clause in AQL is used to apply rollup operations on returned results. Currently the only transform operation supported is REPLACE. This operation does regular expression driven rollups of queries with the pagePath dimension. The following example is from our top pages blog article:

TRANSFORMS

REPLACE [(/|/Default\.aspx)$] WITH [/Default.aspx]

SELECT

METRICS ga:visits ON COLUMNS

DIMENSIONS ga:pagePath, ga:pageTitle ON ROWS TOP 10 NOOTHER BY ga:visits

FROM

[www.shufflepoint.com]

WHERE

TIMEFRAME default

The TRANSFORM says "find pages that end in "/" or in "/default.aspx" and aggregate them to the "/default.aspx" version. Without the transform clause present, the first few rows would be:

Page path Page title Visits
/GoogleAnalytics.aspx Features 367
/ ShufflePoint, Inc. 275
/Default.aspx ShufflePoint, Inc. 117
/help/QuickStartExcelWebQuery.aspx Quick Start - Excel Web Queries 115

With the transform present, the 2nd ("/") and 13th ("/default.aspx") entries are merged with the 3rd entry ("/Default.aspx"). The idea is that these three entries are really the same page and so should only have a single row present in the result set, and ordering should be based upon the rolled up metric values from those three entries.

Page path Page title Visits
/GoogleAnalytics.aspx Features 367
/Default.aspx ShufflePoint, Inc. 408
/help/QuickStartExcelWebQuery.aspx Quick Start - Excel Web Queries 115

NOOTHER qualifier

By default, AQL will add an "other" row to a result set with a TOP clause. So the query:

SELECT

METRICS ga:avgTimeOnPage ON COLUMNS

DIMENSIONS ga:continent ON ROWS top 4 by ga:avgTimeOnPage

FROM

default

WHERE

TIMEFRAME default

will be run as

SELECT

METRICS ga:avgTimeOnPage ON COLUMNS

DIMENSIONS ga:continent ON ROWS top 4 BottomOther BY ga:avgTimeOnPage

FROM

default

WHERE

TIMEFRAME default

because "BottomOther" is the implicit default value for the OTHER qualifier. The result set will have an "other" row added, and it will be the 4th row:

Continent Avg. Time on Page
Americas 00:01:23
Europe 00:01:00
Asia 00:01:35
(other) 00:01:13

If you do not wish to have the "other" row added, add the "NoOther" qualifier to the query after the TOP expression:

SELECT

METRICS ga:avgTimeOnPage ON COLUMNS

DIMENSIONS ga:continent ON ROWS top 4 NoOther by ga:avgTimeOnPage

FROM

default

WHERE

TIMEFRAME default

Continent Avg. Time on Page
Americas 00:01:23
Europe 00:01:00
Asia 00:01:35
Oceania 00:01:01

Header qualifiers

There may be times that you need a result set without row or column headings. This could be useful, for example, if you were merging several queries into one big table in Excel and you wish to avoind duplicate column headings. The "NoRowHeaders" and "NoColumnHeaders" qualifiers serve this purpose. For example, the query below will result in the table below.

SELECT NoRowHeaders

metrics ga:uniquePageviews, ga:bounceRate on columns

dimensions ga:country on rows top 5 by ga:pageviews

FROM

default

WHERE

TIMEFRAME default

Unique Pageviews Bounce Rate
873 .744015
208 .747664
162 .753247
151 .758621
142 .604167

An example which removes both column and row headers follows. Note that the order must be "NoRowHeaders NoColumnHeaders".

SELECT NoRowHeaders NoColumnHeaders

metrics ga:uniquePageviews, ga:bounceRate on columns

dimensions ga:country on rows top 5 by ga:pageviews

FROM

default

WHERE

TIMEFRAME default

The "SingleRowHeader" qualifier, when present, will result in all dimensions merged into a single column. For example, the query below will result in the table below.

SELECT SingleRowHeaders

metrics ga:uniquePageviews, ga:bounceRate on columns

dimensions ga:country, ga:city on rows top 3 by ga:pageviews

FROM

default

WHERE

TIMEFRAME default

Country - City Unique Pageviews Bounce Rate
USA - New York 873 .744015
United Kingdom - London 208 .747664
France - Paris 162 .753247

Custom Dimensions

This advanced but powerful feature allows you to create your own dynamic dimension based upon dimension members which you define. Membership is specified using either a filter or a segment.

A good example of this feature is getting a sorted list of traffic sources:

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

This custom dimension is similar to ga:medium. But ga:medium cannot be used with the metric ga:visitors. You could get visitors for a single segment using a dynamic segment clause:

SELECT

METRICS ga:visitors ON COLUMNS

FROM default

WHERE timeframe default

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"

But that would only return a single row. With a custom variable, you can effectively run several SEGMENT queries and get the sorted results.

The real power of custom dimensions is when used with custom variables. See the AQL Query Samples page for some more examples.

Split Dimensions

Another advanced but powerful feature. Split a single dimension into sub-components by adding a regular expression filter at the end of the dimension name.

Assume that the custom variable 1 has subparts of topic, author, publication date, and size separated by a "/". The following query will decompose the custom variable into four separate columns.

SELECT

METRICS ga:pageviews, ga:visits ON COLUMNS

DIMENSIONS ga:customVarValue1:{(?<Topic>.*?)\/(?<Author>.*?)\/(?<Date>.*?)\/(?<Size>.*?)} ON ROWS BY ga:visitors

FROM default

WHERE timeframe default

Excel Hints

There is a quick-start on using AQL with Excel at /help/QuickStartExcelIQY.aspx. But while you are here, there are a couple of hints we'd like to share.

Large queries (> 255 characters)
You can shorten with our query shortener
OR
You can have the query span several rows in Excel. As long as the part of the query in a single cell is less than 255 characters you are good.
Turn off "background refresh"
It is mentioned in the Excel quick-start but bears repeating. Excel, especially on Mac, will have refresh issues if you have lots of queries and don't turn off Background Refresh.
Excel does have bugs
The most serious bug is when you can open a workbook but Excel crashes when you save it. There is a blog post which explains how to fix this.

 


Related Quick Start Guides