jetstream/sponsored-suggestion-placement.toml

[experiment]

segments = ["exposed", "not_exposed"]


[metrics]

overall = [
    # Legacy SERP
    "legacy_serps_with_ads",
    "legacy_ad_clicks",
    "adm_impressions",
    # Glean
    "adm_clicks",
    "adm_impression_rate",
    "adm_ctr",
    "serp_impressions",
    "serp_impressions_with_ads",
    "serp_has_ad_click",
    "serp_ad_clicks",
    "serp_ad_impression_rate",
    "serp_ad_ctr",
    "serp_ad_click_rate",
    # Glean session-based
    "urlbar_sessions",
    "urlbar_clicks_per_session",
    "serps_per_session",
    "serps_with_ads_per_session",
    "ad_clicks_per_session",
    "adm_impressions_per_session",
    "adm_clicks_per_session",
    # Exposed metrics
    "exposed_urlbar_sessions",
    "exposure_rate",
    "exposed_search_engine_clicks",
    "exposed_search_engine_rate",
    "exposed_serp_impressions",
    "exposed_serp_impressions_with_ads",
    "exposed_serp_has_ad_click",
    "exposed_serp_ad_clicks",
    "exposed_serp_ad_impression_rate",
    "exposed_serp_ad_ctr",
    "exposed_serp_ad_click_rate",
    # Exposed session-based
    "exposed_urlbar_clicks_per_session",
    "exposed_serps_per_session",
    "exposed_serps_with_ads_per_session",
    "exposed_ad_clicks_per_session",
    "exposed_adm_impressions_per_session",
    "exposed_adm_clicks_per_session",
]


#----------------------------------------------

# Legacy SERP metrics

[metrics.legacy_serps_with_ads]
select_expression = """{{agg_sum("CASE WHEN normalized_engine = 'Google' THEN search_with_ads ELSE 0 END")}}"""
data_source = "search_clients_engines_sources_daily"
friendly_name = "Legacy SERPs with ads"
description = "Tagged Google SERP page loads with ads loaded from Legacy Telemetry"

[metrics.legacy_serps_with_ads.statistics.bootstrap_mean]
[metrics.legacy_serps_with_ads.statistics.deciles]

[metrics.legacy_ad_clicks]
select_expression = """{{agg_sum("CASE WHEN normalized_engine = 'Google' THEN ad_click ELSE 0 END")}}"""
data_source = "search_clients_engines_sources_daily"
friendly_name = "Legacy ad clicks"
description = "Ad clicks on tagged Google SERPs from Legacy Telemetry"

[metrics.legacy_ad_clicks.statistics.bootstrap_mean]
[metrics.legacy_ad_clicks.statistics.deciles]

#----------------------------------------------

# Glean metrics: sponsored sugestions

[metrics.adm_impressions]
select_expression = "COUNT(DISTINCT IF(is_terminal AND result.product_result_type = 'admarketplace_sponsored', event_id, NULL))"
data_source = "urlbar_events_unnested_results"
friendly_name = "Sponsored suggestion impressions"
description = "Number of sponsored suggestion impressions at engagement time"

[metrics.adm_impressions.statistics.bootstrap_mean]
[metrics.adm_impressions.statistics.deciles]

[metrics.adm_clicks]
select_expression = """COUNTIF(
  is_terminal
  AND event_action = 'engaged'
  AND product_selected_result = 'admarketplace_sponsored'
)"""
data_source = "urlbar_events"
friendly_name = "Sponsored suggestion clicks"
description = "Number of clicks on sponsored suggestion results"

[metrics.adm_clicks.statistics.bootstrap_mean]
[metrics.adm_clicks.statistics.deciles]

[metrics.adm_impression_rate]
depends_on = ["adm_impressions", "urlbar_impressions"]
friendly_name = "Sponsored suggestion impression rate"
description = "Proportion of urlbar sessions with a sponsored suggestion impression"

[metrics.adm_impression_rate.statistics.population_ratio]
numerator = "adm_impressions"
denominator = "urlbar_impressions"

[metrics.adm_ctr]
depends_on = ["adm_clicks", "adm_impressions"]
friendly_name = "Sponsored suggestion CTR"
description = "Proportion of urlbar sessions with a sponsored suggestion impression where a sponsored suggestion was clicked"

[metrics.adm_ctr.statistics.population_ratio]
numerator = "adm_clicks"
denominator = "adm_impressions"

#----------------------------------------------

# Glean metrics: SERP

[metrics.serp_impressions]
select_expression = "COUNT(DISTINCT impression_id)"
data_source = "tagged_google_serp_events"
friendly_name = "Google SERP loads"
description = "Tagged Google SERP page loads"

[metrics.serp_impressions.statistics.bootstrap_mean]
[metrics.serp_impressions.statistics.deciles]

[metrics.serp_impressions_with_ads]
select_expression = "COUNT(DISTINCT IF(ad_component AND num_ads_loaded_reported > 0, impression_id, NULL))"
data_source = "tagged_google_serp_events"
friendly_name = "SERPs with ads"
description = "Tagged Google SERP page loads with ads loaded"

[metrics.serp_impressions_with_ads.statistics.bootstrap_mean]
[metrics.serp_impressions_with_ads.statistics.deciles]

[metrics.serp_has_ad_click]
select_expression = "COUNT(DISTINCT IF(ad_component AND num_ads_showing > 0 AND num_clicks > 0, impression_id, NULL))"
data_source = "tagged_google_serp_events"
friendly_name = "SERPs with ad clicks"
description = "Tagged Google SERP page loads where an ad was clicked"

[metrics.serp_has_ad_click.statistics.bootstrap_mean]
[metrics.serp_has_ad_click.statistics.deciles]

[metrics.serp_ad_clicks]
select_expression = "SUM(IF(ad_component and num_ads_showing > 0, num_clicks, 0))"
data_source = "tagged_google_serp_events"
friendly_name = "SERP ad clicks"
description = "Ad clicks on tagged Google SERPs"

[metrics.serp_ad_clicks.statistics.bootstrap_mean]
[metrics.serp_ad_clicks.statistics.deciles]

[metrics.serp_ad_impression_rate]
depends_on = ["serp_impressions_with_ads", "serp_impressions"]
friendly_name = "SERP ad impression rate"
description = "Proportion of tagged Google SERP page loads with loaded ads"

[metrics.serp_ad_impression_rate.statistics.population_ratio]
numerator = "serp_impressions_with_ads"
denominator = "serp_impressions"

[metrics.serp_ad_ctr]
depends_on = ["serp_has_ad_click", "serp_impressions_with_ads"]
friendly_name = "SERP ad CTR"
description = "Proportion of tagged Google SERP page loads with ads loaded that had an ad click"

[metrics.serp_ad_ctr.statistics.population_ratio]
numerator = "serp_has_ad_click"
denominator = "serp_impressions_with_ads"

[metrics.serp_ad_click_rate]
depends_on = ["serp_ad_clicks", "serp_impressions"]
friendly_name = "SERP ad click rate"
description = "Number of ad clicks per tagged Google SERP page load"
exposure_basis = ["enrollments"]

[metrics.serp_ad_click_rate.statistics.population_ratio]
numerator = "serp_ad_clicks"
denominator = "serp_impressions"

#----------------------------------------------

# Session-based Glean metrics

[metrics.urlbar_sessions]
select_expression = "COUNTIF(new_session_start)"
data_source = "urlbar_search_sessions"
friendly_name = "Urlbar search sessions"
description = "Number of urlbar search sessions combining urlbar and serp events"

[metrics.urlbar_sessions.statistics.bootstrap_mean]
[metrics.urlbar_sessions.statistics.deciles]

[metrics.urlbar_clicks_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(urlbar_fields.event_action = 'engaged'),
    COUNTIF(new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Urlbar clicks per search session"
description = "Per-client proportion of urlbar search sessions with a click on any urlbar result"

[metrics.urlbar_clicks_per_session.statistics.bootstrap_mean]
[metrics.urlbar_clicks_per_session.statistics.deciles]

[metrics.serps_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(is_serp),
    COUNTIF(new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "SERP impressions per search session"
description = "Per-client rate of tagged Google SERP impressions per urlbar search session"

[metrics.serps_per_session.statistics.bootstrap_mean]
[metrics.serps_per_session.statistics.deciles]

[metrics.serps_with_ads_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(serp_fields.has_ads_loaded),
    COUNTIF(new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "SERP impressions with ads per search session"
description = "Per-client rate of tagged Google SERP impressions with ads loaded per urlbar search session"

[metrics.serps_with_ads_per_session.statistics.bootstrap_mean]
[metrics.serps_with_ads_per_session.statistics.deciles]

[metrics.ad_clicks_per_session]
select_expression = """SAFE_DIVIDE(
    COALESCE(SUM(serp_fields.num_ad_clicks), 0),
    COUNTIF(new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Ad clicks per search session"
description = "Per-client rate of SERP ad clicks per urlbar search session"

[metrics.ad_clicks_per_session.statistics.bootstrap_mean]
[metrics.ad_clicks_per_session.statistics.deciles]

[metrics.adm_impressions_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(urlbar_fields.has_sponsored_impression),
    COUNTIF(new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Sponsored suggestion impressions per search session"
description = "Per-client rate of sponsored suggestion impressions per urlbar search session"

[metrics.adm_impressions_per_session.statistics.bootstrap_mean]
[metrics.adm_impressions_per_session.statistics.deciles]

[metrics.adm_clicks_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(urlbar_fields.event_action = 'engaged' AND urlbar_fields.product_selected_result = 'admarketplace_sponsored'),
    COUNTIF(new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Sponsored suggestion clicks per search session"
description = "Per-client rate of sponsored suggestion clicks per urlbar search session"

[metrics.adm_clicks_per_session.statistics.bootstrap_mean]
[metrics.adm_clicks_per_session.statistics.deciles]


#----------------------------------------------

# Exposed Glean metrics

[metrics.exposed_urlbar_sessions]
select_expression = "COUNTIF(exposed_session AND new_session_start)"
data_source = "urlbar_search_sessions"
friendly_name = "Exposed urlbar sessions"
description = "Number of urlbar sessions exposed to sponsored suggestions"

[metrics.exposed_urlbar_sessions.statistics.bootstrap_mean]
[metrics.exposed_urlbar_sessions.statistics.deciles]

[metrics.exposure_rate]
depends_on = ["exposed_urlbar_sessions", "urlbar_sessions"]
friendly_name = "Urlbar session exposure rate"
description = "Proportion of urlbar search sessions exposed to sponsored suggestions"

[metrics.exposure_rate.statistics.population_ratio]
numerator = "exposed_urlbar_sessions"
denominator = "urlbar_sessions"

[metrics.exposed_search_engine_clicks]
select_expression = """COUNTIF(
  exposed_session
  AND urlbar_fields.event_action = 'engaged'
  AND urlbar_fields.product_selected_result IN ('default_partner_search_suggestion', 'search_engine', 'trending_suggestion')
)"""
data_source = "urlbar_search_sessions"
description = "Number of exposed urlbar sessions ending with a click leading to a SERP"
friendly_name = "Exposed urlbar sessions ending on a SERP"

[metrics.exposed_search_engine_clicks.statistics.bootstrap_mean]
[metrics.exposed_search_engine_clicks.statistics.deciles]

[metrics.exposed_search_engine_rate]
depends_on = ["exposed_search_engine_clicks", "exposed_urlbar_sessions"]
friendly_name = "Exposed SERP engagement rate"
description = "Proportion of exposed urlbar sessions ending with a click leading to a SERP"

[metrics.exposed_search_engine_rate.statistics.population_ratio]
numerator = "exposed_search_engine_clicks"
denominator = "exposed_urlbar_sessions"

[metrics.exposed_serp_impressions]
select_expression = "COUNTIF(exposed_session AND is_serp)"
data_source = "urlbar_search_sessions"
friendly_name = "Exposed SERP impressions"
description = "Number of tagged Google SERP page loads coming following an exposure"

[metrics.exposed_serp_impressions.statistics.bootstrap_mean]
[metrics.exposed_serp_impressions.statistics.deciles]

[metrics.exposed_serp_impressions_with_ads]
select_expression = "COUNTIF(exposed_session AND serp_fields.has_ads_loaded)"
data_source = "urlbar_search_sessions"
friendly_name = "Exposed SERPs with ads"
description = "Number of tagged Google SERP page loads with visible ads following an exposure"

[metrics.exposed_serp_impressions_with_ads.statistics.bootstrap_mean]
[metrics.exposed_serp_impressions_with_ads.statistics.deciles]

[metrics.exposed_serp_has_ad_click]
select_expression = "COUNTIF(exposed_session AND serp_fields.has_ad_click)"
data_source = "urlbar_search_sessions"
friendly_name = "Exposed SERPs with ad clicks"
description = "Number of tagged Google SERP page loads where an ad was clicked following an exposure"

[metrics.exposed_serp_has_ad_click.statistics.bootstrap_mean]
[metrics.exposed_serp_has_ad_click.statistics.deciles]

[metrics.exposed_serp_ad_clicks]
select_expression = "COALESCE(SUM(IF(exposed_session, serp_fields.num_ad_clicks, 0)),0)"
data_source = "urlbar_search_sessions"
friendly_name = "Exposed SERP ad clicks"
description = "Number of ad clicks on tagged Google SERPs following an exposure"

[metrics.exposed_serp_ad_clicks.statistics.bootstrap_mean]
[metrics.exposed_serp_ad_clicks.statistics.deciles]

[metrics.exposed_serp_ad_impression_rate]
depends_on = ["exposed_serp_impressions_with_ads", "exposed_serp_impressions"]
friendly_name = "Exposed SERP ad impression rate"
description = "Proportion of tagged Google SERP page loads with visible ads following an exposure"

[metrics.exposed_serp_ad_impression_rate.statistics.population_ratio]
numerator = "exposed_serp_impressions_with_ads"
denominator = "exposed_serp_impressions"

[metrics.exposed_serp_ad_ctr]
depends_on = ["exposed_serp_has_ad_click", "exposed_serp_impressions_with_ads"]
friendly_name = "Exposed SERP ad CTR"
description = "Proportion of tagged Google SERP page loads with visible ads that had an ad click following an exposure"

[metrics.exposed_serp_ad_ctr.statistics.population_ratio]
numerator = "exposed_serp_has_ad_click"
denominator = "exposed_serp_impressions_with_ads"

[metrics.exposed_serp_ad_click_rate]
depends_on = ["exposed_serp_ad_clicks", "exposed_serp_impressions"]
friendly_name = "Exposed SERP ad click rate"
description = "Number of ad clicks per exposed tagged Google SERP page load"

[metrics.exposed_serp_ad_click_rate.statistics.population_ratio]
numerator = "exposed_serp_ad_clicks"
denominator = "exposed_serp_impressions"

#----------------------------------------------

# Exposed session-based Glean metrics

[metrics.exposed_urlbar_clicks_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(exposed_session AND urlbar_fields.event_action = 'engaged'),
    COUNTIF(exposed_session AND new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Urlbar clicks per exposed search session"
description = "Per-client proportion of exposed urlbar search sessions with a click on any urlbar result"

[metrics.exposed_urlbar_clicks_per_session.statistics.bootstrap_mean]
[metrics.exposed_urlbar_clicks_per_session.statistics.deciles]

[metrics.exposed_serps_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(exposed_session AND is_serp),
    COUNTIF(exposed_session AND new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "SERP impressions per exposed search session"
description = "Per-client rate of tagged Google SERP impressions per exposed urlbar search session"

[metrics.exposed_serps_per_session.statistics.bootstrap_mean]
[metrics.exposed_serps_per_session.statistics.deciles]

[metrics.exposed_serps_with_ads_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(exposed_session AND serp_fields.has_ads_loaded),
    COUNTIF(exposed_session AND new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "SERP impressions with ads per exposed search session"
description = "Per-client rate of tagged Google SERP impressions with ads loaded per exposed urlbar search session"

[metrics.exposed_serps_with_ads_per_session.statistics.bootstrap_mean]
[metrics.exposed_serps_with_ads_per_session.statistics.deciles]

[metrics.exposed_ad_clicks_per_session]
select_expression = """SAFE_DIVIDE(
    COALESCE(SUM(IF(exposed_session, serp_fields.num_ad_clicks, 0)),0),
    COUNTIF(exposed_session AND new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Ad clicks per exposed search session"
description = "Per-client rate of SERP ad clicks per exposed urlbar search session"

[metrics.exposed_ad_clicks_per_session.statistics.bootstrap_mean]
[metrics.exposed_ad_clicks_per_session.statistics.deciles]

[metrics.exposed_adm_impressions_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(exposed_session AND urlbar_fields.has_sponsored_impression),
    COUNTIF(exposed_session AND new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Sponsored suggestion impressions per exposed search session"
description = "Per-client rate of sponsored suggestion impressions per exposed urlbar search session"

[metrics.exposed_adm_impressions_per_session.statistics.bootstrap_mean]
[metrics.exposed_adm_impressions_per_session.statistics.deciles]

[metrics.exposed_adm_clicks_per_session]
select_expression = """SAFE_DIVIDE(
    COUNTIF(
        exposed_session
        AND urlbar_fields.event_action = 'engaged'
        AND urlbar_fields.product_selected_result = 'admarketplace_sponsored'
    ),
    COUNTIF(exposed_session AND new_session_start)
)"""
data_source = "urlbar_search_sessions"
friendly_name = "Sponsored suggestion clicks per exposed search session"
description = "Per-client rate of sponsored suggestion clicks per exposed urlbar search session"

[metrics.exposed_adm_clicks_per_session.statistics.bootstrap_mean]
[metrics.exposed_adm_clicks_per_session.statistics.deciles]


[segments]

[segments.exposed]
select_expression = '{{agg_any("is_exposure")}}'
data_source = "urlbar_exposures"
friendly_name = "Exposed clients"
description = "Clients that saw a sponsored suggestion"

[segments.not_exposed]
select_expression = 'NOT {{agg_any("is_exposure")}}'
data_source = "urlbar_exposures"
friendly_name = "Non-exposed clients"
description = "Clients that never saw a sponsored suggestion"

[segments.data_sources.urlbar_exposures]
from_expression = """(
    SELECT 
      metrics.uuid.legacy_telemetry_client_id AS client_id,
      DATE(submission_timestamp) AS submission_date,
      event.name = 'exposure' AS is_exposure,
    FROM `mozdata.firefox_desktop.events`
      CROSS JOIN UNNEST(events) AS event
    WHERE event.category = 'urlbar'
)"""
friendly_name = "Urlbar exposures"
description = "Glean urlbar exposure events"
window_start = 0
window_end = 21


[data_sources]

[data_sources.tagged_google_serp_events]
from_expression = """(
    SELECT
        * EXCEPT (is_ad_component),
        component IN ('ad_carousel', 'ad_image_row', 'ad_link', 'ad_sidebar',
        'ad_sitelink') AS ad_component
    FROM `mozdata.firefox_desktop.serp_events`
    WHERE search_engine = 'google' AND is_tagged
)"""
client_id_column = "legacy_telemetry_client_id"
experiments_column_type = "native"
submission_date_column = "submission_date"
friendly_name = "Glean Google tagged SERP events"
description = "Glean SERP events aggregated by impression, component for tagged Google search"

[data_sources.urlbar_search_sessions]
friendly_name = "Urlbar search sessions"
description = "Search sessions initiated in the urlbar including urlbar and tagged Google serp Glean events"
client_id_column = "legacy_telemetry_client_id"
submission_date_column = "submission_date"
experiments_column_type = "native"
from_expression = """(
    WITH exposures AS (
      -- urlbar.exposure events sequence info
      SELECT
        client_info.client_id AS glean_client_id,
        ping_info.seq,
        event_timestamp,
        TRUE AS exposed,
      FROM
        `mozdata.firefox_desktop.events_unnested`
      WHERE
        event_category = 'urlbar' AND event_name = 'exposure'
        AND DATE(submission_timestamp) BETWEEN '2023-11-02' AND '2023-12-01'
        AND mozfun.map.get_key(ping_info.experiments, 'sponsored-suggestion-placement') IS NOT NULL
      -- deduplicate exposure events
      GROUP BY
        1, 2, 3, 4
    ),
    urlbar AS (
      -- terminal urlbar events (urlbar sessions)
      SELECT
        *
      FROM
        `mozdata.firefox_desktop.urlbar_events`
      WHERE
        is_terminal
        AND submission_date BETWEEN '2023-11-02' AND '2023-12-01'
        AND mozfun.map.get_key(experiments, 'sponsored-suggestion-placement') IS NOT NULL
    ),
    urlbar_joined AS (
      -- interleave exposure events with main urlbar events
      -- most exposure events have the same event_timestamp as the corresponding urlbar event
      -- some have an earlier timestamp - these will have a row with urlbar fields null and exposed = true
      SELECT
        glean_client_id,
        seq,
        event_timestamp,
        exposures.exposed,
        urlbar.* EXCEPT (glean_client_id, seq, event_timestamp),
        LAG(STRUCT(exposed, event_name)) OVER (PARTITION BY glean_client_id ORDER BY seq, event_timestamp) AS prev
      FROM
        urlbar
      FULL OUTER JOIN
        exposures
      USING
        (glean_client_id, seq, event_timestamp)
    ),
    urlbar_with_exposed AS (
      -- tag exposed urlbar events and drop exposure events
      SELECT
        * EXCEPT (prev, exposed),
        -- exposed if urlbar event has exposed = true (exposure event had same timestamp)
        -- or the previous event was an exposure
        -- otherwise both of these will be null
        COALESCE(exposed, prev.exposed AND prev.event_name IS NULL, FALSE) AS exposed
      FROM
        urlbar_joined
      WHERE
        -- drop exposure events
        event_name IS NOT NULL
    ),
    serp AS (
      -- serp events, aggregated to 1 row per impression_id
      -- only keep tagged Google searches
      SELECT
        glean_client_id,
        ping_seq AS seq,
        event_timestamp,
        sap_source,
        legacy_telemetry_client_id,
        impression_id,
        submission_date,
        ANY_VALUE(experiments) AS experiments,
        LOGICAL_OR(ad_component AND num_ads_loaded_reported > 0) AS has_ads_loaded,
        LOGICAL_OR(ad_component AND num_ads_showing > 0 AND num_clicks > 0) AS has_ad_click,
        SUM(IF(ad_component AND num_ads_showing > 0, num_clicks, 0)) AS num_ad_clicks,
      FROM (
        -- redefine ad componet to exclude non-monetized components
        SELECT
          * EXCEPT (is_ad_component),
          component IN ('ad_carousel', 'ad_image_row', 'ad_link', 'ad_sidebar', 'ad_sitelink') AS ad_component
        FROM
          `mozdata.firefox_desktop.serp_events`
        WHERE
          submission_date BETWEEN '2023-11-02' AND '2023-12-01'
          AND mozfun.map.get_key(experiments, 'sponsored-suggestion-placement') IS NOT NULL
      )
      WHERE
        search_engine = 'google'
        AND is_tagged
      GROUP BY
        1, 2, 3, 4, 5, 6, 7
    ),
    combined AS (
      -- combine urlbar and serp events into single table
      -- keep only relevant fields from each
      SELECT
        glean_client_id,
        seq,
        event_timestamp,
        legacy_telemetry_client_id,
        submission_date,
        experiments,
        FALSE AS is_serp,
        STRUCT(
          exposed,
          event_action,
          product_selected_result,
          (
            SELECT
              COUNTIF(product_result_type = 'admarketplace_sponsored') > 0
            FROM
              UNNEST(results)
          ) AS has_sponsored_impression
        ) AS urlbar_fields,
        NULL AS serp_fields,
      FROM
        urlbar_with_exposed
      UNION ALL (
        SELECT
          glean_client_id,
          seq,
          event_timestamp,
          legacy_telemetry_client_id,
          submission_date,
          experiments,
          TRUE AS is_serp,
          NULL AS urlbar_fields,
          STRUCT(
            sap_source,
            has_ads_loaded,
            has_ad_click,
            num_ad_clicks
          ) AS serp_fields
        FROM
          serp
      )
    ),
    sessions AS (
      -- group events into 'search sessions'
      -- sessions start with either a urlbar event or a SAP-sourced serp load
      -- and include subsequent follow-on-type serp loads
      SELECT
        *,
        COUNTIF(new_session_start) OVER (
          PARTITION BY glean_client_id ORDER BY seq, event_timestamp
        ) AS client_session_id,
      FROM (
        SELECT
          *,
          NOT is_serp OR serp_fields.sap_source IN ('searchbar', 'contextmenu', 'webextension', 'system') AS new_session_start
        FROM
          combined
      )
    ),
    urlbar_sessions AS (
      -- restrict to sessions starting from the urlbar
      SELECT
        *,
        -- add session-level exposure indicator
        FIRST_VALUE(urlbar_fields.exposed IGNORE NULLS) OVER (
          PARTITION BY glean_client_id, client_session_id ORDER BY seq, event_timestamp
        ) AS exposed_session
      FROM
        sessions
      WHERE
        -- drop partial sessions at the start of a window
        client_session_id > 0
      QUALIFY
        COUNTIF(new_session_start AND NOT is_serp) OVER (
          PARTITION BY glean_client_id, client_session_id
        ) > 0
    )
    SELECT
      *
    FROM
      urlbar_sessions
)"""