Action Query Language (AQL)
The Action Query Language (AQL) is an SQL-like query language for querying data that has been processed by Calq. It is designed to feel similar to SQL which makes it easy for developers to learn quickly.
AQL can be written within the Calq reporting interface for a number of reports (e.g. by pressing Advanced Editor from the Detail report), and is also used to make queries through the Query API. Almost all data shown within the Calq reporting interface can be queried remotely using AQL and the Query API.
Time series queries
Time series queries fetch data over a given timespan grouped into slices (such as daily, or monthly). This kind of data is shown within the Calq reporting interface on the Detail and Outline reports.
SELECT [ COUNT(*) | COUNT(property) | COUNT(DISTINCT_ACTOR property) | AVERAGE(property) | SUM(property) | MIN(property) | MAX(property) | ACTIVE_USERS ] FROM action [ WHERE where_condition ] [ GROUP BY property ] PERIOD from TO to BUCKET 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.
WHERE condition allows the result set to be filtered. The condition is similar to SQL and can include
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.
AQL mirrors the ANSI SQL standard for opening and closing strings with a single quote (
'). Double quotes (
") are used for identifiers such as column and table names that need escaping. Some databases, notably MySQL, allow double quoting strings and this sometimes causes confusion for developers used to those environments.
Sometimes it is not possible for Calq to automatically guess the data type of a property. You can force a property to be treated as a specific type by casting it. To cast a property use the syntax
property::type, for example
"myProperty"::NUMBER. The default data type is
STRING, but other valid types are
Valid operations within the
WHERE condition depend on the data type being operated on.
If specifying a
GROUP BY property you may only include one additional property. Unlike traditional SQL, any properties within aggregates (such as
SUM etc) are implicitly part of the group and don't need to be added manually. You can use
GROUP BY to split results by cohorts.
All queries must have a
PERIOD clause (unless being built inside the Advanced Editor). This specifies the timespan of the data to be included in the query along with the size of the bucket. Valid
BUCKET options are
When building queries within the Calq reporting interface (using the Advanced Editor) you should not specify a PERIOD or BUCKET. The period will be taken from the date picker UI and automatically added for you. This allows you to adjust your date range easily after writing your query.
Time series examples
Time series examples
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' BUCKET BY DAY
Query the number of Registration actions, per hour, for September 2014, where the $utm_source 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' BUCKET BY HOUR
Query total sum of the property "Value" for an action "Purchase", per day, for September 2014.
SELECT SUM("Value") FROM "Purchase" PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59' BUCKET BY DAY
Query the number of unique users for an action "Boot Up", per month, for all of 2014, where their "app_version" property is between 12 and 14 (inclusive).
SELECT COUNT(DISCTINCT_ACTOR *) FROM "Boot Up" WHERE ("app_version"::NUMBER >= 12 AND "app_version"::NUMBER <= 14) PERIOD '2014-01-01 00:00:00' TO '2014-12-31 23:59:59' BUCKET BY MONTH
Normally a time series query is specific to a single action. There is a however a special table called
$all. This is an aggregate table containing all actions. It is used by the Outline view to show a count of all actions over a time period. You can not specify a
WHERE clause when querying the
nor can you apply any query expression except
SELECT COUNT(*) FROM $all PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59' BUCKET BY DAY
A funnel query calculates the conversion rate through a sequence of steps. The steps are actions and can be filtered by properties if desired.
SELECT FOR FUNNEL STEP FROM action [ WHERE where_condition ] STEP FROM action [ WHERE where_condition ] ... PERIOD from TO to
STEP in a funnel represents a named action and can have an optional
WHERE condition for filtering by properties. The
WHERE condition has the same syntax and available operations as time series queries.
PERIOD for a funnel query is restricted to a 30 day window.
Selecting user information from a funnel
Calq also supports exporting information on specific user based on their progress through a funnel. The syntax for specifying funnel steps is the same, but the query includes a
USERS list to specify user properties to query.
If no properties are needed then
USERS can be specified without parenthesis to request just actor identities and no additional information.
SELECT USERS(property1, property2, ...) FOR FUNNEL STEP FROM action [ WHERE where_condition ] STEP FROM action [ WHERE where_condition ] ... PERIOD from TO to
Query the conversion rates for a 3 step registration sequence for September 2014.
SELECT FOR FUNNEL STEP FROM "Registration" STEP FROM "Onboarding" WHERE "stage"::NUMBER = 1 STEP FROM "Onboarding" WHERE "stage"::NUMBER = 2 PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59'
Query the profile name and profile email of all users that entered the previous funnel along with which stage they reached.
SELECT USERS($full_name, $email) FOR FUNNEL STEP FROM "Registration" STEP FROM "Onboarding" WHERE "stage"::NUMBER = 1 STEP FROM "Onboarding" WHERE "stage"::NUMBER = 2 PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59'
Differences from SQL
AQL is not exactly the same as SQL. Key differences include:
|JOINs are not supported.||
You can not specify user defined JOIN operations against other actions. Some queries will join data internally (such as joining profile and event data together) but this is handled by Calq automatically.
|Select expressions are not supported.||
Within the parameter list of a SELECT (i.e SELECT params FROM ...) the parameters can only be named parameters wrapped in an aggregate function. At the moment Calq does not support expressions in the SELECT part, e.g. COUNT(param1 / param2). Expressions in WHERE clauses are supported.
Help with AQL
If you need help building a query then please reach out to support who will try to assist you.
We are often looking to expand the types of queries supported by AQL. If you have a query which you can not express in AQL then we will look at implementing it. We may even have internal queries offering partial support already.