Funnel

Daily session-based funnel conversion (last 30 days)

What you’ll learn: Daily session-level funnel conversion rates (view item → add to cart → begin checkout → purchase) using distinct-session denominators. Use this for “conversion rate” questions.

-- Assumptions: timeframe=last_30_days | metric=session_funnel_conversion_rates | grain=date | scope=sessions_with_any_funnel_activity
WITH events AS (
  SELECT
    DATE(event_local_datetime) AS date,
    COALESCE(event_user_session_id, event_session_id) AS session_id,
    sm_event_name
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND DATE(event_local_datetime) < CURRENT_DATE()
    AND (event_user_session_id IS NOT NULL OR event_session_id IS NOT NULL)
    AND sm_event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),
session_daily AS (
  SELECT
    date,
    session_id,
    MAX(IF(sm_event_name = 'view_item', 1, 0)) AS has_view_item,
    MAX(IF(sm_event_name = 'add_to_cart', 1, 0)) AS has_add_to_cart,
    MAX(IF(sm_event_name = 'begin_checkout', 1, 0)) AS has_begin_checkout,
    MAX(IF(sm_event_name = 'purchase', 1, 0)) AS has_purchase
  FROM events
  GROUP BY 1, 2
)
SELECT
  date,
  COUNT(*) AS sessions,
  SUM(has_view_item) AS sessions_with_view_item,
  SUM(has_add_to_cart) AS sessions_with_add_to_cart,
  SUM(has_begin_checkout) AS sessions_with_begin_checkout,
  SUM(has_purchase) AS sessions_with_purchase,
  -- Step conversion rates use *session intersections* so they stay within [0, 1] even if intermediate events are under-tracked.
  SAFE_DIVIDE(
    SUM(IF(has_view_item = 1 AND has_add_to_cart = 1, 1, 0)),
    NULLIF(SUM(has_view_item), 0)
  ) AS view_to_cart_rate,
  SAFE_DIVIDE(
    SUM(IF(has_add_to_cart = 1 AND has_begin_checkout = 1, 1, 0)),
    NULLIF(SUM(has_add_to_cart), 0)
  ) AS cart_to_checkout_rate,
  SAFE_DIVIDE(
    SUM(IF(has_begin_checkout = 1 AND has_purchase = 1, 1, 0)),
    NULLIF(SUM(has_begin_checkout), 0)
  ) AS checkout_to_purchase_rate,
  SAFE_DIVIDE(SUM(has_purchase), NULLIF(COUNT(*), 0)) AS session_conversion_rate
FROM session_daily
GROUP BY 1
ORDER BY date;

Top pages by on-page add-to-cart session rate (last 7 days)

What you’ll learn: Which pages have the highest share of sessions that triggered an add-to-cart event on that same page path. Useful for identifying strong product pages/collections and debugging low-performing pages.

-- Assumptions: timeframe=last_7_days | metric=on_page_add_to_cart_session_rate | grain=event_page_path | scope=sessions_with_page_path
WITH base AS (
  SELECT
    event_page_path,
    COALESCE(event_user_session_id, event_session_id) AS session_id,
    sm_event_name
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    AND DATE(event_local_datetime) < CURRENT_DATE()
    AND event_page_path IS NOT NULL
    AND TRIM(event_page_path) != ''
    AND (event_user_session_id IS NOT NULL OR event_session_id IS NOT NULL)
    AND sm_event_name IN ('page_view', 'add_to_cart')
),
page_session AS (
  SELECT
    event_page_path,
    session_id,
    MAX(IF(sm_event_name = 'page_view', 1, 0)) AS has_page_view,
    MAX(IF(sm_event_name = 'add_to_cart', 1, 0)) AS has_add_to_cart
  FROM base
  GROUP BY 1, 2
)
SELECT
  event_page_path,
  COUNTIF(has_page_view = 1) AS sessions_with_page_view,
  COUNTIF(has_page_view = 1 AND has_add_to_cart = 1) AS sessions_with_add_to_cart_on_page,
  SAFE_DIVIDE(
    COUNTIF(has_page_view = 1 AND has_add_to_cart = 1),
    NULLIF(COUNTIF(has_page_view = 1), 0)
  ) AS add_to_cart_session_rate
FROM page_session
GROUP BY 1
HAVING sessions_with_page_view >= 500
ORDER BY add_to_cart_session_rate DESC
LIMIT 25;

Funnel conversion by UTM source/medium (last 30 days)

What you’ll learn: How different acquisition sources/mediums perform through a session-based funnel (distinct-session denominators). This is the recommended pattern for “conversion rate by channel” questions.

-- Assumptions: timeframe=last_30_days | metric=session_funnel_conversion_rates | grain=utm_source_medium | scope=sessions_with_any_funnel_activity
-- Notes:
-- - Uses DISTINCT sessions (not event-count ratios)
-- - UTM source/medium is attributed to the session using the earliest event in that session
WITH events AS (
  SELECT
    event_local_datetime,
    COALESCE(event_user_session_id, event_session_id) AS session_id,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS utm_medium,
    sm_event_name,
    event_order_revenue
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND DATE(event_local_datetime) < CURRENT_DATE()
    AND (event_user_session_id IS NOT NULL OR event_session_id IS NOT NULL)
),
session_utm AS (
  SELECT
    session_id,
    ARRAY_AGG(utm_source ORDER BY event_local_datetime ASC LIMIT 1)[OFFSET(0)] AS utm_source,
    ARRAY_AGG(utm_medium ORDER BY event_local_datetime ASC LIMIT 1)[OFFSET(0)] AS utm_medium
  FROM events
  GROUP BY 1
),
session_steps AS (
  SELECT
    session_id,
    MAX(IF(sm_event_name = 'view_item', 1, 0)) AS has_view_item,
    MAX(IF(sm_event_name = 'add_to_cart', 1, 0)) AS has_add_to_cart,
    MAX(IF(sm_event_name = 'begin_checkout', 1, 0)) AS has_begin_checkout,
    MAX(IF(sm_event_name = 'purchase', 1, 0)) AS has_purchase,
    SUM(IF(sm_event_name = 'purchase', COALESCE(event_order_revenue, 0), 0)) AS purchase_revenue
  FROM events
  GROUP BY 1
),
by_channel AS (
  SELECT
    CONCAT(u.utm_source, ' / ', u.utm_medium) AS utm_source_medium,
    s.has_view_item,
    s.has_add_to_cart,
    s.has_begin_checkout,
    s.has_purchase,
    s.purchase_revenue
  FROM session_steps s
  JOIN session_utm u USING (session_id)
)
SELECT
  utm_source_medium,
  COUNT(*) AS sessions,
  SUM(has_view_item) AS sessions_with_view_item,
  SUM(has_add_to_cart) AS sessions_with_add_to_cart,
  SUM(has_begin_checkout) AS sessions_with_begin_checkout,
  SUM(has_purchase) AS sessions_with_purchase,
  -- Step conversion rates use *session intersections* so they stay within [0, 1] even if intermediate events are under-tracked.
  SAFE_DIVIDE(
    SUM(IF(has_view_item = 1 AND has_add_to_cart = 1, 1, 0)),
    NULLIF(SUM(has_view_item), 0)
  ) AS view_to_cart_rate,
  SAFE_DIVIDE(
    SUM(IF(has_add_to_cart = 1 AND has_begin_checkout = 1, 1, 0)),
    NULLIF(SUM(has_add_to_cart), 0)
  ) AS cart_to_checkout_rate,
  SAFE_DIVIDE(
    SUM(IF(has_begin_checkout = 1 AND has_purchase = 1, 1, 0)),
    NULLIF(SUM(has_begin_checkout), 0)
  ) AS checkout_to_purchase_rate,
  SAFE_DIVIDE(SUM(has_purchase), NULLIF(COUNT(*), 0)) AS session_conversion_rate,
  SUM(purchase_revenue) AS total_revenue
FROM by_channel
WHERE has_view_item = 1 OR has_add_to_cart = 1 OR has_begin_checkout = 1 OR has_purchase = 1
GROUP BY 1
HAVING sessions >= 100
ORDER BY sessions_with_purchase DESC
LIMIT 25;

This is a session-based funnel. If you want near-real-time monitoring (hourly/daily step volumes and event-based ratios), use rpt_funnel_events_performance_hourly.

Funnel tracking health by event source system (last 30 days)

What you’ll learn: Whether one tracking source (source_system) appears to be missing critical steps (e.g., begin checkout) relative to other sources. This is a fast “do we have tracking regressions?” check.

-- Assumptions: timeframe=last_30_days | metric=funnel_step_event_counts | grain=source_system | scope=monitoring
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  SUM(page_view_event_count) AS page_views,
  SUM(view_item_event_count) AS view_item_events,
  SUM(add_to_cart_event_count) AS add_to_cart_events,
  SUM(begin_checkout_event_count) AS begin_checkout_events,
  SUM(purchase_event_count) AS purchase_events
FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
HAVING page_views >= 5000
ORDER BY page_views DESC;

Hourly funnel anomaly detector (hour-over-hour deltas, last 7 days)

What you’ll learn: Which tracking sources have unusually large hour-over-hour spikes/drops in purchases. Useful for catching instrumentation outages, batch backfills, or sudden traffic changes.

-- Assumptions: timeframe=last_7_days | metric=hour_over_hour_purchase_deltas | grain=hour+source_system | scope=anomaly_triage
WITH hourly AS (
  SELECT
    event_local_datetime AS hour_start,
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY 1, 2
),
scored AS (
  SELECT
    source_system,
    hour_start,
    purchase_events,
    LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start) AS prev_purchase_events,
    purchase_events - LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start) AS delta_purchase_events,
    SAFE_DIVIDE(
      purchase_events - LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start),
      NULLIF(LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start), 0)
    ) AS pct_change_purchase_events,
    event_order_revenue
  FROM hourly
)
SELECT
  source_system,
  hour_start,
  prev_purchase_events,
  purchase_events,
  delta_purchase_events,
  pct_change_purchase_events,
  event_order_revenue
FROM scored
WHERE prev_purchase_events >= 10
ORDER BY ABS(pct_change_purchase_events) DESC
LIMIT 50;

Lead-gen to purchase (email signups vs purchases) by UTM source/medium (last 30 days)

What you’ll learn: Which UTMs drive email signups and purchases (event-based). Useful for diagnosing “lots of leads, few purchases” vs “low leads, high purchases” sources.

-- Assumptions: timeframe=last_30_days | metric=email_signups+purchases | grain=utm_source_medium | scope=event_based
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS utm_medium,
    SUM(email_sign_up_event_count) AS email_signups,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1, 2
)
SELECT
  CONCAT(utm_source, ' / ', utm_medium) AS utm_source_medium,
  email_signups,
  purchase_events,
  event_order_revenue,
  SAFE_DIVIDE(event_order_revenue, NULLIF(purchase_events, 0)) AS revenue_per_purchase_event
FROM base
WHERE email_signups >= 100
ORDER BY email_signups DESC
LIMIT 50;

These are event-based counts and ratios (not user-based). Treat them as directional monitoring signals, not conversion attribution.

Cart drop-off signals (add-to-cart vs remove-from-cart vs checkout) trend (daily, last 30 days)

What you’ll learn: Whether remove-from-cart events are spiking relative to add-to-cart, and whether checkout initiation is dropping. Useful for diagnosing UX issues, tracking regressions, or promo-related cart behavior changes.

-- Assumptions: timeframe=last_30_days | metric=cart_drop_off_signals | grain=date | scope=event_based
WITH daily AS (
  SELECT
    DATE(event_local_datetime) AS date,
    SUM(add_to_cart_event_count) AS add_to_cart_events,
    SUM(remove_from_cart_event_count) AS remove_from_cart_events,
    SUM(begin_checkout_event_count) AS begin_checkout_events,
    SUM(purchase_event_count) AS purchase_events
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1
)
SELECT
  date,
  add_to_cart_events,
  remove_from_cart_events,
  begin_checkout_events,
  purchase_events
FROM daily
ORDER BY date;

Remove-from-cart can exceed add-to-cart in event terms (multi-item carts, repeated events). Focus on trend changes, not absolute levels.

Journeys & Lead Capture

Extend step-level funnel analysis into touchpoint journeys.

Orders & Revenue

Connect funnel movement to commercial outcomes.

Attribution & Data Health

Check tracking and coverage before diagnosing drop-offs.

Managed BI (Dashboards)

Managed Data Warehouse

Templates

Common Analyses

Journeys & Lead Capture

Orders & Revenue

Attribution & Data Health

​Related Categories

Journeys & Lead Capture

Orders & Revenue

Attribution & Data Health

Related Categories