User Activity Overview

1-- core.google_analytics_4.user_activity_overview
2-- User Activity Overview. This SQL template aggregates monthly user activity data
3-- from Google Analytics, including total users, new users, sessions, average
4-- sessions per user, screen page views, and user engagement duration.
5--
6-- Grain: month x property. Months are calendar months, so the same month in
7-- different years stays separate. GA4 reports users per day here, so
8-- total_users is a sum of daily users (user-days), not unique users in the
9-- month; sessions_per_user divides the month's sessions by that same sum.
10--
11-- From the weld.app template library, corrected. Not reconciled against Google
12-- Analytics 4; check the output against the source before relying on it.
13
14SELECT
15  DATE_TRUNC(date, MONTH) AS month,
16  property_id,
17  SUM(total_users) AS total_users,
18  SUM(new_users) AS new_users,
19  SUM(sessions) AS sessions,
20  SAFE_DIVIDE(SUM(sessions), SUM(total_users)) AS sessions_per_user,
21  SUM(screen_page_views) AS screen_page_views,
22  SUM(user_engagement_duration) AS user_engagement_duration
23FROM
24  {{raw.google_analytics_4.audience_overview}}
25GROUP BY
26  month,
27  property_id
28ORDER BY
29  month,
30  property_id