Post performance

1-- core.facebook_pages.post_performance
2-- The Posts list of Page Insights / Meta Business Suite: every post with its
3-- lifetime views, viewers, reactions, comments, shares, clicks and engagement
4-- rate.
5--
6-- Grain: one row per post. Metrics are lifetime totals as of `metrics_as_of`,
7-- the last sync; they keep growing while a post is live, so this is a snapshot,
8-- not a time series. Never sum a post's figures across snapshots.
9-- Depends on: staging.facebook_pages.post, .post_lifetime_insights, .page
10--
11-- ENGAGEMENT RATE = (reactions + comments + shares + clicks) / viewers.
12-- Reactions, comments and shares come from post_activity_by_action_type, clicks
13-- from post_clicks. viewers (post_total_media_view_unique) is Meta's replacement
14-- for reach since June 2026; reach_legacy keeps the old reach figure where it
15-- was synced before the cutover. To roll up a set of posts, divide the summed
16-- engagements by the summed viewers; averaging engagement_rate over posts
17-- weights a small post like a large one.
18
19WITH metrics AS (
20    SELECT
21        post_id,
22        MAX(synced_at)                                                                          AS metrics_as_of,
23        MAX(IF(metric = 'post_media_view_all' AND breakdown IS NULL, value, NULL))              AS views,
24        MAX(IF(metric = 'post_media_view_followers' AND breakdown IS NULL, value, NULL))        AS views_from_followers,
25        MAX(IF(metric = 'post_media_view_non_followers' AND breakdown IS NULL, value, NULL))    AS views_from_non_followers,
26        MAX(IF(metric = 'post_media_view_ads' AND breakdown IS NULL, value, NULL))              AS views_from_ads,
27        MAX(IF(metric = 'post_total_media_view_unique' AND breakdown IS NULL, value, NULL))     AS viewers,
28        MAX(IF(metric = 'post_impressions_unique' AND breakdown IS NULL, value, NULL))          AS reach_legacy,
29        SUM(IF(metric = 'post_activity_by_action_type' AND breakdown = 'like', value, 0))       AS reactions,
30        SUM(IF(metric = 'post_activity_by_action_type' AND breakdown = 'comment', value, 0))    AS comments,
31        SUM(IF(metric = 'post_activity_by_action_type' AND breakdown = 'share', value, 0))      AS shares,
32        SUM(IF(metric = 'post_clicks' AND breakdown IS NULL, value, 0))                         AS clicks
33    FROM {{staging.facebook_pages.post_lifetime_insights}}
34    GROUP BY 1
35)
36
37SELECT
38    p.post_id,
39    p.page_id,
40    pg.page_name,