Weld logo
google-analytics-4 logo
Google analytics 4

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        | 6000

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

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.