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 output
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.