Calculated metrics

This guide explains what calculated metrics are and how to use them in Adverity.

Introduction

Calculated metrics are metrics created based on a combination of other metrics, such as a sum or a ratio. Calculated metrics can be used in Data Explorer and Dashboards to analyze and visualize your data. Adverity displays calculated metrics in green with the sign.

Calculated metrics can be loaded into Adverity Data Storage but not external destinations.

Do not retrieve calculated metrics from data sources - this can result in incorrect data. Instead, fetch the underlying metrics from your data source and use Adverity to compute the calculated metric you need.

Viewing your calculated metrics

To view your calculated metrics, follow these steps:

  1. In the platform navigation menu, click Data Dictionary.

  2. Under the page heading, click Calculated Metrics.

The table in the middle of the page contains important information about your calculated metrics in the following columns:

  • Name - the display name of the calculated metric

  • Format - the type of data that the calculated metric represents

  • Formula - the formula used to calculate this calculated metric

  • Usable in - the number of workspaces in which this calculated metric can be used. Click the link in this column to see a list of these workspaces

Creating a calculated metric

Create calculated metrics to perform calculations on existing metrics and add the results of the calculations to a widget. The example below explains how to create a calculated metric that expresses cost per click (CPC) values. The calculated metric created in this example is identical to the default CPC calculated metric offered by Adverity.

Only users with the required permissions can create calculated metrics. For more information, see Managing user permissions.

To create a calculated metric, follow these steps:

  1. In the platform navigation menu, click Data Dictionary.

  2. Under the page heading, click Calculated Metrics.

  3. In the top right corner, click +Create calculated metric.

  4. In the Display name field, enter a name for the new calculated metric to help you identify it. This is the name that will appear in the Data Dictionary, Data Explorer and Dashboards pages in the Adverity platform.

  5. In the Select metrics to add to formula below field, select a metric to use in the formula for this calculated metric. It will appear in the Formula field below.

  6. Repeat step 5 for all metrics you want to use in this formula.

  7. In the Formula field, use mathematical operators to specify the calculation you want to perform on the metrics you have selected. Adverity will automatically check the formula and display a message stating whether the formula is valid. If the formula is not valid, this message will state where the error is in the formula.

    For help writing complex formulae, see Creating calculated metrics with complex formulae.

  8. In the Format field, select the format of your calculated metric. This depends on the value your calculated metric represents.

    • Number - the calculated metric represents a number, e.g. total likes on a social media post

    • Percent - the calculated metric represents a percent value, e.g. an interest rate

    • Duration - the calculated metric represents a period of time, e.g. the duration of a campaign

    • Currency - the calculated metric represents a monetary value, e.g. costs

  9. In the Fractional digits field, specify how many decimal places this calculated metric will include. This field is only required for the Number, Percent and Currency formats.

  10. In the Currency field, select the currency in which this calculated metric will be displayed. This option is only required for the Currency format.

  11. In the Default total measure field, specify the mathematical function Adverity uses to calculate the total row for the metric:

    • Sum - the total row will contain the sum of all values for this calculated metric (e.g. total cost per click)

    • Avg - the total row will contain the average of all values for this calculated metric (e.g. average cost per click)

    • Count - the total row will contain the number of values for this calculated metric (e.g. total number of cost per click values that have been calculated)

    • Min - the total row will contain the minimum value for calculated metric (e.g. lowest cost per click value)

    • Max - the total row will contain the maximum value for this calculated metric (e.g. highest cost per click value)

    • None - Adverity will not calculate a total row for this calculated metric

  12. Click Create.

As a result, you have created a new calculated metric. The calculated metric will appear in your Data Dictionary and you can now add it to widgets. For more information on creating widgets with calculated metrics, see Creating widgets with calculated metrics.

Creating calculated metrics with complex formulae

The example above explained how to create a calculated metric where the Formula field only contains the following basic mathematical operators:

  • addition +

  • subtraction -

  • multiplication *

  • division /

It is possible to use more complex formulae, such as conditional statements and average calculations.

Using conditional statements in formulae

Specify the condition in the Formula field in the following way:

CASE WHEN {condition} THEN {calculation1} ELSE {calculation2} END

In the code above, replace the following:

  • Replace {condition} with a conditional statement. Use basic mathematical operators and the following: OR, AND, IS NULL, NULLIF.

  • Replace {calculation1} with the value of the calculated metric if the condition is satisfied.

  • Replace {calculation2} with the value of the calculated metric if the condition is not satisfied.

For example, if you use the formula CASE WHEN (clicks)>100 THEN (costs)/(clicks) ELSE (costs)*0.01 END in the example above, Adverity computes the calculated metric in the following way:

  • If the number of clicks for a campaign is greater than 100, the CPC value is the ratio of costs and clicks.

  • If the number of clicks is less or equal to 100, the CPC value is 1% of the costs.

Using dimensions in formulae

Conditions used to compute calculated metrics can include dimensions as well as metrics.

For example, if you use the formula CASE WHEN dimensions->'week' IS NULL AND (clicks)<100 THEN 0 ELSE 1 END in the example above, Adverity computes the calculated metric in the following way:

  • If the week dimension is null and the number of clicks for a campaign is less than 100, the calculated metric value is set to 0.

  • If the week dimension is not null and/or the number of clicks is equal to or greater than 100, the calculated metric value is set to 1.

Generally, you cannot perform calculations on the values of a dimension. One exception is that you can calculate the unique values in a dimension. To do so, use the following code: COUNT(DISTINCT({dimension})), replacing {dimension} with the name of the dimension whose unique values you want to count.

For example, the code COUNT(DISTINCT({campaign_name})) calculates the number of unique campaign names.

Editing and deleting calculated metrics

Only users with the required permissions can edit and delete calculated metrics. For more information, see Managing user permissions.

Editing a calculated metric

To edit a calculated metric, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Data Dictionary.

  2. Under the page heading, click Calculated Metrics.

  3. In the row for the calculated metric you want to edit, click Edit.

  4. In the Edit calculated metric window, make the required changes. You cannot change the Technical name of a calculated metric after it has been created. You can change all other fields.

  5. Click Save.

As a result, you have edited a calculated metric.

Deleting a calculated metric

To delete a calculated metric, follow these steps:

  1. Select the workspace you work with in Adverity and then, in the platform navigation menu, click Data Dictionary.

  2. Under the page heading, click Calculated Metrics.

  3. In the row for the calculated metric you want to edit, click Delete.

  4. In the Delete window, you will see all widgets and calculated metrics in which this calculated metric is used.

    If the calculated metric is not used in any widgets or calculated metrics, you can delete the calculated metric.

    If the calculated metric is used in any widgets or calculated metrics, you must perform one of the following actions for each widget or calculated metric:

    • Remove the calculated metric from the listed widget or calculated metric

    • Delete the listed widget or calculated metric

  5. Click Delete.

As a result, you have deleted a calculated metric.

Performing calculations on data in a table in Data Explorer

To use the Formula field for a calculated metric to perform a calculation on data that is shown in a table in Data Explorer, use the following expressions:

  • To calculate the average of a metric's values, use AVG({metric-name}). Replace {metric-name} with the metric's name.

  • To calculate the sum of a metric's values, use SUM({metric-name}). Replace {metric-name} with the metric's name.

  • To compute a function differently for all distinct combinations of dimension values, use OVER (PARTITION BY 0) after the function.

  • To compute a function differently for all distinct combinations of dimension values, excluding the last dimension, use OVER (PARTITION BY 1) after the function.

  • To compute a function differently for all distinct combinations of dimension values, excluding a number of dimensions at the end of the list, use OVER (PARTITION BY {number-of-dimensions-to-exclude}) after the function. Replace {number-of-dimensions-to-exclude} with the number of dimensions to exclude.

  • To compute a function for all rows without splitting data for any dimensions, use OVER() after the function.

In this example, from left to right, the following fields are represented in a table:

  • The dimensions Datasource and Daily.

  • The metric Clicks.

  • The calculated metrics with the formulae SUM(clicks) and SUM(SUM(clicks)) OVER (PARTITION BY 0). In these columns, every row calculates the sums of clicks for each day and each data source. For example, it displays clicks on Facebook Ads for a particular day. The result is the same as the values in the Clicks column.

  • The calculated metric with the formula SUM(SUM(clicks)) OVER (PARTITION BY 1). In this column, every row calculates the sums of clicks per data source, without splitting data by day. This is why the number of clicks per day is the same within each data source.

  • The calculated metrics with the formulae SUM(SUM(clicks)) OVER (PARTITION BY 2) and SUM(SUM(clicks)) OVER (). In these columns, every row calculates the total sum of clicks, without splitting data by data source and day. This is why the number of clicks is the same for all rows.

Use the following formula to calculate the difference from the average cost:

(costs) - AVG(SUM(costs)) OVER (PARTITION BY 1)

Video guide: How to use calculated metrics in Adverity

This video guide explains what calculated metrics are and how to create and use them in the Data Dictionary and Data Explorer.