Optimization Up

Google Analytics 4 allows exporting all the raw data and events to BigQuery automatically, and use SQL syntax to query and model the data. When you export GA4 data to BigQuery, you own that data, and you can analyze users and their traffic on your property.

To make sense of the data and create reports, you have to write your own SQL queries. Some reports are straightforward to build and require simple structure, however, there are many metrics that require custom calculations, nested queries, and joining multiple datasets.

Today, I replicate the traffic acquisition report in GA4 to analyze the source/medium of users who visited my blog. Here’s the schema of my query:

Dimension:

• Source / Medium

Metrics

• Users
• Sessions
• Engaged Sessions
• Bounces
• Bounce Rate

Here’s the SQL query, make sure to replace with your own table.

WITH
  source_medium AS (
  SELECT
    (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'ga_session_id') AS session_id,
    CASE
      WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'source') IS NULL THEN '(direct)'
    ELSE
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'source')
  END
    AS source,
    CASE
      WHEN ( SELECT value.string_value FROM UNNEST(event_params) WHERE event_name = 'page_view' AND key = 'medium') IS NULL THEN '(none)'
    ELSE
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      event_name = 'page_view'
      AND key = 'medium')
  END
    AS medium,

  --Replace with your own table
  FROM `civic-bruin-2222.analytics_258822732.events_*`
  GROUP BY
    1,
    2,
    3 ),
  engaged_sessions AS (
  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,
  FROM
    `civic-bruin-237115.analytics_258822732.events_*`
  GROUP BY
    1,
    2 )
SELECT
  CONCAT(source,' / ',medium) AS source_medium,
  COUNT(DISTINCT a.session_id) AS sessions,
  COUNT(DISTINCT user_pseudo_id) users,
  COUNT(DISTINCT
    CASE
      WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id,b.session_id)
  END
    ) AS engaged_sessions,
  COUNT(DISTINCT
    CASE
      WHEN session_engaged = '0' THEN CONCAT(user_pseudo_id,b.session_id)
  END
    ) AS bounces,
  COUNT(DISTINCT
    CASE
      WHEN session_engaged = '0' THEN CONCAT(user_pseudo_id,b.session_id)
  END
    ) / COUNT(DISTINCT a.session_id) AS bounce_rate
FROM
  source_medium a
LEFT JOIN
  engaged_sessions b
ON
  a.session_id = b.session_id
GROUP BY
  1
ORDER BY
  sessions DESC

Ahmed Ali

Ex-Google with advanced digital analytics and marketing automation skills. If you are looking for a data-driven professional that will help you save time & money and grow cost-efficiently, you have come to the right person. Contact Me!