Skip to content
Faisal Mahmud TusherDigital marketing, SEO & analytics
with Faisal Tusher

Marketing Analytics › Looker Studio

Calculated Fields in Looker Studio

A step-by-step tutorial for Looker Studio calculated fields: source versus chart-level fields, ratios done correctly, CASE logic, text and date functions.
Tutorialintermediate8 min readUpdated

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.
  1. 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 KPI 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.

  2. 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.

  3. Write ratios as a sum divided by a sum

    Every efficiency metric — ROAS, CPA, CTR, 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.

  4. 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"
    END
    

    Always 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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

FunctionCategoryReturnsTypical marketing use
SUM, AVG, MIN, MAXAggregationMetricTotals and bounds for cost, revenue, sessions
COUNT, COUNT_DISTINCTAggregationMetricCounting rows, or unique transaction and user IDs
CASE … WHEN … ELSE … ENDConditionalDimension or metricCampaign grouping, status flags, channel mapping
IF, IFNULL, NULLIFConditionalSame as inputsSimple branches, filling blanks, guarding denominators
CASTConversionChosen typeTurning a text ID into a number or the reverse
ROUNDArithmeticNumberPresenting ratios to a fixed precision
LOWER, UPPER, TRIMTextDimensionNormalising campaign and source names
CONCATTextDimensionBuilding labels from several fields
REGEXP_MATCHTextBooleanPattern tests inside CASE
REGEXP_EXTRACT, REGEXP_REPLACETextDimensionPulling codes out of naming conventions, stripping suffixes
CONTAINS_TEXT, STARTS_WITH, ENDS_WITHTextBooleanCheaper tests when a regular expression is overkill
DATE_DIFF, DATETIME_DIFFDateNumberAge of a lead, days since last visit
DATETIME_TRUNC, EXTRACTDateDate or numberWeek and month dimensions, day-of-week pacing
TODAY, CURRENT_DATEDateDateRolling windows and ages
PARSE_DATE, FORMAT_DATETIMEDateDate or textConverting text dates from spreadsheets and partner connectors
HYPERLINK, IMAGEPresentationLink or imageClickable campaign names, creative thumbnails in tables
Illustrative example — not a real result

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.

Frequently asked questions

Should I create a calculated field in the data source or in the chart?

Create it in the data source when the field defines a metric or grouping that more than one chart or report will use, because it then exists once and is available to filters and controls. Create it in the chart when it is a one-off for that visual, when you lack edit rights on the source, or when the chart uses blended data, where only chart-level fields are possible. Chart-level fields are easy to lose and easy to duplicate inconsistently.

Why does Looker Studio refuse to mix aggregated and non-aggregated fields?

A formula that applies a function such as SUM to one field and uses another field raw has no single row to evaluate against, so Looker Studio rejects it. Either aggregate every field in the formula, which produces a metric, or aggregate none of them, which produces a per-row field that the chart aggregates afterwards. The choice decides whether the field can be used as a dimension.

What is the right way to calculate ROAS or CPA in a calculated field?

Divide the summed numerator by the summed denominator in one formula, so the ratio is recomputed from totals at whatever grain the chart uses. Averaging a per-row ratio or applying the chart's average aggregation to it gives every row equal weight regardless of spend and produces a figure that changes meaning when the chart is regrouped. Guard the denominator so empty spend returns nothing rather than an error.

Terms used on this page

Tools to try

Where this page is used

Part of these learning paths

Also related

Sources

  1. Looker Studio Help — Looker Studio Help

Related