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.
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.
| Code Block | ||
|---|---|---|
| ||
{
"name": "turnover_metric",
"type": "metric",
"content": {
"type": "function_sum",
"content": [
{
"type": "property",
"value": "baskets.amount"
}
]
}
}
|
| Code Block | ||||
|---|---|---|---|---|
| ||||
{
"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
| Note |
|---|
Important - to properly understand metrics, please see these examples below. |
| Info |
|---|
For a complete list of supported metrics, see the Metrics cheatsheet article. |
| Code Block | ||||
|---|---|---|---|---|
| ||||
{
"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.
_
| Code Block | ||||
|---|---|---|---|---|
| ||||
{
"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.
_
| 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
}
]
}
}
|
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.
| 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 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.
| 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 | ||||
|---|---|---|---|---|
| ||||
{
"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:
| 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"
}
}
}
|
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:
...
| 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"
}
]
}
}
|
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.
Key description
content
...
[dwh query function, ]property, number, variable
...
array of dwh property definitions (see the content.content table)
...
content.content
...
[dwh query function, ]property, number, variable
...
string
long
decimal
...
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
...
specifies datasets which the metric will be aggregated to
if specified, always null
...
[null]
...
specifies datasets which the metric is allowed to be aggregated to
array of dataset names, or name prefixes (with the * wildcard)
only one of acceptAggregateBy and dontAggregateBy keys can be specified
{datasetName}
{datas*}
...
specifies datasets which the metric is not allowed to be aggregated to
array of dataset names, or name prefixes (with the * wildcard)
only one of acceptAggregateBy and dontAggregateBy keys can be specified
{datasetName}
{datas*}
...
specifies dataset properties not to be explicitly joined into the final query
array of dataset properties
only one of withoutFilters and acceptFilters keys can be specified
...
{datasetName}.{datasetProperty}
{datasetName}.*
*.*
...
specifies dataset properties to be explicitly joined into the final query
array of dataset properties
only one of withoutFilters and acceptFilters keys can be specified
...
{datasetName}.{datasetProperty}
{datasetName}.*
*.*
...
the number of places to round the metric result to
only for a metric with function_round
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:
| 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
]
}
}
]
}
}
|
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.
| 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 | ||||
|---|---|---|---|---|
| ||||
{
"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,
withoutFiltersis 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,
withoutFiltersprevents 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.
...
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:
- for the
inoperator, specify an array of values - for all the other operators, use single value
only one of value and query keys can be specified
...
query, by which the property will be filtered (see the content.content table)
only one of value and query keys can be specified
...
the operator that will be used by the filter
see the available filterBy operators
filterBy examples
| Code Block | ||||
|---|---|---|---|---|
| ||||
"filterBy": [
{
"property": "shops.type",
"value": "partner",
"operator": "eq"
}
]
|
| Code Block | ||||
|---|---|---|---|---|
| ||||
"filterBy": [
{
"property": "baskets.amount",
"value": 100,
"operator": "lte"
}
]
|
| 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.
...
function_var_pop(42.0, 17.38, 87.2, 36.9 = 653.8217
Window functions
Window functions provide the ability to perform calculations across sets of rows that are related to the current query row.
...
function_ntile
...
input: buckets = 3, values = [15, 73, 54, 33, 24, 80], argument = 73, sort = "asc"
input values are grouped into these buckets = ([15, 24], [33, 54], [73, 80])
result: 3 (argument 73 is in the 3th bucket)
...
input: values = [15, 73, 54, 33, 24, 15], argument = 54, 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)
...
input: values = [10, 75.2, 35.2, 21, 42.7, 61.1, 105.9], argument = 75.2, 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)
...
input: values = [15, 73, 54, 33, 24, 15], argument = 54, 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)
Arithmetic functions
Basic arithmetic functions.
function_plus,function_minusandfunction_multiplyaccept 2 or more argumentsfunction_divideandfunction_moduloaccept exactly 2 arguments
...
function_plus
...
7 + 2 = 9
...
7 - 2 = 5
...
7 * 2 = 14
...
Mathematical functions
Basic mathematical functions.
...
a rounding function (round to specific number of places)
...
Conditional functions
...
function_ifnull
...
number = 0.0, value = null
function_ifnull(null) = 0.0
Filter operators list
Operators
...
input values: ["apple","orange","banana"], eq = "orange"
result: ["orange"]
...
input values: ["apple","orange","banana"], ne = "orange"
result: ["apple","banana"]
...
input values: [1,2,3,4,5,6,7,8], in = [3,6]
result: [3,4,5,6]
...
input values: [1,2,3,4,5,6,7,8], lt = 5
result: [1,2,3,4]
...
input values: [1,2,3,4,5,6,7,8], lte = 5
result: [1,2,3,4,5]
...
input values: [1,2,3,4,5,6,7,8], gt = 5
result: [6,7,8]
...
input values: [1,2,3,4,5,6,7,8], gt = 5
result: [5,6,7,8]
...
input values: [1,2,3,4,null,6,7,8], isNull = 2
result: false
...
input values: [1,2,3,4,null,6,7,8], isNotNull = 2
result: true
...
Logical operators
Allow you to create advanced filters and to combine operators.
...
Visual representation
...
| Panel | ||||||||
|---|---|---|---|---|---|---|---|---|
| ||||||||
The content you are trying to reach has been moved here: https://docs.clevermaps.io/docs/metricsWe are proud to announce that we have launched a new documentation. Please update your saved links and bookmarks to follow a new address docs.clevermaps.io. |