Monthly Active Viewers

Also known as: MAV, Monthly Viewers, Monthly Unique Viewers, Monthly Active Audience, Active Viewers per Month

EngagementkpiMedia & Streaming

Monthly Active Viewers counts distinct viewer identities that consume at least 30 seconds of eligible video content during a calendar month. Each qualifying viewer is counted once per month after applying identity resolution, content eligibility, bot filtering, and cross-device deduplication rules.

Business Use Case

The Chief Product Officer and Content leadership review Monthly Active Viewers by market, platform, subscription tier, acquisition cohort, and content genre to determine whether audience reach is expanding. If the metric declines materially in a market while subscriber volume remains stable, teams investigate content availability, discovery performance, playback reliability, and release cadence, then adjust merchandising, product placement, content promotion, or distribution investment.

Business Criticality

High

Journey / Navigation Area

Engagement

Owning Function

Product

Scope

User

Formula

COUNT(DISTINCT canonical_viewer_id WHERE SUM(eligible_viewing_seconds within calendar month) >= 30)

Calculation Notes

1️⃣ Calculate at calendar-month level using canonical viewer identity post-deterministic resolution. 2️⃣ Sum eligible playback seconds within the reporting month, including sessions crossing month boundaries. 3️⃣ Count a viewer once after 30 seconds of eligible viewing, regardless of sessions, titles, devices, or platforms. 4️⃣ Exclude advertising playback, trailers, previews, autoplay impressions without qualified viewing, and playback errors. 5️⃣ Do not count pause time, buffering time, seek gaps, or background time without confirmed playback. 6️⃣ Recalculate historical months for late events or deterministic identity merges that materially affect distinct count. 7️⃣ Exclude internal testing, synthetic monitoring, known bots, fraudulent traffic, deleted events, and duplicate telemetry.

Technical Details

Measure Typecount

Aggregation WindowTime based - Monthly

Events

Platform	Event
Google Analytics 4	video_progress
Adobe	video_progress

Data Mappings

W3 Data Layer

{
  "event": [
    {
      "eventInfo": {
        "eventId": "evt_01JAV30SEC8F4K2M9Q7R",
        "eventName": "video_progress",
        "timestamp": "2025-03-15T14:30:30.000Z",
        "eventAction": "progress"
      },
      "attributes": {
        "genre": "documentary",
        "muted": false,
        "region": "CA",
        "country": "US",
        "videoId": "content-12345",
        "autoplay": false,
        "deviceId": "device_63e15a",
        "platform": "web",
        "viewerId": "viewer_7f3a92c1",
        "botStatus": "human",
        "eventDate": "2025-03-15",
        "pageTitle": "Example Program - Episode 4",
        "sessionId": "session_8c271d",
        "seriesName": "Example Program",
        "videoTitle": "Example Program - Episode 4",
        "contentType": "episode",
        "eventSource": "playback_telemetry",
        "fraudStatus": "approved",
        "gaSessionId": "1742049000",
        "pageLocation": "https://www.example.com/watch/content-12345",
        "seasonNumber": 2,
        "videoPercent": 2,
        "appInstanceId": null,
        "calendarMonth": "2025-03",
        "contentRating": "TV-PG",
        "episodeNumber": 4,
        "videoDuration": 1800,
        "videoProvider": "example-player",
        "deviceCategory": "desktop",
        "contentEligible": true,
        "contentLanguage": "en-US",
        "contentProvider": "example-studio",
        "analyticsConsent": "granted",
        "subscriptionTier": "premium",
        "thresholdCrossed": true,
        "videoCurrentTime": 30,
        "eligibilityReason": "eligible_long_form_content",
        "playbackSessionId": "playback_a8b97c",
        "viewerIdNamespace": "viewerId",
        "engagementTimeMsec": 30000,
        "subscriptionStatus": "active",
        "crossDeviceClusterId": "cluster_42bc91",
        "identityAuthenticated": true,
        "personalizationConsent": "granted",
        "identityResolutionStatus": "resolved",
        "qualifyingThresholdSeconds": 30
      }
    }
  ]
}

GA4 Data Layer

{
  "event": "video_progress",
  "genre": "documentary",
  "muted": false,
  "region": "CA",
  "country": "US",
  "user_id": "viewer_7f3a92c1",
  "autoplay": false,
  "event_id": "evt_01JAV30SEC8F4K2M9Q7R",
  "platform": "web",
  "video_id": "content-12345",
  "device_id": "device_63e15a",
  "bot_status": "human",
  "event_date": "2025-03-15",
  "page_title": "Example Program - Episode 4",
  "session_id": "session_8c271d",
  "series_name": "Example Program",
  "video_title": "Example Program - Episode 4",
  "content_type": "episode",
  "event_source": "playback_telemetry",
  "fraud_status": "approved",
  "ga_session_id": "1742049000",
  "page_location": "https://www.example.com/watch/content-12345",
  "season_number": 2,
  "video_percent": 2,
  "calendar_month": "2025-03",
  "content_rating": "TV-PG",
  "episode_number": 4,
  "video_duration": 1800,
  "video_provider": "example-player",
  "app_instance_id": null,
  "device_category": "desktop",
  "event_timestamp": "2025-03-15T14:30:30.000Z",
  "content_eligible": true,
  "content_language": "en-US",
  "content_provider": "example-studio",
  "analytics_consent": "granted",
  "subscription_tier": "premium",
  "threshold_crossed": true,
  "eligibility_reason": "eligible_long_form_content",
  "video_current_time": 30,
  "playback_session_id": "playback_a8b97c",
  "subscription_status": "active",
  "viewer_id_namespace": "viewerId",
  "engagement_time_msec": 30000,
  "identity_authenticated": true,
  "cross_device_cluster_id": "cluster_42bc91",
  "personalization_consent": "granted",
  "identity_resolution_status": "resolved",
  "qualifying_threshold_seconds": 30
}

Adobe Client Data Layer

{
  "data": {
    "genre": "documentary",
    "muted": false,
    "region": "CA",
    "country": "US",
    "eventId": "evt_01JAV30SEC8F4K2M9Q7R",
    "videoId": "content-12345",
    "autoplay": false,
    "deviceId": "device_63e15a",
    "platform": "web",
    "viewerId": "viewer_7f3a92c1",
    "botStatus": "human",
    "eventDate": "2025-03-15",
    "pageTitle": "Example Program - Episode 4",
    "sessionId": "session_8c271d",
    "seriesName": "Example Program",
    "videoTitle": "Example Program - Episode 4",
    "contentType": "episode",
    "eventSource": "playback_telemetry",
    "fraudStatus": "approved",
    "gaSessionId": "1742049000",
    "pageLocation": "https://www.example.com/watch/content-12345",
    "seasonNumber": 2,
    "videoPercent": 2,
    "appInstanceId": null,
    "calendarMonth": "2025-03",
    "contentRating": "TV-PG",
    "episodeNumber": 4,
    "videoDuration": 1800,
    "videoProvider": "example-player",
    "deviceCategory": "desktop",
    "eventTimestamp": "2025-03-15T14:30:30.000Z",
    "contentEligible": true,
    "contentLanguage": "en-US",
    "contentProvider": "example-studio",
    "analyticsConsent": "granted",
    "subscriptionTier": "premium",
    "thresholdCrossed": true,
    "videoCurrentTime": 30,
    "eligibilityReason": "eligible_long_form_content",
    "playbackSessionId": "playback_a8b97c",
    "viewerIdNamespace": "viewerId",
    "engagementTimeMsec": 30000,
    "subscriptionStatus": "active",
    "crossDeviceClusterId": "cluster_42bc91",
    "identityAuthenticated": true,
    "personalizationConsent": "granted",
    "identityResolutionStatus": "resolved",
    "qualifyingThresholdSeconds": 30
  },
  "event": "video_progress"
}

XDM Mapping

{
  "_id": "evt_01JAV30SEC8F4K2M9Q7R",
  "web": {
    "webPageDetails": {
      "URL": "https://www.example.com/watch/content-12345",
      "name": "Example Program - Episode 4"
    }
  },
  "device": {
    "type": "desktop"
  },
  "consents": {
    "collect": {
      "val": "y"
    },
    "personalize": {
      "content": {
        "val": "y"
      }
    }
  },
  "eventType": "media.ping",
  "timestamp": "2025-03-15T14:30:30.000Z",
  "identityMap": {
    "ECID": [
      {
        "id": "12345678901234567890123456789012345678",
        "primary": false,
        "authenticatedState": "ambiguous"
      }
    ],
    "viewerId": [
      {
        "id": "viewer_7f3a92c1",
        "primary": true,
        "authenticatedState": "authenticated"
      }
    ]
  },
  "placeContext": {
    "geo": {
      "countryCode": "US",
      "stateProvince": "CA"
    }
  },
  "mediaReporting": {
    "playhead": 30,
    "sessionDetails": {
      "name": "content-12345",
      "show": "Example Program",
      "genre": "documentary",
      "length": 1800,
      "rating": "TV-PG",
      "season": "2",
      "channel": "example-studio",
      "episode": "4",
      "language": "en-US",
      "playerName": "example-player",
      "streamType": "vod",
      "contentType": "episode",
      "friendlyName": "Example Program - Episode 4",
      "contentProvider": "example-studio"
    }
  },
  "_viewerMeasurement": {
    "muted": false,
    "autoplay": false,
    "deviceId": "device_63e15a",
    "platform": "web",
    "botStatus": "human",
    "eventDate": "2025-03-15",
    "sessionId": "session_8c271d",
    "eventSource": "playback_telemetry",
    "fraudStatus": "approved",
    "gaSessionId": "1742049000",
    "videoPercent": 2,
    "appInstanceId": null,
    "calendarMonth": "2025-03",
    "contentEligible": true,
    "subscriptionTier": "premium",
    "thresholdCrossed": true,
    "eligibilityReason": "eligible_long_form_content",
    "playbackSessionId": "playback_a8b97c",
    "viewerIdNamespace": "viewerId",
    "engagementTimeMsec": 30000,
    "subscriptionStatus": "active",
    "crossDeviceClusterId": "cluster_42bc91",
    "identityAuthenticated": true,
    "identityResolutionStatus": "resolved",
    "qualifyingThresholdSeconds": 30
  }
}

SQL Queries

Google Analytics 4 (BigQuery)

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH source_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS export_event_date,
    TIMESTAMP_MICROS(e.event_timestamp) AS event_timestamp,
    e.event_name,
    e.user_id,
    p.*
  FROM `your-project.analytics_123456789.events_*` AS e
  CROSS JOIN (
    SELECT AS STRUCT
      MAX(IF(ep.key = 'event_id', ep.value.string_value, NULL)) AS event_id,
      MAX(IF(ep.key = 'event_date', ep.value.string_value, NULL)) AS event_date_attribute,
      MAX(IF(ep.key = 'calendar_month', ep.value.string_value, NULL)) AS calendar_month_attribute,
      MAX(IF(ep.key = 'viewer_id', ep.value.string_value, NULL)) AS viewer_id,
      MAX(IF(ep.key = 'viewer_id_namespace', ep.value.string_value, NULL)) AS viewer_id_namespace,
      MAX(IF(ep.key = 'identity_authenticated', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS identity_authenticated,
      MAX(IF(ep.key = 'identity_resolution_status', ep.value.string_value, NULL)) AS identity_resolution_status,
      MAX(IF(ep.key = 'cross_device_cluster_id', ep.value.string_value, NULL)) AS cross_device_cluster_id,
      MAX(IF(ep.key = 'session_id', ep.value.string_value, NULL)) AS session_id,
      MAX(IF(ep.key = 'ga_session_id', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS ga_session_id,
      MAX(IF(ep.key = 'platform', ep.value.string_value, NULL)) AS platform,
      MAX(IF(ep.key = 'device_category', ep.value.string_value, NULL)) AS device_category,
      MAX(IF(ep.key = 'device_id', ep.value.string_value, NULL)) AS device_id,
      MAX(IF(ep.key = 'app_instance_id', ep.value.string_value, NULL)) AS app_instance_id,
      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 = 'video_id', ep.value.string_value, NULL)) AS video_id,
      MAX(IF(ep.key = 'video_title', ep.value.string_value, NULL)) AS video_title,
      MAX(IF(ep.key = 'content_type', ep.value.string_value, NULL)) AS content_type,
      MAX(IF(ep.key = 'series_name', ep.value.string_value, NULL)) AS 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 = 'genre', ep.value.string_value, NULL)) AS genre,
      MAX(IF(ep.key = 'content_rating', ep.value.string_value, NULL)) AS content_rating,
      MAX(IF(ep.key = 'content_language', ep.value.string_value, NULL)) AS content_language,
      MAX(IF(ep.key = 'content_provider', ep.value.string_value, NULL)) AS content_provider,
      MAX(IF(ep.key = 'content_eligible', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS content_eligible,
      MAX(IF(ep.key = 'eligibility_reason', ep.value.string_value, NULL)) AS eligibility_reason,
      MAX(IF(ep.key = 'video_provider', ep.value.string_value, NULL)) AS video_provider,
      MAX(IF(ep.key = 'video_duration', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS video_duration,
      MAX(IF(ep.key = 'video_current_time', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS video_current_time,
      MAX(IF(ep.key = 'engagement_time_msec', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS engagement_time_msec,
      MAX(IF(ep.key = 'qualifying_threshold_seconds', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS qualifying_threshold_seconds,
      MAX(IF(ep.key = 'threshold_crossed', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS threshold_crossed,
      MAX(IF(ep.key = 'playback_session_id', ep.value.string_value, NULL)) AS playback_session_id,
      MAX(IF(ep.key = 'video_percent', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS video_percent,
      MAX(IF(ep.key = 'autoplay', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS autoplay,
      MAX(IF(ep.key = 'muted', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS muted,
      MAX(IF(ep.key = 'subscription_status', ep.value.string_value, NULL)) AS subscription_status,
      MAX(IF(ep.key = 'subscription_tier', ep.value.string_value, NULL)) AS subscription_tier,
      MAX(IF(ep.key = 'analytics_consent', ep.value.string_value, NULL)) AS analytics_consent,
      MAX(IF(ep.key = 'personalization_consent', ep.value.string_value, NULL)) AS personalization_consent,
      MAX(IF(ep.key = 'bot_status', ep.value.string_value, NULL)) AS bot_status,
      MAX(IF(ep.key = 'fraud_status', ep.value.string_value, NULL)) AS fraud_status,
      MAX(IF(ep.key = 'country', ep.value.string_value, NULL)) AS country,
      MAX(IF(ep.key = 'region', ep.value.string_value, NULL)) AS region,
      MAX(IF(ep.key = 'event_source', ep.value.string_value, NULL)) AS event_source
    FROM UNNEST(e.event_params) AS ep
  ) AS p
  WHERE _TABLE_SUFFIX BETWEEN
    FORMAT_DATE('%Y%m%d', DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH), MONTH))
    AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND e.event_name = 'video_progress'
),
normalized_events AS (
  SELECT
    event_id,
    event_timestamp,
    COALESCE(SAFE.PARSE_DATE('%Y-%m-%d', event_date_attribute), export_event_date) AS event_date,
    DATE_TRUNC(COALESCE(SAFE.PARSE_DATE('%Y-%m-%d', event_date_attribute), export_event_date), MONTH) AS calendar_month,
    COALESCE(NULLIF(viewer_id, ''), NULLIF(user_id, '')) AS viewer_id,
    viewer_id_namespace,
    LOWER(identity_authenticated) IN ('true', '1', 'yes') AS identity_authenticated,
    LOWER(identity_resolution_status) AS identity_resolution_status,
    cross_device_cluster_id,
    session_id,
    ga_session_id,
    platform,
    device_category,
    device_id,
    app_instance_id,
    page_location,
    page_title,
    video_id,
    video_title,
    content_type,
    series_name,
    SAFE_CAST(season_number AS INT64) AS season_number,
    SAFE_CAST(episode_number AS INT64) AS episode_number,
    genre,
    content_rating,
    content_language,
    content_provider,
    LOWER(content_eligible) IN ('true', '1', 'yes') AS content_eligible,
    eligibility_reason,
    video_provider,
    SAFE_CAST(video_duration AS FLOAT64) AS video_duration_seconds,
    SAFE_CAST(video_current_time AS FLOAT64) AS video_current_time_seconds,
    SAFE_CAST(engagement_time_msec AS INT64) AS engagement_time_msec,
    COALESCE(SAFE_CAST(qualifying_threshold_seconds AS FLOAT64), 30.0) AS qualifying_threshold_seconds,
    LOWER(threshold_crossed) IN ('true', '1', 'yes') AS threshold_crossed,
    playback_session_id,
    SAFE_CAST(video_percent AS FLOAT64) AS video_percent,
    LOWER(autoplay) IN ('true', '1', 'yes') AS autoplay,
    LOWER(muted) IN ('true', '1', 'yes') AS muted,
    subscription_status,
    subscription_tier,
    LOWER(analytics_consent) AS analytics_consent,
    LOWER(personalization_consent) AS personalization_consent,
    LOWER(bot_status) AS bot_status,
    LOWER(fraud_status) AS fraud_status,
    country,
    region,
    LOWER(event_source) AS event_source,
    CASE
      WHEN NULLIF(cross_device_cluster_id, '') IS NOT NULL
        THEN CONCAT('cluster:', cross_device_cluster_id)
      WHEN COALESCE(NULLIF(viewer_id, ''), NULLIF(user_id, '')) IS NOT NULL
        THEN CONCAT(COALESCE(NULLIF(viewer_id_namespace, ''), 'viewerId'), ':', COALESCE(NULLIF(viewer_id, ''), NULLIF(user_id, '')))
    END AS canonical_viewer_id
  FROM source_events
),
deduplicated_events AS (
  SELECT *
  FROM normalized_events
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY COALESCE(
      event_id,
      CONCAT(
        COALESCE(playback_session_id, 'unknown'), '|',
        COALESCE(video_id, 'unknown'), '|',
        CAST(event_timestamp AS STRING), '|',
        COALESCE(canonical_viewer_id, 'unknown')
      )
    )
    ORDER BY event_timestamp
  ) = 1
),
qualified_viewing_events AS (
  SELECT *
  FROM deduplicated_events
  WHERE canonical_viewer_id IS NOT NULL
    AND identity_authenticated
    AND identity_resolution_status = 'resolved'
    AND content_eligible
    AND analytics_consent IN ('granted', 'y', 'yes', 'true', '1')
    AND bot_status = 'human'
    AND fraud_status = 'approved'
    AND event_source = 'playback_telemetry'
    AND threshold_crossed
    AND GREATEST(
      COALESCE(video_current_time_seconds, 0),
      COALESCE(engagement_time_msec, 0) / 1000.0
    ) >= qualifying_threshold_seconds
)
SELECT
  calendar_month,
  COUNT(DISTINCT canonical_viewer_id) AS monthly_active_viewers,
  COUNT(*) AS qualifying_progress_events,
  COUNT(DISTINCT playback_session_id) AS qualifying_playback_sessions,
  COUNT(DISTINCT video_id) AS viewed_content_items,
  ROUND(SUM(COALESCE(engagement_time_msec, 0)) / 1000.0, 2) AS qualifying_engagement_seconds,
  MIN(event_timestamp) AS first_qualifying_event_timestamp,
  MAX(event_timestamp) AS last_qualifying_event_timestamp
FROM qualified_viewing_events
GROUP BY calendar_month
ORDER BY calendar_month;

Adobe Experience Platform (AEP Query Service)

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH source_events AS (
  SELECT
    x._id AS event_id,
    x.eventType AS event_type,
    x."timestamp" AS event_timestamp,
    CAST(x."timestamp" AS DATE) AS event_date,
    DATE_TRUNC('MONTH', x."timestamp") AS calendar_month,
    x.identityMap['viewerId'][0].id AS viewer_id,
    x._viewerMeasurement.viewerIdNamespace AS viewer_id_namespace,
    x._viewerMeasurement.identityAuthenticated AS identity_authenticated,
    LOWER(x._viewerMeasurement.identityResolutionStatus) AS identity_resolution_status,
    x._viewerMeasurement.crossDeviceClusterId AS cross_device_cluster_id,
    x._viewerMeasurement.sessionId AS session_id,
    x._viewerMeasurement.gaSessionId AS ga_session_id,
    x._viewerMeasurement.platform AS platform,
    x.device.type AS device_category,
    x._viewerMeasurement.deviceId AS device_id,
    x._viewerMeasurement.appInstanceId AS app_instance_id,
    x.web.webPageDetails.URL AS page_location,
    x.web.webPageDetails.name AS page_title,
    x.mediaReporting.sessionDetails.name AS video_id,
    x.mediaReporting.sessionDetails.friendlyName AS video_title,
    x.mediaReporting.sessionDetails.contentType AS content_type,
    x.mediaReporting.sessionDetails.show AS series_name,
    CAST(x.mediaReporting.sessionDetails.season AS INTEGER) AS season_number,
    CAST(x.mediaReporting.sessionDetails.episode AS INTEGER) AS episode_number,
    x.mediaReporting.sessionDetails.genre AS genre,
    x.mediaReporting.sessionDetails.rating AS content_rating,
    x.mediaReporting.sessionDetails.language AS content_language,
    x.mediaReporting.sessionDetails.contentProvider AS content_provider,
    x._viewerMeasurement.contentEligible AS content_eligible,
    x._viewerMeasurement.eligibilityReason AS eligibility_reason,
    x.mediaReporting.sessionDetails.playerName AS video_provider,
    CAST(x.mediaReporting.sessionDetails.length AS DOUBLE) AS video_duration_seconds,
    CAST(x.mediaReporting.playhead AS DOUBLE) AS video_current_time_seconds,
    CAST(x._viewerMeasurement.engagementTimeMsec AS BIGINT) AS engagement_time_msec,
    COALESCE(CAST(x._viewerMeasurement.qualifyingThresholdSeconds AS DOUBLE), 30.0) AS qualifying_threshold_seconds,
    x._viewerMeasurement.thresholdCrossed AS threshold_crossed,
    x._viewerMeasurement.playbackSessionId AS playback_session_id,
    CAST(x._viewerMeasurement.videoPercent AS DOUBLE) AS video_percent,
    x._viewerMeasurement.autoplay AS autoplay,
    x._viewerMeasurement.muted AS muted,
    x._viewerMeasurement.subscriptionStatus AS subscription_status,
    x._viewerMeasurement.subscriptionTier AS subscription_tier,
    LOWER(x.consents.collect.val) AS analytics_consent,
    LOWER(x.consents.personalize.content.val) AS personalization_consent,
    LOWER(x._viewerMeasurement.botStatus) AS bot_status,
    LOWER(x._viewerMeasurement.fraudStatus) AS fraud_status,
    x.placeContext.geo.countryCode AS country,
    x.placeContext.geo.stateProvince AS region,
    LOWER(x._viewerMeasurement.eventSource) AS event_source,
    CASE
      WHEN x._viewerMeasurement.crossDeviceClusterId IS NOT NULL
        AND x._viewerMeasurement.crossDeviceClusterId <> ''
        THEN CONCAT('cluster:', x._viewerMeasurement.crossDeviceClusterId)
      WHEN x.identityMap['viewerId'][0].id IS NOT NULL
        THEN CONCAT(
          COALESCE(x._viewerMeasurement.viewerIdNamespace, 'viewerId'),
          ':',
          x.identityMap['viewerId'][0].id
        )
    END AS canonical_viewer_id
  FROM your_aep_playback_event_dataset AS x
  WHERE x.eventType = 'media.ping'
    AND x."timestamp" >= DATE_TRUNC('MONTH', CURRENT_DATE - INTERVAL '12' MONTH)
    AND x."timestamp" < CURRENT_DATE + INTERVAL '1' DAY
),
deduplicated_events AS (
  SELECT *
  FROM (
    SELECT
      source_events.*,
      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(
          event_id,
          CONCAT(
            COALESCE(playback_session_id, 'unknown'), '|',
            COALESCE(video_id, 'unknown'), '|',
            CAST(event_timestamp AS VARCHAR), '|',
            COALESCE(canonical_viewer_id, 'unknown')
          )
        )
        ORDER BY event_timestamp
      ) AS event_row_number
    FROM source_events
  ) ranked_events
  WHERE event_row_number = 1
),
qualified_viewing_events AS (
  SELECT *
  FROM deduplicated_events
  WHERE canonical_viewer_id IS NOT NULL
    AND identity_authenticated = TRUE
    AND identity_resolution_status = 'resolved'
    AND content_eligible = TRUE
    AND analytics_consent IN ('granted', 'y', 'yes', 'true', '1')
    AND bot_status = 'human'
    AND fraud_status = 'approved'
    AND event_source = 'playback_telemetry'
    AND threshold_crossed = TRUE
    AND GREATEST(
      COALESCE(video_current_time_seconds, 0),
      COALESCE(engagement_time_msec, 0) / 1000.0
    ) >= qualifying_threshold_seconds
)
SELECT
  CAST(calendar_month AS DATE) AS calendar_month,
  COUNT(DISTINCT canonical_viewer_id) AS monthly_active_viewers,
  COUNT(*) AS qualifying_progress_events,
  COUNT(DISTINCT playback_session_id) AS qualifying_playback_sessions,
  COUNT(DISTINCT video_id) AS viewed_content_items,
  ROUND(SUM(COALESCE(engagement_time_msec, 0)) / 1000.0, 2) AS qualifying_engagement_seconds,
  MIN(event_timestamp) AS first_qualifying_event_timestamp,
  MAX(event_timestamp) AS last_qualifying_event_timestamp
FROM qualified_viewing_events
GROUP BY CAST(calendar_month AS DATE)
ORDER BY calendar_month;

Source Data

Playback event telemetry, digital analytics, viewer identity service, content catalog, subscription platform, consent records, fraud and bot-detection services

Dependencies

[ "playback_started_event", "playback_heartbeat_event", "playback_ended_event", "playback_position_seconds", "eligible_viewing_seconds", "event_timestamp_utc", "canonical_viewer_id", "profile_id", "account_id", "anonymous_device_id", "identity_resolution_map", "content_id", "content_type", "content_eligibility_flag", "platform_id", "reporting_timezone", "bot_traffic_flag", "internal_traffic_flag", "subscription_status" ]

Report Attributes

Reporting Month Country Market Region Platform Device Type Operating System Application Version Subscription Tier Subscription Status Viewer Type New or Returning Viewer Acquisition Channel Content Type Genre Franchise Title Live or On-Demand Authenticated or Anonymous Age Band Consent Status

Recommended Reporting Views

Views

Executive Audience Engagement DashboardMonthly Streaming Performance DashboardContent Portfolio Performance DashboardSubscriber and Viewer Growth DashboardPlatform Engagement DashboardMarket Performance Dashboard

Segment Eligibility

true

Governance

Data Sensitivityconfidential

Contains PIIYes - This KPI contains Personally Identifiable Information

Governance

Created bydevyendarm

Created on9/5/2026

Last modified bydevyendarm

StatusPUBLISHED

GitHub

View File on GitHubcatalog/kpis/monthly-active-viewers.yml

Monthly Active Viewers

Business Use Case

Business Criticality

Journey / Navigation Area

Owning Function

Scope

Formula

Calculation Notes

Technical Details

Events

Data Mappings

SQL Queries

Source Data

Dependencies

Report Attributes

Recommended Reporting Views

Segment Eligibility

Governance

Related KPIs

Governance

GitHub

Community Discussion