Weld logo
instagram-business logo
Instagram business

User Engagement and Outreach Report

Generates a report on user engagement and outreach for an Instagram Business integration. It combines data from various tables to calculate average follower count, profile views, website clicks, like count, comments count, feed impressions, and feed reach on a monthly basis. The result is sorted by month and user ID.
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;
Example of output from model:

+---------------------+---------------------+----------------+---------------------+----------------------+-------------------+----------------------+-------------------------+---------------------+
|        month        | avg_followers_count | follower_growth | avg_profile_views   | avg_website_clicks   | avg_like_count    | avg_comments_count   | avg_feed_impressions    | avg_feed_reach      |
+---------------------+---------------------+----------------+---------------------+----------------------+-------------------+----------------------+-------------------------+---------------------+
| 2021-01-01 00:00:00 | 1000                | 0              | 500                 | 100                  | 50                | 10                   | 1000                    | 500                 |
| 2021-02-01 00:00:00 | 1200                | 200            | 600                 | 150                  | 60                | 12                   | 1200                    | 600                 |
| 2021-03-01 00:00:00 | 1500                | 300            | 700                 | 200                  | 70                | 15                   | 1500                    | 700                 |
+---------------------+---------------------+----------------+---------------------+----------------------+-------------------+----------------------+-------------------------+---------------------+

The SQL template "User Engagement and Outreach Report" is designed to analyze user engagement and outreach data from Instagram Business. It consists of three main subqueries: MonthlyFollowers, MonthlyProfileEngagement, and MonthlyMediaEngagement. The MonthlyFollowers subquery calculates the average number of followers for each user on a monthly basis. It groups the data by month and user ID, providing insights into the growth of followers over time. The MonthlyProfileEngagement subquery calculates the average profile views and website clicks for each user on a monthly basis. It also groups the data by month and user ID, allowing for analysis of user engagement with the profile and website. The MonthlyMediaEngagement subquery calculates the average number of likes, comments, feed impressions, and feed reach for each user's media content on a monthly basis. It joins the media history and insights tables, ensuring that the data is synchronized correctly. This subquery provides insights into the engagement and reach of the user's media content. Finally, the main query combines the results from the three subqueries, joining them based on the month and user ID. It calculates the follower growth by subtracting the previous month's average followers count from the current month's count. The query also includes the average profile views, website clicks, likes, comments, feed impressions, and feed reach for each user. This SQL template is useful for generating a comprehensive report on user engagement and outreach on Instagram Business. It provides insights into follower growth, profile views, website clicks, and media engagement metrics. By analyzing this data, businesses can gain valuable insights into the effectiveness of their Instagram marketing strategies and make informed decisions to optimize user engagement and outreach.

Ready to start modeling your own instagram-business data?

Get started building your data warehouse with instagram-business and connect all your apps and databases.

instagram-business logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.