Most common user paths from a given landing page

landing_page_user_paths.sql

with events as (
    select 
      anonymous_id,
      user_id,
      cast(null as string) as name,
      cast(null as string) as email,
      timestamp,
      'page_view' as event_type,
      concat(split(context_ip,'.')[safe_offset(0)],'.***.***.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
      context_page_path,
      context_page_referrer,
      context_page_title,
      context_user_agent,
      context_campaign_source,
      context_campaign_medium,
      context_campaign_name,
      cast (null as string) as requirement    
    from
      `ra-development.company_website.pages`
    union all
    select
      anonymous_id,
      user_id,
      cast(null as string) as name,
      cast(null as string) as email,
      timestamp,
      event as event_type,
      concat(split(context_ip,'.')[safe_offset(0)],'.***.***.',split(context_ip,'.')[safe_offset(3)]) as context_ip,
      context_page_path,
      context_page_referrer,
      context_page_title,
      context_user_agent,
      context_campaign_source,
      context_campaign_medium,
      context_campaign_name,
      cast (null as string) as requirement
    from
      `ra-development.company_website.tracks`
    where
      event in ('podcast_episode_played',
                'pricing_link_clicked',
                'hero_image_clicked',
                'contact_us_submitted',
                'collateral_viewed',
                'clicked_email_link',
                'clicked_email',
                'casestudy_clicked',
                'booked_a_meeting',
                'about_us_clicked',
                'pressed_button',
                'pressed_a_button',
                'contact_us_pressed')
   union all
   select 
      anonymous_id,
      user_id,
      cast(null as string) as name,
      cast(null as string) as email,
      timestamp,
      event as event_type,
      cast(null as string) as context_ip,
      cast(null as string),
      cast(null as string),
      cast(null as string),
      cast(null as string),
      utm_source as context_campaign_source,
      cast(null as string) as context_campaign_medium,
      utm_campaign as context_campaign_name,
      meeting_purpose as requirement
    from
      `ra-development.zapier_source.meeting_booked`
    where
      anonymous_id is not null    
    union all
    select 
      anonymous_id,
      user_id,
      name,
      email,
      timestamp,
      'identify' as event_type,
      cast(null as string) as context_ip,
      cast(null as string),
      cast(null as string),
      cast(null as string),
      cast(null as string),
      cast(null as string) as context_campaign_source,
      cast(null as string) as context_campaign_medium,
      cast(null as string) as context_campaign_name,
      cast(null as string) as requirement
    from
      `ra-development.zapier_source.identifies`
    where
      anonymous_id is not null    
     ),
  id_stitching as (
    select
      distinct anonymous_id as anonymous_id,
      last_value(user_id ignore nulls) over (partition by anonymous_id order by timestamp rows between unbounded preceding and unbounded following ) as user_id,
      min(timestamp) over (partition by anonymous_id ) as first_seen_at,
      max(timestamp) over (partition by anonymous_id ) as last_seen_at
    from
      events ),
  mapped as (
    select
      coalesce(i.user_id,
        e.anonymous_id) as blended_user_id,
      e.*
    from
      events e
    
    left join
      id_stitching i
    using
      (anonymous_id)
    ),
names_backfilled as (
  select
    * except (name,
      email),
    last_value(email ignore nulls) over (partition by blended_user_id order by timestamp rows between unbounded preceding and unbounded following ) as email,
    last_value(name ignore nulls) over (partition by blended_user_id order by timestamp rows between unbounded preceding and unbounded following ) as name
  from
    mapped ),
ordered as (
select
  blended_user_id,
  timestamp,
  event_type,
  replace(context_page_title,' — Rittman Analytics','') as title,
  row_number() over (partition by blended_user_id order by timestamp) as event_seq
from names_backfilled
),
paths as (
select 
       blended_user_id,
       max(case when event_seq = 1 then title end) as page_1,
       max(case when event_seq = 2 then title end) as page_2,
       max(case when event_seq = 3 then title end) as page_3,
       max(case when event_seq = 4 then title end) as page_4,
       max(case when event_seq = 5 then title end) as page_5,
       max(case when event_seq = 6 then title end) as page_6,
       max(case when event_seq = 7 then title end) as page_7,
       max(case when event_seq = 8 then title end) as page_8,
       max(case when event_seq = 9 then title end) as page_9,
       max(case when event_seq = 10 then title end) as page_10
from ordered 
    group by 1)
select 
  page_1,
  page_2,
  page_3,
  page_4,
  page_5,
  count(*) as count
from paths
where page_1 in ('Analyzing the Hacker News Public Dataset using Firebolt Data Warehouse and Looker',
                'Multi-Channel Marketing Attribution using Segment, Google BigQuery, dbt and Looker',
                'Why (and How) Customer Data Warehouses are the New Customer Data Platform',
                'Customer Cohorting, Retention Curves and Predictive Lifetime Value using Looker and Google BigQuery',
                'Lightdash, Looker and dbt as the BI Tool Metrics Layer',
                'Ad Spend and Campaign RoI Analytics using Segment, Looker, dbt and Google BigQuery')
and  page_2 is not null
group by 1,2,3,4,5
order by count desc
limit 40