Calculated fields

Two places to create them

Data source level — available to every report using that source, and reusable. This is where most calculated fields belong.

Chart level — created inside one chart, invisible elsewhere. Useful for a genuine one-off, but a common source of the same calculation existing in five slightly different versions.

Basic formulas

Revenue / Sessions
Clicks / Impressions
CONCAT(First Name, ' ', Last Name)

Field names go in as written. The editor validates as you type and tells you what is wrong, which is more helpful than most formula editors.

CASE

CASE
  WHEN Revenue > 1000 THEN 'High'
  WHEN Revenue > 100  THEN 'Medium'
  ELSE 'Low'
END

Conditions are evaluated in order and the first match wins, so order matters. This is how you build any grouping or banding.

A common use is tidying source data:

CASE
  WHEN REGEXP_MATCH(Source, '.*google.*') THEN 'Google'
  WHEN REGEXP_MATCH(Source, '.*facebook.*') THEN 'Facebook'
  ELSE 'Other'
END

Useful functions

Function	Does
`IFNULL(x, y)`	Substitute for missing values
`NARY_MAX` / `NARY_MIN`	Largest or smallest of several fields
`REGEXP_EXTRACT`	Pull part of a string out
`REGEXP_MATCH`	Boolean pattern test
`TODATE`	Convert and reformat a date
`COUNT_DISTINCT`	Unique count

Aggregation in calculated fields

Ratio of sums, not sum of ratios

SUM(Revenue) / SUM(Sessions) is not the same as SUM(Revenue / Sessions).

The first divides the totals — correct. The second averages per-row ratios, weighting a one-session row equally with a thousand-session row.

When a rate looks wrong, this is usually why.