Calculated fields, CASE, groups and bins in Looker Studio

#01Data-source versus chart-level fields

Google's calculated fields documentation is the source for these limits. Marketers hit the blend limitation constantly in cross-channel reports (lesson 7, lesson 12).

#02Core marketing formulas

Examples assume GA4-style names; rename to your fields.

Conversion rate

SUM(Key events) / SUM(Sessions)

Set the field type to Percent.

Cost per key event (on a blend that already has Ads cost and GA4 key events — chart-level)

SUM(Cost) / SUM(Key events)

Safe divide (avoid divide-by-zero)

SUM(Key events) / NULLIF(SUM(Sessions), 0)

AI Assistant flag (dimension)

CASE
  WHEN Session default channel group = "AI Assistant" THEN "AI Assistant"
  ELSE "Other"
END

High-intent landing pages

CASE
  WHEN REGEXP_MATCH(Landing page, ".*/(pricing|demo|contact).*") THEN "High intent"
  ELSE "Other"
END

Test REGEXP on your real URL shapes. See also GA4 key events for what you should be counting.

#03Custom groups and bins

Use custom groups when you map many campaign names into a few offers. Use bins when you bucket numeric metrics (for example session duration ranges). They are easier to maintain than a 40-line CASE when the rule is "these values become this label."

Rebuild groups when naming conventions change. Put the group definition owner in your connector register.

#04CASE design rules for marketers

1. Order matters. First match wins.
2. Always include an ELSE, even if it is "Uncategorised".
3. Match on stable fields (channel group, medium) before brittle campaign strings when you can.
4. Document the business rule in a text box near the chart for the first two reporting cycles.
5. Do not silently redefine "conversion" differently on every page.

#05Gemini assistance (Pro)

With Data Studio Pro and Gemini in Data Studio enabled, Google documents natural-language help for creating calculated fields. Treat the draft as a starting point. Run it against a known week and compare with GA4 Explorations before you publish. Gemini features have been in preview with limited support; confirm whether your project still shows preview notices (lesson 19).

#06Worked example: Ledgerline

Ledgerline is a fictional accounts-payable software company. Details are illustrative.

Walkthrough

1. Analysts created Lead CVR on the GA4 data source so every chart shared one definition.
2. Intent page started chart-level, then moved to the data source after two weeks of agreement.
3. A blend for Ads + GA4 needed chart-level CPA because the data-source CVR field could not be used on the blend.
4. Leadership asked why CVR differed from the GA4 UI. The footer linked to the exact formula.
5. Competitor C's agency used three incompatible CVR fields; Ledgerline refused to copy that mess.

#07Formula patterns marketers reuse

#08Data-source vs chart-level fields

Rule of thumb: definitions you will defend in a QBR → data source (or warehouse). Blend rates → chart level.

#09Blend limitation to memorise

Google documents that data-source calculated fields cannot be used in blends. If you need a blended CPA, create it as a chart-level calculated field on the blend. This single rule explains most "why did my field disappear" tickets.

#10QA before you trust a field

1. Compare to the source UI for last 7 and last 28 days.
2. Check a day with zero conversions (NULLIF behaviour).
3. Ask a second person to read the CASE logic aloud.
4. Add the field to the metric dictionary.

#11Functions marketers actually touch

Open Google's calculated field function list from the field editor when unsure — function availability can differ slightly by context. Prefer readable formulas over clever one-liners.

#12Common mistakes

• Different CVR formulas on different pages.
• Trying to use a data-source calculated field inside a blend.
• CASE without ELSE, dropping rows into nulls.
• Regex that only matches desktop URL casing.
• Publishing Gemini-drafted formulas untested.

#13Try it yourself

On your GA4 data source, add a percent Key event rate field and a CASE that flags AI Assistant versus Other. Drop both onto a table with Session source and confirm totals look sane for the last 28 days.

#14Quick quiz

1. Can data-source calculated fields be used with blended data?

Answer: No. Use chart-level calculated fields on the blend.

2. What does NULLIF help with in rate metrics?

Answer: Preventing divide-by-zero when the denominator is 0.

3. When are custom groups better than CASE?

Answer: When you are simply bucketing many dimension values into a few labels.

#15What should you check before the next lesson?

• At least one reusable calculated metric exists.
• Blend limitation understood.
• Metric dictionary stub started.

Previous: Build your first marketing dashboard in Looker Studio. Next: Parameters, filters and controls in Looker Studio. Or return to the Looker Studio for Marketers track. Related: GA4 key events setup, GA4 default channel group explained, Claude CSV analysis for GA4 and Ads.

#16Common questions

What is a calculated field in Looker Studio? A formula that creates a new dimension or metric from existing fields, using functions, arithmetic or CASE logic.

Can I use a data-source calculated field in a blend? No. Google documents that data-source calculated fields cannot be used with blended data. Use a chart-level calculated field on the blend instead.

What are custom groups and bins? Custom groups bucket dimension values into labels you define. Custom bins bucket numeric values into ranges. Both are calculated-field types in Data Studio.