Here is how you can calculate ‘New’ and ‘Returning users in GA4 BigQuery.

First thing first. 

Both ‘New’ and ‘Returning’ users metrics need to be manually calculated. 

They are not available as default fields in the GA4 BigQuery export schema.

Here is how you can find if a user is new or returning.

If the value of ‘ga_session_number’ event parameter is 1, classify the user as a ‘New User’.

If the value of ‘ga_session_number’ event parameter is greater than 1, classify the user as a ‘Returning User’.

The ‘ga_session_number’ event parameter is an array of structures within the ‘event_params’ array of structures. It is extracted as an integer value:

Calculating New Users in GA4 BigQuery for each day in the time range.

Let us suppose you want to calculate ‘New Users’ for each day in the date range Jan 1 to Jan 31, 2024

To calculate ‘New Users’ for each day in the date range, use ‘Common Table Expressions’ and classify a user as ‘New User’ if the value of the ‘ga_session_number’ event parameter is 1. 

You can use the following SQL code:

WITH NewUserData AS (

  SELECT

    event_date,

    user_pseudo_id,

    CASE

      WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) = 1 THEN ‘New User’

      ELSE NULL

    END AS user_type

  FROM

    `dbrt-ga4.analytics_207472454.events_*`

  WHERE

    _TABLE_SUFFIX BETWEEN ‘20240101’ AND ‘20240131’

    AND event_name = ‘session_start’

)

SELECT

  event_date,

  COUNTIF(user_type = ‘New User’) AS new_users

FROM

  NewUserData

GROUP BY

  event_date

ORDER BY

  event_date;

Note: Make sure to use your own Table ID. Otherwise, the SQL won’t work.

Copy-paste the SQL code into the SQL editor of your GA4 BigQuery project and then click on the ‘Run’ button. 

You should now see the query result like the one below:

My Step-By-Step Blueprint For Querying GA4 data in BigQuery without understanding a single line of SQL.

Query GA4 BigQuery Data via ChatGPT (73 Pages)

Download the FREE ebook

Calculating the total number of new users in a particular date range.

Let us suppose you want to calculate the total number of  ‘New Users’ in the date range Jan 1 to Jan 31, 2024

You can use the following SQL code:

WITH NewUserCTE AS (

  SELECT

    user_pseudo_id,

    COUNTIF(

      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) = 1

    ) AS is_new_user

  FROM

    `dbrt-ga4.analytics_207472454.events_*`

  WHERE

    event_name = ‘session_start’

    AND _TABLE_SUFFIX BETWEEN ‘20240101’ AND ‘20240131’

  GROUP BY

    user_pseudo_id

)

SELECT

  SUM(is_new_user) AS new_users

FROM

  NewUserCTE

WHERE

  is_new_user = 1;

Note: Make sure to use your own Table ID. Otherwise, the SQL won’t work.

Copy-paste the SQL code into the SQL editor of your GA4 BigQuery project and then click on the ‘Run’ button. 

You should now see the query result like the one below:

Calculating Returning Users in GA4 BigQuery for each day in the time range.

Let us suppose you want to calculate ‘Returning Users’ for each day in the date range Jan 1 to Jan 31, 2024

To calculate ‘Returning Users’ for each day in the date range, use ‘Common Table Expressions’ and classify a user as ‘Returning User’ if the value of the ‘ga_session_number’ event parameter is greater than 1.

You can use the following SQL code:

WITH cte_returning_users AS (

  SELECT

    event_date,

    COUNT(DISTINCT user_pseudo_id) AS returning_users

  FROM

    `dbrt-ga4.analytics_207472454.events_*`,

    UNNEST(event_params) AS ep

  WHERE

    event_name = ‘session_start’

    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) > 1

    AND _TABLE_SUFFIX BETWEEN ‘20240101’ AND ‘20240131’

  GROUP BY

    event_date

)

SELECT

  event_date,

  returning_users

FROM

  cte_returning_users

ORDER BY

  event_date;

Copy-paste the SQL code into the SQL editor of your GA4 BigQuery project and then click on the ‘Run’ button. 

You should now see the query result like the one below:

Calculating the total number of returning users in a particular date range.

Let us suppose you want to calculate the total number of ‘Returning Users’ in the date range Jan 1 to Jan 31, 2024

You can use the following SQL code:

WITH returning_users AS (

  SELECT

    user_pseudo_id,

    COUNTIF(

      (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) > 1

    ) AS is_returning_user

  FROM

    `dbrt-ga4.analytics_207472454.events_*`

  WHERE

    event_name = ‘session_start’

    AND _TABLE_SUFFIX BETWEEN ‘20240101’ AND ‘20240131’

  GROUP BY

    user_pseudo_id

),

total_returning_users AS (

  SELECT

    COUNT(user_pseudo_id) AS returning_users

  FROM

    returning_users

  WHERE

    is_returning_user > 0

)

SELECT

  returning_users

FROM

  total_returning_users;

Copy-paste the SQL code into the SQL editor of your GA4 BigQuery project and then click on the ‘Run’ button. 

You should now see the query result like the one below:

My Step-By-Step Blueprint For Querying GA4 data in BigQuery without understanding a single line of SQL.

Query GA4 BigQuery Data via ChatGPT (73 Pages)

Download the FREE ebook

Calculating New Vs Returning Users in GA4 BigQuery in a particular date range.

Let us suppose you want to calculate new vs returning users in the date range Jan 1 to Jan 31, 2024

You can use the following SQL code:

WITH UserClassification AS (

  SELECT

    user_pseudo_id,

    CASE

      WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) = 1 THEN ‘New Users’

      WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_number’) > 1 THEN ‘Returning Users’

      ELSE NULL

    END AS user_type_calc

  FROM

    `dbrt-ga4.analytics_207472454.events_*`

  WHERE

    event_name = ‘session_start’

    AND _TABLE_SUFFIX BETWEEN ‘20240101’ AND ‘20240131’

)

SELECT

  user_type_calc,

  COUNT(DISTINCT user_pseudo_id) AS users

FROM

  UserClassification

WHERE

  user_type_calc IS NOT NULL

GROUP BY

  user_type_calc

ORDER BY

  users DESC;

Copy-paste the SQL code into the SQL editor of your GA4 BigQuery project and then click on the ‘Run’ button. 

You should now see the query result like the one below: