Follow me on LinkedIn - AI, GA4, BigQuery
You can track the values of the GA4 First Time Users metric in BigQuery via the 'user_first_touch_timestamp' field. This field tells when a user first interacted with your website or app.

Use the following SQL (automatically generated via a text prompt in ChatGPT) to track GA4 First Time Users in BigQuery:

-- Calculate the total number of First time users by Acquisition day

WITH filtered_data AS (
  SELECT
    user_pseudo_id,
    user_first_touch_timestamp
  FROM
    `dbrt-ga4.analytics_207472454.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20251001' AND '20251031'
    AND user_first_touch_timestamp IS NOT NULL
)

SELECT
  FORMAT_DATE('%b %-d, %Y', DATE(TIMESTAMP_MICROS(user_first_touch_timestamp))) AS acquisition_day,
  COUNT(DISTINCT user_pseudo_id) AS first_time_users
FROM
  filtered_data
WHERE
  DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) BETWEEN '2025-10-01' AND '2025-10-31'
GROUP BY
  acquisition_day,
  DATE(TIMESTAMP_MICROS(user_first_touch_timestamp))
ORDER BY
  DATE(TIMESTAMP_MICROS(user_first_touch_timestamp)) ASC;

I am not a fan of cookie-cutter reports. But if you want one, this one could be for you.

All you have to do is supply your table ID to generate SQL that works for you.


If you want to customize this report according to your unique data analysis requirements then you need to understand the “logic” used behind calculating the various metrics in this report.

Once you understand the logic, customizing this report is easy peasy.


I teach you such logic in my GA4 BigQuery course, where you can learn to automate SQL generation via text prompts in ChatGPT.

There could be ‘N’ use cases, and it won’t be possible to provide ready-made SQL code for every possible case.

So, it is better that you understand the logic to scale SQL generation to the moon.


The future belongs to those who can query raw GA4 data in BigQuery, apply SQL logic, automate SQL generation via AI and drive insights beyond what GA4 UI can offer.
  1. GA4 to BigQuery Mapping Tutorial.
  2. Understanding the BigQuery User Interface.
  3. GA4 BigQuery Query Optimization.
  4. How to access a nested field in GA4 BigQuery data table.
  5. How to Calculate Unique Users in GA4 BigQuery.
  6. GA4 BigQuery Export Schema Tutorial.
  7. Calculating First Time Users in GA4 BigQuery.
  8. Extracting Geolocations in GA4 BigQuery.
  9. GA4 BigQuery SQL Optimization Consultant.
  10. Tracking Pages With No Traffic in GA4 BigQuery.
  11. First User Primary Channel Group in GA4 BigQuery
  12. How to handle empty fields in GA4 BigQuery.
  13. Extracting GA4 User Properties in BigQuery.
  14. Calculating New vs Returning GA4 Users in BigQuery.
  15. How to access BigQuery Public Data Sets.
  16. How to access GA4 Sample Data in BigQuery.