Content Release Audience Reach

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "genre": "<primary_genre>",
          "region": "<region_code>",
          "country": "<ISO-3166-1_country_code>",
          "os_name": "<operating_system>",
          "play_id": "<play_id>",
          "user_id": "<pseudonymous_user_id>",
          "autoplay": false,
          "event_id": "<event_id>",
          "platform": "<web_or_app_platform>",
          "referrer": "<referrer_url>",
          "series_id": "<series_id>",
          "content_id": "<content_id>",
          "event_name": "video_progress",
          "profile_id": "<pseudonymous_profile_id>",
          "session_id": "<session_id>",
          "app_version": "<application_version>",
          "campaign_id": "<campaign_id>",
          "device_type": "<device_type>",
          "player_name": "<player_name>",
          "stream_type": "<vod_or_live>",
          "anonymous_id": "<anonymous_id>",
          "content_name": "<content_title>",
          "content_type": "<movie_episode_or_live>",
          "household_id": "<pseudonymous_household_id>",
          "release_type": "<premiere_or_catalog_release>",
          "season_number": 0,
          "episode_number": 0,
          "qualified_play": true,
          "catalog_version": "<catalog_version>",
          "event_timestamp": "<ISO-8601_timestamp>",
          "availability_end": "<ISO-8601_availability_end>",
          "content_language": "<BCP-47_language_tag>",
          "entitlement_tier": "<entitlement_tier>",
          "rights_territory": "<ISO-3166-1_rights_territory>",
          "consent_analytics": true,
          "eligible_audience": true,
          "release_timestamp": "<ISO-8601_release_timestamp>",
          "availability_start": "<ISO-8601_availability_start>",
          "eligibility_reason": "<eligibility_reason>",
          "identity_namespace": "<identity_namespace>",
          "audience_segment_id": "<eligible_audience_segment_id>",
          "subscription_status": "<subscription_status>",
          "play_duration_seconds": 0,
          "identity_authenticated": true,
          "measurement_window_end": "<ISO-8601_window_end>",
          "measurement_window_start": "<ISO-8601_window_start>",
          "playback_position_seconds": 0,
          "qualified_play_threshold_percent": 0,
          "qualified_play_threshold_seconds": 0
        }
      }
    ]
  }
}

{
  "event": "video_progress",
  "genre": "<primary_genre>",
  "region": "<region_code>",
  "country": "<ISO-3166-1_country_code>",
  "os_name": "<operating_system>",
  "play_id": "<play_id>",
  "user_id": "<pseudonymous_user_id>",
  "autoplay": false,
  "event_id": "<event_id>",
  "platform": "<web_or_app_platform>",
  "referrer": "<referrer_url>",
  "series_id": "<series_id>",
  "content_id": "<content_id>",
  "event_name": "video_progress",
  "profile_id": "<pseudonymous_profile_id>",
  "session_id": "<session_id>",
  "app_version": "<application_version>",
  "campaign_id": "<campaign_id>",
  "device_type": "<device_type>",
  "player_name": "<player_name>",
  "stream_type": "<vod_or_live>",
  "anonymous_id": "<anonymous_id>",
  "content_name": "<content_title>",
  "content_type": "<movie_episode_or_live>",
  "household_id": "<pseudonymous_household_id>",
  "release_type": "<premiere_or_catalog_release>",
  "season_number": 0,
  "episode_number": 0,
  "qualified_play": true,
  "catalog_version": "<catalog_version>",
  "event_timestamp": "<ISO-8601_timestamp>",
  "availability_end": "<ISO-8601_availability_end>",
  "content_language": "<BCP-47_language_tag>",
  "entitlement_tier": "<entitlement_tier>",
  "rights_territory": "<ISO-3166-1_rights_territory>",
  "consent_analytics": true,
  "eligible_audience": true,
  "release_timestamp": "<ISO-8601_release_timestamp>",
  "availability_start": "<ISO-8601_availability_start>",
  "eligibility_reason": "<eligibility_reason>",
  "identity_namespace": "<identity_namespace>",
  "audience_segment_id": "<eligible_audience_segment_id>",
  "subscription_status": "<subscription_status>",
  "play_duration_seconds": 0,
  "identity_authenticated": true,
  "measurement_window_end": "<ISO-8601_window_end>",
  "measurement_window_start": "<ISO-8601_window_start>",
  "playback_position_seconds": 0,
  "qualified_play_threshold_percent": 0,
  "qualified_play_threshold_seconds": 0
}

{
  "event": "video_progress",
  "eventInfo": {
    "genre": "<primary_genre>",
    "region": "<region_code>",
    "country": "<ISO-3166-1_country_code>",
    "os_name": "<operating_system>",
    "play_id": "<play_id>",
    "user_id": "<pseudonymous_user_id>",
    "autoplay": false,
    "event_id": "<event_id>",
    "platform": "<web_or_app_platform>",
    "referrer": "<referrer_url>",
    "series_id": "<series_id>",
    "content_id": "<content_id>",
    "event_name": "video_progress",
    "profile_id": "<pseudonymous_profile_id>",
    "session_id": "<session_id>",
    "app_version": "<application_version>",
    "campaign_id": "<campaign_id>",
    "device_type": "<device_type>",
    "player_name": "<player_name>",
    "stream_type": "<vod_or_live>",
    "anonymous_id": "<anonymous_id>",
    "content_name": "<content_title>",
    "content_type": "<movie_episode_or_live>",
    "household_id": "<pseudonymous_household_id>",
    "release_type": "<premiere_or_catalog_release>",
    "season_number": 0,
    "episode_number": 0,
    "qualified_play": true,
    "catalog_version": "<catalog_version>",
    "event_timestamp": "<ISO-8601_timestamp>",
    "availability_end": "<ISO-8601_availability_end>",
    "content_language": "<BCP-47_language_tag>",
    "entitlement_tier": "<entitlement_tier>",
    "rights_territory": "<ISO-3166-1_rights_territory>",
    "consent_analytics": true,
    "eligible_audience": true,
    "release_timestamp": "<ISO-8601_release_timestamp>",
    "availability_start": "<ISO-8601_availability_start>",
    "eligibility_reason": "<eligibility_reason>",
    "identity_namespace": "<identity_namespace>",
    "audience_segment_id": "<eligible_audience_segment_id>",
    "subscription_status": "<subscription_status>",
    "play_duration_seconds": 0,
    "identity_authenticated": true,
    "measurement_window_end": "<ISO-8601_window_end>",
    "measurement_window_start": "<ISO-8601_window_start>",
    "playback_position_seconds": 0,
    "qualified_play_threshold_percent": 0,
    "qualified_play_threshold_seconds": 0
  }
}

{
  "web": {
    "webReferrer": {
      "URL": "<referrer_url>"
    }
  },
  "device": {
    "type": "<device_type>"
  },
  "eventType": "media.ping",
  "marketing": {
    "trackingCode": "<campaign_id>"
  },
  "timestamp": "<ISO-8601_timestamp>",
  "_streaming": {
    "genre": "<primary_genre>",
    "region": "<region_code>",
    "country": "<ISO-3166-1_country_code>",
    "os_name": "<operating_system>",
    "play_id": "<play_id>",
    "user_id": "<pseudonymous_user_id>",
    "autoplay": false,
    "event_id": "<event_id>",
    "platform": "<web_or_app_platform>",
    "referrer": "<referrer_url>",
    "series_id": "<series_id>",
    "content_id": "<content_id>",
    "event_name": "video_progress",
    "profile_id": "<pseudonymous_profile_id>",
    "session_id": "<session_id>",
    "app_version": "<application_version>",
    "campaign_id": "<campaign_id>",
    "device_type": "<device_type>",
    "player_name": "<player_name>",
    "stream_type": "<vod_or_live>",
    "anonymous_id": "<anonymous_id>",
    "content_name": "<content_title>",
    "content_type": "<movie_episode_or_live>",
    "household_id": "<pseudonymous_household_id>",
    "release_type": "<premiere_or_catalog_release>",
    "season_number": 0,
    "episode_number": 0,
    "qualified_play": true,
    "catalog_version": "<catalog_version>",
    "event_timestamp": "<ISO-8601_timestamp>",
    "availability_end": "<ISO-8601_availability_end>",
    "content_language": "<BCP-47_language_tag>",
    "entitlement_tier": "<entitlement_tier>",
    "rights_territory": "<ISO-3166-1_rights_territory>",
    "consent_analytics": true,
    "eligible_audience": true,
    "release_timestamp": "<ISO-8601_release_timestamp>",
    "availability_start": "<ISO-8601_availability_start>",
    "eligibility_reason": "<eligibility_reason>",
    "identity_namespace": "<identity_namespace>",
    "audience_segment_id": "<eligible_audience_segment_id>",
    "subscription_status": "<subscription_status>",
    "play_duration_seconds": 0,
    "identity_authenticated": true,
    "measurement_window_end": "<ISO-8601_window_end>",
    "measurement_window_start": "<ISO-8601_window_start>",
    "playback_position_seconds": 0,
    "qualified_play_threshold_percent": 0,
    "qualified_play_threshold_seconds": 0
  },
  "environment": {
    "operatingSystem": "<operating_system>"
  },
  "identityMap": {
    "ECID": [
      {
        "id": "<anonymous_id>",
        "primary": false,
        "authenticatedState": "ambiguous"
      }
    ],
    "<identity_namespace>": [
      {
        "id": "<pseudonymous_user_id>",
        "primary": true,
        "authenticatedState": "authenticated"
      }
    ]
  },
  "eventMergeId": "<event_id>",
  "placeContext": {
    "geo": {
      "countryCode": "<ISO-3166-1_country_code>",
      "stateProvince": "<region_code>"
    }
  },
  "mediaCollection": {
    "playhead": 0,
    "sessionDetails": {
      "contentID": "<content_id>",
      "playerName": "<player_name>",
      "streamType": "<vod_or_live>",
      "contentType": "<movie_episode_or_live>",
      "friendlyName": "<content_title>"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH source_events AS (
  SELECT
    e.event_date,
    e.event_timestamp AS ga4_event_timestamp,
    e.event_name AS ga4_event_name,
    e.user_id AS ga4_user_id,
    e.user_pseudo_id,
    (SELECT ep.value.string_value FROM UNNEST(e.event_params) ep WHERE ep.key = 'event_id' LIMIT 1) AS source_event_id,
    ep.key AS param_key,
    COALESCE(
      ep.value.string_value,
      CAST(ep.value.int_value AS STRING),
      CAST(ep.value.double_value AS STRING),
      CAST(ep.value.float_value AS STRING)
    ) AS param_value
  FROM `your-project.analytics_123456789.events_*` e
  LEFT JOIN UNNEST(e.event_params) ep
  WHERE _TABLE_SUFFIX BETWEEN @start_date AND @end_date
    AND e.event_name = 'video_progress'
),
normalized_events AS (
  SELECT
    COALESCE(MAX(IF(param_key = 'event_id', param_value, NULL)), source_event_id) AS event_id,
    COALESCE(MAX(IF(param_key = 'event_name', param_value, NULL)), ga4_event_name) AS event_name,
    COALESCE(
      SAFE_CAST(MAX(IF(param_key = 'event_timestamp', param_value, NULL)) AS TIMESTAMP),
      TIMESTAMP_MICROS(ga4_event_timestamp)
    ) AS event_timestamp,
    COALESCE(MAX(IF(param_key = 'user_id', param_value, NULL)), ga4_user_id) AS user_id,
    COALESCE(MAX(IF(param_key = 'anonymous_id', param_value, NULL)), user_pseudo_id) AS anonymous_id,
    MAX(IF(param_key = 'household_id', param_value, NULL)) AS household_id,
    MAX(IF(param_key = 'identity_namespace', param_value, NULL)) AS identity_namespace,
    LOWER(MAX(IF(param_key = 'identity_authenticated', param_value, NULL))) IN ('true','1','yes') AS identity_authenticated,
    LOWER(MAX(IF(param_key = 'consent_analytics', param_value, NULL))) IN ('true','1','yes') AS consent_analytics,
    MAX(IF(param_key = 'session_id', param_value, NULL)) AS session_id,
    MAX(IF(param_key = 'platform', param_value, NULL)) AS platform,
    MAX(IF(param_key = 'app_version', param_value, NULL)) AS app_version,
    MAX(IF(param_key = 'device_type', param_value, NULL)) AS device_type,
    MAX(IF(param_key = 'os_name', param_value, NULL)) AS os_name,
    UPPER(MAX(IF(param_key = 'country', param_value, NULL))) AS country,
    MAX(IF(param_key = 'region', param_value, NULL)) AS region,
    MAX(IF(param_key = 'content_id', param_value, NULL)) AS content_id,
    MAX(IF(param_key = 'content_name', param_value, NULL)) AS content_name,
    MAX(IF(param_key = 'content_type', param_value, NULL)) AS content_type,
    MAX(IF(param_key = 'series_id', param_value, NULL)) AS series_id,
    SAFE_CAST(MAX(IF(param_key = 'season_number', param_value, NULL)) AS INT64) AS season_number,
    SAFE_CAST(MAX(IF(param_key = 'episode_number', param_value, NULL)) AS INT64) AS episode_number,
    MAX(IF(param_key = 'genre', param_value, NULL)) AS genre,
    SAFE_CAST(MAX(IF(param_key = 'release_timestamp', param_value, NULL)) AS TIMESTAMP) AS release_timestamp,
    MAX(IF(param_key = 'release_type', param_value, NULL)) AS release_type,
    UPPER(MAX(IF(param_key = 'rights_territory', param_value, NULL))) AS rights_territory,
    SAFE_CAST(MAX(IF(param_key = 'availability_start', param_value, NULL)) AS TIMESTAMP) AS availability_start,
    SAFE_CAST(MAX(IF(param_key = 'availability_end', param_value, NULL)) AS TIMESTAMP) AS availability_end,
    MAX(IF(param_key = 'entitlement_tier', param_value, NULL)) AS entitlement_tier,
    MAX(IF(param_key = 'subscription_status', param_value, NULL)) AS subscription_status,
    MAX(IF(param_key = 'audience_segment_id', param_value, NULL)) AS audience_segment_id,
    LOWER(MAX(IF(param_key = 'eligible_audience', param_value, NULL))) IN ('true','1','yes') AS eligible_audience,
    MAX(IF(param_key = 'eligibility_reason', param_value, NULL)) AS eligibility_reason,
    SAFE_CAST(MAX(IF(param_key = 'measurement_window_start', param_value, NULL)) AS TIMESTAMP) AS measurement_window_start,
    SAFE_CAST(MAX(IF(param_key = 'measurement_window_end', param_value, NULL)) AS TIMESTAMP) AS measurement_window_end,
    MAX(IF(param_key = 'play_id', param_value, NULL)) AS play_id,
    SAFE_CAST(MAX(IF(param_key = 'playback_position_seconds', param_value, NULL)) AS NUMERIC) AS playback_position_seconds,
    SAFE_CAST(MAX(IF(param_key = 'play_duration_seconds', param_value, NULL)) AS NUMERIC) AS play_duration_seconds,
    SAFE_CAST(MAX(IF(param_key = 'qualified_play_threshold_seconds', param_value, NULL)) AS NUMERIC) AS qualified_play_threshold_seconds,
    SAFE_CAST(MAX(IF(param_key = 'qualified_play_threshold_percent', param_value, NULL)) AS NUMERIC) AS qualified_play_threshold_percent,
    LOWER(MAX(IF(param_key = 'qualified_play', param_value, NULL))) IN ('true','1','yes') AS qualified_play,
    LOWER(MAX(IF(param_key = 'autoplay', param_value, NULL))) IN ('true','1','yes') AS autoplay,
    MAX(IF(param_key = 'stream_type', param_value, NULL)) AS stream_type,
    MAX(IF(param_key = 'player_name', param_value, NULL)) AS player_name,
    MAX(IF(param_key = 'referrer', param_value, NULL)) AS referrer,
    MAX(IF(param_key = 'campaign_id', param_value, NULL)) AS campaign_id,
    MAX(IF(param_key = 'content_language', param_value, NULL)) AS content_language,
    MAX(IF(param_key = 'profile_id', param_value, NULL)) AS profile_id,
    MAX(IF(param_key = 'catalog_version', param_value, NULL)) AS catalog_version
  FROM source_events
  GROUP BY event_date, ga4_event_timestamp, ga4_event_name, ga4_user_id, user_pseudo_id, source_event_id
),
deduplicated_events AS (
  SELECT * EXCEPT(event_row_number)
  FROM (
    SELECT
      n.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(
          event_id,
          TO_HEX(SHA256(CONCAT(
            CAST(event_timestamp AS STRING), '|',
            COALESCE(anonymous_id, ''), '|',
            COALESCE(play_id, ''), '|',
            COALESCE(CAST(playback_position_seconds AS STRING), '')
          )))
        )
        ORDER BY event_timestamp
      ) AS event_row_number
    FROM normalized_events n
  )
  WHERE event_row_number = 1
),
eligible_qualified_events AS (
  SELECT
    d.*,
    CASE
      WHEN identity_authenticated AND user_id IS NOT NULL
        THEN CONCAT(COALESCE(identity_namespace, 'user'), ':', user_id)
      WHEN profile_id IS NOT NULL THEN CONCAT('profile:', profile_id)
      WHEN household_id IS NOT NULL THEN CONCAT('household:', household_id)
      WHEN anonymous_id IS NOT NULL THEN CONCAT('anonymous:', anonymous_id)
    END AS audience_id
  FROM deduplicated_events d
  WHERE event_name = 'video_progress'
    AND consent_analytics
    AND eligible_audience
    AND content_id IS NOT NULL
    AND measurement_window_start IS NOT NULL
    AND measurement_window_end IS NOT NULL
    AND event_timestamp >= measurement_window_start
    AND event_timestamp < measurement_window_end
    AND (availability_start IS NULL OR event_timestamp >= availability_start)
    AND (availability_end IS NULL OR event_timestamp < availability_end)
    AND (rights_territory IS NULL OR country IS NULL OR country = rights_territory)
    AND (
      qualified_play
      OR (
        qualified_play_threshold_seconds IS NOT NULL
        AND GREATEST(
          COALESCE(playback_position_seconds, 0),
          COALESCE(play_duration_seconds, 0)
        ) >= qualified_play_threshold_seconds
      )
      OR (
        qualified_play_threshold_percent IS NOT NULL
        AND play_duration_seconds > 0
        AND SAFE_DIVIDE(playback_position_seconds * 100, play_duration_seconds)
            >= qualified_play_threshold_percent
      )
    )
)
SELECT
  content_id,
  ANY_VALUE(content_name HAVING MAX event_timestamp) AS content_name,
  content_type,
  series_id,
  season_number,
  episode_number,
  genre,
  release_timestamp,
  release_type,
  rights_territory,
  availability_start,
  availability_end,
  entitlement_tier,
  audience_segment_id,
  measurement_window_start,
  measurement_window_end,
  catalog_version,
  COUNT(DISTINCT audience_id) AS content_release_audience_reach,
  COUNT(DISTINCT IF(identity_authenticated, user_id, NULL)) AS authenticated_user_reach,
  COUNT(DISTINCT household_id) AS household_reach,
  COUNT(DISTINCT profile_id) AS profile_reach,
  COUNT(DISTINCT anonymous_id) AS anonymous_audience_reach,
  COUNT(DISTINCT play_id) AS qualified_play_count,
  MIN(event_timestamp) AS first_qualified_reach_timestamp,
  MAX(event_timestamp) AS last_qualified_reach_timestamp
FROM eligible_qualified_events
WHERE audience_id IS NOT NULL
GROUP BY
  content_id, content_type, series_id, season_number, episode_number, genre,
  release_timestamp, release_type, rights_territory, availability_start,
  availability_end, entitlement_tier, audience_segment_id,
  measurement_window_start, measurement_window_end, catalog_version;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH normalized_events AS (
  SELECT
    COALESCE(s._streaming.event_id, s.eventMergeId) AS event_id,
    s._streaming.event_name AS event_name,
    COALESCE(CAST(s._streaming.event_timestamp AS TIMESTAMP), CAST(s.timestamp AS TIMESTAMP)) AS event_timestamp,
    s._streaming.user_id AS user_id,
    s._streaming.anonymous_id AS anonymous_id,
    s._streaming.household_id AS household_id,
    s._streaming.identity_namespace AS identity_namespace,
    s._streaming.identity_authenticated AS identity_authenticated,
    s._streaming.consent_analytics AS consent_analytics,
    s._streaming.session_id AS session_id,
    s._streaming.platform AS platform,
    s._streaming.app_version AS app_version,
    s._streaming.device_type AS device_type,
    s._streaming.os_name AS os_name,
    UPPER(s._streaming.country) AS country,
    s._streaming.region AS region,
    s._streaming.content_id AS content_id,
    s._streaming.content_name AS content_name,
    s._streaming.content_type AS content_type,
    s._streaming.series_id AS series_id,
    CAST(s._streaming.season_number AS INTEGER) AS season_number,
    CAST(s._streaming.episode_number AS INTEGER) AS episode_number,
    s._streaming.genre AS genre,
    CAST(s._streaming.release_timestamp AS TIMESTAMP) AS release_timestamp,
    s._streaming.release_type AS release_type,
    UPPER(s._streaming.rights_territory) AS rights_territory,
    CAST(s._streaming.availability_start AS TIMESTAMP) AS availability_start,
    CAST(s._streaming.availability_end AS TIMESTAMP) AS availability_end,
    s._streaming.entitlement_tier AS entitlement_tier,
    s._streaming.subscription_status AS subscription_status,
    s._streaming.audience_segment_id AS audience_segment_id,
    s._streaming.eligible_audience AS eligible_audience,
    s._streaming.eligibility_reason AS eligibility_reason,
    CAST(s._streaming.measurement_window_start AS TIMESTAMP) AS measurement_window_start,
    CAST(s._streaming.measurement_window_end AS TIMESTAMP) AS measurement_window_end,
    s._streaming.play_id AS play_id,
    CAST(s._streaming.playback_position_seconds AS DECIMAL(18,3)) AS playback_position_seconds,
    CAST(s._streaming.play_duration_seconds AS DECIMAL(18,3)) AS play_duration_seconds,
    CAST(s._streaming.qualified_play_threshold_seconds AS DECIMAL(18,3)) AS qualified_play_threshold_seconds,
    CAST(s._streaming.qualified_play_threshold_percent AS DECIMAL(18,3)) AS qualified_play_threshold_percent,
    s._streaming.qualified_play AS qualified_play,
    s._streaming.autoplay AS autoplay,
    s._streaming.stream_type AS stream_type,
    s._streaming.player_name AS player_name,
    s._streaming.referrer AS referrer,
    s._streaming.campaign_id AS campaign_id,
    s._streaming.content_language AS content_language,
    s._streaming.profile_id AS profile_id,
    s._streaming.catalog_version AS catalog_version
  FROM streaming_playback_events s
  WHERE s.eventType = 'media.ping'
    AND s._streaming.event_name = 'video_progress'
    AND CAST(s.timestamp AS TIMESTAMP) >= CAST(:start_timestamp AS TIMESTAMP)
    AND CAST(s.timestamp AS TIMESTAMP) < CAST(:end_timestamp AS TIMESTAMP)
),
deduplicated_events AS (
  SELECT *
  FROM (
    SELECT
      n.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(
          event_id,
          CONCAT(
            CAST(event_timestamp AS VARCHAR), '|',
            COALESCE(anonymous_id, ''), '|',
            COALESCE(play_id, ''), '|',
            COALESCE(CAST(playback_position_seconds AS VARCHAR), '')
          )
        )
        ORDER BY event_timestamp
      ) AS event_row_number
    FROM normalized_events n
  ) ranked_events
  WHERE event_row_number = 1
),
eligible_qualified_events AS (
  SELECT
    d.*,
    CASE
      WHEN identity_authenticated = TRUE AND user_id IS NOT NULL
        THEN CONCAT(COALESCE(identity_namespace, 'user'), ':', user_id)
      WHEN profile_id IS NOT NULL THEN CONCAT('profile:', profile_id)
      WHEN household_id IS NOT NULL THEN CONCAT('household:', household_id)
      WHEN anonymous_id IS NOT NULL THEN CONCAT('anonymous:', anonymous_id)
    END AS audience_id
  FROM deduplicated_events d
  WHERE event_name = 'video_progress'
    AND consent_analytics = TRUE
    AND eligible_audience = TRUE
    AND content_id IS NOT NULL
    AND measurement_window_start IS NOT NULL
    AND measurement_window_end IS NOT NULL
    AND event_timestamp >= measurement_window_start
    AND event_timestamp < measurement_window_end
    AND (availability_start IS NULL OR event_timestamp >= availability_start)
    AND (availability_end IS NULL OR event_timestamp < availability_end)
    AND (rights_territory IS NULL OR country IS NULL OR country = rights_territory)
    AND (
      qualified_play = TRUE
      OR (
        qualified_play_threshold_seconds IS NOT NULL
        AND GREATEST(
          COALESCE(playback_position_seconds, 0),
          COALESCE(play_duration_seconds, 0)
        ) >= qualified_play_threshold_seconds
      )
      OR (
        qualified_play_threshold_percent IS NOT NULL
        AND play_duration_seconds > 0
        AND (playback_position_seconds * 100.0 / play_duration_seconds)
            >= qualified_play_threshold_percent
      )
    )
)
SELECT
  content_id,
  MAX(content_name) AS content_name,
  content_type,
  series_id,
  season_number,
  episode_number,
  genre,
  release_timestamp,
  release_type,
  rights_territory,
  availability_start,
  availability_end,
  entitlement_tier,
  audience_segment_id,
  measurement_window_start,
  measurement_window_end,
  catalog_version,
  COUNT(DISTINCT audience_id) AS content_release_audience_reach,
  COUNT(DISTINCT CASE WHEN identity_authenticated = TRUE THEN user_id END) AS authenticated_user_reach,
  COUNT(DISTINCT household_id) AS household_reach,
  COUNT(DISTINCT profile_id) AS profile_reach,
  COUNT(DISTINCT anonymous_id) AS anonymous_audience_reach,
  COUNT(DISTINCT play_id) AS qualified_play_count,
  MIN(event_timestamp) AS first_qualified_reach_timestamp,
  MAX(event_timestamp) AS last_qualified_reach_timestamp
FROM eligible_qualified_events
WHERE audience_id IS NOT NULL
GROUP BY
  content_id, content_type, series_id, season_number, episode_number, genre,
  release_timestamp, release_type, rights_territory, availability_start,
  availability_end, entitlement_tier, audience_segment_id,
  measurement_window_start, measurement_window_end, catalog_version;