ga4_engagement.sql

ga4_engagement.sql

 
WITH prep AS(
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')) AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'session_engaged') AS engaged_sessions,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') AS engagement_time_msec,

  FROM `PROJECT_NAME.analytics_PROPERTY_ID.events_*`
  
  WHERE TRUE
    AND _TABLE_SUFFIX BETWEEN '20220601' AND FORMAT_DATE('%Y%m%d', DATE_SUB( CURRENT_DATE(), INTERVAL 1 DAY))
)

SELECT
  event_date,

  COUNT(DISTINCT session_id) AS num_of_sessions,
  COUNT(DISTINCT 
    CASE
      WHEN engaged_sessions = '1' THEN CONCAT(user_pseudo_id, ga_session_id) ELSE NULL
    END) AS num_of_engaged_sessions,
  AVG(engagement_time_msec) AS engagement_time_msec

FROM prep

GROUP BY event_date

ORDER BY event_date DESC