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