Script to compare page view counts for Segment, Rudderstack and GA4 tracking, grouped by day

compare_segment_rudderstack_page_views_by_day.sql

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 
  day, 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