Why (and how) to use GA4 event_timestamp with your local time zone instead of event_date in BigQuery

When you query the GA4 export data it is tempting to use the event_date dimension as preferred date column in your results. And there are good reasons to do so: although its data type is a string and not a date, 20230803 is much better readable for the human eye compared to 1691035113641530 (the corresponding event_timestamp for the same event on August 3rd, 2023).

I must admit that I took this short cut very often myself (even on this platform). The more I worked with the GA4 data set, the more I started using event_timestamp to calculate time related dimensions and metrics, especially when sessionizing event data.

This is why.

• event_timestamp is more precise. It records the exact time (in microseconds) at which the event happened was sent to GA4, while event_date only records the date. This can be important for tasks such as calculating the time it takes for users to complete a certain task or identifying peak traffic times.
• event_timestamp is less susceptible to timezone errors. The event_date field is stored in the timezone of your GA4 property, which means that it can be difficult to compare events across different apps or devices. The event_timestamp field is stored in UTC time, which makes it easier to compare events across different sources.

How to convert event_timestamp to an actual timestamp

Let's take a random day of events from the GA4 sample data set.

select
  event_date,
  event_timestamp
from
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20201130`

As you can see event_date is a string, and event_timestamp is an integer data type.

In this tutorial you will learn how to:

• convert strings and integers to actual date and time data types
• convert UTC based timestamps to local time zones