Loading tutorials…
Loading tutorials…
Calculated fields are how Looker Studio goes from 'shows data' to 'answers questions.' The syntax is its own dialect — not SQL, not spreadsheet. This tutorial covers the formula patterns that pay for themselves in week one.
Who this is forAnyone building reports that need derived metrics — ROAS, conversion rate, custom segments, blended KPIs. Especially valuable for stakeholders who ask "but what about $X / $Y?" — calculated fields turn that into a one-time setup.
What you'll need
Step 1
Calculated fields live at three levels: data source (reusable across reports), report (reusable across charts), or chart (single chart only). Pick the right level upfront.
Data source level — most reusable. Edit data source → + Add field. Available in every report using that source. Use for fundamentals like ROAS, Conversion rate, AOV.
Report level — reusable across charts within one report. Resource → Manage added data sources → click source → Add field. Use for report-specific derived metrics that don't need to live in the source itself.
Chart level — single-chart only. Add metric → CREATE FIELD. Use for one-off calculations or for testing a formula before promoting it to a higher level.
Recommendation: build at the data-source level by default. Promote to higher levels only when truly single-use. Saves rebuild work later.
Caveat: data-source-level calculated fields can't reference other calculated fields from the same source unless you save and re-edit. Build atomic fields first, composite fields second.
Step 2
Build ROAS, Conversion rate, CPA, AOV, and CTR as data-source-level calculated fields. These cover 80% of marketing dashboard needs.
ROAS = Conversion value / Cost. Type: Number. In chart Style: format as a multiplier (4.2x). For Google Ads source.
Conversion rate = Conversions / Clicks (for Google Ads) or Conversions / Sessions (for GA4). Type: Number. Format: Percent.
CPA (Cost per acquisition) = Cost / Conversions. Type: Currency. Pick currency in the field config.
AOV (Average order value) = Revenue / Transactions (for e-commerce). Type: Currency.
CTR (Click-through rate) = Clicks / Impressions. Type: Number. Format: Percent.
Save each. They're now available as metrics in every chart using this source.
Step 3
CASE WHEN ... THEN ... ELSE ... END lets you build categorical segments from raw data. The most powerful Looker Studio formula pattern.
Example — bucket sessions by device: CASE WHEN Device category = 'mobile' THEN 'Mobile' WHEN Device category = 'tablet' THEN 'Tablet' ELSE 'Desktop' END. Save as field 'Device bucket'. Type: Text.
Now charts can group by Device bucket as a custom dimension.
Example — bucket conversion value: CASE WHEN Conversion value >= 100 THEN 'High' WHEN Conversion value >= 50 THEN 'Medium' ELSE 'Low' END. Save as 'Value tier'.
CASE always needs an ELSE clause and an END. Forgetting either causes the formula to fail with an unhelpful error.
CASE is the most reused formula pattern in marketing dashboards. Build a library of 5-10 CASE fields for common segmentations.
Step 4
REGEXP_EXTRACT, REGEXP_MATCH, REGEXP_REPLACE let you parse URL parameters, campaign names, and other structured text.
Example — extract UTM source from a URL: REGEXP_EXTRACT(Page URL, 'utm_source=([^&]+)'). Returns the source value. Type: Text.
Example — match brand vs non-brand campaigns: REGEXP_MATCH(Campaign name, '.*[Bb]rand.*'). Returns TRUE/FALSE. Type: Boolean.
Example — clean campaign name (remove version suffix): REGEXP_REPLACE(Campaign name, '_v[0-9]+$', ''). Returns cleaned name.
REGEXP is case-sensitive by default. Use [Bb] or (?i) flags for case-insensitive matches.
If REGEXP is over your head, hire a specialist for an hour. Learning regex on a deadline produces formulas that work in tests but break in production.
Step 5
DATE_DIFF, DATETIME_ADD, FORMAT_DATETIME let you build time-based segments (this week, last 7 days, year-over-year).
Example — flag conversions from today only: CASE WHEN DATE_DIFF(TODAY(), Date, DAY) = 0 THEN 'Today' ELSE 'Earlier' END.
Example — bucket by day of week: FORMAT_DATETIME('%A', Date). Returns 'Monday', 'Tuesday', etc.
Example — flag weekends: CASE WHEN FORMAT_DATETIME('%a', Date) IN ('Sat', 'Sun') THEN 'Weekend' ELSE 'Weekday' END.
TODAY() and NOW() update every refresh — useful for 'rolling 7 days' style logic.
Date math in Looker Studio is its own dialect. The functions look like SQL but the syntax is slightly different. Test every date formula against known values before relying.
Step 6
Any ratio metric (ROAS, conversion rate, CTR) can hit a zero denominator. Wrap divisions in NULLIF or CASE to handle.
Wrong: ROAS = Conversion value / Cost. If Cost = 0, formula returns infinity and the chart shows a broken cell.
Right: ROAS = Conversion value / NULLIF(Cost, 0). If Cost = 0, returns null. Charts handle null cleanly.
Alternative: CASE WHEN Cost = 0 THEN 0 ELSE Conversion value / Cost END. Returns 0 instead of null. Pick based on what makes more sense in your dashboard (null = 'no data', 0 = 'measured but zero').
Same logic applies to every ratio: Conversion rate = Conversions / NULLIF(Clicks, 0), CPA = Cost / NULLIF(Conversions, 0), etc.
Null-safety is the difference between a dashboard that survives edge cases and one that breaks in week three when a paused campaign hits zero spend.
Step 7
Before relying on a calculated field, test it against a known case (a campaign with known cost, known revenue, known ROAS). Reconcile.
Pick a specific campaign with known data — e.g., "Brand_Search_2026" with $1,000 cost and $5,000 conversion value.
Build a one-row table with the campaign filtered. Add the calculated ROAS metric. Should show 5.0 or 500% depending on format.
If it shows something different, the formula is broken. Common bugs: aggregation order (calculating per-row vs per-aggregate), null handling, or wrong field reference.
Repeat with at least 3 campaigns including edge cases (zero cost, very high cost, mid-range).
Document the test in a Methodology section: 'ROAS calculated as SUM(Conversion value) / SUM(Cost). Validated against Brand_Search_2026 (1000/5000 = 5.0x) on [date].'
Common mistakes
Skipping null-safety on division
What goes wrong: Charts show 'Infinity' or blank cells when a campaign hits zero cost or zero conversions. Stakeholders see broken numbers and lose trust in the dashboard. You spend a week patching every ratio metric retroactively.
How to avoid: Wrap every denominator in NULLIF: Cost / NULLIF(Conversions, 0). Build this as a habit on day one, not after the first broken chart.
Building calculated fields at chart level instead of source level
What goes wrong: You build the same ROAS formula in 12 different charts. When the formula needs updating (e.g., to add null-safety), you update 12 places. One inevitable mistake creates inconsistency: 11 charts show new logic, 1 shows old.
How to avoid: Build at the data-source level by default. One formula, one place to update. Charts inherit automatically.
Using REGEXP without handling the null case
What goes wrong: REGEXP_EXTRACT returns null when no match exists. A field 'UTM source' built without null-handling shows blank cells for direct traffic — making 30% of sessions look like a missing dimension. Dashboards filter weirdly.
How to avoid: Wrap REGEXP_EXTRACT in IFNULL: IFNULL(REGEXP_EXTRACT(URL, pattern), 'direct'). Returns "direct" instead of null, which makes for cleaner charts.
Mismatching CASE syntax
What goes wrong: Formula error: 'Calculation failed'. The CASE statement is missing an ELSE or END, or has an extra comma. You spend 20 minutes debugging the syntax before realizing the issue.
How to avoid: Always include ELSE and END in CASE. Use Looker Studio's formula editor (color-coding helps spot missing pieces). Build incrementally — one WHEN branch at a time.
Not testing against known values
What goes wrong: You ship a True ROAS metric that looks credible. Six weeks in, someone notices it's 50% lower than the per-campaign manual calculation. The formula is averaging instead of summing — a subtle aggregation bug. Budget decisions made on the wrong number for 6 weeks.
How to avoid: Test every calculated field against at least 3 known cases before shipping. Document the test on the dashboard. Repeat after any formula change.
Recap
Done — what's next
How to set up Looker Studio blended data sources correctly
Read the next tutorial
Hand it off
Calculated fields look simple until they aren't. The fields most owners need are also the ones most likely to have edge-case bugs (null handling, aggregation order, blend-aware logic). A specialist who has built these dozens of times can ship a clean library in one session — typically $80-200 for the build, then free maintenance with monthly reporting work.
See specialist rates
A metric is any aggregatable numeric field (count, sum, average). A calculated field is a field defined by a formula — it can be a metric (e.g., ROAS) or a dimension (e.g., a CASE-based segment). Calculated fields are a type of field; metrics are a type of usage.
Yes, but with a quirk: you have to save the first calculated field, then reopen the data source to use it in a second calculated field. Build atomic fields first (e.g., 'Net revenue'), composite fields second (e.g., 'ROAS = Net revenue / Cost').
Most common cause: aggregation order. ROAS = Conversion value / Cost computed per-row, then averaged, gives a different (and wrong) answer than SUM(Conversion value) / SUM(Cost) at the aggregate level. Always think about whether you want per-row math or aggregate math, and structure the formula accordingly.
Yes — calculated fields in blends live inside the blend editor (not the underlying sources). Use the table prefix (e.g., GA4.Sessions) to disambiguate fields with the same name across sources. Some functions behave differently in blends — test carefully.
Looker Studio
Data blending is the killer Looker Studio feature — combining GA4 + Google Ads in one chart, joining a Sheet to GA4, building cross-source ROAS calculations. It's also where most DIY users hit a wall. Here's the working model.
Looker Studio
The native GA4 connector looks like three clicks — but the choices you make in those clicks lock in sampling, scope, and refresh patterns for the life of the report. Here's the right way to wire it up.
Looker Studio
Connecting Google Ads to Looker Studio is two clicks. Connecting it *correctly* — with the right scope, the right conversions, and a layout that survives a board meeting — is what this tutorial does.
Looker Studio
Your dashboard worked yesterday. Today three charts show 'Configuration incomplete' and one shows numbers that look wrong. Here's the diagnostic sequence specialists run before changing anything.