Weld logo
instagram-business logo
Instagram business

Growth Analysis

Calculates the growth rates of various metrics for an Instagram Business account. It retrieves monthly data on average followers, average follows, total feed engagement, total carousel engagement, and total reel likes. It then calculates the growth rate percentages for each metric compared to the previous month. The results are sorted by month in descending order.
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;
Example of output from model:

+-------+-------------------------------+-------------------------------+-------------------------------------+-----------------------------------------------+-------------------------------+
| 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.

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 logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

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