Define Metrics

Metrics provide a semantic layer that gives business-related meaning to your data. They centralize the definition of business indicators so that they can be used consistently throughout your application. There are various types of metrics — Sum, Count, Count Distinct, Min, Max, Average, or Custom (for custom expressions) — depending on how they aggregate data. Depending on your purpose, you can query metrics in Time Series, Counter, Leaderboard, or Metric Report format.

Metrics enable you to build product experiences such as dashboards, leaderboards, key performance indicators, or analytics APIs reusing common definitions. With Metrics, you can define metrics once and use their data throughout your web or mobile applications via Propel's API.

To learn more about querying Metrics, go to our Query Your Data section.

Defining Metrics

A Metric is defined by the Data Pool that powers it, its type, its Metric Filters, and an optional set of Dimensions that can serve as filters at query time. You can define Metrics via the Console, API, or Terraform.

Example: Revenue metric

The following example demonstrates how to create a Sum Metric using the Console to define a “Revenue” metric by summing the values in the “total_price” column in the TacoSoft sample dataset.

A screen capture demonstrating how to define a Metric.

The following example shows the query and input variables for the createSumMetric API mutation to define a “Revenue” metric by summing the values in the “total_price” column in the TacoSoft sample dataset.

Query

mutation CreateSumMetric($input: CreateSumMetricInput) {
  createSumMetric(input: $input) {
    metric {
      id
      uniqueName
    }
  }
}

Variables

{
  "input": {
    "dataPool": "DPOXXX",
    "uniqueName": "Revenue",
    "filters": [
      {
        "column": "ORDER_ID",
        "operator": "IS_NOT_NULL"
      }
    ],
        "dimensions": [
      {
        "columnName": "Restaurant"
      }
    ]
    "measure": "total_price"
  }
}

Data Pool

The Data Pool provides the necessary data for the Metric and ensures that it is served in a performant way, allowing for responsive and interactive product experiences.

The Data Pool's primary timestamp column serves as the Metric's time dimension.

Type

Each type of metric performs a different data aggregation. Below is a list of all the available metric types:

Type	Description	Example
Count	Counts the number of records that match the Metric Filters. For Time Series queries, it will count the values for each time granularity.	Order count
Count Distinct	Counts the number of distinct values in the specified column for every record that matches the Metric Filters. For Time Series queries, it will count the distinct values for each time granularity.	Unique customers
Max	Selects the maximum value of the specified column for every record that matches the Metric Filters. For Time Series queries, it will select the maximum value for each time granularity.	Maximum order size in dollars
Min	Selects the minimum value of the specified column for every record that matches the Metric Filters. For Time Series queries, it will select the minimum value for each time granularity.	Minimum order size in dollars
Average	Averages the values of the specified column for every record that matches the Metric Filters. For Time Series queries, it will average the values for each time granularity.	Average revenue per order
Custom (See Defining Custom Metrics section)	Applies a custom expression to the values of the specified columns for every record that matches the Metric Filters. For Time Series queries, it will apply the custom expression to the values for each time granularity.	Total price defined as: `SUM(unit_price) * SUM(quantity)`

Dimensions

Dimensions are columns used to categorize and segment the Metric data. For example, if you have a revenue metric and want to see revenue by country, product, or salesperson, then country, product, and salesperson would be Dimensions in the revenue Metric definition.

Do not expose as Dimensions columns that are internal and not meant to be used in customer-facing applications.

Metric Filters

Metric Filters enable you to define a Metric with a subset of records from the given Data Pool. If no Filters are present, Propel will include all the Data Pool records in the Metric calculations.

Suppose you need to define a revenue Metric that does not include promotional (PROMO) transactions. In that case, you can create a Metric Filter and exclude transactions with type equals "PROMO" in the Metric definition instead of remembering to filter them every time you query the revenue Metric.

Defining Metrics with JSON fields

You can define Metrics using nested values in JSON column. You can use JSON values in the Metric definition, both as a measure or as Metric Filters. You can access structured JSON data within a column using JavaScript dot and bracket notation.

The following example demonstrates how to create a Sum Metric in the Console using a measure from a JSON column.

A screen capture demonstrating how to define a Metric with a JSON colum.

The following example shows the query and input variables for the createSumMetric API mutation to define a “Revenue” metric by summing the values inside a JSON column under the path: “OBJECT.order.total_price”.

Query

mutation CreateSumMetric($input: CreateSumMetricInput) {
  createSumMetric(input: $input) {
    metric {
      id
      uniqueName
    }
  }
}

Variables

{
  "input": {
    "dataPool": "DPOXXX",
    "uniqueName": "Revenue",
    "filters": [
      {
        "column": "ORDER_ID",
        "operator": "IS_NOT_NULL"
      }
    ],
        "dimensions": [
      {
        "columnName": "Restaurant"
      }
    ]
    "measure": "OBJECT.order.total_price"
  }
}

Defining Custom Metrics

Custom Metrics enable you to define custom expressions to aggregate data from your Data Pool. This provides a more flexible approach to defining Metrics that capture more complex business logic.

Example: Average revenue per order

The following example demonstrates how to create a Custom Metric using the Console to define an “Average revenue per order” metric with a custom expression.

A screen capture demonstrating how to define a Custom Metric.

The following example shows the query and input variables for the createCustomMetric API mutation to define an “Average revenue per order” metric with a custom expression.

Query

mutation CreateCustomMetric($input: CreateCustomMetricInput) {
  createCustomMetric(input: $input) {
    metric {
      id
      uniqueName
    }
  }
}

Variables

{
  "input": {
    "dataPool": "DPOXXX",
    "uniqueName": "Average revenue per order",
    "filters": [
      {
        "column": "ORDER_ID",
        "operator": "IS_NOT_NULL"
      }
    ],
        "dimensions": [
      {
        "columnName": "Restaurant"
      }
    ]
    "expression": "SUM(total_price) / COUNT_DISTINCT(ORDER_ID)"
  }
}

Supported functions

Expressions support the following functions.

Aggregate functions

The following functions aggregate a group of values:

Function	Description	Arguments
COUNT()	The number of records in the group.	It takes no arguments.
COUNT_IF(EXPRESSION)	The number of records in the group that satisfy the expression.	A boolean expression.
COUNT_DISTINCT(COLUMN_NAME)	The number of distinct records in the group.	A column name.
SUM(COLUMN_NAME)	The sum of the values in the group.	A column name.
SUM_IF(COLUMN_NAME, EXPRESSION)	The sum of the values in the group that satisfy the expression.	A column name and boolean expression.
AVG(COLUMN_NAME)	The average of the values in the group.	A column name.
AVG_IF(COLUMN_NAME, EXPRESSION)	The average of the values in the group that satisfy the expression.	A column name and boolean expression.
MIN(COLUMN_NAME)	The minimum value in the group.	A column name.
MAX(COLUMN_NAME)	The maximum value in the group.	A column name.
ANY(COLUMN_NAME)	Selects the first encountered non-NULL value in the group, unless all rows have NULL values in that group.	A column name.
PERCENTILE(0.95, COLUMN_NAME)	The percentile value within the group.	The percentile, a number between 0 and 1, and a column name.
FIRST(COLUMN_NAME)	The first, oldest non-null value in the group.	A column name.
LAST(COLUMN_NAME)	The last, most recent non-null value in the group.	A column name.

Date and time functions

All functions below round down a date with time and take a timestamp as an argument:

Function	Description	Arguments
DATE_DIFF(UNIT, DATE_1, DATE_2, TIMEZONE)	Calculates the difference between two DATE or TIMESTAMP columns.	A time unit, the first DATE or TIMESTAMP, the second DATE or TIMESTAMP, and an optional timezone.
TIMESTAMP_DIFF(UNIT, TIMESTAMP_1, TIMESTAMP_2, TIMEZONE)	Calculates the difference between two DATE or TIMESTAMP columns.	A time unit, the first TIMESTAMP or TIMESTAMP, the second TIMESTAMP or TIMESTAMP, and an optional timezone.
DATE_TRUNC(UNIT, TIMESTAMP, TIMEZONE)	Truncates a DATE or TIMESTAMP value.	A time unit, the DATE or TIMESTAMP, and an optional timezone.
TO_UNIX_TIMESTAMP(DATE/TIMESTAMP[, TIMEZONE])	Converts a DATE or TIMESTAMP value to a Unix timestamp, in seconds. The timezone is optional and defaults to "UTC".	A DATE or TIMESTAMP value, and an optional timezone.
TO_TIMESTAMP(STRING, FORMAT[, TIMEZONE])	Converts a STRING to a TIMESTAMP according to a MySQL format string. The timezone is optional and defaults to "UTC".	A STRING to convert, a MySQL format string, and an optional timezone.
TO_START_OF_MINUTE(COLUMN)	Rounds down to the start of the minute.	A column name.
TO_START_OF_FIVE_MINUTES(COLUMN)	Rounds down to the start of the five-minute interval.	A column name.
TO_START_OF_TEN_MINUTES(COLUMN)	Rounds down to the start of the ten-minute interval.	A column name.
TO_START_OF_FIFTEEN_MINUTES(COLUMN)	Rounds down to the start of the fifteen-minute interval.	A column name.
TO_START_OF_HOUR(COLUMN)	Rounds down to the start of the hour.	A column name.
TO_START_OF_DAY(COLUMN)	Rounds down to the start of the day.	A column name.
TO_START_OF_WEEK(COLUMN)	Rounds down to the start of the week.	A column name.
TO_START_OF_MONTH(COLUMN)	Rounds down to the start of the month.	A column name.
TO_START_OF_YEAR(COLUMN)	Rounds down to the start of the year.	A column name.

Supported time units:

millisecond
second
minute
hour
day
week
month
quarter
year

Arithmetic and logical functions

Function	Description	Arguments
IS_NULL	Returns a boolean value indicating if the value is NULL or not.	A column name.
ADD	Calculates the sum of two or more values. To sum the values in the same column, use the SUM function.	It takes two or more columns or values as arguments.
SUBTRACT	Calculates the difference between two or more values. It takes columns and values as arguments.	It takes two or more columns or values as arguments.
MULTIPLY	Calculates the product of two or more values. It takes columns and values as arguments.	It takes two or more columns or values as arguments.
DIVIDE	Calculates the quotient of two or more values. It takes columns and values as arguments.	It takes two or more columns or values as arguments.
FLOOR(NUMBER)	Returns the largest round number that is less than or equal to the provided value.	A number.
CEIL(NUMBER)	Returns the largest round number that is greater than or equal to the provided value.	A number.

Other functions

Function	Description	Arguments
CONCAT(STRING1, STRING2, ...)	Concatenates strings listed in the arguments without any separator.	Two or more strings to concatenate.
CONCAT_WITH_SEPARATOR(SEPARATOR, STRING1, STRING2, ...)	Concatenates the given strings with a given separator.	A separator string followed by two or more strings to concatenate.
CAST(EXPRESSION AS TYPE)	Converts a value from one type to another using PostgreSQL CAST syntax. For example, `CAST('2023-12-17' AS DATE)` converts a string to a date.	The expression to convert, and the target data type.

Supported arithmetic operators

Expressions support the following arithmetic operators: /, *, +, and -. The operators are evaluated in the standard mathematical order of precedence: /, *, +, and lastly -.

Both functions and arithmetic operators respect precedence defined by parenthesis over default operator precedence.

Custom expressions with JSON columns

Custom expressions can be defined with values inside JSON columns. To access structured JSON data within a column, you can use JavaScript dot and bracket notation to reference the values.

"expression": "SUM(OBJECT.order.total_price) / COUNT_DISTINCT(OBJECT.order.id)"

Examples

The following examples demonstrate how to define expressions to define Custom Metrics of business interest. All examples use the TacoSoft dataset that you can find in our Quickstart.

Average revenue per order

Calculates the average revenue per order.

SUM(total_price) / COUNT_DISTINCT(ORDER_ID)

Median order revenue

The median order revenue. The median is the same as the 50% percentile.

PERCENTILE(0.50, total_price)

Total price

A multiplication of unit price x quantity

SUM(UNIT_PRICE) * SUM(QUANTITY)

Average items per order

The COUNT() counts the number of order items and divides them by the count of distinct order IDs.

COUNT() / COUNT_DISTINCT(ORDER_ID)

Unit price in cents

The unit price is stored in dollars. This expression converts it to cents.

SUM(UNIT_PRICE) / 100

SUM(UNIT_PRICE/100)

Both syntaxes are valid.

Define Metrics

Defining Metrics​

Example: Revenue metric​

Data Pool​

Type​

Dimensions​

Metric Filters​

Defining Metrics with JSON fields​

Defining Custom Metrics​

Example: Average revenue per order​

Supported functions​

Aggregate functions​

Date and time functions​

Arithmetic and logical functions​

Other functions​

Supported arithmetic operators​

Custom expressions with JSON columns​

Examples​

Defining Metrics

Example: Revenue metric

Data Pool

Type

Dimensions

Metric Filters

Defining Metrics with JSON fields

Defining Custom Metrics

Example: Average revenue per order

Supported functions

Aggregate functions

Date and time functions

Arithmetic and logical functions

Other functions

Supported arithmetic operators

Custom expressions with JSON columns

Examples