Profile Overview Report

1-- core.instagram_business.profile_overview_report
2-- Profile Overview Report. Profile overview report for Instagram Business
3-- integration. For each account and month it returns the average follows and
4-- followers count, the followers gained, the average daily reach and the number of
5-- taps on the links in the profile. The results are ordered by account and month.
6--
7-- Grain: account x month. Follows and followers are the average of the account
8-- snapshots taken in that month: Instagram only returns the current count, so
9-- the snapshot time is when it was measured. Followers gained and reach come from
10-- Instagram's daily account insights and are dated by the date Instagram returns
11-- with each value. Instagram returns the daily follower count for the last 30
12-- days only, so older months show followers gained only if a sync collected them
13-- at the time. Profile link taps come from the daily aggregated account insights;
14-- days with no taps have no row and count as zero. The synced account insights
15-- have no profile views or website clicks; profile link taps are the closest
16-- metric, so the report returns those instead.
17--
18-- From the weld.app template library, ported to the current synced schema. Not
19-- reconciled against Instagram Business; check the output against the source
20-- before relying on it.
21
22with
23    MonthlyProfile as (
24        select
25            id as user_id
26          , date_trunc(date(_weld_synced_at), month) as month
27          , avg(follows_count) as avg_follows_count
28          , avg(followers_count) as avg_followers_count
29        from
30            {{raw.instagram.user_history}}
31        group by
32            user_id
33          , month
34    )
35  , MonthlyInsights as (
36        select
37            ui.user_id
38          , date_trunc(date(ui.date), month) as month
39          , sum(ui.follower_count) as new_followers
40          , avg(ui.reach) as avg_daily_reach