Looker Studio Calculated Fields: Date Math, Conditional Metrics, and CASE Statements

GhazanfarApril 17, 20262 min read

Looker Studio’s calculated fields let you transform and combine data without touching the underlying source — but the formula syntax trips up almost everyone at first. Date calculations return unexpected results, CASE statements miss edge cases, and division by zero crashes charts. This guide covers the calculated field patterns you’ll actually use, with working formulas you can copy directly.

Date Math: Days Differences and Date Conversion

-- Days between two date fields
DATE_DIFF(end_date, start_date, DAY)

-- Days since a fixed date
DATE_DIFF(TODAY(), TODATE("20260101", "%Y%m%d"), DAY)

-- Convert GA4's YYYYMMDD string to proper date type
TODATE(Date, "%Y%m%d")

-- Format a date for display
FORMAT_DATETIME("%m/%d/%Y", DATETIME(Date))

CASE Statements for Channel Bucketing

-- Regroup sessions into simplified channel buckets
CASE
  WHEN REGEXP_MATCH(Session medium, "cpc|paid|ppc") THEN "Paid"
  WHEN Session medium = "organic" THEN "Organic Search"
  WHEN Session medium = "email" THEN "Email"
  WHEN Session source = "direct" THEN "Direct"
  WHEN REGEXP_MATCH(Session medium, "social|social-network") THEN "Social"
  ELSE "Other"
END

Safe Division: Avoid Division by Zero

-- Safe CVR calculation (won't crash when sessions = 0)
CASE
  WHEN Sessions = 0 THEN 0
  ELSE Transactions / Sessions * 100
END

-- Or use NARY_MAX for a minimum denominator of 1
Transactions / NARY_MAX(Sessions, 1) * 100

Conditional Metrics

-- Sum revenue only from paid channels
SUM(CASE WHEN Session medium = "cpc" THEN Revenue ELSE 0 END)

-- Percentage of mobile sessions
COUNTIF(Device category = "mobile") / COUNT(Sessions) * 100

-- Sessions with 2+ minute engagement
COUNTIF(Session duration >= 120)

String Manipulation for Clean Labels

-- Extract domain from page URL
REGEXP_EXTRACT(Page location, "https?://([^/]+)")

-- Truncate long campaign names to 30 chars
IF(LENGTH(Campaign) > 30, CONCAT(LEFT(Campaign, 27), "..."), Campaign)

-- Capitalize first letter of source
CONCAT(UPPER(SUBSTR(Session source,1,1)), LOWER(SUBSTR(Session source,2)))

Common Mistakes to Avoid

REGEXP_MATCH requires full-string match — use “.*pattern.*” unless you want exact match
DATE functions require Date-type fields — use TODATE() to convert string date fields first
Can’t use SUM() on a dimension field — only on metrics
CASE uses = for string comparison (not == like in other languages)