Account Report

1-- core.google_analytics_4.account_report
2-- Account Report. This SQL template generates a comprehensive account report by
3-- aggregating user statistics, campaign performance, and event data from Google
4-- Analytics. It combines these metrics to provide insights on total users, new
5-- users, sessions, user engagement, campaign details, and event revenue for each
6-- day. The results are ordered by date in descending order.
7--
8-- Grain: day x property x campaign. The day's user and event totals repeat on
9-- every campaign row of that day, so sum them from one row per day, not
10-- across campaigns.
11--
12-- From the weld.app template library, corrected. Not reconciled against Google
13-- Analytics 4; check the output against the source before relying on it.
14
15WITH
16  user_stats AS (
17    SELECT
18      EXTRACT(DATE FROM date) AS date_day,
19      property_id,
20      SUM(total_users) AS total_users,
21      SUM(new_users) AS new_users,
22      SUM(sessions) AS sessions,
23      SUM(user_engagement_duration) AS user_engagement_duration
24    FROM
25      {{raw.google_analytics_4.audience_overview}}
26    GROUP BY
27      date_day, property_id
28  ),
29  campaign_stats AS (
30    SELECT
31      property_id,
32      session_campaign_name,
33      EXTRACT(DATE FROM date) AS date_day,
34      SUM(total_users) AS campaign_total_users,
35      SUM(sessions) AS campaign_sessions,
36      SUM(engaged_sessions) AS campaign_engaged_sessions,
37      AVG(engagement_rate) AS campaign_engagement_rate,
38      SUM(event_count) AS campaign_event_count,
39      SUM(total_revenue) AS campaign_total_revenue
40    FROM