Weld
Start free
Google Analytics 4 model

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.

See the SQLAll Google Analytics 4 models
Grain
See the notes
Built from
  1. 1
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Google Analytics 4
campaign_performance
Ships as
Weld and dbt SQL, open source on GitHub
core.google_analytics_4.campaign_report
1-- core.google_analytics_4.campaign_report
2-- Campaign Report. This SQL template generates a monthly summary report of campaign
3-- performance metrics from Google Analytics data. It aggregates data such as total
4-- users, sessions, engaged sessions, events per session, engagement rate, event
5-- count, conversions, total revenue, and user engagement duration. The results are
6-- ordered by period and total users in descending order.
7--
8-- From the weld.app template library. Not reconciled against Google Analytics 4;
9-- check the output against the source before relying on it.
10
11WITH
12  campaign_stats AS (
13    SELECT
14      DATE_TRUNC(date, MONTH) AS period,  -- Change to 'DAY' for daily overview
15      session_campaign_name,
16      SUM(total_users) AS total_users,
17      SUM(sessions) AS sessions,
18      SUM(engaged_sessions) AS engaged_sessions,
19      AVG(events_per_session) AS events_per_session,
20      AVG(engagement_rate) AS engagement_rate,
21      SUM(event_count) AS event_count,
22      SUM(conversions) AS conversions,
23      SUM(total_revenue) AS total_revenue,
24      SUM(user_engagement_duration) AS user_engagement_duration
25    FROM
26      {{raw.google_analytics_4.campaign_performance}}
27    GROUP BY
28      period, session_campaign_name
29  )
30
31SELECT
32  period,
33  session_campaign_name AS campaign,
34  total_users,
35  sessions,
36  engaged_sessions,
37  events_per_session,
38  engagement_rate,
39  event_count,
40  conversions,

This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

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.

Raw tables it reads

  • raw.google_analytics_4.campaign_performance

More Google Analytics 4 models

Put Google Analytics 4 in front
of your agents.

Connect Google Analytics 4, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us