...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
...
A metric is an expression which describes a computation that will be performed over the project's data. Metrics are written in a custom 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 Block |
---|
{ "name": "turnover_metric", "type": "metric", "content": { "type": "function_sum", "content": [ { "type": "property", "value": "baskets.amount" } ] } } |
Code Block |
Example of a dumped metric
Code Block | |
---|---|
true | { "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
...
Info | |
---|---|
For a complete list of supported metrics, see the Metrics cheatsheet article. | |
title |
Offline turnover metric object syntax
Code Block | |
---|---|
true | { "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 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 arbitrary number of filters.
_
...
Purchases per customer metric object syntax
Code Block | |
---|---|
true | { "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
collapse | true
---|
Code Block |
{ "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 } ] } } |
...
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
collapse | true
---|
Code Block |
{ "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 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
collapse | true
---|
Code Block |
{ "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" } ] } } } |
Code Block |
Departures metric
collapse | true
---|
Code Block |
{ "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
collapse | true
---|
Code Block |
{ "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" } } } |
...
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
collapse | true
---|
Code Block |
{ "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" } ] } } |
...
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
collapse | true
---|
Code Block |
{ "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 | ||||||
---|---|---|---|---|---|---|---|---|---|---|
| string |
| dwh query property, dwh query function, or a number |
| ||||||||||
| array |
| array of dwh property definitions (see the content.content table) | |||||||
| object |
| function options, allows to specify filters |
content.content
Key | Type | Optionality | Description | Constraints | ||||||
---|---|---|---|---|---|---|---|---|---|---|
| string |
| choose a custom string identifier for the query property |
| ||||||
| string |
| dwh query property, dwh query function, or a number |
| ||||||||||
| string long decimal |
| string identifier of a dataset property, which the function will be applied to (for string with variable name (for long or decimal value (for |
|
dataset
Property
|
content.options
Key | Type | Optionality | Description | Constraints | ||||||
---|---|---|---|---|---|---|---|---|---|---|
| array |
| specifies datasets which the metric will be aggregated to if specified, always | [null] | ||||||
| array |
| specifies datasets which the metric is allowed to be aggregated to array of dataset names, or name prefixes (with the only one of |
| ||||||
| array |
| specifies datasets which the metric is not allowed to be aggregated to array of dataset names, or name prefixes (with the only one of |
| ||||||
| array |
| specifies dataset properties not to be explicitly joined into the final query array of dataset properties. The syntax only one of |
| ||||||
| array |
| specifies dataset properties to be explicitly joined into the final query array of dataset properties. The syntax only one of |
| ||||||
| object |
| object specifying the filter of the metric | |||||||
| integer |
| the number of places to round the metric result to only for a metric with |
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
collapse | true
---|
Code Block |
{ "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 ] } } ] } } |
...
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.
Drawio | ||||||
---|---|---|---|---|---|---|
|
...
|
...
|
...
|
...
|
...
|
...
|
...
|
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
collapse | true
---|
Code Block |
{ "name": "turnover_value_metric", "type": "metric", "content": { "type": "function_sum", "content": [ { "type": "property", "value": "transactions.transaction_size" } ], "options": { "acceptAggregateBy": [ "districts", "wards" ] } } } |
Code Block |
Metric with dontAggregateBy example
collapse | true
---|
Code Block |
{ "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 | ||||||
---|---|---|---|---|---|---|---|---|---|---|
| string |
| identifier of a dataset property, which the filter will be applied to |
| ||||||
| string long decimal boolean |
| 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:
only one of | |||||||
| object |
| query, by which the property will be filtered (see the content.content table) only one of | |||||||
| object |
| 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
collapse | true|
---|---|
Code Block | |
"filterBy": [ { "property": "shops.type", "value": "partner", "operator": "eq" } ] | |
title |
filterBy syntax excerpt with integer value and lte operator
Code Block | |
---|---|
true | "filterBy": [ { "property": "baskets.amount", "value": 100, "operator": "lte" } ] |
Code Block | |
filterBy syntax excerpt with an array of string values and in operator
collapse | true
---|
Code Block |
"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 |
---|---|---|
| arithmetic |
average of all input values |
|
|
sum of all input values |
|
| distinct |
count of all input values that are not |
| |
| maximum value |
|
| minimum value |
|
| sample standard deviation of the input values |
|
| population standard deviation of the input values |
|
| sample variance of the input values |
|
| population variance of the input values |
|
|
|
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 |
---|---|---|
| integer ranging from 1 to the argument value, dividing the partition as equally as possible |
|
| rank of the current row with gaps |
|
| value below which a given percentage of observations in a group of observations fall |
input values sorted = (105.9, 75.2, 61.1, 42.7, 35.2, 21, 10)
|
| a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrary assigned. |
|
| contribution to the total |
|
Arithmetic functions
Basic arithmetic functions.
function_plus
,function_minus
andfunction_multiply
accept 2 or more argumentsfunction_divide
andfunction_modulo
accept exactly 2 arguments
Identifier | Example |
---|---|
|
|
|
|
|
|
|
|
|
|
Mathematical functions
Basic mathematical functions.
Identifier | Description | Example |
---|---|---|
| a rounding function (round to specific number of places) |
|
Conditional functions
Identifier | Description | Example |
---|---|---|
| defines the default value to use if the result of aggregate function is |
|
Filter operators list
Operators
Identifier | Description | Example | ||
---|---|---|---|---|
| is equal |
| ||
| negated |
| ||
| in range |
| ||
| lower than |
| ||
| lower than or equal |
| ||
| greater than |
| ||
| greater than or equal |
| ||
| is null |
| ||
| is not null |
| ||
| is in attribute | see the usage in a complete metric example above | ||
| is not in attribute | complementary to the | ||
| radius query in meters from a central point |
| ||
| generic polygon filter |
|
Logical operators
Allow you to create advanced filters and to combine operators.
Identifier | Description |
---|---|
| logical conjuction - takes at least two arguments |
| logical disjunction - takes at least two arguments |
| negation - takes one argument |
Visual representation
Metrics do not have a visual representation in the application.
...