Engagment Metric Analysis

1with
2    MonthlyEngagementData as (
3        select
4            format_date('%Y-%m', date(mh._weld_synced_at)) as month
5          , count(distinct mh.id) as total_media_posts
6          , sum(mh.like_count) as total_likes
7          , sum(mh.comments_count) as total_comments
8          , sum(mi.feed_engagement) as total_feed_engagement
9          , sum(mi.carousel_album_engagement) as total_carousel_engagement
10          , sum(mi.reel_likes) as total_reel_likes
11          , sum(mi.reel_comments) as total_reel_comments
12          , sum(mi.story_replies) as total_story_replies
13          , sum(mi.story_exits) as total_story_exits
14        from
15            {{raw.instagram.media_history}} mh
16            left join {{raw.instagram.media_insights}} mi on mh.id = mi.id
17        group by
18            month
19    )
20select
21    month
22  , total_media_posts
23  , total_likes
24  , total_comments
25  , total_feed_engagement
26  , total_carousel_engagement
27  , total_reel_likes
28  , total_reel_comments
29  , total_story_replies
30  , total_story_exits -- Engagement per post metrics
31  , round(total_likes / nullif(total_media_posts, 0), 2) as avg_likes_per_post
32  , round(total_comments / nullif(total_media_posts, 0), 2) as avg_comments_per_post
33  , round(
34        total_feed_engagement / nullif(total_media_posts, 0)
35      , 2
36    ) as avg_feed_engagement_per_post
37  , round(
38        total_carousel_engagement / nullif(total_media_posts, 0)
39      , 2
40    ) as avg_carousel_engagement_per_post
41  , round(
42        total_reel_likes / nullif(total_media_posts, 0)
43      , 2
44    ) as avg_reel_likes_per_post
45  , round(
46        total_reel_comments / nullif(total_media_posts, 0)
47      , 2
48    ) as avg_reel_comments_per_post
49from
50    MonthlyEngagementData
51order by
52    month desc;