Analytics Formula Library

CASE
  WHEN REGEXP_MATCH(Country, "(?i)^(Austria|Belgium|Bulgaria|Croatia|Cyprus|Czech Republic|Czechia|Denmark|Estonia|Finland|France|Germany|Greece|Hungary|Ireland|Italy|Latvia|Lithuania|Luxembourg|Malta|Netherlands|Poland|Portugal|Romania|Slovakia|Slovenia|Spain|Sweden)$")
    THEN "European Union"
  ELSE Country
END

REGEXP_MATCH(
  Country,
  "(?i)^(Austria|Belgium|Bulgaria|Croatia|Cyprus|Czech Republic|Czechia|Denmark|Estonia|Finland|France|Germany|Greece|Hungary|Ireland|Italy|Latvia|Lithuania|Luxembourg|Malta|Netherlands|Poland|Portugal|Romania|Slovakia|Slovenia|Spain|Sweden)$"
)

REGEXP_MATCH(
  Page path + query string,
  "^/commodities/[0-9]{10}([/?].*)?$"
)

REGEXP_MATCH(
  Page path + query string,
  ".*(find_commodity|/commodities/|/headings/).*"
)

CASE
  WHEN REGEXP_MATCH(Page path, "^/commodities/[0-9]{10}(/.*)?$") THEN "Commodity Detail"
  WHEN REGEXP_MATCH(Page path, "^/commodities(/.*)?$") THEN "Commodities"
  WHEN REGEXP_MATCH(Page path, "^/headings(/.*)?$") THEN "Headings"
  WHEN REGEXP_MATCH(Page path, ".*find_commodity.*") THEN "Find Commodity"
  WHEN Page path = "/" THEN "Homepage"
  ELSE "Other"
END

CASE
  WHEN Event name = "internal_link_click"
    THEN click_text
  ELSE NULL
END

CASE
  WHEN Event name = "internal_link_click"
    THEN value_of_closest_parent_class
  ELSE NULL
END

DATETIME_DIFF(event_end_timestamp, event_start_timestamp, SECOND)

CASE
  WHEN sessions_per_user IS NULL OR sessions_per_user < 1 THEN "0 - Unknown"
  WHEN sessions_per_user = 1 THEN "1 - One session"
  WHEN sessions_per_user < 4 THEN "2 - 2 to 3 sessions"
  WHEN sessions_per_user < 8 THEN "3 - 4 to 7 sessions"
  WHEN sessions_per_user < 16 THEN "4 - 8 to 15 sessions"
  ELSE "5 - 16 or more sessions"
END

CASE
  WHEN SUM(has_search) > 0
    THEN SUM(has_commodity_after_search) / SUM(has_search)
  ELSE NULL
END

REGEXP_EXTRACT(
  Page path + query string,
  "^/commodities/([0-9]{10})(?:[/?]|$)"
)

CASE
  WHEN SUM(session_count) > 0
    THEN SUM(sessions_with_key_event) / SUM(session_count)
  ELSE NULL
END

GTM variable display name: Click Text
GA4 event name: internal_link_click
GTM event parameter name: click_text
GTM event parameter value: {{Click Text}}
GA4 custom dimension:
  Scope: Event
  Event parameter: click_text
  Display name: Click text

Parameter names are case-sensitive.
Use letters, numbers and underscores; start with a letter.
Avoid reserved names and prefixes.
Select the actual custom dimension field in Looker Studio.

Explore > Segments > New segment > User segment
Add sequence

Step 1:
  Event name exactly matches view_search_results

Step 2:
  Event name exactly matches page_view
  AND Page location matches regex:
  ^https?://[^/]+/commodities/[0-9]{10}([/?#].*)?$

Choose "indirectly followed by" to allow intervening events.
Choose "directly followed by" only if the next recorded
event must be the detail page view.

Set the sequence scope to within the same session
where that option is available in your property.

Custom dimension reporting: allow 24 to 48 hours after setup.
GA4 audience membership: allow 24 to 48 hours to populate.
Google Ads audience availability: check linked-account
status, consent, eligibility and destination processing.
Do not treat a single timing estimate as a guaranteed SLA.

Looker Studio: refresh data-source fields after registering
a dimension, then check data freshness and field compatibility.
Avoid reconnecting the property before investigating the issue.

1. Identify the exact fields: first-user, session or event scope.
2. Check the original tagged URL and every redirect.
   Confirm utm_source, utm_medium and utm_campaign survive.
3. Check consent and when the first analytics hit is sent.
4. Use DebugView to confirm received page_location and events.
5. For cross-domain journeys, configure cross-domain measurement
   and verify that the linker parameter survives navigation.
6. Use unwanted-referral settings only for appropriate referrers;
   they do not replace cross-domain measurement.
7. For Measurement Protocol, validate client_id and session_id
   association and the requirements of the intended report.
8. If exported, inspect collected_traffic_source and
   session_traffic_source_last_click in BigQuery.

Do not assume a later referral rewrites the original session source.
Do not add source/medium blindly to every Measurement Protocol hit.

function() {
  var el = {{Click Element}};
  if (!el || el.nodeType !== 1) return undefined;
  var target = el.closest('[data-tree-target]');
  return target ? target.getAttribute('data-tree-target') : undefined;
}

GA4 Event tag:
  Event name: internal_link_click
  Event parameter name: click_text
  Event parameter value: {{Click Text}}

Use the variable picker, or type the exact {{Click Text}} token.
Do not enter the plain text Click Text as the parameter value.

In GA4, create an event-scoped custom dimension:
  Event parameter: click_text
  Display name: Click text

Verify the collected value in Preview mode and DebugView.

Variable Type     : DOM Element
Selection Method  : CSS Selector
Element Selector  : a[data-tree-target]
Attribute Name    : data-tree-target

-- Returns the value of data-tree-target from the FIRST
-- matching element on the page.
-- Use Custom JavaScript (above) if multiple elements exist
-- and you need to target the one that was clicked.

WITH extracted AS (
  SELECT
    user_pseudo_id,
    event_name,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_location
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
),
events AS (
  SELECT * FROM extracted
  WHERE user_pseudo_id IS NOT NULL AND session_id IS NOT NULL
),
searches AS (
  SELECT user_pseudo_id, session_id, MIN(event_timestamp) AS search_ts
  FROM events
  WHERE event_name = 'view_search_results'
  GROUP BY 1, 2
),
matched AS (
  SELECT
    s.user_pseudo_id, s.session_id, s.search_ts,
    MIN(e.event_timestamp) AS commodity_ts
  FROM searches s
  LEFT JOIN events e
    ON e.user_pseudo_id = s.user_pseudo_id
    AND e.session_id = s.session_id
    AND e.event_name = 'page_view'
    AND e.event_timestamp > s.search_ts
    AND REGEXP_CONTAINS(e.page_location,
      r'^https?://[^/]+/commodities/[0-9]{10}([/?#].*)?$')
  GROUP BY 1, 2, 3
)
SELECT
  user_pseudo_id, session_id,
  TIMESTAMP_MICROS(search_ts) AS search_timestamp,
  TIMESTAMP_MICROS(commodity_ts) AS commodity_timestamp,
  1 AS has_search,
  IF(commodity_ts IS NOT NULL, 1, 0) AS has_commodity_after_search,
  (commodity_ts - search_ts) / 1000000.0 AS seconds_to_commodity
FROM matched;

SELECT
  event_name,
  user_pseudo_id,
  TIMESTAMP_MICROS(event_timestamp) AS event_ts,
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'click_text') AS click_text,
  (SELECT value.string_value FROM UNNEST(event_params)
   WHERE key = 'page_location') AS page_location,
  (SELECT COALESCE(CAST(value.int_value AS FLOAT64),
                   value.double_value, value.float_value)
   FROM UNNEST(event_params)
   WHERE key = 'value') AS event_value
FROM `your-project.analytics_XXXXXX.events_*`
WHERE event_name = 'internal_link_click'
  AND _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
LIMIT 1000;

1 - Engagement rate

CASE
  WHEN SUM(session_count) > 0
    THEN (SUM(session_count) - SUM(engaged_session_count)) / SUM(session_count)
  ELSE NULL
END

CASE
  WHEN Session default channel group = "Paid Search"
    AND REGEXP_MATCH(Session campaign, "(?i)^brand([_ -].*)?$")
    THEN "Paid Search - Brand"
  WHEN Session default channel group = "Paid Search"
    THEN "Paid Search - Non-Brand"
  WHEN Session default channel group = "Paid Social"
    AND REGEXP_MATCH(Session source, "(?i).*(facebook|instagram|meta).*")
    THEN "Meta Ads"
  WHEN Session default channel group = "Paid Social"
    AND REGEXP_MATCH(Session source, "(?i).*linkedin.*")
    THEN "LinkedIn Ads"
  WHEN Session default channel group = "Organic Social" THEN "Organic Social"
  ELSE Session default channel group
END

CASE
  WHEN Device category = "desktop" AND REGEXP_MATCH(Operating system, "(?i).*windows.*") THEN "Desktop - Windows"
  WHEN Device category = "desktop" AND REGEXP_MATCH(Operating system, "(?i).*mac.*") THEN "Desktop - Mac"
  WHEN Device category = "desktop" THEN "Desktop - Other"
  WHEN Device category = "mobile" AND REGEXP_MATCH(Operating system, "(?i).*ios.*") THEN "Mobile - iOS"
  WHEN Device category = "mobile" AND REGEXP_MATCH(Operating system, "(?i).*android.*") THEN "Mobile - Android"
  WHEN Device category = "mobile" THEN "Mobile - Other"
  WHEN Device category = "tablet" THEN "Tablet"
  ELSE "Unknown"
END

CASE
  WHEN Device category = "mobile" THEN "Mobile"
  WHEN Device category = "desktop" OR Device category = "tablet" THEN "Desktop / Tablet"
  ELSE "Unknown"
END

REGEXP_REPLACE(Landing page + query string, "[?#].*$", "")

REGEXP_EXTRACT(Page location, "^https?://[^/?#]+(/[^?#]*)")

CASE
  WHEN session_number = 1 THEN "First observed session"
  WHEN session_number = 2 THEN "Second observed session"
  WHEN session_number >= 3 AND session_number <= 5 THEN "Sessions 3 to 5"
  WHEN session_number >= 6 THEN "Session 6 or later"
  ELSE "Unknown"
END

CASE
  WHEN SUM(ad_cost) > 0 THEN SUM(attributed_purchase_revenue) / SUM(ad_cost)
  ELSE NULL
END

CASE WHEN SUM(acquisitions) > 0 THEN SUM(ad_cost) / SUM(acquisitions) ELSE NULL END

CASE WHEN SUM(session_count) > 0 THEN SUM(purchase_revenue) / SUM(session_count) ELSE NULL END

CASE WHEN SUM(user_count) > 0 THEN SUM(purchase_revenue) / SUM(user_count) ELSE NULL END

CASE WHEN SUM(purchase_revenue) > 0 THEN (SUM(purchase_revenue) - SUM(purchase_revenue) * 0.4) / SUM(purchase_revenue) ELSE NULL END

CASE
  WHEN percent_scrolled IS NULL THEN "Unknown"
  WHEN CAST(percent_scrolled AS NUMBER) >= 90 THEN "4 - 90% or more"
  WHEN CAST(percent_scrolled AS NUMBER) >= 75 THEN "3 - 75% to 89%"
  WHEN CAST(percent_scrolled AS NUMBER) >= 50 THEN "2 - 50% to 74%"
  WHEN CAST(percent_scrolled AS NUMBER) >= 25 THEN "1 - 25% to 49%"
  ELSE "0 - Below 25%"
END

CASE
  WHEN Link URL IS NULL OR Link URL = "" THEN "No URL"
  WHEN REGEXP_MATCH(Link URL, "(?i)^https?://(?:[^/?#:]+[.])*yourdomain[.]com(?::[0-9]+)?(?:[/?#].*)?$") THEN "Internal"
  WHEN REGEXP_MATCH(Link URL, "(?i)^https?://.*") THEN "Outbound"
  ELSE "Other URL type"
END

REGEXP_EXTRACT(Link URL, "(?i)^https?://(?:www[.])?([^/?#:]+)")

CASE WHEN SUM(session_count) > 0 THEN SUM(view_count) / SUM(session_count) ELSE NULL END

CASE WHEN SUM(active_user_count) > 0 THEN SUM(view_count) / SUM(active_user_count) ELSE NULL END

CASE WHEN SUM(active_user_count) > 0 THEN SUM(engaged_session_count) / SUM(active_user_count) ELSE NULL END

CASE WHEN SUM(session_count) > 0 THEN SUM(engagement_seconds) / SUM(session_count) ELSE NULL END

-- Step 1: Create Scroll Depth Trigger
Trigger type      : Scroll Depth
Vertical depths   : ☑ Percentages → 25, 50, 75, 90
Evaluate trigger  : Window Load (adapt and test for your page)
Enable trigger on : All Pages, or a defined subset

-- Step 2: Enable built-in variables
☑ Scroll Depth Threshold   (returns 25, 50, 75, 90)
☑ Scroll Depth Units       (returns "percent")

-- Step 3: GA4 Event Tag
Event name: scroll_depth
Event parameters:
  percent_scrolled  →  {{Scroll Depth Threshold}}
  scroll_units      →  {{Scroll Depth Units}}

-- Step 4: Register in GA4
Custom dimension: percent_scrolled (Event scope)
Custom dimension: scroll_units     (Event scope)

-- Test in Preview mode: scroll the page and check
-- each threshold fires once per page load

function() { return window.location.pathname; }

function() {
  return new URL(window.location.href).searchParams.get('utm_campaign');
}

function() { return window.location.hostname; }

function() { return window.location.hash || undefined; }

function() { return window.location.origin + window.location.pathname; }

function() {
  var name = 'your_cookie_name';
  var cookies = document.cookie ? document.cookie.split(';') : [];
  for (var i = 0; i < cookies.length; i++) {
    var item = cookies[i].replace(/^\s+/, '');
    var equals = item.indexOf('=');
    if (equals !== -1 && item.slice(0, equals) === name) {
      var value = item.slice(equals + 1);
      try { return decodeURIComponent(value); }
      catch (e) { return value; }
    }
  }
  return undefined;
}

WITH extracted AS (
  SELECT
    user_pseudo_id,
    event_name,
    event_timestamp,
    (SELECT value.int_value FROM UNNEST(event_params)
     WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_location
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
),
events AS (
  SELECT * FROM extracted
  WHERE user_pseudo_id IS NOT NULL AND session_id IS NOT NULL
),
views AS (
  SELECT user_pseudo_id, session_id, MIN(event_timestamp) AS view_ts
  FROM events WHERE event_name = 'view_item'
  GROUP BY 1, 2
),
carts AS (
  SELECT v.user_pseudo_id, v.session_id, v.view_ts,
         MIN(e.event_timestamp) AS cart_ts
  FROM views v LEFT JOIN events e
    ON e.user_pseudo_id = v.user_pseudo_id AND e.session_id = v.session_id
    AND e.event_name = 'add_to_cart' AND e.event_timestamp > v.view_ts
  GROUP BY 1, 2, 3
),
checkouts AS (
  SELECT c.user_pseudo_id, c.session_id, c.view_ts, c.cart_ts,
         MIN(e.event_timestamp) AS checkout_ts
  FROM carts c LEFT JOIN events e
    ON e.user_pseudo_id = c.user_pseudo_id AND e.session_id = c.session_id
    AND e.event_name = 'begin_checkout' AND e.event_timestamp > c.cart_ts
  GROUP BY 1, 2, 3, 4
),
purchases AS (
  SELECT c.user_pseudo_id, c.session_id, c.view_ts, c.cart_ts, c.checkout_ts,
         MIN(e.event_timestamp) AS purchase_ts
  FROM checkouts c LEFT JOIN events e
    ON e.user_pseudo_id = c.user_pseudo_id AND e.session_id = c.session_id
    AND e.event_name = 'purchase' AND e.event_timestamp > c.checkout_ts
  GROUP BY 1, 2, 3, 4, 5
),
counts AS (
  SELECT COUNT(*) AS viewed,
         COUNTIF(cart_ts IS NOT NULL) AS carted,
         COUNTIF(checkout_ts IS NOT NULL) AS checked_out,
         COUNTIF(purchase_ts IS NOT NULL) AS purchased
  FROM purchases
),
steps AS (
  SELECT 1 AS step_number, 'View item' AS step, viewed AS sessions,
         CAST(NULL AS INT64) AS previous_sessions FROM counts
  UNION ALL SELECT 2, 'Add to cart', carted, viewed FROM counts
  UNION ALL SELECT 3, 'Checkout', checked_out, carted FROM counts
  UNION ALL SELECT 4, 'Purchase', purchased, checked_out FROM counts
)
SELECT *, SAFE_DIVIDE(sessions, previous_sessions) AS step_rate,
       1 - SAFE_DIVIDE(sessions, previous_sessions) AS drop_off_rate
FROM steps ORDER BY step_number;

WITH daily AS (
  SELECT PARSE_DATE('%Y%m%d', event_date) AS date,
         COUNT(DISTINCT IF(is_active_user, user_pseudo_id, NULL)) AS dau
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  GROUP BY 1
),
calendar AS (
  SELECT date FROM UNNEST(
    GENERATE_DATE_ARRAY(DATE '2026-01-01', DATE '2026-01-31')
  ) AS date
),
filled AS (
  SELECT c.date, COALESCE(d.dau, 0) AS dau
  FROM calendar c LEFT JOIN daily d USING (date)
)
SELECT date, dau,
       IF(COUNT(*) OVER w = 7, AVG(dau) OVER w, NULL) AS dau_7day_avg
FROM filled
WINDOW w AS (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ORDER BY date;