The following tables provide SQL snippet examples that can help with creating common queries for retrieving data from JWP 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 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, |
| Analytics ID |
WHERE ANALYTICS_ID = '' or GROUP BY ANALYTICS_ID |
| 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, |
| 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 |
