aw_session_calc.sql

WITH
  -- Subquery to define static and/or dynamic start and end date for the whole query
  period AS (
  SELECT
    '20200220' AS start_date,
    FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) AS end_date),
  -- Subquery to prepare and calculate engagement data
  engagement AS (
  SELECT
    COUNT(DISTINCT
      CASE
        WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id,session_id)
    END
      ) AS engaged_sessions,
    SUM(engagement_time_msec)/1000 AS engagement_time_seconds,
    COUNT(DISTINCT
      CASE
        WHEN session_engaged = '0' THEN CONCAT(user_pseudo_id,session_id)
    END
      ) AS bounces
  FROM (
    SELECT
      user_pseudo_id,
      (
      SELECT
        value.int_value
      FROM
        UNNEST(event_params)
      WHERE
        key = 'ga_session_id') AS session_id,
      MAX( (
        SELECT
          value.string_value
        FROM
          UNNEST(event_params)
        WHERE
          key = 'session_engaged')) AS session_engaged,
      MAX( (
        SELECT
          value.int_value
        FROM
          UNNEST(event_params)
        WHERE
          key = 'engagement_time_msec')) AS engagement_time_msec
    FROM
      -- Change this to your Google Analytics 4 export location in BigQuery
      `ga4.analytics_1234567890.events_*`,
      period
    WHERE
      _table_suffix BETWEEN period.start_date
      AND period.end_date
    GROUP BY
      1,
      2 ))
  -- Main query
SELECT
  -- Sessions (metric | the total number of sessions)
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'session_start' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS sessions,
  -- Engaged sessions (metric | the number of sessions that lasted longer than 10 seconds, or had a conversion event, or had 2 or more screen or page views)
  MAX(engaged_sessions) AS engaged_sessions,
  -- Engagement rate (metric | the percentage of engaged sessions compared to all sessions)
  MAX(engaged_sessions)/COUNT(DISTINCT
    CASE
      WHEN event_name = 'session_start' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS engagement_rate,
  -- Engagement time (metric | the average length of time in seconds that the app was in the foreground, or the web site had focus in the browser)
  MAX(engagement_time_seconds)/MAX(engaged_sessions) AS engagement_time,
  -- Bounces (metric | the total number of non-engaged sessions)
  MAX(bounces) AS bounces,
  -- Bounce Rate (metric | bounces divided by total sessions)
  MAX(bounces) / COUNT(DISTINCT
    CASE
      WHEN event_name = 'session_start' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS bounce_rate,
  -- Event count per session (metric | number of times an individual event (i.e. 'page_view') was triggered divided by all sessions)
  COUNT(DISTINCT
    CASE
      WHEN event_name = 'page_view' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) / COUNT(DISTINCT
    CASE
      WHEN event_name = 'session_start' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
  END
    ) AS event_count_per_session
FROM
  -- Change this to your Google Analytics 4 export location in BigQuery
  `ga4.analytics_1234567890.events_*`,
  period,
  engagement
WHERE
  _table_suffix BETWEEN period.start_date
  AND period.end_date