← Back to KPIs
Content Discovery Success Rate
Also known as: Discovery Success Rate, Content Findability Success Rate, Discovery-to-Play Rate, Find-to-Watch Rate, Successful Discovery Session Rate
Measures the percentage of eligible discovery sessions in which a user finds and begins consuming content attributable to a search, browse, recommendation, or navigation action. A session is successful only when the discovered content reaches the defined qualified-consumption threshold within the attribution window.
Business Use Case
The Product organization reviews this KPI by discovery surface, platform, market, and audience segment to identify where users search or browse but fail to begin meaningful consumption. For example, if connected-TV recommendation rails fall below the warning threshold while search remains stable, Product can change rail ranking, artwork, metadata, or placement and evaluate whether the release restores successful discovery sessions without increasing playback errors or reducing completion.
Business Criticality
High
Journey / Navigation Area
Discovery
Owning Function
Product
Scope
Session
Formula
(Number of Successful Discovery Sessions / Number of Eligible Discovery Sessions) × 100Calculation Notes
1️⃣ Count each eligible session once in the denominator and no more than once in the numerator. 2️⃣ Classify a session as successful if any content item meets the qualified-consumption threshold. 3️⃣ Attribute success to the most recent qualifying discovery action that exposed or selected the consumed content. 4️⃣ Ensure the content identifier matches an item returned, displayed, or selected through the attributed discovery surface. 5️⃣ Use the first qualifying discovery-action timestamp to assign the session to a reporting date. 6️⃣ Exclude autoplay without user discovery, direct deep links, external campaign landings, background playback, and unsupported client events. 7️⃣ Recalculate affected dates for late playback milestones, identity reconciliation, or corrected content metadata.
Technical Details
Measure Typepercentage
Aggregation WindowTime based - Daily
Events
| Platform | Event |
|---|---|
| Google Analytics 4 | select_content |
| Adobe | select_content |
Data Mappings
W3 Data Layer
{
"digitalData": {
"event": [
{
"eventInfo": {
"eventName": "select_content",
"eventAction": "select_content",
"eventTimestamp": "2025-01-15T14:30:00Z"
},
"attributes": {
"channel": "onsite",
"user_id": "user-10482",
"event_id": "evt-7f91c284",
"page_url": "https://www.example.com/search",
"platform": "web",
"page_name": "Search Results",
"content_id": "content-28492",
"event_name": "select_content",
"session_id": "session-583920",
"playback_id": "playback-729105",
"anonymous_id": "anonymous-89531",
"content_name": "Featured Documentary",
"content_type": "video",
"referrer_url": "https://www.example.com/",
"content_genre": "history",
"consent_status": "granted",
"catalog_version": "2025-01-15",
"discovery_query": "featured documentary",
"event_timestamp": "2025-01-15T14:30:00Z",
"navigation_path": "/search/content-detail",
"content_category": "documentary",
"content_language": "en",
"content_provider": "example-studios",
"discovery_source": "onsite_search",
"session_eligible": true,
"telemetry_source": "playback_telemetry",
"content_series_id": "series-391",
"discovery_surface": "search_results",
"recommendation_id": null,
"content_episode_id": "episode-2048",
"discovery_position": 3,
"consumption_percent": 10,
"consumption_seconds": 30,
"discovery_action_id": "discovery-48295",
"attribution_eligible": true,
"recommendation_model": null,
"search_results_count": 24,
"discovery_action_type": "search",
"qualification_timestamp": "2025-01-15T14:30:35Z",
"qualified_threshold_met": true,
"recommendation_strategy": null,
"content_duration_seconds": 300,
"qualified_threshold_type": "seconds",
"content_publish_timestamp": "2025-01-01T09:00:00Z",
"discovery_session_success": true,
"qualified_threshold_value": 30,
"attribution_window_seconds": 1800,
"attribution_elapsed_seconds": 35,
"consumption_start_timestamp": "2025-01-15T14:30:05Z"
}
}
]
}
}GA4 Data Layer
{
"event": "select_content",
"item_id": "content-28492",
"discovery": {
"channel": "onsite",
"user_id": "user-10482",
"event_id": "evt-7f91c284",
"page_url": "https://www.example.com/search",
"platform": "web",
"page_name": "Search Results",
"content_id": "content-28492",
"event_name": "select_content",
"session_id": "session-583920",
"playback_id": "playback-729105",
"anonymous_id": "anonymous-89531",
"content_name": "Featured Documentary",
"content_type": "video",
"referrer_url": "https://www.example.com/",
"content_genre": "history",
"consent_status": "granted",
"catalog_version": "2025-01-15",
"discovery_query": "featured documentary",
"event_timestamp": "2025-01-15T14:30:00Z",
"navigation_path": "/search/content-detail",
"content_category": "documentary",
"content_language": "en",
"content_provider": "example-studios",
"discovery_source": "onsite_search",
"session_eligible": true,
"telemetry_source": "playback_telemetry",
"content_series_id": "series-391",
"discovery_surface": "search_results",
"recommendation_id": null,
"content_episode_id": "episode-2048",
"discovery_position": 3,
"consumption_percent": 10,
"consumption_seconds": 30,
"discovery_action_id": "discovery-48295",
"attribution_eligible": true,
"recommendation_model": null,
"search_results_count": 24,
"discovery_action_type": "search",
"qualification_timestamp": "2025-01-15T14:30:35Z",
"qualified_threshold_met": true,
"recommendation_strategy": null,
"content_duration_seconds": 300,
"qualified_threshold_type": "seconds",
"content_publish_timestamp": "2025-01-01T09:00:00Z",
"discovery_session_success": true,
"qualified_threshold_value": 30,
"attribution_window_seconds": 1800,
"attribution_elapsed_seconds": 35,
"consumption_start_timestamp": "2025-01-15T14:30:05Z"
},
"content_type": "video"
}Adobe Client Data Layer
{
"data": {
"discovery": {
"channel": "onsite",
"user_id": "user-10482",
"event_id": "evt-7f91c284",
"page_url": "https://www.example.com/search",
"platform": "web",
"page_name": "Search Results",
"content_id": "content-28492",
"event_name": "select_content",
"session_id": "session-583920",
"playback_id": "playback-729105",
"anonymous_id": "anonymous-89531",
"content_name": "Featured Documentary",
"content_type": "video",
"referrer_url": "https://www.example.com/",
"content_genre": "history",
"consent_status": "granted",
"catalog_version": "2025-01-15",
"discovery_query": "featured documentary",
"event_timestamp": "2025-01-15T14:30:00Z",
"navigation_path": "/search/content-detail",
"content_category": "documentary",
"content_language": "en",
"content_provider": "example-studios",
"discovery_source": "onsite_search",
"session_eligible": true,
"telemetry_source": "playback_telemetry",
"content_series_id": "series-391",
"discovery_surface": "search_results",
"recommendation_id": null,
"content_episode_id": "episode-2048",
"discovery_position": 3,
"consumption_percent": 10,
"consumption_seconds": 30,
"discovery_action_id": "discovery-48295",
"attribution_eligible": true,
"recommendation_model": null,
"search_results_count": 24,
"discovery_action_type": "search",
"qualification_timestamp": "2025-01-15T14:30:35Z",
"qualified_threshold_met": true,
"recommendation_strategy": null,
"content_duration_seconds": 300,
"qualified_threshold_type": "seconds",
"content_publish_timestamp": "2025-01-01T09:00:00Z",
"discovery_session_success": true,
"qualified_threshold_value": 30,
"attribution_window_seconds": 1800,
"attribution_elapsed_seconds": 35,
"consumption_start_timestamp": "2025-01-15T14:30:05Z"
}
},
"event": "select_content"
}XDM Mapping
{
"_id": "evt-7f91c284",
"web": {
"webReferrer": {
"URL": "https://www.example.com/"
},
"webInteraction": {
"URL": "https://www.example.com/content/content-28492",
"name": "select_content",
"type": "other",
"linkClicks": {
"value": 1
}
},
"webPageDetails": {
"URL": "https://www.example.com/search",
"name": "Search Results"
}
},
"eventType": "web.webinteraction.linkClicks",
"timestamp": "2025-01-15T14:30:00Z",
"_experience": {
"analytics": {
"customDimensions": {
"eVars": {
"eVar1": "evt-7f91c284",
"eVar2": "select_content",
"eVar3": "2025-01-15T14:30:00Z",
"eVar4": "session-583920",
"eVar5": "user-10482",
"eVar6": "anonymous-89531",
"eVar7": "granted",
"eVar8": "web",
"eVar9": "onsite",
"eVar10": "https://www.example.com/search",
"eVar11": "Search Results",
"eVar12": "https://www.example.com/",
"eVar13": "discovery-48295",
"eVar14": "search",
"eVar15": "onsite_search",
"eVar16": "search_results",
"eVar17": "3",
"eVar18": "featured documentary",
"eVar19": "24",
"eVar20": "",
"eVar21": "",
"eVar22": "",
"eVar23": "/search/content-detail",
"eVar24": "content-28492",
"eVar25": "Featured Documentary",
"eVar26": "video",
"eVar27": "documentary",
"eVar28": "history",
"eVar29": "series-391",
"eVar30": "episode-2048",
"eVar31": "example-studios",
"eVar32": "en",
"eVar33": "300",
"eVar34": "2025-01-01T09:00:00Z",
"eVar35": "playback-729105",
"eVar36": "2025-01-15T14:30:05Z",
"eVar37": "30",
"eVar38": "10",
"eVar39": "seconds",
"eVar40": "30",
"eVar41": "true",
"eVar42": "2025-01-15T14:30:35Z",
"eVar43": "1800",
"eVar44": "35",
"eVar45": "true",
"eVar46": "true",
"eVar47": "true",
"eVar48": "2025-01-15",
"eVar49": "playback_telemetry"
}
}
}
},
"identityMap": {
"ECID": [
{
"id": "anonymous-89531",
"primary": true,
"authenticatedState": "ambiguous"
}
],
"CRMID": [
{
"id": "user-10482",
"primary": false,
"authenticatedState": "authenticated"
}
]
}
}SQL Queries
Google Analytics 4 (BigQuery)
-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
WITH source_events AS (
SELECT
PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
TIMESTAMP_MICROS(e.event_timestamp) AS ga4_event_timestamp,
e.event_name AS ga4_event_name,
e.user_pseudo_id,
p.*
FROM `your-project.analytics_123456789.events_*` AS e
CROSS JOIN UNNEST([
(
SELECT AS STRUCT
MAX(IF(ep.key = 'event_id', ep.value.string_value, NULL)) AS event_id,
MAX(IF(ep.key = 'event_name', ep.value.string_value, NULL)) AS payload_event_name,
MAX(IF(ep.key = 'event_timestamp', ep.value.string_value, NULL)) AS payload_event_timestamp,
MAX(IF(ep.key = 'session_id', ep.value.string_value, NULL)) AS session_id,
MAX(IF(ep.key = 'user_id', ep.value.string_value, NULL)) AS user_id,
MAX(IF(ep.key = 'anonymous_id', ep.value.string_value, NULL)) AS anonymous_id,
MAX(IF(ep.key = 'consent_status', ep.value.string_value, NULL)) AS consent_status,
MAX(IF(ep.key = 'platform', ep.value.string_value, NULL)) AS platform,
MAX(IF(ep.key = 'channel', ep.value.string_value, NULL)) AS channel,
MAX(IF(ep.key = 'page_url', ep.value.string_value, NULL)) AS page_url,
MAX(IF(ep.key = 'page_name', ep.value.string_value, NULL)) AS page_name,
MAX(IF(ep.key = 'referrer_url', ep.value.string_value, NULL)) AS referrer_url,
MAX(IF(ep.key = 'discovery_action_id', ep.value.string_value, NULL)) AS discovery_action_id,
MAX(IF(ep.key = 'discovery_action_type', ep.value.string_value, NULL)) AS discovery_action_type,
MAX(IF(ep.key = 'discovery_source', ep.value.string_value, NULL)) AS discovery_source,
MAX(IF(ep.key = 'discovery_surface', ep.value.string_value, NULL)) AS discovery_surface,
MAX(IF(ep.key = 'discovery_position', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS discovery_position,
MAX(IF(ep.key = 'discovery_query', ep.value.string_value, NULL)) AS discovery_query,
MAX(IF(ep.key = 'search_results_count', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS search_results_count,
MAX(IF(ep.key = 'recommendation_id', ep.value.string_value, NULL)) AS recommendation_id,
MAX(IF(ep.key = 'recommendation_model', ep.value.string_value, NULL)) AS recommendation_model,
MAX(IF(ep.key = 'recommendation_strategy', ep.value.string_value, NULL)) AS recommendation_strategy,
MAX(IF(ep.key = 'navigation_path', ep.value.string_value, NULL)) AS navigation_path,
MAX(IF(ep.key = 'content_id', ep.value.string_value, NULL)) AS content_id,
MAX(IF(ep.key = 'content_name', 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_category', ep.value.string_value, NULL)) AS content_category,
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_episode_id', ep.value.string_value, NULL)) AS content_episode_id,
MAX(IF(ep.key = 'content_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_duration_seconds', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS content_duration_seconds,
MAX(IF(ep.key = 'content_publish_timestamp', ep.value.string_value, NULL)) AS content_publish_timestamp,
MAX(IF(ep.key = 'playback_id', ep.value.string_value, NULL)) AS playback_id,
MAX(IF(ep.key = 'consumption_start_timestamp', ep.value.string_value, NULL)) AS consumption_start_timestamp,
MAX(IF(ep.key = 'consumption_seconds', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS consumption_seconds,
MAX(IF(ep.key = 'consumption_percent', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS consumption_percent,
MAX(IF(ep.key = 'qualified_threshold_type', ep.value.string_value, NULL)) AS qualified_threshold_type,
MAX(IF(ep.key = 'qualified_threshold_value', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING), CAST(ep.value.double_value AS STRING)), NULL)) AS qualified_threshold_value,
MAX(IF(ep.key = 'qualified_threshold_met', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS qualified_threshold_met,
MAX(IF(ep.key = 'qualification_timestamp', ep.value.string_value, NULL)) AS qualification_timestamp,
MAX(IF(ep.key = 'attribution_window_seconds', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS attribution_window_seconds,
MAX(IF(ep.key = 'attribution_elapsed_seconds', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS attribution_elapsed_seconds,
MAX(IF(ep.key = 'attribution_eligible', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS attribution_eligible,
MAX(IF(ep.key = 'session_eligible', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS session_eligible,
MAX(IF(ep.key = 'discovery_session_success', COALESCE(ep.value.string_value, CAST(ep.value.int_value AS STRING)), NULL)) AS discovery_session_success,
MAX(IF(ep.key = 'catalog_version', ep.value.string_value, NULL)) AS catalog_version,
MAX(IF(ep.key = 'telemetry_source', ep.value.string_value, NULL)) AS telemetry_source
FROM UNNEST(e.event_params) AS ep
)
]) AS p
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 = 'select_content'
),
typed_events AS (
SELECT
* REPLACE (
COALESCE(SAFE_CAST(payload_event_timestamp AS TIMESTAMP), ga4_event_timestamp) AS payload_event_timestamp,
COALESCE(NULLIF(session_id, ''), CONCAT('ga4:', user_pseudo_id, ':', CAST(ga4_event_timestamp AS STRING))) AS session_id,
COALESCE(NULLIF(anonymous_id, ''), user_pseudo_id) AS anonymous_id,
SAFE_CAST(discovery_position AS INT64) AS discovery_position,
SAFE_CAST(search_results_count AS INT64) AS search_results_count,
SAFE_CAST(content_duration_seconds AS NUMERIC) AS content_duration_seconds,
SAFE_CAST(content_publish_timestamp AS TIMESTAMP) AS content_publish_timestamp,
SAFE_CAST(consumption_start_timestamp AS TIMESTAMP) AS consumption_start_timestamp,
SAFE_CAST(consumption_seconds AS NUMERIC) AS consumption_seconds,
SAFE_CAST(consumption_percent AS NUMERIC) AS consumption_percent,
SAFE_CAST(qualified_threshold_value AS NUMERIC) AS qualified_threshold_value,
LOWER(qualified_threshold_met) IN ('true', '1') AS qualified_threshold_met,
SAFE_CAST(qualification_timestamp AS TIMESTAMP) AS qualification_timestamp,
SAFE_CAST(attribution_window_seconds AS INT64) AS attribution_window_seconds,
SAFE_CAST(attribution_elapsed_seconds AS INT64) AS attribution_elapsed_seconds,
LOWER(attribution_eligible) IN ('true', '1') AS attribution_eligible,
LOWER(session_eligible) IN ('true', '1') AS session_eligible,
LOWER(discovery_session_success) IN ('true', '1') AS discovery_session_success
)
FROM source_events
),
deduplicated_events AS (
SELECT *
FROM typed_events
QUALIFY ROW_NUMBER() OVER (
PARTITION BY COALESCE(
NULLIF(event_id, ''),
CONCAT(session_id, '|', COALESCE(discovery_action_id, ''), '|', CAST(payload_event_timestamp AS STRING), '|', COALESCE(content_id, ''))
)
ORDER BY ga4_event_timestamp
) = 1
),
classified_events AS (
SELECT
*,
(
qualified_threshold_met
OR CASE LOWER(qualified_threshold_type)
WHEN 'seconds' THEN consumption_seconds >= qualified_threshold_value
WHEN 'percent' THEN consumption_percent >= qualified_threshold_value
WHEN 'percentage' THEN consumption_percent >= qualified_threshold_value
ELSE FALSE
END
) AS calculated_qualification_met,
(
attribution_eligible
OR (
attribution_elapsed_seconds >= 0
AND attribution_window_seconds > 0
AND attribution_elapsed_seconds <= attribution_window_seconds
)
) AS calculated_attribution_eligible
FROM deduplicated_events
),
session_rollup AS (
SELECT
event_date,
session_id,
COALESCE(platform, 'unknown') AS platform,
COALESCE(channel, 'unknown') AS channel,
COALESCE(discovery_action_type, 'unknown') AS discovery_action_type,
COALESCE(discovery_source, 'unknown') AS discovery_source,
COALESCE(discovery_surface, 'unknown') AS discovery_surface,
MAX(
IF(
session_eligible
AND playback_id IS NOT NULL
AND calculated_qualification_met
AND calculated_attribution_eligible,
1,
0
)
) AS successful_session
FROM classified_events
WHERE session_eligible
AND session_id IS NOT NULL
GROUP BY
event_date,
session_id,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface
)
SELECT
event_date,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface,
COUNT(*) AS eligible_discovery_sessions,
COUNTIF(successful_session = 1) AS successful_discovery_sessions,
SAFE_DIVIDE(COUNTIF(successful_session = 1), COUNT(*)) AS content_discovery_success_rate,
ROUND(100 * SAFE_DIVIDE(COUNTIF(successful_session = 1), COUNT(*)), 2) AS content_discovery_success_rate_percent
FROM session_rollup
GROUP BY
event_date,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface
ORDER BY
event_date,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface;Adobe Experience Platform (AEP Query Service)
-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH source_events AS (
SELECT
CAST(x.timestamp AS DATE) AS event_date,
x.timestamp AS aep_event_timestamp,
x._experience.analytics.customDimensions.eVars.eVar1 AS event_id,
x._experience.analytics.customDimensions.eVars.eVar2 AS payload_event_name,
CAST(x._experience.analytics.customDimensions.eVars.eVar3 AS TIMESTAMP) AS payload_event_timestamp,
x._experience.analytics.customDimensions.eVars.eVar4 AS session_id,
x._experience.analytics.customDimensions.eVars.eVar5 AS user_id,
x._experience.analytics.customDimensions.eVars.eVar6 AS anonymous_id,
x._experience.analytics.customDimensions.eVars.eVar7 AS consent_status,
x._experience.analytics.customDimensions.eVars.eVar8 AS platform,
x._experience.analytics.customDimensions.eVars.eVar9 AS channel,
x._experience.analytics.customDimensions.eVars.eVar10 AS page_url,
x._experience.analytics.customDimensions.eVars.eVar11 AS page_name,
x._experience.analytics.customDimensions.eVars.eVar12 AS referrer_url,
x._experience.analytics.customDimensions.eVars.eVar13 AS discovery_action_id,
x._experience.analytics.customDimensions.eVars.eVar14 AS discovery_action_type,
x._experience.analytics.customDimensions.eVars.eVar15 AS discovery_source,
x._experience.analytics.customDimensions.eVars.eVar16 AS discovery_surface,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar17, '') AS BIGINT) AS discovery_position,
x._experience.analytics.customDimensions.eVars.eVar18 AS discovery_query,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar19, '') AS BIGINT) AS search_results_count,
NULLIF(x._experience.analytics.customDimensions.eVars.eVar20, '') AS recommendation_id,
NULLIF(x._experience.analytics.customDimensions.eVars.eVar21, '') AS recommendation_model,
NULLIF(x._experience.analytics.customDimensions.eVars.eVar22, '') AS recommendation_strategy,
x._experience.analytics.customDimensions.eVars.eVar23 AS navigation_path,
x._experience.analytics.customDimensions.eVars.eVar24 AS content_id,
x._experience.analytics.customDimensions.eVars.eVar25 AS content_name,
x._experience.analytics.customDimensions.eVars.eVar26 AS content_type,
x._experience.analytics.customDimensions.eVars.eVar27 AS content_category,
x._experience.analytics.customDimensions.eVars.eVar28 AS content_genre,
x._experience.analytics.customDimensions.eVars.eVar29 AS content_series_id,
x._experience.analytics.customDimensions.eVars.eVar30 AS content_episode_id,
x._experience.analytics.customDimensions.eVars.eVar31 AS content_provider,
x._experience.analytics.customDimensions.eVars.eVar32 AS content_language,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar33, '') AS DECIMAL(18,4)) AS content_duration_seconds,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar34, '') AS TIMESTAMP) AS content_publish_timestamp,
NULLIF(x._experience.analytics.customDimensions.eVars.eVar35, '') AS playback_id,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar36, '') AS TIMESTAMP) AS consumption_start_timestamp,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar37, '') AS DECIMAL(18,4)) AS consumption_seconds,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar38, '') AS DECIMAL(18,4)) AS consumption_percent,
x._experience.analytics.customDimensions.eVars.eVar39 AS qualified_threshold_type,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar40, '') AS DECIMAL(18,4)) AS qualified_threshold_value,
CASE WHEN LOWER(x._experience.analytics.customDimensions.eVars.eVar41) IN ('true', '1') THEN 1 ELSE 0 END AS qualified_threshold_met,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar42, '') AS TIMESTAMP) AS qualification_timestamp,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar43, '') AS BIGINT) AS attribution_window_seconds,
CAST(NULLIF(x._experience.analytics.customDimensions.eVars.eVar44, '') AS BIGINT) AS attribution_elapsed_seconds,
CASE WHEN LOWER(x._experience.analytics.customDimensions.eVars.eVar45) IN ('true', '1') THEN 1 ELSE 0 END AS attribution_eligible,
CASE WHEN LOWER(x._experience.analytics.customDimensions.eVars.eVar46) IN ('true', '1') THEN 1 ELSE 0 END AS session_eligible,
CASE WHEN LOWER(x._experience.analytics.customDimensions.eVars.eVar47) IN ('true', '1') THEN 1 ELSE 0 END AS discovery_session_success,
x._experience.analytics.customDimensions.eVars.eVar48 AS catalog_version,
x._experience.analytics.customDimensions.eVars.eVar49 AS telemetry_source
FROM your_aep_discovery_experience_event_dataset AS x
WHERE x.timestamp >= CAST(CURRENT_DATE - INTERVAL '30' DAY AS TIMESTAMP)
AND x.timestamp < CAST(CURRENT_DATE AS TIMESTAMP)
AND x.eventType = 'web.webinteraction.linkClicks'
AND x.web.webInteraction.name = 'select_content'
),
deduplicated_events AS (
SELECT *
FROM (
SELECT
source_events.*,
ROW_NUMBER() OVER (
PARTITION BY COALESCE(
NULLIF(event_id, ''),
CONCAT(
session_id,
'|',
COALESCE(discovery_action_id, ''),
'|',
CAST(payload_event_timestamp AS VARCHAR),
'|',
COALESCE(content_id, '')
)
)
ORDER BY aep_event_timestamp
) AS event_row_number
FROM source_events
) AS ranked
WHERE event_row_number = 1
),
classified_events AS (
SELECT
*,
CASE
WHEN qualified_threshold_met = 1 THEN 1
WHEN LOWER(qualified_threshold_type) = 'seconds'
AND consumption_seconds >= qualified_threshold_value THEN 1
WHEN LOWER(qualified_threshold_type) IN ('percent', 'percentage')
AND consumption_percent >= qualified_threshold_value THEN 1
ELSE 0
END AS calculated_qualification_met,
CASE
WHEN attribution_eligible = 1 THEN 1
WHEN attribution_elapsed_seconds >= 0
AND attribution_window_seconds > 0
AND attribution_elapsed_seconds <= attribution_window_seconds THEN 1
ELSE 0
END AS calculated_attribution_eligible
FROM deduplicated_events
),
session_rollup AS (
SELECT
event_date,
session_id,
COALESCE(platform, 'unknown') AS platform,
COALESCE(channel, 'unknown') AS channel,
COALESCE(discovery_action_type, 'unknown') AS discovery_action_type,
COALESCE(discovery_source, 'unknown') AS discovery_source,
COALESCE(discovery_surface, 'unknown') AS discovery_surface,
MAX(
CASE
WHEN session_eligible = 1
AND playback_id IS NOT NULL
AND calculated_qualification_met = 1
AND calculated_attribution_eligible = 1
THEN 1
ELSE 0
END
) AS successful_session
FROM classified_events
WHERE session_eligible = 1
AND session_id IS NOT NULL
GROUP BY
event_date,
session_id,
COALESCE(platform, 'unknown'),
COALESCE(channel, 'unknown'),
COALESCE(discovery_action_type, 'unknown'),
COALESCE(discovery_source, 'unknown'),
COALESCE(discovery_surface, 'unknown')
)
SELECT
event_date,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface,
COUNT(*) AS eligible_discovery_sessions,
SUM(successful_session) AS successful_discovery_sessions,
CAST(SUM(successful_session) AS DECIMAL(18,6)) / NULLIF(COUNT(*), 0) AS content_discovery_success_rate,
ROUND(100.0 * CAST(SUM(successful_session) AS DECIMAL(18,6)) / NULLIF(COUNT(*), 0), 2) AS content_discovery_success_rate_percent
FROM session_rollup
GROUP BY
event_date,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface
ORDER BY
event_date,
platform,
channel,
discovery_action_type,
discovery_source,
discovery_surface;Source Data
Digital Analytics, recommendation-service logs, search-service logs, content catalog metadata, and playback telemetry
Dependencies
[ "session_id", "event_timestamp", "event_name", "content_id", "discovery_surface", "discovery_action_type", "result_set_id", "recommendation_request_id", "search_query_id", "playback_duration_seconds", "content_duration_seconds", "is_autoplay", "is_preview", "is_bot", "is_internal_user", "platform", "market", "content_catalog_snapshot" ]
Report Attributes
Reporting Date Platform Device Type Application Version Market Language Subscription Tier Authentication State Discovery Surface Discovery Action Type Search or Browse Method Recommendation Algorithm Content Type Genre Content Availability Status New or Returning User Experiment Cohort
Recommended Reporting Views
Views
Executive Content Discovery PerformanceSearch and Browse FunnelRecommendation EffectivenessStreaming Product HealthContent Findability by PlatformExperiment Performance Scorecard
Segment Eligibility
true
Governance
Data Sensitivityinternal
Governance
Created bydevyendarm
Created on9/5/2026
Last modified bydevyendarm
StatusPUBLISHED
GitHub
View File on GitHub
catalog/kpis/content-discovery-success-rate.yml