Media Insights Deep Dive

1-- core.instagram_business.media_insights_deep_dive
2-- Media Insights Deep Dive. Retrieves various metrics for different types of media
3-- posts (carousel, reel, story, feed). It also calculates engagement rates for each
4-- type of media. The template joins the media data with the media history table to
5-- include additional information such as caption, timestamp, media type, likes
6-- count, and comments count. The final result is sorted by month and media posted
7-- date in descending order.
8--
9-- Grain: one row per media post, dated by the month it was posted. Insights
10-- are lifetime totals as of the latest sync. Instagram has renamed some
11-- metrics: reel plays are now views, and story exits and taps are reported as
12-- story_tap_exit, story_tap_back and story_tap_forward; the output keeps the
13-- older column names where the meaning is the same. Instagram reports views
14-- instead of impressions for current posts, so the views columns replace the
15-- impressions and video views columns, and the carousel and feed engagement
16-- rates are per view. Feed engagement is the sum of a feed post's likes,
17-- comments, saves and shares, which is how Instagram defines its total
18-- interactions.
19--
20-- From the weld.app template library, corrected. Not reconciled against
21-- Instagram Business; check the output against the source before relying on it.
22
23with
24    latest_media as (
25        -- media_history keeps a snapshot of each post per sync; the latest one
26        -- carries the current like and comment counts
27        select
28            *
29        from
30            {{raw.instagram.media_history}}
31        where
32            true
33        qualify
34            row_number() over(
35                partition by
36                    id
37                order by
38                    _weld_synced_at desc
39            ) = 1
40    )