Video performance

1-- core.tiktok_organic.video_performance
2-- The TikTok Analytics Content tab: every video with its lifetime views, reach,
3-- likes, comments, shares, watch time, completion rate and engagement rate.
4--
5-- Grain: one row per video. Metrics are lifetime totals as of `metrics_as_of`,
6-- the last sync, and keep growing while a video gets views, so this is a
7-- snapshot, not a time series.
8-- Depends on: staging.tiktok_organic.video, .profile
9--
10-- engagement_rate = (likes + comments + shares) / video_views, the usual TikTok
11-- definition. engagement_rate_by_reach divides by unique viewers instead. To roll
12-- up a set of videos, divide summed engagements by summed video_views; reach is
13-- unique per video, so summed reach over videos counts a person once per video.
14
15SELECT
16    v.video_id,
17    v.business_id,
18    p.username,
19    v.created_at,
20    DATE(v.created_at)                                          AS created_date,
21    v.caption,
22    v.share_url,
23    v.thumbnail_url,
24    v.duration_seconds,
25    v.synced_at                                                 AS metrics_as_of,
26    v.video_views,
27    v.reach,
28    v.likes,
29    v.comments,
30    v.shares,
31    v.likes + v.comments + v.shares                             AS engagements,
32    SAFE_DIVIDE(v.likes + v.comments + v.shares, v.video_views) AS engagement_rate,
33    SAFE_DIVIDE(v.likes + v.comments + v.shares, v.reach)       AS engagement_rate_by_reach,
34    v.total_time_watched_seconds,
35    v.average_time_watched_seconds,
36    SAFE_DIVIDE(v.average_time_watched_seconds, v.duration_seconds) AS average_share_watched,
37    v.full_video_watched_rate
38FROM {{staging.tiktok_organic.video}} v
39-- One row per account after staging, so this cannot fan out.
40LEFT JOIN {{staging.tiktok_organic.profile}} p USING (business_id)