Growth Analysis
1with
2 MonthlyGrowthData as (
3 select
4 format_date('%Y-%m', date(uh._weld_synced_at)) as month
5 , avg(uh.followers_count) as avg_followers
6 , avg(uh.follows_count) as avg_follows
7 , sum(mi.feed_engagement) as total_feed_engagement
8 , sum(mi.carousel_album_engagement) as total_carousel_engagement
9 , sum(mi.reel_likes) as total_reel_likes
10 from
11 {{raw.instagram.user_history}} uh
12 left join {{raw.instagram.media_history}} h on h.user_id = uh.id
13 left join {{raw.instagram.media_insights}} mi on mi.id = h.id
14 group by
15 month
16 )
17 , GrowthRates as (
18 select
19 month
20 , avg_followers
21 , avg_follows
22 , total_feed_engagement
23 , total_carousel_engagement
24 , total_reel_likes
25 , lag(avg_followers, 1) over (
26 order by
27 month
28 ) as last_month_followers
29 , lag(avg_follows, 1) over (
30 order by
31 month
32 ) as last_month_follows
33 , lag(total_feed_engagement, 1) over (
34 order by
35 month
36 ) as last_month_feed_engagement
37 , lag(total_carousel_engagement, 1) over (
38 order by
39 month
40 ) as last_month_carousel_engagement
41 , lag(total_reel_likes, 1) over (
42 order by
43 month
44 ) as last_month_reel_likes
45 from
46 MonthlyGrowthData
47 )
48select
49 month
50 , round(
51 (avg_followers - last_month_followers) / nullif(last_month_followers, 0) * 100
52 , 2
53 ) as followers_growth_rate_percentage
54 , round(
55 (avg_follows - last_month_follows) / nullif(last_month_follows, 0) * 100
56 , 2
57 ) as follows_growth_rate_percentage
58 , round(
59 (
60 total_feed_engagement - last_month_feed_engagement
61 ) / nullif(last_month_feed_engagement, 0) * 100
62 , 2
63 ) as feed_engagement_growth_rate_percentage
64 , round(
65 (
66 total_carousel_engagement - last_month_carousel_engagement
67 ) / nullif(last_month_carousel_engagement, 0) * 100
68 , 2
69 ) as carousel_engagement_growth_rate_percentage
70 , round(
71 (total_reel_likes - last_month_reel_likes) / nullif(last_month_reel_likes, 0) * 100
72 , 2
73 ) as reel_likes_growth_rate_percentage
74from
75 GrowthRates
76order by
77 month desc;
+-------+-------------------------------+-------------------------------+-------------------------------------+-----------------------------------------------+-------------------------------+
| month | followers_growth_rate_percent | follows_growth_rate_percent | feed_engagement_growth_rate_percent | carousel_engagement_growth_rate_percent | reel_likes_growth_rate_percent |
+-------+-------------------------------+-------------------------------+-------------------------------------+-----------------------------------------------+-------------------------------+
| 2022-02 | 10.00 | 5.00 | 15.00 | 20.00 | 12.50 |
| 2022-01 | 8.00 | 3.00 | 12.00 | 18.00 | 10.00 |
| 2021-12 | 6.00 | 2.50 | 10.00 | 15.00 | 8.00 |
| 2021-11 | 4.00 | 2.00 | 8.00 | 12.00 | 6.00 |
| 2021-10 | 2.00 | 1.50 | 6.00 | 10.00 | 4.00 |
+-------+-------------------------------+-------------------------------+-------------------------------------+-----------------------------------------------+-------------------------------+
The Growth Analysis SQL template is designed to analyze the growth rates of various metrics for an Instagram Business account. By integrating with Instagram Business data, this SQL template calculates the percentage growth rates for average followers, average follows, total feed engagement, total carousel engagement, and total reel likes on a monthly basis. The SQL code first creates a temporary table called MonthlyGrowthData, which aggregates the necessary data from the Instagram Business integration. It calculates the average followers, average follows, and total engagement metrics for each month. Next, the code creates another temporary table called GrowthRates. This table calculates the growth rates by comparing the current month's metrics with the previous month's metrics. It uses the lag function to retrieve the values from the previous month. Finally, the SQL code selects the month and calculates the growth rates for each metric using the formula: (current month value - previous month value) / previous month value * 100. These growth rates are rounded to two decimal places. The resulting output provides insights into the growth trends of the Instagram account over time. It shows the percentage growth rates for followers, follows, feed engagement, carousel engagement, and reel likes. By analyzing these growth rates, users can gain valuable insights into the performance and popularity of their Instagram Business account.
