Weld logo
google-analytics-4 logo
Google analytics 4

Channel Traffic Overview

This SQL template provides a monthly overview of channel traffic from Google Analytics, summarizing metrics such as total users, sessions, engaged sessions, events per session, engagement rate, event count, conversions, total revenue, and user engagement duration, grouped by channel.
1select
2    date_trunc(date, month) as period -- Change to 'DAY' for daily overview
3  , session_default_channel_grouping as channel
4  , sum(total_users) as total_users
5  , sum(sessions) as sessions
6  , sum(engaged_sessions) as engaged_sessions
7  , avg(events_per_session) as events_per_session
8  , avg(engagement_rate) as engagement_rate
9  , sum(event_count) as event_count
10  , sum(conversions) as conversions
11  , sum(total_revenue) as total_revenue
12  , sum(user_engagement_duration) as user_engagement_duration
13from
14    {{raw.google_analytics_4.channel_traffic}}
15group by
16    period
17  , channel
18order by
19    total_users desc;
Example of output from model:
period     | channel          | total_users | sessions | engaged_sessions | events_per_session | engagement_rate | event_count | conversions | total_revenue | user_engagement_duration
-----------|------------------|-------------|----------|------------------|--------------------|-----------------|-------------|-------------|---------------|--------------------------
2023-01-01 | Organic Search   | 1500        | 2000     | 1800             | 3.5                | 0.85            | 7000        | 50          | 10000         | 360000
2023-01-01 | Direct           | 1200        | 1500     | 1300             | 4.0                | 0.87            | 6000        | 40          | 8000          | 300000
2023-01-01 | Paid Search      | 900         | 1100     | 950              | 3.8                | 0.86            | 4200        | 30          | 6000          | 220000
2023-01-01 | Social           | 800         | 1000     | 850              | 3.6                | 0.85            | 3600        | 25          | 5000          | 200000
2023-01-01 | Referral         | 600         | 700      | 650              | 3.7                | 0.86            | 2600        | 20          | 4000          | 150000

The "Channel Traffic Overview" SQL model provides a comprehensive summary of traffic data from Google Analytics. By aggregating data on a monthly basis (modifiable to daily), it offers insights into various metrics segmented by traffic channels. This model is useful for understanding user behavior and engagement across different channels, tracking the performance of marketing campaigns, and identifying the most effective channels in driving traffic and revenue. Key metrics include total users, sessions, engaged sessions, events per session, engagement rate, event count, conversions, total revenue, and user engagement duration. This information can help businesses optimize their marketing strategies and improve user engagement.

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.