Weld logo
instagram-business logo
Instagram business

Profile Overview Report

Profile overview report for Instagram Business integration. It combines data from different tables to calculate average follows count, average followers count, average profile views, and average website clicks for each user on a monthly basis. The results are then ordered by month.
1with
2    MonthlyData as (
3        select
4            date_trunc(_weld_synced_at, month) as month
5          , avg(follows_count) as avg_follows_count
6          , avg(followers_count) as avg_followers_count
7          , id as user_id
8        from
9            {{raw.instagram.user_history}}
10        group by
11            month
12          , user_id
13    )
14  , MonthlyProfileViews as (
15        select
16            date_trunc(_weld_synced_at, month) as month
17          , avg(profile_views) as avg_profile_views
18          , user_id
19        from
20            {{raw.instagram.user_insights}}
21        group by
22            month
23          , user_id
24    )
25  , MonthlyWebsiteClicks as (
26        select
27            date_trunc(_weld_synced_at, month) as month
28          , avg(website_clicks) as avg_website_clicks
29          , user_id
30        from
31            {{raw.instagram.user_insights}}
32        group by
33            month
34          , user_id
35    )
36select
37    md.month
38  , md.avg_follows_count
39  , md.avg_followers_count
40  , mpv.avg_profile_views
41  , mwc.avg_website_clicks
42from
43    MonthlyData md
44    join MonthlyProfileViews mpv on md.month = mpv.month
45    and md.user_id = mpv.user_id
46    join MonthlyWebsiteClicks mwc on md.month = mwc.month
47    and md.user_id = mwc.user_id
48order by
49    md.month
Example of output from model:
+---------------------+-------------------+---------------------+---------------------+
|       month         | avg_follows_count | avg_followers_count | avg_profile_views   |
+---------------------+-------------------+---------------------+---------------------+
| 2022-01-01 00:00:00 |       1500        |        5000         |        2000         |
| 2022-02-01 00:00:00 |       1800        |        5500         |        2200         |
| 2022-03-01 00:00:00 |       1600        |        5200         |        2100         |
+---------------------+-------------------+---------------------+---------------------+
`

The Profile Overview Report SQL template is designed to provide an overview of key metrics for Instagram Business profiles. By integrating with Instagram's user history and user insights data, this SQL model calculates average values for metrics such as follows count, followers count, profile views, and website clicks on a monthly basis. The SQL code utilizes common table expressions (CTEs) to organize and aggregate the data. The MonthlyData CTE calculates the average follows count and followers count for each user, grouped by month. The MonthlyProfileViews CTE calculates the average profile views for each user, also grouped by month. Similarly, the MonthlyWebsiteClicks CTE calculates the average website clicks for each user, grouped by month. The final SELECT statement combines the data from the three CTEs, joining them based on the month and user ID. This allows for a comprehensive overview of the profile's performance over time. The result set includes the month, average follows count, average followers count, average profile views, and average website clicks. This SQL template can be useful for analyzing the growth and engagement of Instagram Business profiles. By visualizing the data obtained from this SQL, users can gain insights into the trends and patterns of their profile's performance. They can identify the months with the highest or lowest engagement, track the effectiveness of their marketing efforts, and make data-driven decisions to optimize their Instagram presence.

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.