Content Completion Rate

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventId": "evt-550e8400-e29b-41d4-a716-446655440000",
          "eventName": "video_complete",
          "eventAction": "complete",
          "eventTimestamp": "2025-03-08T14:30:00.000Z"
        },
        "attributes": {
          "muted": false,
          "userId": "user-12345",
          "country": "US",
          "autoplay": false,
          "platform": "web",
          "videoUrl": "https://stream.example.com/content/content-10001",
          "contentId": "content-10001",
          "pageTitle": "Example Episode | Example",
          "sessionId": "session-67890",
          "appVersion": "web-2025.03.08",
          "deviceType": "desktop",
          "isEligible": true,
          "playerName": "web-player",
          "streamType": "vod",
          "contentName": "Example Episode",
          "contentType": "episode",
          "isCompleted": true,
          "contentGenre": "documentary",
          "deliveryType": "on_demand",
          "pageLocation": "https://www.example.com/watch/content-10001",
          "pageReferrer": "https://www.example.com/series/example-series",
          "seasonNumber": 2,
          "contentRating": "TV-14",
          "episodeNumber": 5,
          "playerVersion": "5.4.1",
          "contentCategory": "entertainment",
          "contentLanguage": "en-US",
          "contentProvider": "Example Studio",
          "contentSeriesId": "series-20001",
          "operatingSystem": "Windows 11",
          "playbackPercent": 95,
          "completionReason": "threshold_reached",
          "consentAnalytics": "granted",
          "subscriptionTier": "premium",
          "contentSeriesName": "Example Series",
          "eligibilityReason": "eligible_on_demand_content",
          "entitlementStatus": "entitled",
          "playbackAttemptId": "attempt-def456",
          "playbackSessionId": "playback-session-abc123",
          "contentReleaseDate": "2025-02-15",
          "isQualifiedAttempt": true,
          "qualificationReason": "minimum_playback_requirement_met",
          "contentDurationSeconds": 1800,
          "playbackPositionSeconds": 1710,
          "completionThresholdPercent": 95,
          "completionThresholdSeconds": 1710
        }
      }
    ]
  }
}

{
  "event": "video_complete",
  "muted": false,
  "country": "US",
  "user_id": "user-12345",
  "autoplay": false,
  "event_id": "evt-550e8400-e29b-41d4-a716-446655440000",
  "platform": "web",
  "video_url": "https://stream.example.com/content/content-10001",
  "content_id": "content-10001",
  "page_title": "Example Episode | Example",
  "session_id": "session-67890",
  "app_version": "web-2025.03.08",
  "device_type": "desktop",
  "is_eligible": true,
  "player_name": "web-player",
  "stream_type": "vod",
  "video_title": "Example Episode",
  "content_type": "episode",
  "is_completed": true,
  "content_genre": "documentary",
  "delivery_type": "on_demand",
  "page_location": "https://www.example.com/watch/content-10001",
  "page_referrer": "https://www.example.com/series/example-series",
  "season_number": 2,
  "video_percent": 95,
  "content_rating": "TV-14",
  "episode_number": 5,
  "player_version": "5.4.1",
  "video_duration": 1800,
  "video_provider": "Example Studio",
  "event_timestamp": "2025-03-08T14:30:00.000Z",
  "content_category": "entertainment",
  "content_language": "en-US",
  "operating_system": "Windows 11",
  "completion_reason": "threshold_reached",
  "consent_analytics": "granted",
  "content_series_id": "series-20001",
  "subscription_tier": "premium",
  "eligibility_reason": "eligible_on_demand_content",
  "entitlement_status": "entitled",
  "video_current_time": 1710,
  "content_series_name": "Example Series",
  "playback_attempt_id": "attempt-def456",
  "playback_session_id": "playback-session-abc123",
  "content_release_date": "2025-02-15",
  "is_qualified_attempt": true,
  "qualification_reason": "minimum_playback_requirement_met",
  "completion_threshold_percent": 95,
  "completion_threshold_seconds": 1710
}

{
  "data": {
    "muted": false,
    "userId": "user-12345",
    "country": "US",
    "eventId": "evt-550e8400-e29b-41d4-a716-446655440000",
    "autoplay": false,
    "platform": "web",
    "videoUrl": "https://stream.example.com/content/content-10001",
    "contentId": "content-10001",
    "pageTitle": "Example Episode | Example",
    "sessionId": "session-67890",
    "appVersion": "web-2025.03.08",
    "deviceType": "desktop",
    "isEligible": true,
    "playerName": "web-player",
    "streamType": "vod",
    "contentName": "Example Episode",
    "contentType": "episode",
    "isCompleted": true,
    "contentGenre": "documentary",
    "deliveryType": "on_demand",
    "pageLocation": "https://www.example.com/watch/content-10001",
    "pageReferrer": "https://www.example.com/series/example-series",
    "seasonNumber": 2,
    "contentRating": "TV-14",
    "episodeNumber": 5,
    "playerVersion": "5.4.1",
    "eventTimestamp": "2025-03-08T14:30:00.000Z",
    "contentCategory": "entertainment",
    "contentLanguage": "en-US",
    "contentProvider": "Example Studio",
    "contentSeriesId": "series-20001",
    "operatingSystem": "Windows 11",
    "playbackPercent": 95,
    "completionReason": "threshold_reached",
    "consentAnalytics": "granted",
    "subscriptionTier": "premium",
    "contentSeriesName": "Example Series",
    "eligibilityReason": "eligible_on_demand_content",
    "entitlementStatus": "entitled",
    "playbackAttemptId": "attempt-def456",
    "playbackSessionId": "playback-session-abc123",
    "contentReleaseDate": "2025-02-15",
    "isQualifiedAttempt": true,
    "qualificationReason": "minimum_playback_requirement_met",
    "contentDurationSeconds": 1800,
    "playbackPositionSeconds": 1710,
    "completionThresholdPercent": 95,
    "completionThresholdSeconds": 1710
  },
  "event": "media.complete"
}

{
  "web": {
    "webReferrer": {
      "URL": "https://www.example.com/series/example-series"
    },
    "webPageDetails": {
      "URL": "https://www.example.com/watch/content-10001",
      "name": "Example Episode | Example"
    }
  },
  "device": {
    "type": "desktop"
  },
  "consents": {
    "collect": {
      "val": "y"
    }
  },
  "eventType": "media.sessionComplete",
  "timestamp": "2025-03-08T14:30:00.000Z",
  "environment": {
    "operatingSystem": "Windows 11"
  },
  "identityMap": {
    "CRMID": [
      {
        "id": "user-12345",
        "primary": true,
        "authenticatedState": "authenticated"
      }
    ]
  },
  "eventMergeId": "evt-550e8400-e29b-41d4-a716-446655440000",
  "placeContext": {
    "geo": {
      "countryCode": "US"
    }
  },
  "mediaCollection": {
    "playhead": 1710,
    "sessionDetails": {
      "ID": "content-10001",
      "name": "content-10001",
      "show": "Example Series",
      "genre": "documentary",
      "length": 1800,
      "rating": "TV-14",
      "season": "2",
      "channel": "Example Studio",
      "episode": "5",
      "authorized": true,
      "originator": "Example Studio",
      "playerName": "web-player",
      "streamType": "vod",
      "contentType": "episode",
      "friendlyName": "Example Episode"
    }
  },
  "_contentCompletion": {
    "muted": false,
    "autoplay": false,
    "platform": "web",
    "videoUrl": "https://stream.example.com/content/content-10001",
    "sessionId": "session-67890",
    "appVersion": "web-2025.03.08",
    "isEligible": true,
    "isCompleted": true,
    "deliveryType": "on_demand",
    "playerVersion": "5.4.1",
    "contentCategory": "entertainment",
    "contentLanguage": "en-US",
    "contentSeriesId": "series-20001",
    "playbackPercent": 95,
    "completionReason": "threshold_reached",
    "subscriptionTier": "premium",
    "eligibilityReason": "eligible_on_demand_content",
    "entitlementStatus": "entitled",
    "playbackAttemptId": "attempt-def456",
    "playbackSessionId": "playback-session-abc123",
    "contentReleaseDate": "2025-02-15",
    "isQualifiedAttempt": true,
    "qualificationReason": "minimum_playback_requirement_met",
    "completionThresholdPercent": 95,
    "completionThresholdSeconds": 1710
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH event_parameter_pivot AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    TIMESTAMP_MICROS(e.event_timestamp) AS event_timestamp,
    e.event_name,
    e.user_pseudo_id,
    e.platform AS collected_platform,
    e.device.category AS collected_device_type,
    e.device.operating_system AS collected_operating_system,
    e.geo.country AS collected_country,
    MAX(IF(ep.key = 'event_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS event_id,
    MAX(IF(ep.key = 'user_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS event_user_id,
    MAX(IF(ep.key = 'session_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS session_id,
    MAX(IF(ep.key = 'playback_session_id', ep.value.string_value, NULL)) AS playback_session_id,
    MAX(IF(ep.key = 'playback_attempt_id', ep.value.string_value, NULL)) AS playback_attempt_id,
    MAX(IF(ep.key = 'content_id', ep.value.string_value, NULL)) AS content_id,
    MAX(IF(ep.key = 'video_title', ep.value.string_value, NULL)) AS content_name,
    MAX(IF(ep.key = 'content_type', ep.value.string_value, NULL)) AS content_type,
    MAX(IF(ep.key = 'content_genre', ep.value.string_value, NULL)) AS content_genre,
    MAX(IF(ep.key = 'content_series_id', ep.value.string_value, NULL)) AS content_series_id,
    MAX(IF(ep.key = 'content_series_name', ep.value.string_value, NULL)) AS content_series_name,
    MAX(IF(ep.key = 'season_number', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS season_number,
    MAX(IF(ep.key = 'episode_number', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS episode_number,
    MAX(IF(ep.key = 'video_duration', COALESCE(ep.value.int_value, CAST(ep.value.double_value AS INT64), CAST(ep.value.float_value AS INT64)), NULL)) AS content_duration_seconds,
    MAX(IF(ep.key = 'video_provider', ep.value.string_value, NULL)) AS content_provider,
    MAX(IF(ep.key = 'content_language', ep.value.string_value, NULL)) AS content_language,
    MAX(IF(ep.key = 'content_rating', ep.value.string_value, NULL)) AS content_rating,
    MAX(IF(ep.key = 'content_release_date', ep.value.string_value, NULL)) AS content_release_date,
    MAX(IF(ep.key = 'content_category', ep.value.string_value, NULL)) AS content_category,
    MAX(IF(ep.key = 'delivery_type', ep.value.string_value, NULL)) AS delivery_type,
    MAX(IF(ep.key = 'stream_type', ep.value.string_value, NULL)) AS stream_type,
    MAX(IF(ep.key = 'player_name', ep.value.string_value, NULL)) AS player_name,
    MAX(IF(ep.key = 'player_version', ep.value.string_value, NULL)) AS player_version,
    MAX(IF(ep.key = 'video_current_time', COALESCE(ep.value.double_value, ep.value.float_value, CAST(ep.value.int_value AS FLOAT64)), NULL)) AS playback_position_seconds,
    MAX(IF(ep.key = 'video_percent', COALESCE(ep.value.double_value, ep.value.float_value, CAST(ep.value.int_value AS FLOAT64)), NULL)) AS playback_percent,
    MAX(IF(ep.key = 'completion_threshold_percent', COALESCE(ep.value.double_value, ep.value.float_value, CAST(ep.value.int_value AS FLOAT64)), NULL)) AS completion_threshold_percent,
    MAX(IF(ep.key = 'completion_threshold_seconds', COALESCE(ep.value.double_value, ep.value.float_value, CAST(ep.value.int_value AS FLOAT64)), NULL)) AS completion_threshold_seconds,
    MAX(IF(ep.key = 'completion_reason', ep.value.string_value, NULL)) AS completion_reason,
    MAX(IF(ep.key = 'is_completed', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) AS is_completed,
    MAX(IF(ep.key = 'is_eligible', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) AS is_eligible,
    MAX(IF(ep.key = 'eligibility_reason', ep.value.string_value, NULL)) AS eligibility_reason,
    MAX(IF(ep.key = 'is_qualified_attempt', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) AS is_qualified_attempt,
    MAX(IF(ep.key = 'qualification_reason', ep.value.string_value, NULL)) AS qualification_reason,
    MAX(IF(ep.key = 'autoplay', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) AS autoplay,
    MAX(IF(ep.key = 'muted', LOWER(COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING))), NULL)) AS muted,
    MAX(IF(ep.key = 'entitlement_status', ep.value.string_value, NULL)) AS entitlement_status,
    MAX(IF(ep.key = 'subscription_tier', ep.value.string_value, NULL)) AS subscription_tier,
    MAX(IF(ep.key = 'platform', ep.value.string_value, NULL)) AS event_platform,
    MAX(IF(ep.key = 'device_type', ep.value.string_value, NULL)) AS event_device_type,
    MAX(IF(ep.key = 'operating_system', ep.value.string_value, NULL)) AS event_operating_system,
    MAX(IF(ep.key = 'app_version', ep.value.string_value, NULL)) AS app_version,
    MAX(IF(ep.key = 'video_url', ep.value.string_value, NULL)) AS video_url,
    MAX(IF(ep.key = 'page_location', ep.value.string_value, NULL)) AS page_location,
    MAX(IF(ep.key = 'page_title', ep.value.string_value, NULL)) AS page_title,
    MAX(IF(ep.key = 'page_referrer', ep.value.string_value, NULL)) AS page_referrer,
    MAX(IF(ep.key = 'country', ep.value.string_value, NULL)) AS event_country,
    MAX(IF(ep.key = 'consent_analytics', ep.value.string_value, NULL)) AS consent_analytics
  FROM `your-project.analytics_123456789.events_*` AS e
  LEFT JOIN UNNEST(e.event_params) AS ep
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 30 DAY))
                          AND FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('UTC'), INTERVAL 1 DAY))
    AND e.event_name IN ('video_start', 'video_progress', 'video_complete')
  GROUP BY
    e.event_date,
    e.event_timestamp,
    e.event_name,
    e.user_pseudo_id,
    e.platform,
    e.device.category,
    e.device.operating_system,
    e.geo.country
),
deduplicated_events AS (
  SELECT * EXCEPT(event_deduplication_rank)
  FROM (
    SELECT
      p.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(
          p.event_id,
          CONCAT(
            CAST(p.event_timestamp AS STRING), '|',
            p.event_name, '|',
            COALESCE(p.playback_attempt_id, ''), '|',
            COALESCE(p.content_id, '')
          )
        )
        ORDER BY p.event_timestamp
      ) AS event_deduplication_rank
    FROM event_parameter_pivot AS p
  )
  WHERE event_deduplication_rank = 1
),
normalized_events AS (
  SELECT
    *,
    COALESCE(
      NULLIF(playback_attempt_id, ''),
      NULLIF(CONCAT(COALESCE(playback_session_id, ''), '|', COALESCE(content_id, '')), '|')
    ) AS attempt_key,
    COALESCE(NULLIF(event_user_id, ''), user_pseudo_id) AS user_id,
    COALESCE(NULLIF(event_platform, ''), collected_platform) AS platform,
    COALESCE(NULLIF(event_device_type, ''), collected_device_type) AS device_type,
    COALESCE(NULLIF(event_operating_system, ''), collected_operating_system) AS operating_system,
    COALESCE(NULLIF(event_country, ''), collected_country) AS country,
    IF(is_eligible IN ('true', '1', 'yes'), 1, 0) AS eligible_event_flag,
    IF(is_qualified_attempt IN ('true', '1', 'yes'), 1, 0) AS qualified_event_flag,
    IF(is_completed IN ('true', '1', 'yes'), 1, 0) AS completed_event_flag,
    IF(autoplay IN ('true', '1', 'yes'), TRUE, FALSE) AS autoplay_flag,
    IF(muted IN ('true', '1', 'yes'), TRUE, FALSE) AS muted_flag
  FROM deduplicated_events
),
attempt_facts AS (
  SELECT
    attempt_key,
    MIN(event_date) AS activity_date,
    MAX(user_id) AS user_id,
    MAX(session_id) AS session_id,
    MAX(playback_session_id) AS playback_session_id,
    MAX(playback_attempt_id) AS playback_attempt_id,
    MAX(content_id) AS content_id,
    MAX(content_name) AS content_name,
    MAX(content_type) AS content_type,
    MAX(content_genre) AS content_genre,
    MAX(content_series_id) AS content_series_id,
    MAX(content_series_name) AS content_series_name,
    MAX(SAFE_CAST(season_number AS INT64)) AS season_number,
    MAX(SAFE_CAST(episode_number AS INT64)) AS episode_number,
    MAX(content_duration_seconds) AS content_duration_seconds,
    MAX(content_provider) AS content_provider,
    MAX(content_language) AS content_language,
    MAX(content_rating) AS content_rating,
    MAX(SAFE_CAST(content_release_date AS DATE)) AS content_release_date,
    MAX(content_category) AS content_category,
    MAX(delivery_type) AS delivery_type,
    MAX(stream_type) AS stream_type,
    MAX(player_name) AS player_name,
    MAX(player_version) AS player_version,
    MAX(playback_position_seconds) AS maximum_playback_position_seconds,
    MAX(playback_percent) AS maximum_playback_percent,
    MAX(completion_threshold_percent) AS completion_threshold_percent,
    MAX(completion_threshold_seconds) AS completion_threshold_seconds,
    MAX(completion_reason) AS completion_reason,
    MAX(eligibility_reason) AS eligibility_reason,
    MAX(qualification_reason) AS qualification_reason,
    LOGICAL_OR(autoplay_flag) AS autoplay,
    LOGICAL_OR(muted_flag) AS muted,
    MAX(entitlement_status) AS entitlement_status,
    MAX(subscription_tier) AS subscription_tier,
    MAX(platform) AS platform,
    MAX(device_type) AS device_type,
    MAX(operating_system) AS operating_system,
    MAX(app_version) AS app_version,
    MAX(video_url) AS video_url,
    MAX(page_location) AS page_location,
    MAX(page_title) AS page_title,
    MAX(page_referrer) AS page_referrer,
    MAX(country) AS country,
    MAX(consent_analytics) AS consent_analytics,
    MAX(eligible_event_flag) AS eligible_attempt_flag,
    MAX(qualified_event_flag) AS qualified_attempt_flag,
    MAX(IF(event_name = 'video_start', 1, 0)) AS started_attempt_flag,
    MAX(
      IF(
        event_name = 'video_complete'
        OR completed_event_flag = 1
        OR (
          playback_percent IS NOT NULL
          AND playback_percent >= COALESCE(completion_threshold_percent, 95.0)
        )
        OR (
          playback_position_seconds IS NOT NULL
          AND playback_position_seconds >= completion_threshold_seconds
        ),
        1,
        0
      )
    ) AS completed_attempt_flag
  FROM normalized_events
  WHERE attempt_key IS NOT NULL
  GROUP BY attempt_key
),
qualified_eligible_attempts AS (
  SELECT *
  FROM attempt_facts
  WHERE started_attempt_flag = 1
    AND eligible_attempt_flag = 1
    AND qualified_attempt_flag = 1
    AND LOWER(COALESCE(consent_analytics, 'granted')) IN ('granted', 'y', 'yes', 'true', '1')
)
SELECT
  activity_date,
  content_id,
  content_name,
  content_type,
  content_genre,
  content_series_id,
  content_series_name,
  season_number,
  episode_number,
  content_duration_seconds,
  content_provider,
  content_language,
  content_rating,
  content_release_date,
  content_category,
  delivery_type,
  stream_type,
  player_name,
  player_version,
  eligibility_reason,
  qualification_reason,
  completion_threshold_percent,
  completion_threshold_seconds,
  autoplay,
  muted,
  entitlement_status,
  subscription_tier,
  platform,
  device_type,
  operating_system,
  app_version,
  video_url,
  page_location,
  page_title,
  page_referrer,
  country,
  consent_analytics,
  COUNT(*) AS qualified_eligible_attempts,
  COUNTIF(completed_attempt_flag = 1) AS completed_attempts,
  ROUND(
    100 * SAFE_DIVIDE(COUNTIF(completed_attempt_flag = 1), COUNT(*)),
    4
  ) AS content_completion_rate_percent
FROM qualified_eligible_attempts
GROUP BY
  1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
  11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
  21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
  31, 32, 33, 34, 35, 36, 37
ORDER BY activity_date DESC, content_id;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH source_events AS (
  SELECT
    eventMergeId AS event_id,
    "timestamp" AS event_timestamp,
    eventType AS event_type,
    identityMap['CRMID'][0].id AS user_id,
    _contentCompletion.sessionId AS session_id,
    _contentCompletion.playbackSessionId AS playback_session_id,
    _contentCompletion.playbackAttemptId AS playback_attempt_id,
    mediaCollection.sessionDetails."ID" AS content_id,
    mediaCollection.sessionDetails.friendlyName AS content_name,
    mediaCollection.sessionDetails.contentType AS content_type,
    mediaCollection.sessionDetails.genre AS content_genre,
    _contentCompletion.contentSeriesId AS content_series_id,
    mediaCollection.sessionDetails.show AS content_series_name,
    mediaCollection.sessionDetails.season AS season_number,
    mediaCollection.sessionDetails.episode AS episode_number,
    mediaCollection.sessionDetails.length AS content_duration_seconds,
    mediaCollection.sessionDetails.originator AS content_provider,
    _contentCompletion.contentLanguage AS content_language,
    mediaCollection.sessionDetails.rating AS content_rating,
    _contentCompletion.contentReleaseDate AS content_release_date,
    _contentCompletion.contentCategory AS content_category,
    _contentCompletion.deliveryType AS delivery_type,
    mediaCollection.sessionDetails.streamType AS stream_type,
    mediaCollection.sessionDetails.playerName AS player_name,
    _contentCompletion.playerVersion AS player_version,
    mediaCollection.playhead AS playback_position_seconds,
    _contentCompletion.playbackPercent AS playback_percent,
    _contentCompletion.completionThresholdPercent AS completion_threshold_percent,
    _contentCompletion.completionThresholdSeconds AS completion_threshold_seconds,
    _contentCompletion.completionReason AS completion_reason,
    _contentCompletion.isCompleted AS is_completed,
    _contentCompletion.isEligible AS is_eligible,
    _contentCompletion.eligibilityReason AS eligibility_reason,
    _contentCompletion.isQualifiedAttempt AS is_qualified_attempt,
    _contentCompletion.qualificationReason AS qualification_reason,
    _contentCompletion.autoplay AS autoplay,
    _contentCompletion.muted AS muted,
    _contentCompletion.entitlementStatus AS entitlement_status,
    _contentCompletion.subscriptionTier AS subscription_tier,
    _contentCompletion.platform AS platform,
    device.type AS device_type,
    environment.operatingSystem AS operating_system,
    _contentCompletion.appVersion AS app_version,
    _contentCompletion.videoUrl AS video_url,
    web.webPageDetails.URL AS page_location,
    web.webPageDetails.name AS page_title,
    web.webReferrer.URL AS page_referrer,
    placeContext.geo.countryCode AS country,
    consents.collect.val AS consent_analytics
  FROM your_aep_dataset
  WHERE "timestamp" >= CURRENT_DATE - INTERVAL '30 days'
    AND "timestamp" < CURRENT_DATE
    AND eventType IN ('media.sessionStart', 'media.sessionComplete', 'media.playheadUpdate')
),
ranked_events AS (
  SELECT
    s.*,
    ROW_NUMBER() OVER (
      PARTITION BY COALESCE(
        event_id,
        CONCAT(
          CAST(event_timestamp AS VARCHAR), '|',
          COALESCE(event_type, ''), '|',
          COALESCE(playback_attempt_id, ''), '|',
          COALESCE(content_id, '')
        )
      )
      ORDER BY event_timestamp
    ) AS event_deduplication_rank
  FROM source_events s
),
deduplicated_events AS (
  SELECT *
  FROM ranked_events
  WHERE event_deduplication_rank = 1
),
normalized_events AS (
  SELECT
    d.*,
    COALESCE(
      NULLIF(playback_attempt_id, ''),
      NULLIF(CONCAT(COALESCE(playback_session_id, ''), '|', COALESCE(content_id, '')), '|')
    ) AS attempt_key,
    CASE WHEN is_eligible = TRUE THEN 1 ELSE 0 END AS eligible_event_flag,
    CASE WHEN is_qualified_attempt = TRUE THEN 1 ELSE 0 END AS qualified_event_flag,
    CASE WHEN is_completed = TRUE THEN 1 ELSE 0 END AS completed_event_flag,
    CASE WHEN autoplay = TRUE THEN 1 ELSE 0 END AS autoplay_flag,
    CASE WHEN muted = TRUE THEN 1 ELSE 0 END AS muted_flag
  FROM deduplicated_events d
),
attempt_facts AS (
  SELECT
    attempt_key,
    CAST(MIN(event_timestamp) AS DATE) AS activity_date,
    MAX(user_id) AS user_id,
    MAX(session_id) AS session_id,
    MAX(playback_session_id) AS playback_session_id,
    MAX(playback_attempt_id) AS playback_attempt_id,
    MAX(content_id) AS content_id,
    MAX(content_name) AS content_name,
    MAX(content_type) AS content_type,
    MAX(content_genre) AS content_genre,
    MAX(content_series_id) AS content_series_id,
    MAX(content_series_name) AS content_series_name,
    MAX(CAST(season_number AS INTEGER)) AS season_number,
    MAX(CAST(episode_number AS INTEGER)) AS episode_number,
    MAX(content_duration_seconds) AS content_duration_seconds,
    MAX(content_provider) AS content_provider,
    MAX(content_language) AS content_language,
    MAX(content_rating) AS content_rating,
    MAX(CAST(content_release_date AS DATE)) AS content_release_date,
    MAX(content_category) AS content_category,
    MAX(delivery_type) AS delivery_type,
    MAX(stream_type) AS stream_type,
    MAX(player_name) AS player_name,
    MAX(player_version) AS player_version,
    MAX(playback_position_seconds) AS maximum_playback_position_seconds,
    MAX(playback_percent) AS maximum_playback_percent,
    MAX(completion_threshold_percent) AS completion_threshold_percent,
    MAX(completion_threshold_seconds) AS completion_threshold_seconds,
    MAX(completion_reason) AS completion_reason,
    MAX(eligibility_reason) AS eligibility_reason,
    MAX(qualification_reason) AS qualification_reason,
    MAX(autoplay_flag) AS autoplay_flag,
    MAX(muted_flag) AS muted_flag,
    MAX(entitlement_status) AS entitlement_status,
    MAX(subscription_tier) AS subscription_tier,
    MAX(platform) AS platform,
    MAX(device_type) AS device_type,
    MAX(operating_system) AS operating_system,
    MAX(app_version) AS app_version,
    MAX(video_url) AS video_url,
    MAX(page_location) AS page_location,
    MAX(page_title) AS page_title,
    MAX(page_referrer) AS page_referrer,
    MAX(country) AS country,
    MAX(consent_analytics) AS consent_analytics,
    MAX(eligible_event_flag) AS eligible_attempt_flag,
    MAX(qualified_event_flag) AS qualified_attempt_flag,
    MAX(CASE WHEN event_type = 'media.sessionStart' THEN 1 ELSE 0 END) AS started_attempt_flag,
    MAX(
      CASE
        WHEN event_type = 'media.sessionComplete'
          OR completed_event_flag = 1
          OR (
            playback_percent IS NOT NULL
            AND playback_percent >= COALESCE(completion_threshold_percent, 95.0)
          )
          OR (
            playback_position_seconds IS NOT NULL
            AND playback_position_seconds >= completion_threshold_seconds
          )
        THEN 1
        ELSE 0
      END
    ) AS completed_attempt_flag
  FROM normalized_events
  WHERE attempt_key IS NOT NULL
  GROUP BY attempt_key
),
qualified_eligible_attempts AS (
  SELECT *
  FROM attempt_facts
  WHERE started_attempt_flag = 1
    AND eligible_attempt_flag = 1
    AND qualified_attempt_flag = 1
    AND LOWER(COALESCE(consent_analytics, 'y')) IN ('granted', 'y', 'yes', 'true', '1')
)
SELECT
  activity_date,
  content_id,
  content_name,
  content_type,
  content_genre,
  content_series_id,
  content_series_name,
  season_number,
  episode_number,
  content_duration_seconds,
  content_provider,
  content_language,
  content_rating,
  content_release_date,
  content_category,
  delivery_type,
  stream_type,
  player_name,
  player_version,
  eligibility_reason,
  qualification_reason,
  completion_threshold_percent,
  completion_threshold_seconds,
  CASE WHEN autoplay_flag = 1 THEN TRUE ELSE FALSE END AS autoplay,
  CASE WHEN muted_flag = 1 THEN TRUE ELSE FALSE END AS muted,
  entitlement_status,
  subscription_tier,
  platform,
  device_type,
  operating_system,
  app_version,
  video_url,
  page_location,
  page_title,
  page_referrer,
  country,
  consent_analytics,
  COUNT(*) AS qualified_eligible_attempts,
  SUM(completed_attempt_flag) AS completed_attempts,
  ROUND(
    100.0 * SUM(completed_attempt_flag) / NULLIF(COUNT(*), 0),
    4
  ) AS content_completion_rate_percent
FROM qualified_eligible_attempts
GROUP BY
  activity_date,
  content_id,
  content_name,
  content_type,
  content_genre,
  content_series_id,
  content_series_name,
  season_number,
  episode_number,
  content_duration_seconds,
  content_provider,
  content_language,
  content_rating,
  content_release_date,
  content_category,
  delivery_type,
  stream_type,
  player_name,
  player_version,
  eligibility_reason,
  qualification_reason,
  completion_threshold_percent,
  completion_threshold_seconds,
  autoplay_flag,
  muted_flag,
  entitlement_status,
  subscription_tier,
  platform,
  device_type,
  operating_system,
  app_version,
  video_url,
  page_location,
  page_title,
  page_referrer,
  country,
  consent_analytics
ORDER BY activity_date DESC, content_id;