Account Report

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;