User Engagement and Outreach Report

1-- core.instagram_business.user_engagement_and_outreach_report
2-- User Engagement and Outreach Report. Generates a report on user engagement and
3-- outreach for an Instagram Business integration. For each account and month it
4-- returns the average follower count and its change from the previous month, the
5-- followers gained, the average daily reach, the profile link taps, and the
6-- average likes, comments, feed views and feed reach per post. The result is
7-- sorted by month and user ID.
8--
9-- Grain: account x month. The follower count is the average of the account
10-- snapshots taken in that month: Instagram only returns the current count, so
11-- the snapshot time is when it was measured. Followers gained and reach come from
12-- Instagram's daily account insights, dated by the date Instagram returns with
13-- each value; Instagram returns the daily follower count for the last 30 days
14-- only. Profile link taps come from the daily aggregated account insights; days
15-- with no taps have no row and count as zero. Post metrics count each post once,
16-- with its latest lifetime counts, in the month it was posted; feed views and feed
17-- reach are averaged over the posts that have them. The synced data has no
18-- profile views or website clicks, so profile link taps take their place, and
19-- feed views take the place of feed impressions, which Instagram no longer
20-- reports for current posts.
21--
22-- From the weld.app template library, ported to the current synced schema. Not
23-- reconciled against Instagram Business; check the output against the source
24-- before relying on it.
25
26with
27    MonthlyFollowers as (
28        select
29            id as user_id
30          , date_trunc(date(_weld_synced_at), month) as month
31          , avg(followers_count) as avg_followers_count
32        from
33            {{raw.instagram.user_history}}
34        group by
35            user_id
36          , month
37    )
38  , FollowerGrowth as (
39        select
40            user_id