Data Sharing SQL Calculations

Discover SQL snippets for crafting common queries to fetch data from JWP Data Sharing

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



Metric Calculations Reference

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

Ads

Metric SQL Query
Ad Clicks SUM(AD_CLICKS)
Ad Completes SUM(AD_COMPLETES)
Ad Skips SUM(AD_SKIPS)

Engagement

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

Performance

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)

Viewers

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)


Dimension Calculations Reference

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

_Content

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,

Date/Time

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

Device

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

Geography

Dimension SQL Query
Region WHERE REGION = '' or GROUP BY REGION

Placement

Dimension SQL Query
Domain WHERE PAGE_DOMAIN = '' or GROUP BY PAGE_DOMAIN


Strategy Rules Queries

These SQL queries demonstrate how to retrieve data from the Strategy Rules Data Sharing datasets.


Daily Placement Metrics View

The following SQL query creates a compiled view that rolls up daily metrics from JW_DATA.PUBLIC.PLAY_SESSIONS_<jw_account_id> and JW_DATA.PUBLIC.PLACEMENT_SESSIONS_<jw_account_id>. This view is grouped by placement_embed_id, analytics_id, placement_id, andย eastern_date.

๐Ÿ’ก

To customize this query, uncomment the joins related to strategy outcomes (strategy_outcome_id) and placement evaluations (placement_eval_id).

with placement_play_sessions as (
select 
    eastern_date
    , analytics_id
    , placement_id
    , placement_embed_id
    -- , strategy_outcome_id
    -- , placement_eval_id
    , sum(case when embeds > 0 then 1 else 0 end) as embeds
    , sum(case when play_attempts > 0 then 1 else 0 end) as play_attempts
    , sum(case when plays > 0 then 1 else 0 end) as plays
    , sum(case when completes > 0 then 1 else 0 end) as completes
    , sum(ad_requests) as ad_requests
    , sum(ad_impressions) as ad_impressions
    , sum(ad_viewable_impressions) as ad_viewable_impressions
    , sum(ad_completes) as ad_completes
    , sum(ad_clicks) as ad_clicks
    , sum(ad_skips) as ad_skips
    -- , count(distinct viewer_id) as player_viewers
from prd_analytics.public.play_sessions
where placement_id is not null
group by
    eastern_date
    , analytics_id
    , placement_id
    , placement_embed_id
    -- ,strategy_outcome_id
    -- ,placement_eval_id
)
select
    pcms.analytics_id
    , pcms.eastern_date
    , pcms.placement_id
    , pcms.strategy_outcome_id
    -- placement_sessions metrics
    , sum(pcms.placement_embed_attempts) as placement_embed_attempts
    , sum(pcms.placement_embeds) as placement_embeds
    , sum(pcms.strategy_evaluations) as strategy_evaluations
    , sum(pcms.strategy_evaluation_errors) as strategy_evaluation_errors
    -- , count(distinct pcms.viewer_id) as placement_viewers
    -- play_sessions metrics
    , sum(ps.embeds) as embeds
    , sum(ps.play_attempts) as play_attempts
    , sum(ps.plays) as plays
    , sum(ps.completes) as completes
    , sum(ps.ad_requests) as ad_requests
    , sum(ps.ad_impressions) as ad_impressions
    , sum(ps.ad_viewable_impressions) as ad_viewable_impressions
    , sum(ps.ad_completes) as ad_completes
    , sum(ps.ad_clicks) as ad_clicks
    , sum(ps.ad_skips) as ad_skips
    -- , sum(ps.player_viewers) as player_viewers
from prd_analytics.public.placement_sessions pcms 
left join placement_play_sessions ps
    on pcms.eastern_date = ps.eastern_date
    and pcms.analytics_id = ps.analytics_id
    and pcms.placement_id = ps.placement_id
    and pcms.placement_embed_id = ps.placement_embed_id
    -- and pcms.strategy_outcome_id = ps.strategy_outcome_id
    -- and pcms.placement_eval_id = ps.placement_evaluation_id
where pcms.eastern_date = current_date - 1
group by 
    pcms.analytics_id
    , pcms.eastern_date
    , pcms.placement_id
    , pcms.strategy_outcome_id;



Placement Performance Metrics

The following SQL query creates a placement performance report with the following filter conditions:

  • Placements must be enabled and deliverable.
  • Date range is the last 7 complete days.
  • Data are grouped by device_type, a detailed breakdown not typically included in roll-up views such as strategy_outcome_daily_metrics or placement_daily_metrics.
  • Metrics include total plays split by US and international viewers.

๐Ÿ’ก

To customize this query, uncomment the joins related to strategy outcomes (strategy_outcome_id) and placement evaluations (placement_eval_id).

with placement_play_sessions as (
select 
    eastern_date
    , analytics_id
    , placement_id
    , placement_embed_id
    -- , strategy_outcome_id
    -- , placement_eval_id
    , sum(case when embeds > 0 then 1 else 0 end) as embeds
    , sum(case when play_attempts > 0 then 1 else 0 end) as play_attempts
    , sum(case when plays > 0 then 1 else 0 end) as plays
        -- example of how metrics can be split by a dimension
        , sum(case when plays > 0 and country_code = 'US' then 1 else 0 end) as us_plays
        , sum(case when plays > 0 and country_code <> 'US' then 1 else 0 end) as international_plays
        --
    , sum(case when completes > 0 then 1 else 0 end) as completes
    , sum(ad_impressions) as ad_impressions
    , sum(ad_viewable_impressions) as ad_viewable_impressions
    , sum(ad_completes) as ad_completes
    , sum(ad_clicks) as ad_clicks
    , sum(ad_skips) as ad_skips
    , count(distinct viewer_id) as player_viewers
    from play_sessions_view
where placement_id is not null
group by 
    eastern_date
    , analytics_id
    , placement_id
    , placement_embed_id
)
select 
    pcmd.id as placement_id
    , pcmd.site_id
    , pcmd.strategy_id
    , pcmd.name as placement_name
    , pcmd.description as placement_description
    , pcms.device_type
    -- placement_sessions metrics
    , sum(pcms.placement_loads) as placement_loads
    , sum(pcms.placement_embed_attempts) as placement_embed_attempts
    , sum(pcms.placement_embeds) as placement_embeds
    , sum(pcms.strategy_evaluations) as strategy_evaluations
    , sum(pcms.strategy_evaluation_errors) as strategy_evaluation_errors
    , count(distinct pcms.viewer_id) as placement_viewers
    -- play_sessions metrics
    , sum(ps.embeds) as embeds
    , sum(ps.play_attempts) as play_attempts
    , sum(ps.plays) as plays
        -- example of how metrics can be split by a dimension
        , sum(us_plays) as us_plays
        , sum(international_plays) as international_plays
        --
    , sum(ps.completes) as completes
    , sum(ps.ad_impressions) as ad_impressions
    , sum(ps.ad_viewable_impressions) as ad_viewable_impressions
    , sum(ps.ad_completes) as ad_completes
    , sum(ps.ad_clicks) as ad_clicks
    , sum(ps.ad_skips) as ad_skips
    , sum(player_viewers) as player_viewers
from placement_definitions_view pcmd
left join placement_sessions_view pcms 
    on pcms.placement_id = pcmd.id
left join placement_play_sessions ps
    on ps.analytics_id = pcms.analytics_id
    and ps.eastern_date = pcms.eastern_date
    and ps.placement_id = pcms.placement_id
    and ps.placement_embed_id = pcms.placement_embed_id
    -- and ps.strategy_outcome_id = pcms.strategy_outcome_id
    -- and ps.placement_evaluation_id = pcms.placement_eval_id
where 
    pcmd.enabled
    and pcmd.is_deliverable
    and not pcmd.is_deleted
    and pcms.eastern_date >= current_date - 7 and pcms.eastern_date < current_date
group by 
    pcmd.id
    , pcmd.site_id
    , pcmd.strategy_id
    , pcmd.name
    , pcmd.description
    , pcms.device_type