Before you start
- A data source you can edit, built as the data sources guide describes, with field types already corrected. Calculated fields inherit type problems from their inputs.
- The definitions you intend to implement, written down. A formula for cost per lead is only correct relative to a definition of lead; the metrics layer is where that definition should live.
- One figure you trust for one campaign, one day and one metric, taken from the platform's own interface. The last step compares the field's output against it.
- The function reference open. Looker Studio's help centre lists every function with its signature; this tutorial names the ones marketers use most and does not reproduce the full list.
- A report with a table chart on a test page, so each new field can be dropped in and inspected row by row before it reaches a scorecard.
Decide where to create the field
There are two homes for a calculated field. A data source field is created in the source editor, exists for every report that uses the source, and can be used in filters, controls and other calculated fields. A chart-specific field is created from a chart's data panel, exists only in that chart, and can be made by a report editor who has no edit rights on the source. The default should be the source: a KPIA metric tied directly to an objective. defined once is a definition; the same formula typed into nine charts is nine chances to drift. Chart-level fields are the right choice for a presentational tweak, for a quick experiment, and for blended data, where source fields do not exist and only chart-level formulas are possible, as the blending tutorial explains.
Start with arithmetic
The simplest fields combine existing fields with the usual operators and parentheses: revenue minus cost for gross return, clicks plus calls for total contacts, cost divided by a thousand for a per-mille figure. Write them against aggregated inputs so the result is a metric that re-aggregates correctly at any grain, as in a gross return field defined as summed revenue minus summed cost. Keep the units consistent: do not add a currency to a count, and check that two currency fields share a currency before combining them, because Looker Studio will happily add pounds to dollars. Test every new field in a table before it goes near a scorecard; a formula that returns the wrong magnitude is obvious in rows and invisible in a single large number.
Write ratios as a sum divided by a sum
Every efficiency metric — ROASRevenue generated per unit of ad spend., CPACost of each conversion., CTRShare of impressions that result in a click., conversion rate, cost per lead — is a ratio, and every ratio must be written as the aggregated numerator divided by the aggregated denominator:
SUM(Revenue) / SUM(Cost)This recomputes the ratio from totals whatever the chart groups by, so the campaign row, the monthly row and the grand total each show the figure for their own scope. The wrong pattern divides row by row and then lets the chart average the result, which weights a tiny campaign with a flattering ratio the same as a large one. The ROAS glossary entry defines the metric; the ROAS calculator is a quick way to sanity-check a single figure against the field's output. The illustrative example after the steps shows the two patterns diverging on real-looking rows.
Group values with CASE
CASE evaluates conditions in order and returns the first match, which makes it the tool for turning raw campaign names, channels or landing pages into the groupings a dashboard reports on. A searched CASE reads naturally:
CASE WHEN REGEXP_MATCH(Campaign, "(?i).*brand.*") THEN "Brand" WHEN REGEXP_MATCH(Campaign, "(?i).*generic.*") THEN "Generic" WHEN Campaign IS NULL THEN "Unassigned" ELSE "Other" ENDAlways finish with an ELSE so unexpected values land in a visible bucket rather than disappearing. Order the branches from most specific to least, because the first match wins. A CASE that compares metrics must aggregate them, for example a status that tests whether summed cost exceeds summed revenue, and the result is then a metric-level field rather than a dimension. Keep CASE fields at the source level: the whole point of a grouping is that every chart agrees on it.
Clean and extract with text functions
Text functions repair naming inconsistencies and pull structure out of a naming convention. Lower-casing and trimming a campaign name gives a reliable join key and a clean dimension:
LOWER(TRIM(Campaign))Regular-expression extraction pulls a segment out of a structured name, such as the market code before the first underscore:
REGEXP_EXTRACT(Campaign, "^([^_]+)_")Concatenation builds a label from several fields, and regular-expression replacement strips a prefix or a tracking suffix. Text functions on dimensions produce dimensions, so the results can be used to group, filter and sort. Expect nulls when the pattern finds nothing, and wrap the extraction in a CASE or an IFNULL so the empty bucket has a name. Naming conventions themselves belong to the measurement plan, not the dashboard; the field only decodes what the convention promised.
Derive periods and ages with date functions
Date functions convert a date into the period a chart needs or the age a comparison depends on. Truncating a date to its week or month gives a clean trend dimension that aligns across sources with different native granularity; extracting the day of week supports a pacing view; the difference between today and a lead's created date gives the age used in a follow-up report:
DATE_DIFF(TODAY(), Created_date)Where a connector delivers dates as text, parse them into a true date first with the parse function and the format the source uses, then set the field type to date so the date-range control recognises it. Looker Studio documents which date functions accept which types; a formula that works on a date can fail on a date-and-time, and the fix is usually to cast or truncate the input before applying the function.
Understand aggregation and scope
A formula either aggregates every field it uses or none of them; mixing the two is rejected. Aggregated formulas produce metrics whose aggregation shows as auto and cannot be changed in the chart, which is what you want for ratios. Non-aggregated formulas produce per-row fields, which can be dimensions if they return text or dates and metrics if they return numbers, with the chart applying its own aggregation afterwards. Scope follows the home you chose in the first step: source fields are visible to every chart, control and filter; chart fields are visible to that chart alone and cannot drive a report-level filter. Count distinct is the aggregation to reach for when a text field such as a transaction ID must become a count.
Test against a known total
Drop the new field into a table with the dimension and date range that match the figure you noted before starting, and compare. A ratio should match the platform's own figure for that campaign and day to the rounding; a grouping should assign the test campaign to the bucket you expected; a date field should place a known event in the right week. Then widen the date range and change the grouping, and confirm that the ratio recomputes rather than averaging and that no rows have fallen into an unnamed null bucket. Record the field name, formula and the test you ran in the definitions document, so the next person can repeat the check when a connector changes its field names. A field drafted by an AI tool needs this test and a few more, which the tutorial on checking AI-written formulas and calculated fields sets out, from lookups and blanks to the level a ratio is calculated at.
Function reference
| Function | Category | Returns | Typical marketing use |
|---|---|---|---|
SUM, AVG, MIN, MAX | Aggregation | Metric | Totals and bounds for cost, revenue, sessions |
COUNT, COUNT_DISTINCT | Aggregation | Metric | Counting rows, or unique transaction and user IDs |
CASE … WHEN … ELSE … END | Conditional | Dimension or metric | Campaign grouping, status flags, channel mapping |
IF, IFNULL, NULLIF | Conditional | Same as inputs | Simple branches, filling blanks, guarding denominators |
CAST | Conversion | Chosen type | Turning a text ID into a number or the reverse |
ROUND | Arithmetic | Number | Presenting ratios to a fixed precision |
LOWER, UPPER, TRIM | Text | Dimension | Normalising campaign and source names |
CONCAT | Text | Dimension | Building labels from several fields |
REGEXP_MATCH | Text | Boolean | Pattern tests inside CASE |
REGEXP_EXTRACT, REGEXP_REPLACE | Text | Dimension | Pulling codes out of naming conventions, stripping suffixes |
CONTAINS_TEXT, STARTS_WITH, ENDS_WITH | Text | Boolean | Cheaper tests when a regular expression is overkill |
DATE_DIFF, DATETIME_DIFF | Date | Number | Age of a lead, days since last visit |
DATETIME_TRUNC, EXTRACT | Date | Date or number | Week and month dimensions, day-of-week pacing |
TODAY, CURRENT_DATE | Date | Date | Rolling windows and ages |
PARSE_DATE, FORMAT_DATETIME | Date | Date or text | Converting text dates from spreadsheets and partner connectors |
HYPERLINK, IMAGE | Presentation | Link or image | Clickable campaign names, creative thumbnails in tables |
Why the average of ratios misleads
Three campaigns run in a month. Campaign A spends £1,000 and returns £4,000; campaign B spends £1,000 and returns £1,000; campaign C spends £2,000 and returns £500. Their individual ROAS figures are 4.0, 1.0 and 0.25. A field written as Revenue / Cost and left on the chart's average aggregation reports 1.75 for the account. A field written as a ratio of sums reports the true figure: £5,500 of revenue over £4,000 of cost, or 1.38 rounded. The second formula, with a guard so an unspent campaign shows blank rather than an error, is:
ROUND(SUM(Revenue) / NULLIF(SUM(Cost), 0), 2)
A grouping test for the same account uses a quarter code embedded in the campaign name:
CASE
WHEN REGEXP_MATCH(Campaign, "(?i).*_q[1-4]_.*") THEN "Quarterly push"
ELSE "Always on"
END
The team checks campaign A's 4.0 against the Google Ads interface for the same month, confirms the quarterly bucket catches the three campaigns named with a quarter code, and records both formulas in the definitions sheet before any scorecard is built.
Common mistakes
- Averaging a ratio. A per-row ROAS or CPA field aggregated as average is the single most common wrong number on marketing dashboards; divide sums instead.
- Defining the same metric in several charts. Chart-level copies drift apart; the source is where a definition belongs.
- No ELSE branch in CASE. Unmatched values become null and vanish from totals without warning.
- Mixing aggregated and raw fields. The editor rejects the formula; the fix is to aggregate every input or none.
- Dividing by an empty denominator. Guard with a null check so campaigns without spend show a blank cell, not an error.
- Text dates left as text. Date functions and the date-range control need a true date type.
- Skipping the test against a known figure. A formula that compiles is not a formula that is right.
The dashboard design guide covers what to do with the fields once they are correct, and the Looker Studio hub links the rest of the series.
