GA4 BigQuery event_date and event_timestamp Fields Demystified

When you send GA4 data to BigQuery the event table has two fields event_date and event_timestamp.  event_date field contains the date in string format and event_timestamp field contains the date and time of the event, which is not easily readable.

Even though it is easier to read the event_date field, the event_timestamp is the one that you should use if you are going to be doing any serious analysis.

In this post, we will discuss why event_timestamp is a much better field than event_date even when you have to use just the data. I will also show you how you can convert revent_timestamp to a human-readable format.

What does event_date field contain?

event_date field in the BigQuery GA4 event table contains date when that particular event was logged. It only logs the data and that date is in the timezone of your GA4 property.

So if you have to do any analysis across different timezones then you will have to keep that in mind and know the timezone you are using in the GA4 reports.  If you don't do that properly then you will get the wrong results.

What does event_timestamp contain?

event_timestamp field contains the date and time in microseconds at the event was logged in GA4. The value stored in this field is in UTC time, short for Coordinated Universal Time. Since the data and time are timezone neutral you can use it to compare across time zones.

The data stored in this field is in UNIX format.

How to convert event_timestamp human readable date and time?

Here is a sample query that you can use to convert the event_timestamp into a human-readable format.

SELECT TIMESTAMP_MICROS(event_timestamp)
 FROM [provide the name of your table]

Here is the output that you will see

The above result shows the data in UTC. However, for most practical purposes you might need to convert it into your own timezone.

So let’s say I want to convert this into New York time. In that case, I will modify the SQL as shown below:

SELECT datetime(TIMESTAMP_MICROS(event_timestamp), 'America/New_York')
 FROM [provide the name of your table]

Here is the screenshot now showing the timestamp in New York Timezone.

You can use the same code to convert the timestamp into any timezone of the works. You just have to replace America/New_York with your desired time zone. For example, to convert the above data into Los Angeles time you will use, 'America/Los_Angeles' instead of 'America/New_York'

The complete list of timezones that you can use in the above SQL is available at the end of this article.

BigQuery also has several other date and time functions that you can use to extract the various parts of this field. The names and descriptions of these functions are covered in the section below with much more details and examples in the BigQuery course.

Advanced Notes

Subscribe to Premium Membership to read the rest.