MIQL Syntax Guide for Plugins
MIQL (Metric Insights Query Language) is a simple query language designed for fetching and processing data. It is supported by the majority of Metric Insights' plugins.
- Entire field names that contain special characters, aggregation and commas must be enclosed in quotes (single or double).
- It is acceptable to enclose all fields and values in quotes.
[…] + Notation is used to signify that the MIQL parts of a statement are optional/can be repeated.
1. Building a General Query
|Adds fields and variables to the result set.
||fields = text, Country, Region, number
If this clause is omitted, all available columns will be retrieved.
|fields = <Name of field or variable> [, <Name of field or variable>]+
||Fetches a subset of data from the database.
||filter = County != 'Island'
||filter = <Name of field or variable> (Available operators are: == | != | < | <= | > | >=) <'Constant value'> [ AND <Filter expression> ]+
filter = <Name of field or variable> is [not] null [ AND <Filter expression> ]+
|Performs calculations on a set of numeric data values.
||aggregates = count(*), sum(Population)
||aggregates = (Available functions are: sum | avg | count | min | max) (<Name of field or variable>) [, <Aggregation>]+
||Sorts data from the specified field. It is possible to apply sorting to several fields.||sort = Index DESC, Country DESC
Sorting by Ascending order is the default and may be excluded from the query.
|sort = <Name of field or variable> [ASC | DESC] [, <Sort expression>]
||Brings the top N rows into the result.
||limit = 50
||limit = <Integer>
2. MIQL Variables
MIQL allows for the creation of variables based on source data and constant values.
Variables can be used as regular fields in filters, aggregations, sorts and can be added to the results set (fields).
Use var keyword to declare a variable.
Syntax: var <variable_name> = <expression>
|Constant value||Uses variable value as it is passed to the query.||var a = 1
var country = "Canada"
|Date||Date Variable takes from 1 to 6 parameters: Year, Month, Day, Hour, Minutes and Seconds accordingly.
For the Month param text values are allowed.
Constant values for all fields are also allowed.
|var monthStart = date(Year, Month, 1)
var newYear = date(Year, 'Jan', 1, 0, 0)
|Date constants||Supports a number of constants that are calculated depending on the current time:
||var today = TODAY
var beginOfWeek = CURRENT_WEEK
|Math expression||This feature uses exp4j v0.3.11, so all operations supported in this version are available.||var ab = [a] * [b]
var avr = [Total] / [Count]
var val = sin([alpha]) * 2 * pi()
|Allows to do calculations on aggregated values||var aggAvr = sum(Sales) / count(Sales)|
The following functions use the source date field to create a new field with the date and exact start time of the period (year, month, week, day, or hour)
|var f_calendar_date = startOfYear (calendar_date)
3. MIQL Parameters
It is possible to pass connection parameters in a MIQL request.
Use param keyword to declare a parameter.
- Syntax: param <parameter_name> = <value>
- Example: param primary_date_format = yyyy-MM-dd'T'hh:mm
4. Example of MIQL data fetching
The example below features a query for loading data into a Dataset.
5. What's next?
See how to use MIQL when collecting Data from Google Analytics