Advanced queries with AQL - the Action Query Language

Calq allows you to run queries against your data using an SQL-like language called the Action Query Language (AQL). This has been designed to feel like SQL so developers can learn it easily.

AQL is used to write advanced queries within Calq where the query builder UI is not expressive enough for your needs, or simply when you are more comfortable writing AQL code than using the UI.

AQL is also used "under the hood" by the Calq reporting interface to query your data - almost everything shown in Calq is shown using AQL in combination with the Query API. This means you can write AQL queries to consume data within your own dashboards and admin systems.

Writing AQL within Calq

Specific reports within Calq have a UI option to open the "Advanced Editor". This allows you to write your own AQL. If you have a query (or part of a query) already built then this will be used as a base.

AQL allows you to treat actions as if they were SQL tables, and properties as if they were properties of those tables. The general syntax for an AQL SELECT query for time series data is:

SELECT [ COUNT(*) | COUNT(property) | COUNT(DISTINCT_ACTOR property) | AVERAGE(property) | SUM(property) | MIN(property) | MAX(property) | ACTIVE_USERS ]  
FROM action  
[ WHERE where_condition ]
PERIOD from TO to GROUP BY bucket  

The query is used to fetch data from a single action. The name of the action is specified by the action placeholder. If the name contains spaces or keywords the action will need to be surrounded with double quotes (e.g. "My Action"). This is the same way you would query a table in most databases.

There optional WHERE condition allows the result set to be filtered. The condition is similar to SQL and can include AND and OR operations. Property names as part of a WHERE clause should again be double quoted if they contain spaces or are a keyword (e.g. "My Property").

Further details on what types of expression are valid in a WHERE condition can be found in the AQL documentation.

Note that when using the Advanced Editor you do not specify the PERIOD and GROUP BY line as that is handled automatically by the data picker. You only specify these when using the query API.

Example: Query the number of "Registration" actions, per day, for September 2014.

SELECT COUNT(*) FROM "Registration" PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59' GROUP BY DAY  

Example: Query the number of Registration actions, per hour, for September 2014, where the $utmsource parameter started with the string "Foo" or "Bar_"

SELECT COUNT(*) FROM "Registration"  
WHERE "$utm_source" LIKE 'Foo_%' OR "$utm_source" LIKE 'Bar_%'  
PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59' GROUP BY HOUR  

It is possible to write other types of query using AQL including conversion funnels (SELECT FOR FUNNEL ...) and retention (SELECT FOR RETENTION ...). For further information on all the supported query types see the AQL documentation.