Using Calculated KPIs and adding them to widgets

Using Calculated KPIs and adding them to widgets#

This guide explains what Calculated KPIs are, how to manage your Calculated KPIs, and how to add them to widgets in the Explore page.

Introduction#

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

Calculated KPIs can be loaded into Dashboards but not external destinations.

For guidance on standard marketing KPI definitions and how to operationalize them in Explore and Present, see Marketing KPI best practices.

Warning

Do not retrieve Calculated KPIs 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 KPI you need.

Creating Calculated KPIs#

Create Calculated KPIs 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 KPI that expresses cost per click (CPC) values. The Calculated KPI created in this example is identical to the default CPC Calculated KPI offered by Adverity.

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

To create a Calculated KPI from the administration, follow these steps:

Go to the Explore page.
In the top left corner, click Select workspace, then Administration.
In the Explore section of the administration menu, click KPIs and dimensions.
In the top right corner, click Create calculated KPI.

Or, to create a Calculated KPI from Explore, follow these steps:

Go to the Explore page.
Next to the View tabs at the top of the page, click Create new view.
In the Metrics section of the view, click + Create new calculated KPI. This opens the Create a new calculated KPI screen.

To configure the new Calculated KPI, follow these steps:

In the Define KPI formula section, configure the following fields:

Select metrics to add to formula below
In the drop-down list, select a metric to use in your formula, then click + Add metric. Repeat this step for all metrics you want to use in the formula.

Formula
Use mathematical operators to specify the calculation to perform using the metrics or previously created Calculated KPIs you have selected. For example, use / to add a division sign between two metrics: (costs) / (clicks).

Use Ctrl+Space to see suggestions for the formula.

Calculated formula
This field shows the full formula you have created and whether or not it is valid. If the formula is not valid, this field tells you where the syntax error is.
In the Define calculated KPI section, configure the following fields:

Calculated KPI name
Enter a name for the new Calculated KPI to help you identify it. This is the name that is used for this Calculated KPI in the Adverity platform.

Format
If your Calculated KPI represents a percentage value, currency, or duration, select the corresponding format option.

Visibilities
Select the workspaces that can access the new Calculated KPI.
Click Create calculated KPI.

As a result, you have created a new Calculated KPI. You can now add this Calculated KPI to widgets.

Adding a Calculated KPI to a widget#

To add a Calculated KPI to a widget in the Explore page, follow these steps:

Go to the Explore page.
Next to the View tabs at the top of the page, click Create new view.
From the Dimensions & Metrics list on the left, select the Calculated KPI that you want to add to your widget. Calculated KPIs are displayed in green with the sign.

Warning

Calculated KPIs appear in the Dimensions & Metrics list for all data sources, but the formula will only provide a correct result if the data you have fetched for the selected data source contains values for the metrics used in the calculation.

For example, if you collect Costs and Clicks data, add these metrics to your widget, and add the (costs) / (clicks) Calculated KPI to the widget, this Calculated KPI will provide a correct result in the data table.

If you collect Costs and Impressions data, add these metrics to your widget, and add the (costs) / (clicks) Calculated KPI to the widget, this Calculated KPI will not provide a correct calculation result for this data.

As a result, you have added the selected Calculated KPI to your widget. You can now continue adding other dimensions and metrics to your widget as normal.

Editing and deleting a Calculated KPI#

To edit or delete an existing Calculated KPI, follow these steps:

Go to the Explore page.
In the top left corner, click Select workspace, then Administration.
In the Explore section of the administration menu, click KPIs and dimensions.
In the top right corner, click Create calculated KPI.
At the bottom of the screen, click Open calculated KPI.
Select the Calculated KPI you want to edit and click Apply.
Perform any of the following actions:
- To edit the selected Calculated KPI, make any changes to the fields in the Define KPI formula and Define calculated KPI sections. Then click Update calculated KPI.
- To delete the selected Calculated KPI, at the bottom of the screen, click Delete.

As a result, you have edited or deleted the selected Calculated KPI.

Viewing your Calculated KPIs#

To view your Calculated KPIs, follow these steps:

Go to the Explore page.
In the top left corner, click Select workspace, then Administration.
In the Explore section of the administration menu, click KPIs and dimensions.
At the top of the page, select the Calculated KPIs tab.

For more information, see Viewing and configuring dimensions, metrics and calculated KPIs.

Creating Calculated KPIs with complex formulas#

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

addition +
subtraction -
multiplication *
division /

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

Using conditional statements in formulas#

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. You can use the following in a condition:
- Comparison operators: =, !=, <>, <, <=, >, >=
- Arithmetic operators: +, -, *, /. For example, (clicks) * 5 > 100.
- Ranges and lists: BETWEEN ... AND ..., IN (...), NOT IN (...)
- Null checks: IS NULL, IS NOT NULL, NULLIF
- Text patterns: LIKE, NOT LIKE, ILIKE, NOT ILIKE. For more information, see Matching dimension values against a pattern.
- Logical operators: AND, OR, NOT
Replace {calculation1} with the value of the Calculated KPI if the condition is satisfied.
Replace {calculation2} with the value of the Calculated KPI if the condition is not satisfied.

For example, use the following formula in the example above:

CASE WHEN (clicks) > 100 THEN (costs) / (clicks) ELSE (costs) * 0.01 END

Adverity computes the Calculated KPI 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.

To check several conditions in turn, add more WHEN ... THEN ... clauses. Adverity uses the value of the first condition that is satisfied:

CASE
  WHEN (clicks) > 1000 THEN 3
  WHEN (clicks) > 100 THEN 2
  ELSE 1
END

If a condition uses a dimension or the date of a row, additional rules apply. Some of these conditions must be placed inside an aggregate function. For more information, see Using dimensions in formulas.

Using dimensions in formulas#

Conditions used to compute Calculated KPIs can include dimensions as well as metrics. Use this to count only the rows that match a specific campaign, data source, country, or date range, without creating a new field in a transformation and fetching your data again.

Write dimension names without brackets, for example campaign_name. Write metric names in brackets, for example (costs).

Important

If your formula uses a dimension in a condition, add that dimension to the widget in Explore. Otherwise, the Calculated KPI column can be empty. The column is also empty if you limit the number of rows in the widget with the Limit rows option, even if the dimension is in the widget.

For more information, see Showing calculated KPIs that use dimensions in widgets. For examples, see Examples of calculated KPIs with dimensions.

For example, use the following formula in the example above:

CASE WHEN week IS NULL AND (clicks) < 100 THEN 0 ELSE 1 END

Adverity computes the Calculated KPI in the following way:

If the week dimension is null and the number of clicks for a campaign is less than 100, the Calculated KPI 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 KPI 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.

Use the following formula to calculate the number of unique campaign names:

COUNT(DISTINCT(campaign_name))

Comparing dimension values with text#

To compare a dimension with a text value, enclose the text value in single quotes. The comparison is case-sensitive.

Use the following formula to calculate the clicks from Google Ads only:

CASE WHEN datasource = 'Google Ads' THEN (clicks) ELSE 0 END

Use the following formula to calculate the costs of all campaigns except the campaign named Brand:

CASE WHEN campaign_name != 'Brand' THEN (costs) ELSE 0 END

Use the following formula to calculate the costs in three countries:

CASE WHEN country IN ('AT', 'DE', 'CH') THEN (costs) ELSE 0 END

Matching dimension values against a pattern#

To match a dimension against a pattern instead of an exact value, use LIKE. This is useful when you want to combine many values that follow a naming convention, such as campaign or conversion names, into one Calculated KPI without listing each value.

In the pattern, use the following wildcards:

% matches any number of characters, including none. For example, '%brand%' matches values that contain brand, and 'brand%' matches values that start with brand.
_ matches exactly one character. For example, 'g__gle' matches google but not gogle.

LIKE is case-sensitive. To ignore upper and lower case, use ILIKE. To match the values that do not fit the pattern, use NOT LIKE or NOT ILIKE.

Use the following formula to calculate the conversions whose name contains purchase or checkout:

CASE
  WHEN conversion_name LIKE '%purchase%'
    OR conversion_name LIKE '%checkout%'
  THEN (conversions)
  ELSE 0
END

Use the following formula to calculate the costs of campaigns whose name contains black friday in any combination of upper and lower case:

CASE WHEN campaign_name ILIKE '%black friday%' THEN (costs) ELSE 0 END

Use the following formula to calculate the costs of campaigns whose name does not start with Brand:

CASE WHEN campaign_name NOT LIKE 'Brand%' THEN (costs) ELSE 0 END

Rows that have no value for the dimension never match a pattern, with or without NOT. The same applies to comparisons such as !=. For example, the formula above does not include the costs of rows that have no campaign name. To include these rows, add a condition with IS NULL:

CASE
  WHEN campaign_name NOT LIKE 'Brand%' OR campaign_name IS NULL
  THEN (costs)
  ELSE 0
END

Excluding values with NOT#

To reverse a condition, add NOT in front of it. NOT applies only to the condition directly after it. To reverse several conditions together, enclose them in brackets.

Use the following formula to calculate the costs in all countries except the US:

CASE WHEN NOT country = 'US' THEN (costs) ELSE 0 END

Use the following formula to calculate all costs except the costs of Google Ads brand campaigns:

CASE
  WHEN NOT (datasource = 'Google Ads' AND campaign_name LIKE '%brand%')
  THEN (costs)
  ELSE 0
END

Rows that have no value for a dimension in the condition are not included, with or without NOT. For example, the first formula above does not include the costs of rows that have no country. To include these rows, add a condition with IS NULL, for example NOT country = 'US' OR country IS NULL.

Returning dimension values from a condition#

In a CASE statement, every THEN and ELSE part must return the same type of value. If one part returns a dimension or a text value in single quotes, and another part returns a number or a metric, the formula editor shows the error String and numeric values cannot be mixed in CASE results. For example, the formula editor rejects the following formula:

CASE WHEN product_category LIKE '%DIY%' THEN campaign_name ELSE 0 END

You can only use a CASE statement that returns a dimension inside COUNT. You cannot use it in calculations or in other aggregate functions. Use the following formula to count the rows of data whose product category contains DIY and that have a campaign name:

COUNT(CASE WHEN product_category LIKE '%DIY%' THEN campaign_name END)

Limiting a Calculated KPI to a date range#

To use the date of each row in a condition, use ROW_DATE. Enter the date in single quotes in the format YYYY-MM-DD. You can compare ROW_DATE with the comparison operators and BETWEEN, which includes both dates.

Place the whole condition inside an aggregate function: SUM, AVG, MIN, MAX, COUNT, or STDDEV.

Use the following formula to calculate the costs from 11 May 2026 to 21 June 2026:

SUM(
  CASE
    WHEN ROW_DATE BETWEEN '2026-05-11' AND '2026-06-21'
    THEN (costs)
    ELSE 0
  END
)

Use the following formula to calculate the clicks of the Summer sale campaign from 11 May 2026 onwards:

SUM(
  CASE
    WHEN ROW_DATE >= '2026-05-11' AND campaign_name = 'Summer sale'
    THEN (clicks)
    ELSE 0
  END
)

The following limitations apply to ROW_DATE:

ROW_DATE only works inside an aggregate function. ROW_DATE contains the date of a single row of data, so Adverity must read it before the rows are combined. If you use ROW_DATE outside an aggregate function, the formula editor shows the error ROW_DATE can only be used inside an aggregate function. For example, the formula editor rejects the following formula:
```
CASE WHEN ROW_DATE >= '2026-05-11' THEN (costs) ELSE 0 END
```
A window function does not count as an aggregate function for this rule. For example, the formula editor rejects the following formula:
```
SUM(CASE WHEN ROW_DATE >= '2026-05-11' THEN (costs) ELSE 0 END)
  OVER (PARTITION BY 1)
```
You cannot place the aggregate function inside another aggregate function, because databases do not allow nested aggregate functions. For example, the following formula is not valid, and the formula editor shows an error from the database:
```
MAX(SUM(CASE WHEN ROW_DATE >= '2026-05-11' THEN (costs) ELSE 0 END))
```
You can only compare ROW_DATE with a date in single quotes in the format YYYY-MM-DD. If you compare ROW_DATE with a value that is not in single quotes, such as a number or a metric, the formula editor shows the error ROW_DATE can only be compared to a date literal. If the date is in a different format, for example '11-05-2026', or does not exist, for example '2026-02-30', the formula editor shows the error Invalid date literal.
You can only compare ROW_DATE with dates. You cannot use it in calculations, in functions, or with LIKE. For example, you cannot calculate the number of days between two dates.
ROW_DATE only limits the data within the date range of the widget. If the dates in your formula are outside the date range of the widget, the Calculated KPI does not include any data.
In a table, the total row is empty for a Calculated KPI that uses ROW_DATE.

Showing Calculated KPIs that use dimensions in widgets#

Add every dimension that your formula uses in a condition to the widget, as described at the start of Using dimensions in formulas. For more information about the Limit rows option, see Configuring a data table.

Place the whole condition inside an aggregate function, such as SUM, so that Adverity also calculates the total row of the table. This does not apply to formulas that use ROW_DATE: their total row is always empty. For example, in a widget that contains the Datasource dimension, the total row is empty for the following formula, unless you filter the widget to a single data source:

CASE WHEN datasource = 'Google Ads' THEN (clicks) ELSE 0 END

The following formula calculates the same values and also shows the correct total row:

SUM(CASE WHEN datasource = 'Google Ads' THEN (clicks) ELSE 0 END)

Note

The following limitations apply to dimensions in formulas:

You cannot change text values. For example, you cannot use functions such as LOWER, CONCAT, or SUBSTRING, or regular expressions.
You cannot match the % or _ characters themselves with LIKE or ILIKE.
You can only use LIKE and ILIKE with a dimension and a text pattern in single quotes, not with a metric.

Examples of Calculated KPIs with dimensions#

The following examples show how Adverity calculates a Calculated KPI that uses dimensions. Each picture shows the widget in Explore on the left, and the rows of data that its values come from on the right. The green arrows show which rows count towards each value. The blue arrow shows that the dimension used in the formula is also in the widget.

Use the following formula to calculate the costs of campaigns whose name contains brand in any combination of upper and lower case. Add the Datasource and Campaign name dimensions to the widget:

SUM(CASE WHEN campaign_name ILIKE '%brand%' THEN (costs) ELSE 0 END)

Use the following formula to calculate the clicks from 27 to 30 November 2026 in countries other than the US. Add the Datasource and Country dimensions to the widget:

SUM(
  CASE
    WHEN ROW_DATE BETWEEN '2026-11-27' AND '2026-11-30'
      AND NOT country = 'US'
    THEN (clicks)
    ELSE 0
  END
)

Performing calculations on data in a table in Explore#

To use the Formula field for a Calculated KPI to perform a calculation on data that is shown in a table in Explore, use the following expressions.

Several of these expressions use OVER (PARTITION BY N) to control the scope of the aggregation: the higher the value of N, the fewer dimensions are included in the grouping. Use this to show subtotals or a grand total alongside your detailed row data — for example, to calculate what percentage of total clicks came from a particular data source on a given day.

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 group by all dimensions — producing the same granularity as the raw metric — add OVER (PARTITION BY 0) after the function.
To exclude the last dimension from the grouping — producing one total per unique combination of the remaining dimensions, repeated on every row that shares that combination — add OVER (PARTITION BY 1) after the function.
To exclude a number of trailing dimensions from the grouping, add OVER (PARTITION BY {N}) after the function. Replace {N} with the number of dimensions to exclude from the end of the list.
To include no dimensions in the grouping — producing a single grand total repeated on every row — add OVER() after the function.

Hint

The example below shows a table with two dimensions (Datasource and Daily) and a Clicks metric. Each Calculated KPI column demonstrates a different aggregation scope:

SUM(clicks) and SUM(SUM(clicks)) OVER (PARTITION BY 0) — Groups by both dimensions. Produces the same value as the raw Clicks metric (for example, 133 for Facebook Ads on a specific day).
SUM(SUM(clicks)) OVER (PARTITION BY 1) — Groups by Datasource only (Daily is excluded). Every row within a data source shows the same total: 553 for Facebook Ads across all days, 271 for LinkedIn.
SUM(SUM(clicks)) OVER (PARTITION BY 2) and SUM(SUM(clicks)) OVER () — Groups by no dimensions. Every row shows the same grand total: 824.