markrittman

WITH
  events AS (
  SELECT
    TIMESTAMP_MICROS(event_timestamp) AS event_ts,
    CONCAT(user_pseudo_id,'-',event_name,'-',CAST(event_timestamp AS STRING)) AS event_id,
    user_pseudo_id AS user_pseudo_id,
    user_id,
    traffic_source.name AS utm_channel,
    traffic_source.medium AS utm_medium,
    traffic_source.source AS utm_source,

markrittman

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,

markrittman

SELECT
  user_pseudo_id,
  geo.city,
  TIMESTAMP_MICROS(MIN(event_timestamp)) AS session_start_ts,
  traffic_source.name AS channel,
  traffic_source.medium AS medium,
  traffic_source.source AS source,
  (
  SELECT
    value.int_value

markrittman

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%'

markrittman

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%'

markrittman

with segment_pageviews as (
  select 
    'segment' as source, id, timestamp_trunc(timestamp,HOUR) as timestamp_minute, path, context_page_title, anonymous_id, user_id, context_ip
    
  from
    `ra-development.company_website.pages_view` p
  where 
    date(timestamp_trunc(p.received_at,DAY)) > '2023-05-10' 
    and context_page_title = 'Adding Looker Regression Tests to the dbtCloud CI/CD Pipeline using Spectacles — Rittman Analytics'
  

markrittman

WITH session_attributiON AS
      (SELECT *,
       CASE WHEN session_id = LAST_VALUE(session_id)
                              OVER (partitiON by user_id order by session_start_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) THEN 1
            ELSE 0
       END AS LAST_click_attrib_pct,
       CASE WHEN session_id = FIRST_VALUE(session_id)
                              OVER (partitiON by user_id order by session_start_tstamp ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) THEN 1
            ELSE 0
       END AS first_click_attrib_pct,

markrittman

  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS session_start_ts,
    CAST(LEAD(TIMESTAMP_MICROS(event_timestamp),1) OVER (PARTITION BY CONCAT(user_pseudo_id)
      ORDER BY
        event_timestamp) AS timestamp) AS session_end_ts,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_number') AS session_number,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_referrer') AS referrer,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS landing_page_path,

markrittman

/*
First-Click, Last-Click, Even-Click and Time Decay Attribution for GA4
Replace "event_name in ('Contact Us','Contact Us Clicked','CTA Pressed','Form Submitted')" with the name(s) of your conversion event in lines 54, 57, 61 and 76
*/
WITH
  events AS (
  SELECT
    event_timestamp as event_ts,
    user_pseudo_id AS user_pseudo_id,
    user_id,

markrittman

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,