image
Optimization Up
GA4 Traffic Acquisition Report in BigQuery
Analyze

Recreating GA4 Traffic Acquisition Report in BigQuery

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 it with your own table. If you would like to customize it, please use GA4 SQL which is a GA4 BigQuery builder.


SELECT
  IFNULL(
    CONCAT(session_source, ' / ', session_medium),
    CONCAT(first_user_source, ' / ', first_user_medium)
  ) AS session_source_medium,
  COUNT(DISTINCT session_id) AS sessions,
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  SUM(CAST(engaged_sessions AS INT)) AS engaged_sessions,
  1 - SAFE_DIVIDE(
    SUM(CAST(engaged_sessions AS INT)),
    COUNT(DISTINCT session_id)
  ) AS bounce_rate,
  SAFE_DIVIDE(
    SUM(CAST(engaged_sessions AS INT)),
    COUNT(DISTINCT session_id)
  ) AS engagement_rate,
  SUM(purchases) AS purchases,
  SUM(revenue) AS revenue
FROM
  (
    SELECT
      MAX(
        CASE
          WHEN (
            SELECT
              value.int_value
            FROM
              UNNEST (event_params)
            WHERE
              event_name = 'page_view'
              AND KEY = 'entrances'
          ) = 1
          AND (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              event_name = 'page_view'
              AND KEY = 'source'
          ) IS NULL THEN CONCAT(traffic_source.source)
          ELSE (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              KEY = 'source'
          )
        END
      ) AS session_source,
      traffic_source.source AS first_user_source,
      MAX(
        CASE
          WHEN (
            SELECT
              value.int_value
            FROM
              UNNEST (event_params)
            WHERE
              event_name = 'page_view'
              AND KEY = 'entrances'
          ) = 1
          AND (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              event_name = 'page_view'
              AND KEY = 'source'
          ) IS NULL THEN CONCAT(traffic_source.medium)
          ELSE (
            SELECT
              value.string_value
            FROM
              UNNEST (event_params)
            WHERE
              KEY = 'medium'
          )
        END
      ) AS session_medium,
      traffic_source.medium AS first_user_medium,
      CONCAT(
        user_pseudo_id,
        (
          SELECT
            value.int_value
          FROM
            UNNEST (event_params)
          WHERE
            key = 'ga_session_id'
        )
      ) AS session_id,
      user_pseudo_id,
      MAX(
        (
          SELECT
            value.string_value
          FROM
            UNNEST (event_params)
          WHERE
            key = 'session_engaged'
        )
      ) AS engaged_sessions,
      SUM(
        CASE
          WHEN event_name = 'purchase' THEN 1
          ELSE 0
        END
      ) AS purchases,
      SUM(ecommerce.purchase_revenue) AS revenue
    FROM
      `analytics_12345.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m01', CURRENT_DATE()) AND FORMAT_DATE(
        '%Y%m%d',
        DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
      )
    GROUP BY
      first_user_source,
      first_user_medium,
      session_id,
      user_pseudo_id
  )
GROUP BY
  session_source_medium