Google analytics
Page Path Analysis
This SQL template aggregates Google Analytics data to provide a monthly overview of page paths, including metrics such as screen page views, total users, new users, event count, conversions, total revenue, and user engagement duration, grouped by traffic source.
1select
2 date_trunc(pp.date, month) as period -- Change to 'DAY' for daily overview
3 , pp.page_path
4 , ct.session_default_channel_grouping as traffic_source
5 , sum(pp.screen_page_views) as screen_page_views
6 , sum(pp.total_users) as total_users
7 , sum(pp.new_users) as new_users
8 , sum(pp.event_count) as event_count
9 , sum(pp.conversions) as conversions
10 , sum(pp.total_revenue) as total_revenue
11 , sum(pp.user_engagement_duration) as user_engagement_duration
12from
13 {{raw.google_analytics_4.page_path}} pp
14 left join {{raw.google_analytics_4.channel_traffic}} ct on pp.property_id = ct.property_id
15 and pp.date = ct.date
16group by
17 period
18 , pp.page_path
19 , traffic_source
20order by
21 screen_page_views desc;Example of output from model:
period | page_path | traffic_source | screen_page_views | total_users | new_users | event_count | conversions | total_revenue | user_engagement_duration
-----------|--------------------|----------------|-------------------|-------------|-----------|-------------|-------------|---------------|-------------------------
2023-01-01 | /home | Organic Search | 1500 | 1200 | 300 | 5000 | 50 | 1000.00 | 36000
2023-01-01 | /product | Direct | 1200 | 1000 | 200 | 4000 | 40 | 800.00 | 30000
2023-01-01 | /contact | Referral | 800 | 600 | 100 | 2000 | 20 | 400.00 | 18000
2023-01-01 | /about | Social | 600 | 500 | 50 | 1500 | 10 | 200.00 | 12000
2023-01-01 | /services | Paid Search | 400 | 300 | 30 | 1000 | 5 | 100.00 | 6000The Page Path Analysis SQL model is designed to provide comprehensive insights into user interactions on your website by leveraging Google Analytics data. This model aggregates key metrics such as screen page views, total users, new users, event count, conversions, total revenue, and user engagement duration, grouped by page path and traffic source. By summarizing these metrics on a monthly basis (with an option to switch to daily), businesses can identify the most visited pages, understand user behavior, and evaluate the effectiveness of different traffic sources. This analysis is particularly useful for optimizing website content, improving user engagement, and driving conversions.
