Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save markrittman/07eb6cd98513b5c57aaee3e29e16c9d5 to your computer and use it in GitHub Desktop.
Save markrittman/07eb6cd98513b5c57aaee3e29e16c9d5 to your computer and use it in GitHub Desktop.
Script to show total page views for all pages on our site as recorded by Segment, Rudderstack and GA4
with segment_pageviews as (
select
timestamp_trunc(p.received_at,day) as day_day,
context_page_title as page_title,
count(distinct id) as page_views
from
`ra-development.company_website.pages_view` p
where
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-01'
and context_page_url not like '%info.rittmananalytics.com%'
group by
1,2
),
rudderstack_pageviews as (
select
timestamp_trunc(p.received_at,day) as day_day,
context_page_title as page_title,
count(distinct id) as page_views
from
`ra-development.rudderstack_ra_website.pages_view` p
where
date(timestamp_trunc(p.received_at,DAY)) > '2023-05-01'
group by
1,2
),
ga4_pageviews as (
select
timestamp_trunc(timestamp_micros(g.event_timestamp),day) as day_day,
(
SELECT
value.string_value
FROM
UNNEST(event_params)
WHERE
event_name = 'page_view'
AND KEY = 'page_title') AS page_title,
sum(1) as page_views
from `ra-development.analytics_277223877.events_*` g
where event_name = 'page_view'
and
timestamp_trunc(timestamp_micros(g.event_timestamp),day) > '2023-05-01'
group by 1,2
),
comparison as (
select
date(coalesce(s.day_day, r.day_day,g.day_day)) as day, coalesce(s.page_title,r.page_title,g.page_title) as page_title, s.page_views as segment_page_views, r.page_views as rudderstack_page_views, g.page_views
from
segment_pageviews s
full outer join
rudderstack_pageviews r
on
s.day_day = r.day_day
and
s.page_title = r.page_title
full outer join
ga4_pageviews g
on
s.day_day = g.day_day
and
s.page_title = g.page_title
)
select
page_title, sum(segment_page_views) as total_segment_page_views, sum(rudderstack_page_views) as total_rudderstack_page_views,
1-((sum(segment_page_views) - sum(rudderstack_page_views))/sum(segment_page_views)) as rudderstack_variance,
sum(page_views) as total_ga_page_views,
1-((sum(segment_page_views) - sum(page_views)) /sum(segment_page_views)) as ga4_variance
from
comparison
group by
1
order by 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment