Page performance daily

1-- core.facebook_pages.page_performance_daily
2-- The Facebook Page Insights overview per Page per day: followers, new likes,
3-- views and viewers of the Page's content, engagement, Page visits and video
4-- views.
5--
6-- Grain: date_day x page_id. Days are Pacific Time, as Page Insights shows them.
7-- Depends on: staging.facebook_pages.page_daily_insights, .page
8--
9-- FOLLOWERS IS A RUNNING TOTAL. page_follows is the follower count at the end of
10-- the day, so `followers` must never be summed across days; follower_change is
11-- the difference from the previous calendar day, NULL when that day is missing. Every other metric here counts the day itself and
12-- can be summed, except `viewers`, which is unique people per day.
13--
14-- VIEWS REPLACED IMPRESSIONS. Meta removed Page impressions and reach from the
15-- API on 15 June 2026 and replaced them with views (page_media_view) and viewers
16-- (page_total_media_view_unique). post_impressions_legacy and post_reach_legacy
17-- carry the old metrics for the days before that and stay NULL afterwards; views
18-- and impressions are counted differently, so do not splice them into one series.
19
20WITH pivoted AS (
21    SELECT
22        page_id,
23        date_day,
24        MAX(IF(metric = 'page_follows' AND breakdown IS NULL, value, NULL))                                AS followers,
25        SUM(IF(metric = 'page_fan_adds_by_paid_non_paid_unique' AND breakdown = 'total', value, 0))        AS new_likes,
26        SUM(IF(metric = 'page_fan_adds_by_paid_non_paid_unique' AND breakdown = 'paid', value, 0))         AS new_likes_paid,
27        SUM(IF(metric = 'page_fan_adds_by_paid_non_paid_unique' AND breakdown = 'unpaid', value, 0))       AS new_likes_organic,
28        MAX(IF(metric = 'page_media_view' AND breakdown IS NULL, value, NULL))                             AS views,
29        MAX(IF(metric = 'page_total_media_view_unique' AND breakdown IS NULL, value, NULL))                AS viewers,
30        SUM(IF(metric = 'page_post_engagements' AND breakdown IS NULL, value, 0))                          AS post_engagements,
31        SUM(IF(metric = 'page_actions_post_reactions_total', value, 0))                                    AS post_reactions,
32        SUM(IF(metric = 'page_views_total' AND breakdown IS NULL, value, 0))                               AS page_views,
33        SUM(IF(metric = 'page_total_actions' AND breakdown IS NULL, value, 0))                             AS page_actions,
34        SUM(IF(metric = 'page_video_views' AND breakdown IS NULL, value, 0))                               AS video_views,
35        -- Facebook reports video view time in milliseconds.
36        SUM(IF(metric = 'page_video_view_time' AND breakdown IS NULL, value, 0)) / 1000                    AS video_view_time_seconds,
37        MAX(IF(metric = 'page_posts_impressions' AND breakdown IS NULL, value, NULL))                      AS post_impressions_legacy,
38        MAX(IF(metric = 'page_posts_impressions_unique' AND breakdown IS NULL, value, NULL))               AS post_reach_legacy
39    FROM {{staging.facebook_pages.page_daily_insights}}
40    GROUP BY 1, 2