Content Discovery-to-Play Rate

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "view_item_list",
          "eventAction": "content:impression",
          "type": "impression"
        },
        "category": {
          "primaryCategory": "content discovery"
        },
        "attributes": {
          "item_list_id": "rail-trending-now",
          "item_list_name": "Trending Now",
          "discovery_source": "recommendation",
          "discovery_surface": "home",
          "recommendation_model_variant": "ranker-v7-treatment",
          "items": [
            {
              "content_id": "series-204",
              "content_name": "Example Series",
              "genre": "drama",
              "content_type": "series",
              "position": 3,
              "content_series_id": "series-204",
              "entitlement_status": "entitled"
            },
            {
              "content_id": "title-12345",
              "content_name": "Example Film",
              "genre": "thriller",
              "content_type": "film",
              "position": 4,
              "content_series_id": null,
              "entitlement_status": "not_in_plan"
            }
          ]
        }
      },
      {
        "eventInfo": {
          "eventName": "video_start",
          "eventAction": "video:start",
          "type": "playback"
        },
        "category": {
          "primaryCategory": "content playback"
        },
        "attributes": {
          "content_id": "episode-204-02-05",
          "content_series_id": "series-204",
          "content_type": "episode",
          "play_trigger": "user_click",
          "video_title": "Example Series S2 E5",
          "video_provider": "example_player",
          "video_duration": 2700,
          "discovery_source": "recommendation",
          "discovery_surface": "home",
          "item_list_id": "rail-trending-now"
        }
      }
    ]
  }
}

{
  "view_item_list": {
    "event": "view_item_list",
    "discovery_source": "recommendation",
    "discovery_surface": "home",
    "recommendation_model_variant": "ranker-v7-treatment",
    "ecommerce": {
      "item_list_id": "rail-trending-now",
      "item_list_name": "Trending Now",
      "items": [
        {
          "item_id": "series-204",
          "item_name": "Example Series",
          "item_category": "drama",
          "item_variant": "series",
          "index": 3,
          "content_series_id": "series-204",
          "entitlement_status": "entitled"
        },
        {
          "item_id": "title-12345",
          "item_name": "Example Film",
          "item_category": "thriller",
          "item_variant": "film",
          "index": 4,
          "content_series_id": null,
          "entitlement_status": "not_in_plan"
        }
      ]
    }
  },
  "video_start": {
    "event": "video_start",
    "content_id": "episode-204-02-05",
    "content_series_id": "series-204",
    "content_type": "episode",
    "play_trigger": "user_click",
    "video_title": "Example Series S2 E5",
    "video_provider": "example_player",
    "video_duration": 2700,
    "discovery_source": "recommendation",
    "discovery_surface": "home",
    "item_list_id": "rail-trending-now"
  }
}

{
  "content:impression": {
    "event": "content:impression",
    "eventInfo": {
      "path": "content.list"
    },
    "content": {
      "list": {
        "item_list_id": "rail-trending-now",
        "item_list_name": "Trending Now",
        "discovery_source": "recommendation",
        "discovery_surface": "home",
        "recommendation_model_variant": "ranker-v7-treatment"
      },
      "items": [
        {
          "content_id": "series-204",
          "content_name": "Example Series",
          "genre": "drama",
          "content_type": "series",
          "position": 3,
          "content_series_id": "series-204",
          "entitlement_status": "entitled"
        },
        {
          "content_id": "title-12345",
          "content_name": "Example Film",
          "genre": "thriller",
          "content_type": "film",
          "position": 4,
          "content_series_id": null,
          "entitlement_status": "not_in_plan"
        }
      ]
    }
  },
  "video:start": {
    "event": "video:start",
    "eventInfo": {
      "path": "media.playback"
    },
    "media": {
      "playback": {
        "content_id": "episode-204-02-05",
        "content_series_id": "series-204",
        "content_type": "episode",
        "play_trigger": "user_click",
        "video_title": "Example Series S2 E5",
        "video_provider": "example_player",
        "video_duration": 2700,
        "discovery_source": "recommendation",
        "discovery_surface": "home",
        "item_list_id": "rail-trending-now"
      }
    }
  }
}

{
  "view_item_list": {
    "eventType": "commerce.productListViews",
    "commerce": {
      "productListViews": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "series-204",
        "name": "Example Series",
        "productCategories": [
          {
            "categoryName": "drama"
          }
        ],
        "_experience": {
          "analytics": {
            "customDimensions": {
              "eVars": {
                "eVar11": "series",
                "eVar12": "3",
                "eVar13": "series-204",
                "eVar14": "entitled"
              }
            }
          }
        }
      },
      {
        "SKU": "title-12345",
        "name": "Example Film",
        "productCategories": [
          {
            "categoryName": "thriller"
          }
        ],
        "_experience": {
          "analytics": {
            "customDimensions": {
              "eVars": {
                "eVar11": "film",
                "eVar12": "4",
                "eVar14": "not_in_plan"
              }
            }
          }
        }
      }
    ],
    "_tenant": {
      "discovery": {
        "listId": "rail-trending-now",
        "listName": "Trending Now",
        "discoverySource": "recommendation",
        "discoverySurface": "home",
        "recommendationModelVariant": "ranker-v7-treatment"
      }
    }
  },
  "video_start": {
    "eventType": "media.sessionStart",
    "mediaCollection": {
      "playhead": 0,
      "sessionDetails": {
        "name": "episode-204-02-05",
        "friendlyName": "Example Series S2 E5",
        "length": 2700,
        "contentType": "vod",
        "streamType": "video",
        "playerName": "example_player"
      }
    },
    "mediaTimed": {
      "primaryAssetReference": {
        "_id": "episode-204-02-05"
      },
      "starts": {
        "value": 1
      }
    },
    "_tenant": {
      "playback": {
        "contentId": "episode-204-02-05",
        "contentSeriesId": "series-204",
        "contentType": "episode",
        "playTrigger": "user_click",
        "discoverySource": "recommendation",
        "discoverySurface": "home",
        "listId": "rail-trending-now"
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Content Discovery-to-Play Rate = session x title exposures followed by a user-initiated video_start of the same title within 30 minutes / all eligible session x title exposures x 100
-- Set before running: reporting time zone and reporting date range (end the range at least 3 days ago so late video_start hits have landed)
DECLARE reporting_tz STRING DEFAULT 'America/New_York';
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 30 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE('America/New_York'), INTERVAL 3 DAY);
DECLARE play_window_micros INT64 DEFAULT 30 * 60 * 1000000;

WITH base AS (
  SELECT
    user_pseudo_id,
    event_name,
    event_timestamp,
    COALESCE(device.category, '(not set)') AS device_cat,
    items,
    (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id') AS ga_session_id,
    (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'traffic_type') AS traffic_type,
    (SELECT LOWER(ep.value.string_value) FROM UNNEST(event_params) AS ep WHERE ep.key = 'discovery_source') AS discovery_source,
    (SELECT NULLIF(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), '') FROM UNNEST(event_params) AS ep WHERE ep.key = 'content_id') AS content_id,
    (SELECT NULLIF(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), '') FROM UNNEST(event_params) AS ep WHERE ep.key = 'content_series_id') AS content_series_id,
    (SELECT LOWER(ep.value.string_value) FROM UNNEST(event_params) AS ep WHERE ep.key = 'content_type') AS content_type,
    (SELECT LOWER(ep.value.string_value) FROM UNNEST(event_params) AS ep WHERE ep.key = 'play_trigger') AS play_trigger
  FROM `your-project.analytics_123456789.events_*`
  -- Two extra export days on each side cover time zone shifts and plays that land after midnight
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(start_date, INTERVAL 2 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 2 DAY))
    AND event_name IN ('view_item_list', 'video_start')
),
scoped AS (
  SELECT
    *,
    CONCAT(user_pseudo_id, '.', CAST(ga_session_id AS STRING)) AS session_key
  FROM base
  WHERE user_pseudo_id IS NOT NULL
    AND ga_session_id IS NOT NULL
    -- Internal traffic flagged by the GA4 internal traffic rule (office IP ranges); known bots are removed by GA4 at collection
    AND COALESCE(traffic_type, '') <> 'internal'
),
exposure_items AS (
  -- One row per title tile impression; episodes roll up to the parent series through content_series_id
  SELECT
    s.session_key,
    s.event_timestamp,
    s.device_cat,
    s.discovery_source,
    COALESCE(
      (SELECT NULLIF(COALESCE(ip.value.string_value, CAST(ip.value.int_value AS STRING)), '') FROM UNNEST(i.item_params) AS ip WHERE ip.key = 'content_series_id'),
      i.item_id
    ) AS title_key,
    (SELECT LOWER(ip.value.string_value) FROM UNNEST(i.item_params) AS ip WHERE ip.key = 'entitlement_status') AS entitlement_status
  FROM scoped AS s
  CROSS JOIN UNNEST(s.items) AS i
  WHERE s.event_name = 'view_item_list'
    -- Discovery surfaces only: hero billboard and continue-watching sources are outside this list
    AND s.discovery_source IN ('search', 'browse', 'recommendation', 'editorial')
    AND COALESCE(LOWER(i.item_variant), '') NOT IN ('trailer', 'preview')
    AND i.item_id IS NOT NULL
),
exposures AS (
  -- Denominator grain: distinct session x title, timestamped at the first impression in the session
  SELECT
    session_key,
    title_key,
    MIN(event_timestamp) AS first_exposure_ts,
    ARRAY_AGG(STRUCT(device_cat, entitlement_status) ORDER BY event_timestamp LIMIT 1)[OFFSET(0)] AS first_ctx
  FROM exposure_items
  GROUP BY session_key, title_key
),
plays AS (
  -- User-initiated main-content starts only
  SELECT
    session_key,
    COALESCE(content_series_id, content_id) AS title_key,
    event_timestamp AS play_ts
  FROM scoped
  WHERE event_name = 'video_start'
    AND COALESCE(play_trigger, '') NOT IN ('autoplay', 'next_episode')
    AND COALESCE(content_type, '') NOT IN ('trailer', 'preview')
    AND COALESCE(content_series_id, content_id) IS NOT NULL
),
first_play AS (
  -- Numerator: first qualifying start of the same title in the same session, from the first exposure up to 30 minutes later; repeat starts count once
  SELECT
    e.session_key,
    e.title_key,
    MIN(p.play_ts) AS first_play_ts
  FROM exposures AS e
  JOIN plays AS p
    ON p.session_key = e.session_key
   AND p.title_key = e.title_key
   AND p.play_ts >= e.first_exposure_ts
   AND p.play_ts <= e.first_exposure_ts + play_window_micros
  GROUP BY e.session_key, e.title_key
),
title_level AS (
  SELECT
    e.session_key,
    e.title_key,
    DATE(TIMESTAMP_MICROS(e.first_exposure_ts), reporting_tz) AS report_date,
    e.first_ctx.device_cat AS device_cat,
    e.first_ctx.entitlement_status AS entitlement_status,
    fp.first_play_ts
  FROM exposures AS e
  LEFT JOIN first_play AS fp
    ON fp.session_key = e.session_key
   AND fp.title_key = e.title_key
),
credited_source AS (
  -- Breakdown credit: the last discovery source that exposed the title at or before the converting play
  SELECT
    t.session_key,
    t.title_key,
    ARRAY_AGG(x.discovery_source ORDER BY x.event_timestamp DESC LIMIT 1)[OFFSET(0)] AS credited_discovery_source
  FROM title_level AS t
  JOIN exposure_items AS x
    ON x.session_key = t.session_key
   AND x.title_key = t.title_key
  WHERE t.first_play_ts IS NOT NULL
    AND x.event_timestamp <= t.first_play_ts
  GROUP BY t.session_key, t.title_key
),
source_level AS (
  SELECT DISTINCT
    session_key,
    title_key,
    discovery_source
  FROM exposure_items
),
unified AS (
  -- Total rows: session x title grain
  SELECT
    report_date,
    'all_sources' AS discovery_source,
    device_cat,
    entitlement_status,
    CONCAT(session_key, '|', title_key) AS exposure_key,
    first_play_ts IS NOT NULL AS converted
  FROM title_level
  UNION ALL
  -- Breakdown rows: session x title x discovery source grain, so they do not sum to the total
  SELECT
    t.report_date,
    s.discovery_source,
    t.device_cat,
    t.entitlement_status,
    CONCAT(s.session_key, '|', s.title_key, '|', s.discovery_source) AS exposure_key,
    COALESCE(c.credited_discovery_source = s.discovery_source, FALSE) AS converted
  FROM source_level AS s
  JOIN title_level AS t
    ON t.session_key = s.session_key
   AND t.title_key = s.title_key
  LEFT JOIN credited_source AS c
    ON c.session_key = s.session_key
   AND c.title_key = s.title_key
)
SELECT
  report_date,
  discovery_source,
  IF(GROUPING(device_cat) = 1, 'all_devices', device_cat) AS device_category,
  COUNT(DISTINCT exposure_key) AS exposures,
  COUNT(DISTINCT IF(converted, exposure_key, NULL)) AS converted_exposures,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT IF(converted, exposure_key, NULL)), COUNT(DISTINCT exposure_key)) * 100, 2) AS content_discovery_to_play_rate,
  COUNT(DISTINCT IF(entitlement_status = 'entitled', exposure_key, NULL)) AS entitled_exposures,
  COUNT(DISTINCT IF(converted AND entitlement_status = 'entitled', exposure_key, NULL)) AS entitled_converted_exposures,
  ROUND(SAFE_DIVIDE(COUNT(DISTINCT IF(converted AND entitlement_status = 'entitled', exposure_key, NULL)), COUNT(DISTINCT IF(entitlement_status = 'entitled', exposure_key, NULL))) * 100, 2) AS entitled_discovery_to_play_rate
FROM unified
WHERE report_date BETWEEN start_date AND end_date
GROUP BY GROUPING SETS ((report_date, discovery_source), (report_date, discovery_source, device_cat))
ORDER BY 1, 2, 3;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Content Discovery-to-Play Rate = session x title exposures followed by a user-initiated media.sessionStart of the same title within 30 minutes / all eligible session x title exposures x 100
-- Set before running: dataset table name (web_experience_events = your Web SDK experience event dataset table), query parameters $start_date and $end_date (YYYY-MM-DD, end at least 3 days ago) and $reporting_tz (for example America/New_York)
WITH events AS (
  -- All experience events: sessionization runs before any event-type filter so playback pings keep sessions alive
  SELECT
    identityMap['ECID'][0].id AS ecid,
    timestamp,
    eventType,
    environment.ipV4 AS ip_v4,
    environment.browserDetails.userAgent AS user_agent,
    device.type AS device_type,
    productListItems AS product_list_items,
    _tenant.discovery.discoverySource AS discovery_source,
    mediaTimed.primaryAssetReference._id AS media_asset_id,
    _tenant.playback.contentId AS play_content_id,
    _tenant.playback.contentSeriesId AS play_series_id,
    _tenant.playback.contentType AS play_content_type,
    _tenant.playback.playTrigger AS play_trigger
  FROM web_experience_events
  -- Two extra days on each side cover time zone shifts and plays that land after midnight
  WHERE timestamp >= to_timestamp(date_sub(to_date('$start_date'), 2))
    AND timestamp < to_timestamp(date_add(to_date('$end_date'), 3))
    AND identityMap['ECID'][0].id IS NOT NULL
),
sessionized AS (
  SELECT
    *,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY ecid
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM events
),
scoped AS (
  SELECT
    *,
    CONCAT(ecid, '-', CAST(session.num AS STRING)) AS session_key
  FROM sessionized
  -- Internal traffic: same office IP ranges as the GA4 internal traffic rule (use your own office IP ranges)
  WHERE COALESCE(ip_v4, '') NOT LIKE '203.0.113.%'
    -- Known bots and crawlers: Web SDK experience events are not bot-filtered by default
    AND NOT (LOWER(COALESCE(user_agent, '')) RLIKE 'bot|crawl|spider|headless')
),
exposure_items AS (
  -- One row per title tile impression; episodes roll up to the parent series through eVar13 content_series_id
  SELECT
    s.session_key,
    s.timestamp AS event_ts,
    COALESCE(s.device_type, '(not set)') AS device_cat,
    LOWER(s.discovery_source) AS discovery_source,
    COALESCE(NULLIF(pli._experience.analytics.customDimensions.eVars.eVar13, ''), pli.SKU) AS title_key,
    LOWER(pli._experience.analytics.customDimensions.eVars.eVar14) AS entitlement_status
  FROM scoped AS s
  LATERAL VIEW explode(s.product_list_items) pli_table AS pli
  WHERE s.eventType = 'commerce.productListViews'
    -- Discovery surfaces only: hero billboard and continue-watching sources are outside this list
    AND LOWER(s.discovery_source) IN ('search', 'browse', 'recommendation', 'editorial')
    AND COALESCE(LOWER(pli._experience.analytics.customDimensions.eVars.eVar11), '') NOT IN ('trailer', 'preview')
    AND pli.SKU IS NOT NULL
),
exposures AS (
  -- Denominator grain: distinct session x title, timestamped at the first impression in the session
  SELECT
    session_key,
    title_key,
    MIN(event_ts) AS first_exposure_ts,
    min_by(device_cat, event_ts) AS device_cat,
    min_by(entitlement_status, event_ts) AS entitlement_status
  FROM exposure_items
  GROUP BY session_key, title_key
),
plays AS (
  -- User-initiated main-content starts only
  SELECT
    session_key,
    COALESCE(NULLIF(play_series_id, ''), NULLIF(play_content_id, ''), media_asset_id) AS title_key,
    timestamp AS play_ts
  FROM scoped
  WHERE eventType = 'media.sessionStart'
    AND COALESCE(LOWER(play_trigger), '') NOT IN ('autoplay', 'next_episode')
    AND COALESCE(LOWER(play_content_type), '') NOT IN ('trailer', 'preview')
    AND COALESCE(NULLIF(play_series_id, ''), NULLIF(play_content_id, ''), media_asset_id) IS NOT NULL
),
first_play AS (
  -- Numerator: first qualifying start of the same title in the same session, from the first exposure up to 30 minutes later; repeat starts count once
  SELECT
    e.session_key,
    e.title_key,
    MIN(p.play_ts) AS first_play_ts
  FROM exposures AS e
  JOIN plays AS p
    ON p.session_key = e.session_key
   AND p.title_key = e.title_key
   AND p.play_ts >= e.first_exposure_ts
   AND p.play_ts <= e.first_exposure_ts + INTERVAL 30 MINUTES
  GROUP BY e.session_key, e.title_key
),
title_level AS (
  SELECT
    e.session_key,
    e.title_key,
    to_date(from_utc_timestamp(e.first_exposure_ts, '$reporting_tz')) AS report_date,
    e.device_cat,
    e.entitlement_status,
    fp.first_play_ts
  FROM exposures AS e
  LEFT JOIN first_play AS fp
    ON fp.session_key = e.session_key
   AND fp.title_key = e.title_key
),
credited_source AS (
  -- Breakdown credit: the last discovery source that exposed the title at or before the converting play
  SELECT
    t.session_key,
    t.title_key,
    max_by(x.discovery_source, x.event_ts) AS credited_discovery_source
  FROM title_level AS t
  JOIN exposure_items AS x
    ON x.session_key = t.session_key
   AND x.title_key = t.title_key
  WHERE t.first_play_ts IS NOT NULL
    AND x.event_ts <= t.first_play_ts
  GROUP BY t.session_key, t.title_key
),
source_level AS (
  SELECT DISTINCT
    session_key,
    title_key,
    discovery_source
  FROM exposure_items
),
unified AS (
  -- Total rows: session x title grain
  SELECT
    report_date,
    'all_sources' AS discovery_source,
    device_cat,
    entitlement_status,
    CONCAT(session_key, '|', title_key) AS exposure_key,
    first_play_ts IS NOT NULL AS converted
  FROM title_level
  UNION ALL
  -- Breakdown rows: session x title x discovery source grain, so they do not sum to the total
  SELECT
    t.report_date,
    s.discovery_source,
    t.device_cat,
    t.entitlement_status,
    CONCAT(s.session_key, '|', s.title_key, '|', s.discovery_source) AS exposure_key,
    COALESCE(c.credited_discovery_source = s.discovery_source, false) AS converted
  FROM source_level AS s
  JOIN title_level AS t
    ON t.session_key = s.session_key
   AND t.title_key = s.title_key
  LEFT JOIN credited_source AS c
    ON c.session_key = s.session_key
   AND c.title_key = s.title_key
)
SELECT
  report_date,
  discovery_source,
  CASE WHEN grouping(device_cat) = 1 THEN 'all_devices' ELSE device_cat END AS device_category,
  COUNT(DISTINCT exposure_key) AS exposures,
  COUNT(DISTINCT CASE WHEN converted THEN exposure_key END) AS converted_exposures,
  ROUND(COUNT(DISTINCT CASE WHEN converted THEN exposure_key END) * 100.0 / NULLIF(COUNT(DISTINCT exposure_key), 0), 2) AS content_discovery_to_play_rate,
  COUNT(DISTINCT CASE WHEN entitlement_status = 'entitled' THEN exposure_key END) AS entitled_exposures,
  COUNT(DISTINCT CASE WHEN converted AND entitlement_status = 'entitled' THEN exposure_key END) AS entitled_converted_exposures,
  ROUND(COUNT(DISTINCT CASE WHEN converted AND entitlement_status = 'entitled' THEN exposure_key END) * 100.0 / NULLIF(COUNT(DISTINCT CASE WHEN entitlement_status = 'entitled' THEN exposure_key END), 0), 2) AS entitled_discovery_to_play_rate
FROM unified
WHERE report_date BETWEEN to_date('$start_date') AND to_date('$end_date')
GROUP BY report_date, discovery_source, device_cat
  GROUPING SETS ((report_date, discovery_source), (report_date, discovery_source, device_cat))
ORDER BY 1, 2, 3;