Action Query Language (AQL)

Overview

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. "My Action").

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").

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 NUMBER, DATETIME, and BOOL.

Valid operations within the WHERE condition depend on the data type being operated on.

STRING types
  • = (equals)
  • != (not equals)
  • LIKE (case insensitive match, can use % for wildcard)
  • NOT LIKE
  • IS NULL (has no value set)
  • IS NOT NULL (has a value set)
NUMBER types
  • Numeric comparisons including: = (equals), != (not equals), > (greater than), < (less than), >= (greater than or equals), <= (less than or equals)
DATETIME types
  • Time comparisons in conjunction with AGO: > (greater than), < (less than), >= (greater than or equals), <= (less than or equals)
  • Either compare to literal date e.g. "myDate"::DATETIME < '2014-09-01 00:00:00', or use AGO function e.g. "myDate"::DATETIME > AGO('DAY', 7)
BOOL types
  • Either = TRUE (is true), or = FALSE (is false)

If specifying a GROUP BY property you may only include one additional property. Unlike traditional SQL, any properties within aggregates (such as COUNT 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 HOUR, DAY, WEEK, and MONTH.

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

Outline table

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 $all table, nor can you apply any query expression except COUNT(*) or COUNT(DISTINCT_ACTOR *).

SELECT COUNT(*) FROM $all PERIOD '2014-09-01 00:00:00' TO '2014-09-30 23:59:59' BUCKET BY DAY

Funnel queries

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

Each 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.

The 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

Funnel examples

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.