Custom Metric Formula Syntax

Custom metrics (also called calculated metrics) let you build a new metric from metrics you already have — for example, dividing clicks by impressions to get click-through rate. This page is the complete reference for what you can and can't put in a formula.

Formulas are intentionally simple: basic arithmetic on your existing metrics. There are no functions and no conditional logic. If you keep to the rules below, your formula will validate the first time.

What you can use in a formula

A formula is built from three kinds of building blocks:

• Metric fields — the metrics you already report on (clicks, cost, impressions, conversions, and so on). You add these using the metric picker in the formula editor rather than typing their names. Click where you want the field, choose it from the picker, and it's inserted for you.
• Numbers — plain numeric values you type directly, such as 1000 or 0.5.
• Built-in constants — four special values the platform provides:
  • NUM_ROWS — the number of rows in the current data
  • NUM_ACCOUNTS — the number of accounts in the current data
  • DAYS_IN_RANGE — the number of days in the selected date range
  • MONTHS_IN_RANGE — the number of months in the selected date range

Note: Only metrics can be used in a formula. Dimensions (such as campaign name, date, or channel) cannot be used as part of the math.

Supported operators

These are the only operators a formula supports:

That's the complete list. Anything not shown here is not supported and will cause the formula to fail validation (see What's not supported).

Automatic handling you don't have to worry about

Two things are taken care of for you automatically — you don't need to write anything to handle them:

• Empty values are treated as zero. If a metric has no value for a given row, it's counted as 0 in your formula.
• Dividing by zero is safe. If the bottom of a division works out to zero, the formula returns 0 instead of an error. You do not need to add any special protection around denominators — just write the division normally.

Limits

There are effectively no size limits on a formula:

• No limit on formula length
• No limit on how deeply you can nest parentheses (they just need to balance)
• No limit on how many metric fields you can reference

Examples

Each example below is written in plain terms. In the editor, insert each metric using the metric picker; the math (the operators and parentheses) is what you type.

The blended example shows how parentheses let you add two costs together and two click counts together before dividing.

What's not supported

To keep formulas reliable, the following are not available. Using any of them will cause a validation error.

• Functions of any kind. There are no formula functions. Things like IF, CASE/WHEN, COALESCE, IFNULL, NULLIF, ABS, ROUND, SUM, GREATEST, LEAST, and DATEDIFF are not available in a formula.
• Conditional / "bucketing" logic. You cannot build if/then rules inside a formula (for example, "if spend is over X, label it High"). Formulas produce a single calculated number — they can't branch based on a condition.
• The modulo operator (%).
• Comparison operators — =, <, >, <=, >=, !=.
• Logical operators — AND, OR, NOT.
• Joining text together (string concatenation).
• Dimensions. Only metrics can be used as building blocks.

Troubleshooting

"Formula is invalid"

This means the formula couldn't be understood. The usual causes are:

• An unsupported operator or symbol (only +, -, *, /, and parentheses are allowed)
• A function or keyword (functions and conditional logic aren't supported — see above)
• A field that can't be found
• Parentheses that don't balance (every ( needs a matching ))

How to fix it: Use only +, -, *, /, and parentheses; insert every field from the metric picker instead of typing it; and make sure your parentheses are balanced.

"This field is not available in the widget's current data source mapping"

The formula itself is fine, but a metric it references no longer matches the widget's current data source or mapping (this can happen after the data source or mapping is changed). How to fix it: Re-open the metric and re-pick the affected field from the picker so it points at the current data source.