Weld logo
instagram-business logo
Instagram business

Media Performance Report

Generates a media performance report for Instagram Business integration. It combines metrics from the media_history and media_insights tables to provide a comprehensive view of monthly media performance. The report includes metrics such as total media posts, likes, comments, carousel engagement, impressions, reach, reel likes, plays, feed engagement, impressions, and reach.
1with
2    MonthlyMediaMetrics as (
3        -- Aggregating metrics from media_history
4        select
5            user_id
6          , date_trunc(_weld_synced_at, month) month
7          , count(distinct id) as total_media_posts
8          , sum(like_count) as total_likes
9          , sum(comments_count) as total_comments
10        from
11            {{raw.instagram.media_history}}
12        group by
13            user_id
14          , month
15    )
16  , MonthlyMediaInsightsMetrics as (
17        -- Aggregating metrics from media_insights
18        select
19            h.user_id
20          , date_trunc(i._weld_synced_at, month) month
21          , sum(carousel_album_engagement) as total_carousel_engagement
22          , sum(carousel_album_impressions) as total_carousel_impressions
23          , sum(carousel_album_reach) as total_carousel_reach
24          , sum(carousel_album_saved) as total_carousel_saved
25          , sum(reel_likes) as total_reel_likes
26          , sum(reel_plays) as total_reel_plays
27          , sum(reel_reach) as total_reel_reach
28          , sum(feed_engagement) as total_feed_engagement
29          , sum(feed_impressions) as total_feed_impressions
30          , sum(feed_reach) as total_feed_reach
31        from
32            {{raw.instagram.media_insights}} i
33            left join {{raw.instagram.media_history}} h on i.id = h.id
34        group by
35            month
36          , h.user_id
37    ) -- Merging the two aggregated tables to provide a comprehensive view
38select
39    mmm.user_id
40  , mmm.month
41  , mmm.total_media_posts
42  , mmm.total_likes
43  , mmm.total_comments
44  , mmim.total_carousel_engagement
45  , mmim.total_carousel_impressions
46  , mmim.total_carousel_reach
47  , mmim.total_carousel_saved
48  , mmim.total_reel_likes
49  , mmim.total_reel_plays
50  , mmim.total_reel_reach
51  , mmim.total_feed_engagement
52  , mmim.total_feed_impressions
53  , mmim.total_feed_reach
54from
55    MonthlyMediaMetrics mmm
56    join MonthlyMediaInsightsMetrics mmim on mmm.user_id = mmim.user_id
57    and mmm.month = mmim.month
58order by
59    mmm.user_id
60  , mmm.month desc;
Example of output from model:

+---------+------------+-------------------+--------------+----------------+---------------------------+-----------------------------+---------------------------+-------------------------+--------------------+----------------+-----------------+-----------------------+-------------------------+-------------------+
| user_id |   month    | total_media_posts | total_likes  | total_comments | total_carousel_engagement | total_carousel_impressions | total_carousel_reach     | total_carousel_saved    | total_reel_likes   | total_reel_plays | total_reel_reach | total_feed_engagement | total_feed_impressions   | total_feed_reach  |
+---------+------------+-------------------+--------------+----------------+---------------------------+-----------------------------+---------------------------+-------------------------+--------------------+-----------------+-----------------------+-------------------------+-------------------+
|   123   | 2022-01-01 |        10         |     1000     |      200       |           500             |           10000             |           2000            |          1000           |        3000        |       5000      |        10000      |          2000          |          50000          |       10000       |
|   123   | 2021-12-01 |        15         |     1500     |      300       |           700             |           12000             |           2500            |          1500           |        4000        |       6000      |        12000      |          2500          |          60000          |       12000       |
|   456   | 2022-01-01 |        5          |     500      |      100       |           200             |           5000              |           1000            |          500            |        1500        |       2500      |        5000       |          1000          |          25000          |       5000        |
|   456   | 2021-12-01 |        8          |     800      |      150       |           400             |           8000              |           1500            |          800            |        2000        |       4000      |        8000       |          1500          |          40000          |       8000        |
+---------+------------+-------------------+--------------+----------------+---------------------------+-----------------------------+---------------------------+-------------------------+--------------------+-----------------+-----------------------+-------------------------+-------------------+

The SQL template "Media Performance Report" is designed to analyze the performance of Instagram Business accounts. It combines data from two tables, "media_history" and "media_insights", to provide a comprehensive view of media metrics. The template starts by aggregating metrics from the "media_history" table, including the total number of media posts, likes, and comments for each user and month. It then proceeds to aggregate metrics from the "media_insights" table, incorporating metrics such as carousel engagement, impressions, reach, saved, reel likes, plays, feed engagement, impressions, and reach. By merging the two aggregated tables, the SQL template provides valuable insights into the performance of Instagram media. Users can analyze trends, track engagement levels, and understand the impact of their media content over time. The results are sorted by user ID and month in descending order, allowing for easy analysis and comparison. Overall, the SQL template "Media Performance Report" is a powerful tool for Instagram Business users to gain actionable insights and make data-driven decisions to optimize their media performance on the platform.

Ready to start modeling your own instagram-business data?

Get started building your data warehouse with instagram-business and connect all your apps and databases.

instagram-business logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.