Creating a Composite (Formula) Metric (supported in v6.x and earlier)

When sourcing Metric data from other existing Elements it is essential to decide which type of Element to use (either Reports or Metrics) since both element types cannot be used as Data Sources for a single Metric. This article describes how to create a Metric that uses another Metric as a source.

The syntax required to construct the 'Metric Formula' sourced from other Metrics is simpler than the command required to use data from one or more Reports since the fetch command requires only using the source Metric’s <element_id> preceded by a colon “:”. This feature also gives you the ability to define a Composite (Formula) Metric to create a calculated metric by combining two or more Metrics arithmetically. You can compute the value of this new Metric using the Element ID's and a mathematical symbol rather than having to construct a SQL Statement.

In the example covered in this article we describe how we can use data from 'Daily Sales' and 'Daily Units Sold' Metrics to calculate data for 'Total Daily Sales per Unit' Metric.

To obtain data from a Report instead of a Metric, see Source a Metric from an Existing Report

1. Start Creating a New Metric

Access +New > Metric > Create New

  1. Measured: Select the measurement interval that applies to the level of aggregation that you want in your result set
  2. Name: Give the Metric a descriptive name
  3. Category: Select the Category to place the Metric
  4. [Next: Define Details] to open the Editor

2. Complete the Data Tab

  1. Data Source: Select "Existing Metrics" option
  2. Data Collection Trigger: Choose the frequency of data collecting
  3. Metric Formula: Add the formula for the resulting Metric using Metric IDs and mathematical symbols.
    • If you do not know the Element ID of the Metric, click Find Source Metrics. Select a Category and the Metric, and click [Add to Formula]

3. Adding Element ID to the Metric Formula

Notice that the Element ID of the first selected Metric is placed in the Metric Formula text box and is preceded by a colon (:). Additionally, each time you enter or select a source Metric, its Element ID and Name appear in the hint's text box as shown below above.

Repeat the processes in the Step 2 to select another Metric or enter a Metric's Element ID and the mathematical operation symbol of the calculation that is required.

3.1. Alternative Methods of Finding Element ID

When element Viewer or Editor is opened Element ID is always specified its URL as the number preceded by the word "element". All Element IDs are also listed on the Elements page (access Content > Elements).

4. Finalize the Formula

  1. After you add a second source Metric's Element ID, complete your formula by defining the method of calculating your new metric
  2. [Validate]
  3. Change the default values if needed
  4. [Validate]

NOTE: You can combine more than two source Metrics if you like.

The IFNULL function provides a way to gracefully handle missing Metric values. For instance, the formula above could be modified  to read " :1 / IFNULL( :5, 1 ) ".  If the value for Metric #5 is missing for any particular calculation, Metric Insights will insert a value of '1' in its place.

5. Collect Data

Review validation results and click Collect Data.

[Enable&Publish] afterwards.