User Engagement and Outreach Report

1with
2    MonthlyFollowers as (
3        select
4            date_trunc(_weld_synced_at, month) month
5          , id as user_id
6          , avg(followers_count) as avg_followers_count
7        from
8            {{raw.instagram.user_history}}
9        group by
10            month
11          , user_id
12    )
13  , MonthlyProfileEngagement as (
14        select
15            date_trunc(_weld_synced_at, month) month
16          , user_id
17          , avg(profile_views) as avg_profile_views
18          , avg(website_clicks) as avg_website_clicks
19        from
20            {{raw.instagram.user_insights}}
21        group by
22            month
23          , user_id
24    )
25  , MonthlyMediaEngagement as (
26        select
27            date_trunc(h._weld_synced_at, month) month
28          , user_id
29          , avg(like_count) as avg_like_count
30          , avg(comments_count) as avg_comments_count
31          , avg(feed_impressions) as avg_feed_impressions
32          , avg(feed_reach) as avg_feed_reach
33        from
34            {{raw.instagram.media_history}} h
35            left join {{raw.instagram.media_insights}} i on i.id = h.id
36            and date_trunc(i._weld_synced_at, month) = date_trunc(h._weld_synced_at, month)
37        group by
38            month
39          , user_id
40    )
41select
42    mf.month
43  , mf.avg_followers_count
44  , coalesce(
45        mf.avg_followers_count - lag(mf.avg_followers_count) over (
46            partition by
47                mf.user_id
48            order by
49                mf.month
50        )
51      , 0
52    ) as follower_growth
53  , mpe.avg_profile_views
54  , mpe.avg_website_clicks
55  , mme.avg_like_count
56  , mme.avg_comments_count
57  , mme.avg_feed_impressions
58  , mme.avg_feed_reach
59from
60    MonthlyFollowers mf
61    join MonthlyProfileEngagement mpe on mf.month = mpe.month
62    and mf.user_id = mpe.user_id
63    join MonthlyMediaEngagement mme on mf.month = mme.month
64    and mf.user_id = mme.user_id
65order by
66    mf.month
67  , mf.user_id;