aw_user_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 user data
  user AS (
  SELECT
    user_pseudo_id,
    CASE
      WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'session_start' AND key = 'ga_session_number') = 1 THEN 'New Visitor'
      WHEN (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'session_start'
      AND key = 'ga_session_number') > 1 THEN 'Returning Visitor'
    ELSE
    NULL
  END
    AS user_type,
    (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'session_start'
      AND key = 'ga_session_number') AS count_of_sessions,
    COUNT(DISTINCT user_pseudo_id) AS users,
    COUNT(DISTINCT
      CASE
        WHEN ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'session_start' AND key = 'ga_session_number') = 1 THEN user_pseudo_id
      ELSE
      NULL
    END
      ) AS new_users,
    COUNT(DISTINCT
      CASE
        WHEN event_name = 'session_start' AND ( SELECT value.int_value FROM UNNEST(event_params) WHERE event_name = 'session_start' AND key = 'ga_session_number') = 1 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 percentage_new_sessions,
    COUNT(DISTINCT
      CASE
        WHEN event_name = 'session_start' THEN CONCAT(user_pseudo_id, CAST(event_timestamp AS STRING))
    END
      ) / COUNT(DISTINCT user_pseudo_id) AS number_of_sessions_per_user
  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,
    3)
  -- Main query
SELECT
  -- User Type (dimension | a boolean, either New Visitor or Returning Visitor, indicating if the users are new or returning)
  user_type,
  -- Count of Sessions (dimension | the session index for a user, each session from a unique user will get its own incremental index starting from 1 for the first session)
  count_of_sessions,
  -- Users (metric | the total number of active users)
  SUM(users) AS users,
  -- New users (metric | the number of users who interacted with your site or launched your app for the first time)
  SUM(new_users) AS new_users,
  -- % New Sessions (metric | the percentage of sessions by users who had never visited before)
  AVG(percentage_new_sessions) AS percentage_new_sessions,
  -- Number of Sessions per User (metric | the total number of sessions divided by the total number of users)
  AVG(number_of_sessions_per_user) AS number_of_sessions_per_user
FROM
  user
WHERE
  user_type IS NOT NULL
GROUP BY
  1,
  2
ORDER BY
  3 DESC
LIMIT
  10