Campaign Report

This SQL template generates a monthly summary report of campaign performance metrics from Google Analytics data. It aggregates data such as total users, sessions, engaged sessions, events per session, engagement rate, event count, conversions, total revenue, and user engagement duration. The results are ordered by period and total users in descending order.

1with
2    campaign_stats as (
3        select
4            date_trunc(date, month) as period -- Change to 'DAY' for daily overview
5          , session_campaign_name
6          , sum(total_users) as total_users
7          , sum(sessions) as sessions
8          , sum(engaged_sessions) as engaged_sessions
9          , avg(events_per_session) as events_per_session
10          , avg(engagement_rate) as engagement_rate
11          , sum(event_count) as event_count
12          , sum(conversions) as conversions
13          , sum(total_revenue) as total_revenue
14          , sum(user_engagement_duration) as user_engagement_duration
15        from
16            {{raw.google_analytics_4.campaign_performance}}
17        group by
18            period
19          , session_campaign_name
20    )
21select
22    period
23  , session_campaign_name as campaign
24  , total_users
25  , sessions
26  , engaged_sessions
27  , events_per_session
28  , engagement_rate
29  , event_count
30  , conversions
31  , total_revenue
32  , user_engagement_duration
33from
34    campaign_stats
35order by
36    period desc
37  , total_users desc;

Example of output from model:

period     | campaign       | total_users | sessions | engaged_sessions | events_per_session | engagement_rate | event_count | conversions | total_revenue | user_engagement_duration
-----------|----------------|-------------|----------|------------------|--------------------|-----------------|-------------|-------------|---------------|-------------------------
2023-09-01 | Campaign A     | 1500        | 2000     | 1800             | 3.5                | 0.9             | 7000        | 300         | 5000          | 12000
2023-09-01 | Campaign B     | 1200        | 1500     | 1300             | 2.8                | 0.87            | 4200        | 250         | 4000          | 10000
2023-08-01 | Campaign A     | 1400        | 1800     | 1600             | 3.2                | 0.88            | 6400        | 280         | 4800          | 11500
2023-08-01 | Campaign C     | 1100        | 1400     | 1250             | 2.9                | 0.89            | 4050        | 220         | 3500          | 9500
2023-07-01 | Campaign B     | 1300        | 1700     | 1500             | 3.1                | 0.88            | 5300        | 270         | 4500          | 10500
2023-07-01 | Campaign D     | 900         | 1100     | 1000             | 2.7                | 0.91            | 2970        | 180         | 3200          | 8500

The Campaign Report SQL model is designed to aggregate and analyze campaign performance data from Google Analytics. By summarizing key metrics such as total users, sessions, engaged sessions, events per session, engagement rate, event count, conversions, total revenue, and user engagement duration, this model provides a comprehensive overview of campaign effectiveness over specified periods. This SQL template is particularly useful for marketers and analysts looking to track the performance of different campaigns, identify trends, and make data-driven decisions to optimize marketing strategies. The output allows users to compare campaigns based on user engagement and revenue generation, helping to pinpoint which campaigns are most successful and where improvements can be made.

Campaign Report

Ready to start modeling your own google-analytics-4 data?