Weld logo
google-analytics-4 logo
Google analytics 4

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              | 15500

The "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.

Ready to start modeling your own google-analytics-4 data?

Get started building your data warehouse with google-analytics-4 and connect all your apps and databases.

google-analytics-4 logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.