Google analytics
User Activity Overview
This SQL template aggregates monthly user activity data from Google Analytics, including total users, new users, sessions, average sessions per user, screen page views, and user engagement duration.
1select
2 extract(
3 month
4 from
5 date
6 ) as month
7 , sum(total_users) as total_users
8 , sum(new_users) as new_users
9 , sum(sessions) as sessions
10 , avg(sessions_per_user) as sessions_per_user
11 , sum(screen_page_views) as screen_page_views
12 , sum(user_engagement_duration) as user_engagement_duration
13from
14 {{raw.google_analytics_4.audience_overview}}
15group by
16 month
17order by
18 month;Example of output from model:
month | total_users | new_users | sessions | sessions_per_user | screen_page_views | user_engagement_duration
------|--------------|-----------|----------|-------------------|-------------------|--------------------------
1 | 1500 | 300 | 2000 | 1.33 | 5000 | 10000
2 | 1600 | 320 | 2100 | 1.31 | 5200 | 10500
3 | 1700 | 340 | 2200 | 1.29 | 5400 | 11000
4 | 1800 | 360 | 2300 | 1.28 | 5600 | 11500
5 | 1900 | 380 | 2400 | 1.26 | 5800 | 12000
6 | 2000 | 400 | 2500 | 1.25 | 6000 | 12500
7 | 2100 | 420 | 2600 | 1.24 | 6200 | 13000
8 | 2200 | 440 | 2700 | 1.23 | 6400 | 13500
9 | 2300 | 460 | 2800 | 1.22 | 6600 | 14000
10 | 2400 | 480 | 2900 | 1.21 | 6800 | 14500
11 | 2500 | 500 | 3000 | 1.20 | 7000 | 15000
12 | 2600 | 520 | 3100 | 1.19 | 7200 | 15500The "User Activity Overview" SQL model leverages Google Analytics data to provide a comprehensive monthly summary of user engagement metrics. This SQL query extracts and aggregates data from the Google Analytics 4 audience overview, grouping the results by month. Key metrics include the total number of users, new users, sessions, average sessions per user, screen page views, and user engagement duration. This model is useful for identifying trends in user activity over time, understanding user engagement patterns, and making data-driven decisions to enhance user experience and optimize marketing strategies.
