> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Funnel

> Funnel conversion, step drop-off, and conversion path analysis queries.

<Tip>
  Use the same query standards from the [SQL Query Library overview](/data-activation/template-resources/sql-query-library): `is_order_sm_valid = TRUE` for order analyses, `sm_store_id` scoping for multi-store setups, and `your_project` placeholders.
</Tip>

<AccordionGroup>
  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```

    <Info>
      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`](/data-activation/data-tables/sm_transformed_v2/rpt_funnel_events_performance_hourly).
    </Info>
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```

    <Info>
      These are **event-based** counts and ratios (not user-based). Treat them as directional monitoring signals, not conversion attribution.
    </Info>
  </Accordion>

  <Accordion title="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.

    ```sql theme={null}
    -- 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;
    ```

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

***

## Related Categories

Back to the [SQL Query Library](/data-activation/template-resources/sql-query-library).

<CardGroup cols={2}>
  <Card title="Journeys & Lead Capture" icon="route" href="/data-activation/template-resources/sql-query-library/journeys-and-lead-capture">
    Extend step-level funnel analysis into touchpoint journeys.
  </Card>

  <Card title="Orders & Revenue" icon="dollar-sign" href="/data-activation/template-resources/sql-query-library/orders-and-revenue">
    Connect funnel movement to commercial outcomes.
  </Card>

  <Card title="Attribution & Data Health" icon="heart-pulse" href="/data-activation/template-resources/sql-query-library/attribution-and-data-health">
    Check tracking and coverage before diagnosing drop-offs.
  </Card>
</CardGroup>