Weld logo
instagram-business
Instagram business

Engagment Metric Analysis

Calculates various engagement metrics for Instagram Business accounts on a monthly basis. It retrieves data such as the total number of media posts, likes, comments, feed engagement, carousel engagement, reel likes, reel comments, story replies, and story exits. Additionally, it calculates average engagement per post metrics, including average likes, comments, feed engagement, carousel engagement, reel likes, and reel comments per post. The results are ordered by month in descending order.
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;
Example of output from model:

+-------+-------------------+-------------+----------------+------------------------+-----------------------------+-------------------+---------------------+---------------------+-------------------+------------------+--------------------------+----------------------------+
| month | total_media_posts | total_likes | total_comments | total_feed_engagement | total_carousel_engagement | total_reel_likes | total_reel_comments | total_story_replies | total_story_exits | avg_likes_per_post       | avg_comments_per_post       |
+-------+-------------------+-------------+----------------+------------------------+-----------------------------+-------------------+---------------------+---------------------+-------------------+------------------+--------------------------+----------------------------+
| 2022-02 | 100               | 500         | 200            | 1000                   | 500                         | 300               | 150                 | 50                  | 100               | 5.00             | 2.00                   |
| 2022-01 | 80                | 400         | 150            | 800                    | 400                         | 250               | 100                 | 40                  | 80                | 5.00             | 1.88                   |
| 2021-12 | 120               | 600         | 250            | 1200                   | 600                         | 350               | 200                 | 60                  | 120               | 5.00             | 2.08                   |
+-------+-------------------+-------------+----------------+------------------------+-----------------------------+-------------------+---------------------+---------------------+-------------------+------------------+--------------------------+----------------------------+

The SQL template "Engagement Metric Analysis" is designed for analyzing engagement metrics from Instagram Business integration. It retrieves data from the tables "media_history" and "media_insights" in the Instagram database. The SQL code calculates various engagement metrics on a monthly basis, including the total number of media posts, likes, comments, feed engagement, carousel engagement, reel likes, reel comments, story replies, and story exits. Additionally, it calculates average engagement per post metrics such as average likes per post, average comments per post, average feed engagement per post, average carousel engagement per post, average reel likes per post, and average reel comments per post. This SQL template is useful for gaining insights into the engagement performance of Instagram Business accounts over time. By analyzing the engagement metrics, users can understand the level of interaction and interest generated by their media posts. The results can help identify trends, evaluate the effectiveness of content strategies, and make data-driven decisions to optimize engagement on Instagram.

Ready to start modeling your own instagram-business data?

Get started building your data warehouse with instagram-business and 100+ more apps and databases available.

instagram-business
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.