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.

Note! 

  1. Entire field names that contain special characters, aggregation and commas must be enclosed in quotes (single or double). 
  2. 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

Clause
 Usage description
 Sample statement
 Syntax
fields
(dimensions)
 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>]+
filter
 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> ]+
aggregates
(metrics)
 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>]+
sort
 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>]
limit
 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>

Type Usage description Example
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:

  1. LastWeek:  now() - interval 1 week (now minus interval ONE week)
  2. LastHour:  now() - 1 hour (now minus ONE hour)
  3. LastMinute: now() - 1 minute (now minus ONE minute)
  4. LastMonth:  now() - 1 month (now minus ONE month)
  5. CurrentHour, CurrentMinute, CurrentWeek, CurrentMonth:  now()
 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()
Math expressions 
(with aggregations) 		Allows to do calculations on aggregated values var aggAvr = sum(Sales) / count(Sales)
Date expressions

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)

  1. startOfYear
  2. startOfMonth
  3. startOfWeek
  4. startOfDay
  5. startOfHour
 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