Skip to main content
Skip table of contents

Metrics

A metric is an expression which describes a computation that will be performed over the project's data. Metrics are written in a custom JSON query language, which is fairly simple and doesn't require users to know SQL. A metric written in this language is then translated to SQL by a backend service and executed as multidimensional query on data warehouse.

A metric is visualised on the map using an indicator, in which it is referenced by URL.

All properties used in metrics are dataset properties, located in dwh.ref.properties. On these properties, a set of functions can be applied. They can be nested, and the results of these functions can be combined and filtered. For better maintainability, metrics can be composed of other metrics.

Syntax

This is the simplest type of metric. It returns the sum (function_sum) of all basket amounts (property baskets.amount).

All available functions and operators are described below.

Turnover metric object syntax
CODE
{
    "name": "turnover_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "baskets.amount"
            }
        ]
    }
}


Example of a dumped metric
CODE
{
    "url": "/rest/projects/yufqzxkbiecj7jot/md/metrics/efea9kgccehnnt2n",
    "dumpTime": "2018-01-31T15:07:37Z",
    "version": "0",
    "content": {
        "id": "efea9kgccehnnt2n",
        "name": "turnover_metric",
        "type": "metric",
        "content": {
            "type": "function_sum",
            "content": [
                {
                    "type": "property",
                    "value": "baskets.amount"
                }
            ]
        },
        "accessInfo": {
            "createdAt": "2017-10-05T08:27:06Z"
        },
        "links": [
            {
                "rel": "self",
                "href": "/rest/projects/yufqzxkbiecj7jot/md/metrics/efea9kgccehnnt2n"
            }
        ]
    }
}

Additional syntax examples

Important - to properly understand metrics, please see these examples below.

For a list of examples of different metrics, see the Metrics cheatsheet article.

Offline turnover metric object syntax
CODE
{
    "name": "offline_turnover_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "baskets.amount"
            }
        ],
        "options": {
            "filterBy": [
                {
                    "property": "baskets.on_off_name",
                    "value": "Offline",
                    "operator": "eq"
                }
            ]
        }
    }
}

This metric extends the metric from the first example by filtering. The filter is defined in options.filterBy object filters the baskets.on_off_name property with an eq operator - so the metric computes the turnover of baskets ordered offline.

All metrics can have an arbitrary number of filters.

Purchases per customer metric object syntax
CODE
{
    "name": "purchases_customer_metric",
    "type": "metric",
    "content": {
        "type": "function_divide",
        "content": [
            {
                "type": "function_count",
                "content": [
                    {
                        "type": "property",
                        "value": "baskets.basket_id"
                    }
                ]
            },
            {
                "type": "function_count",
                "content": [
                    {
                        "type": "property",
                        "value": "clients.client_id"
                    }
                ]
            }
        ]
    }
}

Let's have a look at a more complex example. This metric computes the number of purchases per customer.

Consider this metric a fraction. On the top level, the aggregate function is function_divide, which represents a fraction bar. The numerator here is a function_count of the number of baskets, and the denominator is the count of all clients.

Market penetration metric object syntax
CODE
{
    "name": "market_penetration_metric",
    "type": "metric",
    "content": {
        "type": "function_ifnull",
        "content": [
            {
                "type": "function_divide",
                "content": [
                    {
                        "type": "function_count",
                        "content": [
                            {
                                "type": "property",
                                "value": "clients.client_id"
                            }
                        ]
                    },
                    {
                        "type": "function_sum",
                        "content": [
                            {
                                "type": "property",
                                "value": "demography_postcode.households"
                            }
                        ],
                        "options": {
                            "withoutFilters": [
                                "dim_dates.*"
                            ]
                        }
                    }
                ]
            },
            {
                "type": "number",
                "value": 0.0
            }
        ]
    }
}

This metric computes the penetration of the market. Market penetration is computed as a number of customers, divided by the sum of the number of households. This example demonstrates the use of withoutFilters. In our view, we have defined a globalDate filter which filters the dim_dates.date_iso property. There is no way to link the dim_dates and demography_postcode datasets, yet they both appear in one metric. To evade an error of finding a non-existent join path between these datasets, we use withoutFilters on all properties of the dim_dates dataset.

Another thing to note here is the use of function_ifnull. If the result of the function_divide should be null (e.g. in case of division by zero), the result of the metric in that case will be 0.0.

Population where revenue is greater than 10000 metric syntax
CODE
{
    "name": "population_where_revenue_gt_10000",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "demography.population"
            }
        ],
        "options": {
            "filterBy": [
                {
                    "operator": "inAttribute",
                    "property": "wards.ward_id",
                    "query": {
                        "properties": [
                            {
                                "id": "ward_id",
                                "type": "property",
                                "value": "wards.ward_id"
                            },
                            {
                                "id": "transaction_sum",
                                "type": "function_sum",
                                "content": [
                                    {
                                        "type": "property",
                                        "value": "transactions.value"
                                    }
                                ]
                            }
                        ],
                        "having": [
                            {
                                "operator": "gt",
                                "propertyId": "transaction_sum",
                                "value": 10000
                            }
                        ]
                    }
                }
            ],
            "withoutFilters": [
                "transactions.store_id"
            ]
        }
    }
}

This metric computes the population in areas where the turnover is greater than 10000 (given that the contents of the demography dataset are computed to the ward level).

This metric uses the inAttribute operator, which is a specific operator that allows you to filter the metric based on the result of another metric (query). This query is specified in the filterBy.query object. This functionality - filtering areas based on the result of a different query - is also available in the form of indicator filters defined in the view object.

Arrivals metric
CODE
{
    "name": "arrivals_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "departures_arrivals.arrivals"
            }
        ],
        "options": {
            "withoutFilters": [
                "*.*"
            ],
            "filterBy": [
                {
                    "property": "departures_arrivals.source_country",
                    "query": {
                        "properties": [
                            {
                                "id": "country_name",
                                "type": "property",
                                "value": "countries_dwh.country_name"
                            },
                            {
                                "id": "aux_count",
                                "type": "function_count",
                                "content": [
                                    {
                                        "type": "property",
                                        "value": "countries_dwh.country_name"
                                    }
                                ],
                                "options": {
                                    "withoutFilters": [
                                        "countries_dwh.x_*",
                                        "countries_dwh.y_*"
                                    ]
                                }
                            }
                        ],
                        "having": [
                            {
                                "propertyId": "aux_count",
                                "value": 0,
                                "operator": "gte"
                            }
                        ]
                    },
                    "operator": "inAttribute"
                },
                {
                    "property": "departures_arrivals.arrivals",
                    "value": 0,
                    "operator": "gt"
                }
            ]
        }
    }
}

Departures metric
CODE
{
    "name": "departures_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "departures_arrivals.departures"
            }
        ],
        "options": {
            "withoutFilters": [
                "*.*"
            ],
            "filterBy": [
                {
                    "property": "departures_arrivals.destination_country",
                    "query": {
                        "properties": [
                            {
                                "id": "country_name",
                                "type": "property",
                                "value": "countries_dwh.country_name"
                            },
                            {
                                "id": "aux_count",
                                "type": "function_count",
                                "content": [
                                    {
                                        "type": "property",
                                        "value": "countries_dwh.country_name"
                                    }
                                ],
                                "options": {
                                    "withoutFilters": [
                                        "countries_dwh.x_*",
                                        "countries_dwh.y_*"
                                    ]
                                }
                            }
                        ],
                        "having": [
                            {
                                "propertyId": "aux_count",
                                "value": 0,
                                "operator": "gte"
                            }
                        ]
                    },
                    "operator": "inAttribute"
                },
                {
                    "property": "departures_arrivals.departures",
                    "value": 0,
                    "operator": "gt"
                }
            ]
        }
    }
}

Arrivals/departures metrics compute the number of, e.g. people which have arrived to or departed from a destination. Whether it is a country, a city, (polygon) or a shop (marker). The syntax of these two metrics is very similar. Apart from this specific syntax, a indicator.content.relations.reversedMetric must be specified in the corresponding indicator:

Arrivals indicator
CODE
{
    "name": "arrivals_indicator",
    "type": "indicator",
    "title": "Arrivals",
    "description": "Daily arrivals to a specific country",
    "content": {
        "metric": "/rest/projects/$projectId/md/metrics?name=arrivals_metric",
        "scale": "standard",
        "distribution": "geometric",
        "format": {
            "type": "number",
            "fraction": 0
        },
        "relations": {
            "type": "self",
            "reversedMetric": "/rest/projects/$projectId/md/metrics?name=departures_metric"
        }
    }
}

And vice versa for destinations_indicator.

Also, the data which these metrics work with must have a specific format - mirrored pairs with values for each source/destination node:

id

source_country

destination_country

arrivals

departures

1

USA

Canada

20 000

8 000

2

Canada

USA

8 000

20 000

3

USA

United Kingdom

32 000

18 500

4

United Kingdom

USA

18 500

32 000


Metric with variable syntax
CODE
{
    "name": "exposure_index_metric",
    "type": "metric",
    "content": {
        "type": "function_multiply",
        "content": [
            {
                "type": "function_sum",
                "content": [
                    {
                        "type": "property",
                        "value": "poi.point_value"
                    }
                ]
            },
            {
                "type": "variable",
                "value": "index_variable"
            }
        ]
    }
}

This is a metric which uses metric variables. The resulting sum of an exposure index is simply multiplied by the value substituted in the index_variable. The variables are set in the variables filter in the view object.

For better maintainability, metrics can be composed of other metrics. The following example shows a new offline_turnover_ratio_metric that reuses two already-defined metrics:

Reuse of metric syntax
CODE
{
    "name": "offline_turnover_ratio_metric",
    "type": "metric",
    "content": {
        "type": "function_divide",
        "content": [
            {
                "type": "metric",
                "metric": "/rest/projects/$projectId/md/metrics?name=offline_turnover_metric"
            },
            {
                "type": "metric",
                "metric": "/rest/projects/$projectId/md/metrics?name=turnover_metric"
            }
        ]
    }
}


Key description

content

Key

Type

Optionality

Description

Constraints

type

string

REQUIRED

dwh query property, dwh query function, or a number

[dwh query function, property, number, variable]

content

array

REQUIRED

array of dwh property definitions (see the content.content table)


options

object

OPTIONAL

function options, allows to specify filters


content.content

Key

Type

Optionality

Description

Constraints

id

string

OPTIONAL

choose a custom string identifier for the query property

(a-z0-9_-)

type

string

REQUIRED

dwh query property, dwh query function, or a number

[dwh query function, property, number, variable]

value

string

long

decimal

REQUIRED

string identifier of a dataset property, which the function will be applied to (for type=function)

string with variable name (for type=variable)

long or decimal value (for type=number)

{datasetName}.{datasetProperty}

content.options

Key

Type

Supported by function types

Optionality

Description

Constraints

aggregateBy

array

Aggregate

OPTIONAL

specifies datasets which the metric will be aggregated to

if specified, always null

[null]

acceptAggregateBy

array

All function types

OPTIONAL

specifies datasets which the metric is allowed to be aggregated to

array of dataset names, or name prefixes (with the * wildcard)

(warning) only one of acceptAggregateBy and dontAggregateBy keys can be specified

For nested metrics, only the options.acceptAggregateBy for a top level metric is evaluated.

{datasetName}

{datas*}


dontAggregateBy

array

All function types

OPTIONAL

specifies datasets which the metric is not allowed to be aggregated to

array of dataset names, or name prefixes (with the * wildcard)

(warning) only one of acceptAggregateBy and dontAggregateBy keys can be specified

For nested metrics, only the options.acceptAggregateBy for a top level metric is evaluated.

{datasetName}

{datas*}


withoutFilters

array

Aggregate

OPTIONAL

specifies dataset properties not to be explicitly joined into the final query

array of dataset properties. The syntax from:{datasetName} is expanded to all datasets following given {datasetName} including the {datasetName} itself.

(warning) only one of withoutFilters and acceptFilters keys can be specified

{datasetName}.{datasetProperty}

{datasetName}.*

*.*

from:{datasetName}

acceptFilters

array

Aggregate

OPTIONAL

specifies dataset properties to be explicitly joined into the final query

array of dataset properties. The syntax from:{datasetName} is expanded to all datasets following given {datasetName} including the {datasetName} itself. 

(warning) only one of withoutFilters and acceptFilters keys can be specified

{datasetName}.{datasetProperty}

{datasetName}.*

*.*

from:{datasetName}

filterBy

object

Aggregate

OPTIONAL

object specifying the filter of the metric


places

integer

function_round

OPTIONAL

the number of places to round the metric result to


bucket

integer

Window

REQUIRED

number of buckets (approximately equal groups) to break the result set into

sort

string

Window

OPTIONAL

define how to sort input values

asc, desc

partitionBy

array

function_ntile

OPTIONAL

list of dataset properties to be partioned by

dataset

string

Geospatial

REQUIRED

name of geometryPoint dataset

resolution

integer

function_h3_grid

REQUIRED

specify H3 grid resolution

0 - 15

centralPoint

object

function_distance

REQUIRED

object defining a static lng and lat coordinates

Detailed options description

aggregateBy:

Specifies datasets which the metric will be aggregated to, usually some administrative unit - district, ward, etc.

So far, only null is implemented. That means, do not aggregate to any datasets. For example, we'd like to see a metric that computes the turnover share of one administrative unit, in comparison to the total turnover. Syntax of this metric would be:

Metric with aggregateBy example
CODE
{
    "name": "turnover_share_metric",
    "type": "metric",
    "content": {
        "type": "function_divide",
        "content": [
            {
                "type": "function_sum",
                "content": [
                    {
                        "type": "property",
                        "value": "transactions.transaction_size"
                    }
                ]
            },
            {
                "type": "function_sum",
                "content": [
                    {
                        "type": "property",
                        "value": "transactions.transaction_size"
                    }
                ],
                "options": {
                    "aggregateBy": [
                        null
                    ]
                }
            }
        ]
    }
}


acceptAggregateBy and dontAggregateBy:

Specify datasets which the metric is allowed/not allowed to be aggregated to, usually some administrative unit - district, ward, etc.

Some metrics are not computable when aggregated to certain datasets. Say we have a 3-level hierarchical administrative units - districts, wards and postcodes. But the business data is available only up to the 2nd level - wards.

When aggregating to the 3rd level (selecting the postcodes granularity), the metric would not be computable and respective indicator would show the "N/A" result. To avoid this, use acceptAggregateBy or dontAggregateBy

Metric with acceptAggregateBy example
CODE
{
    "name": "turnover_value_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "transactions.transaction_size"
            }
        ],
        "options": {
            "acceptAggregateBy": [
                "districts",
                "wards"
            ]
        }
    }
}


Metric with dontAggregateBy example
CODE
{
    "name": "turnover_value_metric",
    "type": "metric",
    "content": {
        "type": "function_sum",
        "content": [
            {
                "type": "property",
                "value": "transactions.transaction_size"
            }
        ],
        "options": {
            "dontAggregateBy": [
                "postcodes"
            ]
        }
    }
}

Using these options, the indicator card will show an explanation of why is there no metric result.

_

withoutFilters has 3 use cases:

  • If the map is zoomed in on a certain level, the map window has its bounding box properties. In this case, withoutFilters is used to not apply the filter to the areas which are out of the current bounding box. This is a performance improvement.

  • If there is an strictly defined metric e.g. "total number of customers", as shown in the example below. After applying a filter, the result of this metric wouldn't make sense. So here, withoutFilters prevents these possible semantic issues.

  • In an indicator, two tables can have a relationship through a catchment area - e.g. the demography of, and the orders made in a certain county. These tables however, are not linked through foreign key, and thus cannot be explicitly joined. If we apply a filter to this indicator, using withoutFilters, we can prevent errors of not finding the join path between orders and demography. This is more of an error evasion technique.

Both withoutFilters and acceptFilters may contain:

  • specific dataset properties "clients.client_id"

  • wildcard on all dataset properties "clients.*"

  • multiple datasets, e.g. "dim_dates*.*" (this would filter out all datasets from the can-dim-dates dimension)

  • all datasets "*.*"

The behaviour of the acceptFilters array is the opposite of withoutFilters. Because sometimes, it is much simpler to define a list of filters to be accepted than those to be ignored.

An example can be seen above in the "Market penetration metric object syntax" code excerpt.

content.options.filterBy

filterBy is a versatile object that provides various ways of filtering the result of the metric.

Key

Type

Optionality

Description

Constraints

property

string

REQUIRED

identifier of a dataset property, which the filter will be applied to

{datasetName}.{datasetProperty}

value

string

long

decimal

boolean

VARIES

value, by which the property will be filtered

this key is polymorphic - it doesn't have only one type

it can also be a single value, or an array:

  • for the in operator, specify an array of values

  • for all the other operators, use single value

(warning) only one of value and query keys can be specified


query

object

VARIES

query, by which the property will be filtered (see the content.content table)

(warning) only one of value and query keys can be specified


operator

object

REQUIRED

the operator that will be used by the filter

see the available filterBy operators


filterBy examples

filterBy syntax excerpt with string value and eq operator
CODE
    "filterBy": [
        {
            "property": "shops.type",
            "value": "partner",
            "operator": "eq"
        }
    ]

filterBy syntax excerpt with integer value and lte operator
CODE
    "filterBy": [
        {
            "property": "baskets.amount",
            "value": 100,
            "operator": "lte"
        }
    ]

filterBy syntax excerpt with an array of string values and in operator
CODE
    "filterBy": [
        {
            "property": "baskets.day_of_week",
            "value": [
                "Monday",
                "Tuesday",
                "Wednesday",
                "Thursday",
                "Friday"
            ],
            "operator": "in"
        }
    ]

DWH query function list

Aggregate functions

Aggregate functions compute a single result from a set of input values. 

Identifier

Description

Example

function_avg

arithmetic average of all input values

function_avg(42.0, 17.38, 87.2, 36.9) = 45,87

function_sum

sum of all input values

function_sum(42.0, 17.38, 87.2, 36.9) = 183,48

function_count

distinct count of all input values that are not null

function_count("apple", "banana", "apple", "orange", "banana") = 3

function_max

maximum value

function_max(42.0, 17.38, 87.2, 36.9) = 87.2

function_min

minimum value

function_min(42.0, 17.38, 87.2, 36.9) = 17.38

function_stddev_samp

sample standard deviation of the input values

function_stddev_samp(42.0, 17.38, 87.2, 36.9) = 29.525620512813

function_stddev_pop

population standard deviation of the input values

function_stddev_pop(42.0, 17.38, 87.2, 36.9) = 25.569937426595

function_var_samp

sample variance of the input values

function_var_samp(42.0, 17.38, 87.2, 36.9) = 871.76226666667

function_var_pop

population variance of the input values

function_var_pop(42.0, 17.38, 87.2, 36.9) = 653.8217

function_percent_to_total


function_percent_to_total(42.0) = 653.8217

Window functions

Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.

Identifier

Description

Example

function_ntile

integer ranging from 1 to the argument value, dividing the partition as equally as possible

input: options.buckets = 3, values = [15, 73, 54, 33, 24, 80], argument = 73, options.sort = "asc"

input values are grouped into these buckets = ([15, 24], [33, 54], [73, 80])

result: 3 (argument 73 is in the 3th bucket)

function_rank

rank of the current row with gaps

input: values = [15, 73, 54, 33, 24, 15], argument = 54, options.sort = "asc"

input values sorted = (15, 15, 33, 54, 73, 80)

result: 4 (argument 54 is the 4th element in sorted list of input values)

function_percentile

value below which a given percentage of observations in a group of observations fall

input: values = [10, 75.2, 35.2, 21, 42.7, 61.1, 105.9], argument = 75.2, options.sort = "desc"

input values sorted = (105.9, 75.2, 61.1, 42.7, 35.2, 21, 10)

result: 83,3 (number 75.2 is higher than 83,3% of other input values)

function_row_number

a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrary assigned.

input: values = [15, 73, 54, 33, 24, 15], argument = 54, options.sort = "asc"

input values sorted = (15, 15, 33, 54, 73, 80)

result: 4 (argument 54 is the 4th element in sorted list of input values)

function_percent_to_total

contribution to the total

input: all values = [10, 75.2, 35.2, 21, 42.7, 61.1, 105.9], current group = 75.2

result: 0.21 (group 75.2 has a contribution 21% to the total)

Arithmetic functions

Basic arithmetic functions.

  • function_plus, function_minus and function_multiply accept 2 or more arguments

  • function_divide and function_modulo accept exactly 2 arguments

Identifier

Example

function_plus

7 + 2 = 9

function_minus

7 - 2 = 5

function_multiply

7 * 2 = 14

function_divide

7 / 2 = 3.5

function_modulo

7 mod 2 = 1

Mathematical functions

Basic mathematical functions.

Identifier

Description

Example

function_round

a rounding function (round to specific number of places)

input: value=56.157094235 options.places=1

result: 56.2

Conditional functions

Identifier

Description

Example

function_ifnull

defines the default value to use if the result of aggregate function is null

number = 0.0, value = null

function_ifnull(null) = 0.0


Geospatial functions

Identifier

Description

Example

function_h3_grid

computes the H3 grid ID of each record in given GeometryPoints dataset and selected H3 grid resolution

input: options.resolution = 13, options.dataset = customers

result: [ "8d195c0238e44bf", "8d195c05940c4bf", "8d195c05950da3f" ]

function_distance

computes the minimum geodesic distance between given central point and points in GeometryPoints dataset in meters.

See example on the right: a dataset customer contains three customers and the result is the distance to each customer from a given central point.

input: options.centralPoint = { lat = 52.4805067, lng = -1.8939561}, options.dataset = customers

result: [ 80.25360958, 235.71597112, 22802.9219321 ]

Filter operators list

FilterBy operators

Operator

Description

Example

eq

is equal

input values: ["apple","orange","banana"], eq = "orange"

result: ["orange"]

ne

negated

input values: ["apple","orange","banana"], ne = "orange"

result: ["apple","banana"]

in

in range

input values: [1,2,3,4,5,6,7,8], in = [3,6]

result: [3,4,5,6]

lt

lower than

input values: [1,2,3,4,5,6,7,8], lt = 5

result: [1,2,3,4]

lte

lower than or equal

input values: [1,2,3,4,5,6,7,8], lte = 5

result: [1,2,3,4,5]

gt

greater than

input values: [1,2,3,4,5,6,7,8], gt = 5

result: [6,7,8]

gte

greater than or equal

input values: [1,2,3,4,5,6,7,8], gt = 5

result: [5,6,7,8]

isNull

is null

input values: [1,2,3,4,null,6,7,8], isNull = 2

result: false

isNotNull

is not null

input values: [1,2,3,4,null,6,7,8], isNotNull = 2

result: true

inAttribute

is in attribute

see the usage in a complete metric example above

notInAttribute

is not in attribute

complementary to the inAttribute example 

distanceLte

radius query in meters from a central point

CODE
{
  "operator": "distanceLte",
  "dataset": "customers",
  "distance": 500,
  "centralPoint": {
    "lat": 52.4805067,
    "lng": -1.8939561
  }
}

nearest

the nearest (or N nearest) dataset records to a central point

CODE
 {
    "operator": "nearest",
    "dataset": "customers",
    "centralPoint": {
        "lat": 52.4805067,
        "lng": -1.8939561
    },
    "limit": 1
}

polygonWithin

generic polygon filter

CODE
{
    "operator": "polygonWithin",
    "dataset": "customers",
    "geometry": {
        "type": "Polygon",
        "coordinates": [[[-1.85, 52.47], [-1.85, 52.50], [-5, 52.47], [-1.85, 52.47]]]
    }
}

Logical operators

Allows you to create advanced filters and to combine operators.

Identifier

Description

and

logical conjuction - takes at least two arguments

or

logical disjunction - takes at least two arguments

not

negation - takes one argument

Visual representation

Metrics do not have a visual representation in the application.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.