The following tables provide SQL snippet examples that can help with creating common queries for retrieving data from JWX Data Sharing.
The following tables provide SQL calculations for common, useful metrics.
Metric
SQL Query
Ad Clicks
SUM(AD_CLICKS)
Ad Completes
SUM(AD_COMPLETES)
Ad Skips
SUM(AD_SKIPS)
Metric
SQL Query
25% Completes
SUM(CASE WHEN NVL(WATCHED_PCT, 0) >= 25 THEN 1 ELSE 0 END)
50% Completes
SUM(CASE WHEN NVL(WATCHED_PCT, 0) >= 50 THEN 1 ELSE 0 END)
75% Completes
SUM(CASE WHEN NVL(WATCHED_PCT, 0) >= 75 THEN 1 ELSE 0 END)
Complete Rate
SUM(CASE WHEN COMPLETES > 0 THEN 1 ELSE 0 END) / SUM(CASE WHEN PLAYS > 0 THEN 1 ELSE 0 END) as complete_rate
Video Seeks
SUM(VIDEO_SEEKS)
Video Shares
SUM(VIDEO_SHARES)
Watched Duration Viewable
SUM(WATCHED_DURATION_VIEWABLE)
Watched Percentage
SUM(watched_duration_viewable) / SUM(watched_duration) AS percent_viewable_watch_time
Metric
SQL Query
Ad Impressions
SUM(AD_IMPRESSIONS)
Ad Viewability Rate
SUM(AD_VIEWABLE_IMPRESSIONS) / SUM(AD_IMPRESSIONS)
Ad Viewable Impressions
SUM(AD_VIEWABLE_IMPRESSIONS)
Completes
SUM(CASE WHEN COMPLETES > 0 THEN 1 ELSE 0 END)
Count Playback Stalls
SUM(COUNT_PLAYBACK_STALLS)
Embeds
SUM(CASE WHEN EMBEDS > 0 THEN 1 ELSE 0 END) or COUNT(DISTINCT EMBED_ID)
First Frame
AVG(FIRST_FRAME)
Play Attempts
SUM(CASE WHEN PLAY_ATTEMPTS > 0 THEN 1 ELSE 0 END)
Plays
SUM(CASE WHEN PLAYS > 0 THEN 1 ELSE 0 END)
Time Watched
SUM(WATCHED_DURATION)
Metric
SQL Query
Ad Impressions per Viewer
SUM(AD_IMPRESSIONS) / count(distinct viewer_id) as ads_per_viewer
Plays per Viewer
SUM(CASE WHEN PLAYS > 0 THEN 1 ELSE 0 END) / count(distinct viewer_id) as plays_per_viewer
Time Watched per Viewer
sum(watched_duration) / count(distinct viewer_id) as time_watched_per_viewer
Unique Viewers
COUNT(DISTINCT VIEWER_ID)
The following tables provide SQL calculations for common, useful dimensions.
Dimension
SQL Query
Ad Client
case AD_CLIENT when 0 then 'VAST' when 1 then 'Google IMA' when 2 then 'Freewheel' when 3 then 'DAI' ELSE 'Unknown' END as AD_CLIENT,
Feed ID
WHERE FEED_ID = '' , GROUP BY FEED_ID
Is Autostart
case is_autostart when 0 then 'Click Start' when 1 then 'Autostart' when 2 then 'Autostart on viewable' END as start_setting
Is Click to Play
WHERE is_click_to_play = 1
Media Title
GROUP BY MEDIA_TITLE
Media Type
WHERE MEDIA_TYPE = '' or GROUP BY MEDIA_TYPE
Number of Ad Breaks
SUM(NUM_AD_BREAKS) or WHERE NUM_AD_BREAKS > 0
Play Sequence
case ps.play_seq when 1 then 'Seed' else 'Incremental' END as is_seed_play,
Site ID
WHERE SITE_ID = '' or GROUP BY SITE_ID
Dimension
SQL Query
Start Time
date_trunc('hour', convert_timezone('UTC', 'America/New_York', sap.START_TIME)) as EASTERN_START_HR
Viewer Timezone Offset
DATEADD('hour', VIEWER_TZ_OFFSET, START_TIME) as local_start_time
Dimension
SQL Query
App User ID (Apps)
count(distinct ott_app_user_id) or HLL(ott_app_user_id)
Average Player Height
AVG(AVG_PLAYER_HEIGHT)
Average Player Width
AVG(AVG_PLAYER_WIDTH)
Device Model
where ott_device_model = '' or group by ott_device_model
Operating System Language (Apps)
where ott_os_language = ''
Operating System
WHERE OPERATING_SYSTEM IN/= '' or GROUP BY OPERATING_SYSTEM
Operating System (Apps)
where ott_device_firmware_version = '' or group by ott_device_firmware_version
Dimension
SQL Query
Region
WHERE REGION = '' or GROUP BY REGION
Dimension
SQL Query
Domain
WHERE PAGE_DOMAIN = '' or GROUP BY PAGE_DOMAIN
