The following tables provide SQL snippet examples that can help with creating common queries for retrieving data from JWP Data Sharing.

<br />


ο»Ώ

## Metric Calculations Reference

The following tables provide SQL calculations for common, useful metrics.

### Ads

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Metric </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Ad Clicks</strong> </td> <td width="80%"> <code>SUM(AD_CLICKS)</code> </td> </tr> <tr> <td> <strong>Ad Completes</strong> </td> <td> <code>SUM(AD_COMPLETES)</code> </td> </tr> <tr> <td> <strong>Ad Skips</strong> </td> <td> <code>SUM(AD_SKIPS)</code> </td> </tr> </tbody> </table> </span>

<br />

### Engagement

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Metric </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>25% Completes</strong> </td> <td width="80%"> <code>SUM(CASE WHEN NVL(WATCHED_PCT, 0) >= 25 THEN 1 ELSE 0 END)</code> </td> </tr> <tr> <td> <strong>50% Completes</strong> </td> <td> <code>SUM(CASE WHEN NVL(WATCHED_PCT, 0) >= 50 THEN 1 ELSE 0 END)</code> </td> </tr> <tr> <td> <strong>75% Completes</strong> </td> <td> <code>SUM(CASE WHEN NVL(WATCHED_PCT, 0) >= 75 THEN 1 ELSE 0 END)</code> </td> </tr> <tr> <td> <strong>Complete Rate</strong> </td> <td> <code>SUM(CASE WHEN COMPLETES > 0 THEN 1 ELSE 0 END) / SUM(CASE WHEN PLAYS > 0 THEN 1 ELSE 0 END) as complete_rate</code> </td> </tr> <tr> <td> <strong>Video Seeks</strong> </td> <td> <code>SUM(VIDEO_SEEKS)</code> </td> </tr> <tr> <td> <strong>Video Shares</strong> </td> <td> <code>SUM(VIDEO_SHARES)</code> </td> </tr> <tr> <td> <strong>Watched Duration Viewable</strong> </td> <td> <code>SUM(WATCHED_DURATION_VIEWABLE)</code> </td> </tr> <tr> <td> <strong>Watched Percentage</strong> </td> <td> <code>SUM(watched_duration_viewable) / SUM(watched_duration) AS percent_viewable_watch_time</code> </td> </tr> </tbody> </table> </span>

<br />

### Performance

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Metric </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Ad Impressions</strong> </td> <td width="80%"> <code>SUM(AD_IMPRESSIONS)</code> </td> </tr> <tr> <td> <strong>Ad Viewable Impressions</strong> </td> <td> <code>SUM(AD_VIEWABLE_IMPRESSIONS)</code> </td> </tr> <tr> <td> <strong>Completes</strong> </td> <td> <code>SUM(CASE WHEN COMPLETES > 0 THEN 1 ELSE 0 END)</code> </td> </tr> <tr> <td> <strong>Count Playback Stalls</strong> </td> <td> <code>SUM(COUNT_PLAYBACK_STALLS)</code> </td> </tr> <tr> <td> <strong>Embeds</strong> </td> <td> <code>SUM(CASE WHEN EMBEDS > 0 THEN 1 ELSE 0 END) or COUNT(DISTINCT EMBED_ID)</code> </td> </tr> <tr> <td> <strong>First Frame</strong> </td> <td> <code>AVG(FIRST_FRAME)</code> </td> </tr> <tr> <td> <strong>Play Attempts</strong> </td> <td> <code>SUM(CASE WHEN PLAY_ATTEMPTS > 0 THEN 1 ELSE 0 END)<br />WHERE PLAY_ATTEMPTS > 0 AND PLAYS = 0</code> </td> </tr> <tr> <td> <strong>Plays</strong> </td> <td> <code>SUM(CASE WHEN PLAYS > 0 THEN 1 ELSE 0 END)</code> </td> </tr> <tr> <td> <strong>Time Watched</strong> </td> <td> <code>SUM(WATCHED_DURATION)</code> </td> </tr> </tbody> </table> </span>

<br />

### Viewers

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Metric </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Ad Impressions per Viewer</strong> </td> <td width="80%"> <code>SUM(AD_IMPRESSIONS) / count(distinct viewer_id) as ads_per_viewer</code> </td> </tr> <tr> <td> <strong>Plays per Viewer</strong> </td> <td> <code>SUM(CASE WHEN PLAYS > 0 THEN 1 ELSE 0 END) / count(distinct viewer_id) as plays_per_viewer</code> </td> </tr> <tr> <td> <strong>Time Watched per Viewer</strong> </td> <td> <code>sum(watched_duration) / count(distinct viewer_id) as time_watched_per_viewer</code> </td> </tr> <tr> <td> <strong>Unique Viewers</strong> </td> <td> <code>COUNT(DISTINCT VIEWER_ID)</code> </td> </tr> </tbody> </table> </span>

<br />


ο»Ώ

## Dimension Calculations Reference

The following tables provide SQL calculations for common, useful dimensions.

### \_Content

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Dimension </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Ad Client</strong> </td> <td width="80%"> <code>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,</code> </td> </tr> <tr> <td> <strong>Analytics ID</strong> </td> <td> <code>WHERE ANALYTICS_ID = '' or GROUP BY ANALYTICS_ID</code> </td> </tr> <tr> <td> <strong>Feed ID</strong> </td> <td> <code>WHERE FEED_ID = '' , GROUP BY FEED_ID</code> </td> </tr> <tr> <td> <strong>Is Autostart</strong> </td> <td> <code>case is_autostart when 0 then 'Click Start' when 1 then 'Autostart' when 2 then 'Autostart on viewable' END as start_setting</code> </td> </tr> <tr> <td> <strong>Is Click to Play</strong> </td> <td> <code>WHERE is_click_to_play = 1</code> </td> </tr> <tr> <td> <strong>Media Title</strong> </td> <td> <code>GROUP BY MEDIA_TITLE</code> </td> </tr> <tr> <td> <strong>Media Type</strong> </td> <td> <code>WHERE MEDIA_TYPE = '' or GROUP BY MEDIA_TYPE</code> </td> </tr> <tr> <td> <strong>Number of Ad Breaks</strong> </td> <td> <code>SUM(NUM_AD_BREAKS) or WHERE NUM_AD_BREAKS > 0</code> </td> </tr> <tr> <td> <strong>Play Sequence</strong> </td> <td> <code>case ps.play_seq when 1 then 'Seed' else 'Incremental' END as is_seed_play,</code> </td> </tr> </tbody> </table> </span>

<br />

### Date/Time

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Dimension </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Start Time</strong> </td> <td width="80%"> <code>date_trunc('hour', convert_timezone('UTC', 'America/New_York', sap.START_TIME)) as EASTERN_START_HR</code> </td> </tr> <tr> <td> <strong>Viewer Timezone Offset</strong> </td> <td> <code>DATEADD('hour', VIEWER_TZ_OFFSET, START_TIME) as local_start_time</code> </td> </tr> </tbody> </table> </span>

<br />

### Device

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Dimension </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>App User ID (Apps)</strong> </td> <td width="80%"> <code>count(distinct ott_app_user_id) or HLL(ott_app_user_id)</code> </td> </tr> <tr> <td> <strong>Average Player Height</strong> </td> <td> <code>AVG(AVG_PLAYER_HEIGHT)</code> </td> </tr> <tr> <td> <strong>Average Player Width</strong> </td> <td> <code>AVG(AVG_PLAYER_WIDTH)</code> </td> </tr> <tr> <td> <strong>Device Model</strong> </td> <td> <code>where ott_device_model = '' or group by ott_device_model</code> </td> </tr> <tr> <td> <strong>Operating System Language (Apps)</strong> </td> <td> <code>where ott_os_language = ''</code> </td> </tr> <tr> <td> <strong>Operating System</strong> </td> <td> <code>WHERE OPERATING_SYSTEM IN/= '' or GROUP BY OPERATING_SYSTEM</code> </td> </tr> <tr> <td> <strong>Operating System (Apps)</strong> </td> <td> <code>where ott_device_firmware_version = '' or group by ott_device_firmware_version</code> </td> </tr> </tbody> </table> </span>

<br />

### Geography

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Dimension </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Region</strong> </td> <td width="80%"> <code>WHERE REGION = '' or GROUP BY REGION</code> </td> </tr> </tbody> </table> </span>

<br />

### Placement

<span class="manual-tbl-font"> <table> <thead> <tr> <th> Dimension </th> <th> SQL Query </th> </tr> </thead> <tbody> <tr> <td width="20%"> <strong>Domain</strong> </td> <td width="80%"> <code>WHERE PAGE_DOMAIN = '' or GROUP BY PAGE_DOMAIN</code> </td> </tr> </tbody> </table> </span>

<br />

<!-- Removes the automatic page-to-page navigation at the bottom of the page -->

<style> .rm-Pagination { display: none; } </style>