Video performance daily

1-- core.youtube_analytics.video_performance_daily
2-- Per-video views, watch time, engagement, subscribers and thumbnail reach, the
3-- YouTube Studio "Content" tab by day.
4--
5-- Grain: date_day x channel_id x video_id. Dates are Pacific Time, as YouTube
6-- reports them.
7-- Depends on: staging.youtube_analytics.channel_basic, .channel_reach
8--
9-- Averages are recomputed from sums: average view duration is watch time over
10-- views, and average percentage viewed is weighted by views. average_percentage_viewed
11-- stays on YouTube's 0 to 100 scale, as Studio shows it. To report a week or
12-- a month, re-sum the base columns and divide again. The *_weighted columns are
13-- there for exactly that.
14--
15-- The Reporting API carries no video titles. Join a video list of your own on
16-- video_id if you need them.
17
18WITH activity AS (
19    SELECT
20        date_day,
21        channel_id,
22        video_id,
23        SUM(views)                       AS views,
24        SUM(engaged_views)               AS engaged_views,
25        SUM(premium_views)               AS premium_views,
26        SUM(watch_time_minutes)          AS watch_time_minutes,
27        SUM(average_view_duration_percentage * views) AS view_percentage_weighted,
28        SUM(subscribers_gained)          AS subscribers_gained,
29        SUM(subscribers_lost)            AS subscribers_lost,
30        SUM(likes)                       AS likes,
31        SUM(dislikes)                    AS dislikes,
32        SUM(shares)                      AS shares,
33        SUM(comments)                    AS comments,
34        SUM(IF(subscribed_status = 'subscribed', views, 0)) AS views_from_subscribers,
35        SUM(IF(live_or_on_demand = 'live', views, 0))       AS live_views
36    FROM {{staging.youtube_analytics.channel_basic}}
37    WHERE video_id IS NOT NULL
38    GROUP BY 1, 2, 3
39),
40