How to handle empty fields in GA4 BigQuery

Follow me on LinkedIn - AI, GA4, BigQuery

Empty strings in query results from a GA4 BigQuery data table often indicate that certain fields are unpopulated or undefined for specific events.

You should convert empty values to NULL.

Unlike NULL values, aggregation functions (like COUNT, SUM, GROUP BY) do not ignore the empty strings, leading to incorrect results.

Your SQL query may behave differently based on whether you are dealing with a missing value (NULL) or an intentional empty string.

So, every query you run, make sure that any empty value is converted to null.

Converting empty strings to NULL ensures that truly missing data is correctly identified and handled.

To convert empty values to 'null', you can use the following text prompt:

If any of the columns contain empty values/strings, convert them to 'null'.

The SQL below is automatically generated via a text prompt in GA4 BigQuery Composer (a custom chatGPT) that calculates the total number of unique users by geo locations and convert any empty string to NULL

-- Calculate the total number of unique users by geo locations (continent, sub_continent, country, region and city) 
WITH
  prep AS (
    SELECT
      user_pseudo_id,
      NULLIF(geo.continent, '') AS continent,
      NULLIF(geo.sub_continent, '') AS sub_continent,
      NULLIF(geo.country, '') AS country,
      NULLIF(geo.region, '') AS region,
      NULLIF(geo.city, '') AS city
    FROM
      `dbrt-ga4.analytics_207472454.events_*`
    WHERE
      _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
  )
SELECT
  continent,
  sub_continent,
  country,
  region,
  city,
  COUNT(DISTINCT user_pseudo_id) AS total_users
FROM
  prep
GROUP BY
  continent,
  sub_continent,
  country,
  region,
  city
ORDER BY
  total_users DESC;

Note: Use your table ID. Otherwise, the code would not work.

======================

Learn the underlying logic for calculating various GA4 dimensions and metrics in BigQuery and leave the actual SQL generation to ChatGPT.

Your GA4 BigQuery data is only as good as the query logic you use. Once you understand the query logic, you can scale across 'N' SQL use cases.

That's what I teach in my GA4 BigQuery Course. The focus is entirely on teaching the underlying logic (instead of the actual SQL).

You should convert empty values to NULL.

To convert empty values to 'null', you can use the following text prompt:

Related Articles:

Himanshu Sharma