Growth Analysis

1-- core.instagram_business.growth_analysis
2-- Growth Analysis. Calculates the growth rates of various metrics for an Instagram
3-- Business account. It retrieves monthly data on average followers, average
4-- follows, total feed engagement, total carousel engagement, and total reel likes.
5-- It then calculates the growth rate percentages for each metric compared to the
6-- previous month. The results are sorted by month in descending order.
7--
8-- Grain: account x month. Followers and follows are the average of the
9-- account snapshots Weld took in that month (the snapshot time is when the
10-- count was read). Engagement is the latest lifetime engagement of the posts
11-- published in that month; feed engagement is the sum of a feed post's likes,
12-- comments, saves and shares. Growth rates compare each month with the
13-- account's previous month in the data.
14--
15-- From the weld.app template library, corrected. Not reconciled against
16-- Instagram Business; check the output against the source before relying on it.
17
18with
19    MonthlyFollowers as (
20        select
21            id as user_id
22          , format_date('%Y-%m', date(_weld_synced_at)) as month
23          , avg(followers_count) as avg_followers
24          , avg(follows_count) as avg_follows
25        from
26            {{raw.instagram.user_history}}
27        group by
28            user_id
29          , month
30    )
31  ,
32    latest_media as (
33        -- media_history keeps a snapshot of each post per sync; the latest one
34        -- carries the current like and comment counts
35        select
36            *
37        from
38            {{raw.instagram.media_history}}
39        where
40            true