Google analytics
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 | 150000The "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.
