Engagment Metric Analysis

1-- core.instagram_business.engagement_metric_analysis
2-- Engagment Metric Analysis. Calculates various engagement metrics for Instagram
3-- Business accounts on a monthly basis. It retrieves data such as the total number
4-- of media posts, likes, comments, feed engagement, carousel engagement, reel
5-- likes, reel comments, story replies, and story exits. Additionally, it calculates
6-- average engagement per post metrics, including average likes, comments, feed
7-- engagement, carousel engagement, reel likes, and reel comments per post. The
8-- results are ordered by month in descending order.
9--
10-- Grain: month the media was posted. Each post counts once, with its latest
11-- lifetime likes, comments and insights, so a month's totals keep growing
12-- while its posts collect engagement. Instagram reports story exits as
13-- story_tap_exit. Feed engagement is the sum of a feed post's likes, comments,
14-- saves and shares, which is how Instagram defines its total interactions.
15--
16-- From the weld.app template library, corrected. Not reconciled against
17-- Instagram Business; check the output against the source before relying on it.
18
19with
20    latest_media as (
21        -- media_history keeps a snapshot of each post per sync; the latest one
22        -- carries the current like and comment counts
23        select
24            *
25        from
26            {{raw.instagram.media_history}}
27        where
28            true
29        qualify
30            row_number() over(
31                partition by
32                    id
33                order by
34                    _weld_synced_at desc
35            ) = 1
36    )
37  , insights as (
38        -- Instagram's total interactions for a post are its likes, comments,
39        -- saves and shares. Feed posts are synced with those counts but without
40        -- the total, so feed engagement is their sum; carousels use the synced