Weld logo
google-analytics-4 logo
Google analytics 4

Account Report

This SQL template generates a comprehensive account report by aggregating user statistics, campaign performance, and event data from Google Analytics. It combines these metrics to provide insights on total users, new users, sessions, user engagement, campaign details, and event revenue for each day. The results are ordered by date in descending order.
1with
2    user_stats as (
3        select
4            extract(
5                date
6                from
7                    date
8            ) as date_day
9          , property_id
10          , sum(total_users) as total_users
11          , sum(new_users) as new_users
12          , sum(sessions) as sessions
13          , sum(user_engagement_duration) as user_engagement_duration
14        from
15            {{raw.google_analytics_4.audience_overview}}
16        group by
17            date_day
18          , property_id
19    )
20  , campaign_stats as (
21        select
22            property_id
23          , session_campaign_name
24          , extract(
25                date
26                from
27                    date
28            ) as date_day
29          , sum(total_users) as campaign_total_users
30          , sum(sessions) as campaign_sessions
31          , sum(engaged_sessions) as campaign_engaged_sessions
32          , avg(engagement_rate) as campaign_engagement_rate
33          , sum(event_count) as campaign_event_count
34          , sum(total_revenue) as campaign_total_revenue
35        from
36            {{raw.google_analytics_4.campaign_performance}}
37        group by
38            property_id
39          , session_campaign_name
40          , date_day
41    )
42  , event_stats as (
43        select
44            extract(
45                date
46                from
47                    date
48            ) as date_day
49          , sum(event_count) as event_total_count
50          , sum(total_revenue) as event_total_revenue
51        from
52            {{raw.google_analytics_4.events_overview}}
53        group by
54            date_day
55    )
56select
57    us.date_day
58  , us.property_id as account_id
59  , us.total_users
60  , us.new_users
61  , us.sessions
62  , us.user_engagement_duration
63  , cs.session_campaign_name as campaign_name
64  , cs.campaign_total_users
65  , cs.campaign_sessions
66  , cs.campaign_engaged_sessions
67  , cs.campaign_engagement_rate
68  , cs.campaign_event_count
69  , cs.campaign_total_revenue
70  , es.event_total_count
71  , es.event_total_revenue
72from
73    user_stats us
74    left join campaign_stats cs on us.date_day = cs.date_day
75    and us.property_id = cs.property_id
76    left join event_stats es on us.date_day = es.date_day
77order by
78    us.date_day desc;
Example of output from model:
date_day   | account_id | total_users | new_users | sessions | user_engagement_duration | campaign_name | campaign_total_users | campaign_sessions | campaign_engaged_sessions | campaign_engagement_rate | campaign_event_count | campaign_total_revenue | event_total_count | event_total_revenue
-----------|------------|-------------|-----------|----------|--------------------------|---------------|----------------------|-------------------|---------------------------|--------------------------|----------------------|-----------------------|-------------------|---------------------
2023-10-01 | 12345      | 1500        | 300       | 2000     | 36000                    | Fall_Sale     | 500                  | 700               | 600                       | 0.85                     | 1200                 | 1500.00               | 1800              | 2000.00
2023-10-01 | 12346      | 1200        | 250       | 1600     | 32000                    | Summer_Sale   | 400                  | 600               | 500                       | 0.83                     | 1000                 | 1200.00               | 1500              | 1700.00
2023-10-01 | 12347      | 1000        | 200       | 1400     | 28000                    | Winter_Sale   | 300                  | 500               | 400                       | 0.80                     | 800                  | 1000.00               | 1200              | 1500.00
2023-09-30 | 12345      | 1400        | 280       | 1900     | 35000                    | Fall_Sale     | 480                  | 680               | 580                       | 0.84                     | 1150                 | 1450.00               | 1750              | 1950.00
2023-09-30 | 12346      | 1100        | 230       | 1500     | 31000                    | Summer_Sale   | 380                  | 580               | 480                       | 0.82                     | 950                  | 1150.00               | 1400              | 1600.00
2023-09-30 | 12347      | 900         | 180       | 1300     | 27000                    | Winter_Sale   | 280                  | 480               | 380                       | 0.79                     | 750                  | 950.00                | 1100              | 1400.00

The Account Report SQL model aggregates and combines data from Google Analytics to provide a comprehensive overview of user activity, campaign performance, and event metrics for different properties. By extracting daily statistics, it offers insights into total and new users, session counts, user engagement duration, campaign-specific user and session data, engagement rates, event counts, and revenue. This model is useful for understanding user behavior trends, evaluating the effectiveness of marketing campaigns, and analyzing event-driven revenue, helping businesses make data-driven decisions to optimize their online presence and 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.