Profile Overview Report
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+---------------------+-------------------+---------------------+---------------------+
| 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.
