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]
A complete list of timezones is available at https://en.wikipedia.org/wiki/List_of_tz_database_time_zones.
There are several other date and time functions that you can use to get the data and time from this field or perform other operations.
If you are interested in learning those and getting in-depth knowledge of BigQuery then enroll in the BigQuery for Marketing Analytics course at https://academy.optizent.com/courses/bigquery-for-marketers-and-marketing-analysts
Video of event_timestamp SQL
Other BigQuery and GA4 posts that you might like
- What is user_pseudo_id in GA4 BigQuery Export?
- Google Analytics 4 and BigQuery User Data Export
- How To Get Started With BigQuery
GA4 + BigQuery Integration
If you haven't yet integrated GA4 with BigQuery then you should do that right away. Check out, these posts
Common Questions about GA4 event_timestamp in BigQuery
What is the format of the timestamp in BigQuery GA4?
The timestamp field for the GA4 data in BigQuery is stored as an integer value. It contains the data and time in UTC and the data is stored in Unix format. What this essentially means is that it contains the number of milliseconds that have elapsed since January 1st of 1970.
How to extract data from GA4 and store it in BigQuery
I have covered this in detail in another post as well as a video. Check out the post at GA4 BigQuery Export; Why and How. The post also has a link to the video.