Media Performance Report
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;
+---------+------------+-------------------+--------------+----------------+---------------------------+-----------------------------+---------------------------+-------------------------+--------------------+----------------+-----------------+-----------------------+-------------------------+-------------------+
| 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.
