Simple Segment Pages row parser for generating page category, product name size and colour, channel and source for eCommerce site

segment_pageview_parser.sql

with page_views as (
SELECT *,
       case when split(context_page_path,'/')[safe_offset(1)] = 'products' then 'Product Category Page'
            when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then 'Product Details Page'
            when split(context_page_path,'/')[safe_offset(1)] = 'search-products' then 'Search Page'
            else 'Home Page' end as page_type,
       case when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then 
            replace(replace(replace(replace(replace(split(split(context_page_path,'/')[safe_offset(2)],'_')[safe_offset(0)],
              '-white',''),'-blush-pink',''),'-black',''),'-black-black',''),'-',' ') end as product_name,
       case when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then 
            replace(replace(replace(replace(split(split(context_page_path,'/')[safe_offset(2)],'_')[safe_offset(0)],replace(replace(replace(replace(split(split(context_page_path,'/')[safe_offset(2)],'_')[safe_offset(0)],
       '-white',''),'-blush-pink',''),'-black',''),'-black-black',''),''),'-',''),'whitewhite','white'),'blackblack','black') end as product_colour,
       case when split(context_page_path,'/')[safe_offset(1)] = 'products' then split(split(context_page_path,'/')[safe_offset(2)],'_')[safe_offset(0)] end as category_name,
       case when split(context_page_path,'/')[safe_offset(1)] = 'shop-all' then split(split(context_page_path,'_')[safe_offset(1)],'_')[safe_offset(0)] end as product_size,
       case when url like '%fbclid%' then 'PPC' 
            when referrer like '%instagram%' or referrer like '%facebook%' and url not like '%fbclid%' then 'Social'
            when referrer like '%google%' then 'Organic'
            else 'Direct' end as channel,
       case when referrer like '%instagram%' then 'Instagram'
            when referrer like '%facebook%' or url like '%fbclid%' then 'Facebook'
            when referrer like '%google%' then 'Google'
            when referrer not null then 'Other Referrer'
            else null end as source,
       row_number() over (partition by anonymous_id order by received_at) as visitor_pageview_sequence,
       case when lead(received_at) over (partition by anonymous_id order by received_at) is null and row_number() over (partition by anonymous_id order by received_at) = 1 then true else false end as is_bounced_entry,
FROM `ra-development.segment_salt_website.pages` 
where split(context_page_path,'/')[safe_offset(1)] in ('products','shop-all','search-products')
and split(context_page_path,'/')[safe_offset(2)] is not null)
select *
from page_views