DATES, How to Extract Dates from a GA4 table in BigQuery (beginner level)

Table of contents

Introduction
GA4 Table and Schema
event_date
event_timestamp
Time Event Example
Time Period Functions
Final Words

Introduction

Each hit reported in Google Analytics is associated with a timestamp value automatically collected. Previewing the GA4 table in BigQuery there are two dimensions related to these values: event_date and event_timestamp.

This is an essential feature for analysts, as dates allow us to create filters for our reports and dashboards. However, when consuming this data in BigQuery we soon observe that values are in Raw format. So, we need to transform them into understandable ones.

In today’s article we will see different sql functions to decipher raw numbers into readable dates for our analysis.

GA4 Table and Schema

Each row within a table represents a single event (event_name). Each event has the event_date and event_timestamp values along with numerous nested event-specific parameters (event_params) with their corresponding values. This is what a GA4 table looks like in BigQuery:

• As the name suggests, event_date indicates the exact date on which the event occurred. By default it uses the format YY/mm/dd and its schema is a STRING.
• Regarding event_timestamp it refers to the number of milliseconds elapsed from 1970–01–01 00:00:00 UTC until the event was created. Its schema is an INTEGER.

Note: Schemas are important because they determine what functions we can run. Sometimes though we can to change the Schema using conversion functions like CAST (exp)

At first glance, we can see that event_date is relatively easy to decipher because of its simple YY/mm/dd format. With respect to event_timestamp, things get a little complicated.

For instance, today is the 2nd of July 2022.
My GA4 event_timestamp value shows: 1656729312056608
Check the result with this time converter

Now it’s clear how the values work, let’s start with functions!

event_date

With the PARSE_DATE("%Y%m%d", event_date)function we will obtain the date in an understandable format. And, if we wanted, we could also convert the given value to other formats such as dd/mm/YY or mm/dd/YY by using FORMAT_DATE('date-representation', PARSE_DATE('%Y%m%d', event_date)).

Use the one you like the most or chose the format according to your country date and time representation. Make sure %Y is in upper case and %m and %d in lower case.

In the SELECT statement:

That’s it. Now we can use the PARSE_DATE("%Y%m%d", event_date)function in our queries and know the date each event occurred.

In the WHERE statement:
The function PARSE_DATE("%Y%m%d", event_date) can also be used in filters, so that the query returns those values compressed between the dates we specifically indicate:

WHERE PARSE_DATE(“%Y%m%d”,event_date) = “2022–06–29” (equal)
WHERE PARSE_DATE(“%Y%m%d”,event_date)>=“2022–06–29”(equal or bigger)
WHERE PARSE_DATE(“%Y%m%d”,event_date)<=“2022–06–29” (equal or minor)
WHERE PARSE_DATE(“%Y%m%d”,event_date) > “2022–06–29” (bigger than)
WHERE PARSE_DATE(“%Y%m%d”,event_date) < “2022–06–29” (minor than)
WHERE PARSE_DATE(“%Y%m%d”,event_date) != “2022–06–29” (is not that date)
WHERE PARSE_DATE(“%Y%m%d”,event_date) BETWEEN “2022–06–29” AND “2022–01–29”

event_timestamp

As the value of event_timestamp shows the milliseconds that have passed since 1970–01–01 00:00:00 UTC, different functions and combinations of them can be applied to transform this raw value into a specific date. As this is an article for beginners, I have chosen the 4 functions that I consider the easiest to apply and that return comprehensive enough values to perform the analysis.

In the SELECT statement:

From left to right, the table shows the event_timestamp as a raw number and the results each functions: full_date, year_month_day, only_year, only_month, date_format and time. Notice that they can also be used in the WHERE statement, the same as the previous one.

Time Example

One of the functions I like to use the most in my queries is the this one: FOMAT_TIME('%T', TIME(TIMESTAMP_MICROS(event_timestamp))).With this function you can perform analysis based on the date and the exact time the event ocurred.

You can even calculate the elapsed time since users make their first visit to complete a specific event such as add_to_cart or purchase.

Note: Following this time differentiation logic, it came to my mind another function to calculate date differences. SELECT DATE_DIFF ("2021-06-28", "2021-06-18", DAY). If we run we run this query the result will be 10.

Last day, week or month

Once we have a general idea on how to use basic functions to decipher dates from event_date and event_timestamp, it’s time to make things even better.

• For instance, create a query that returns the values from the last day, 7 days or 30 days. To do this, we’ll use the _TABLE_SUFFIX function in conjunction with FORMAT_DATE, DATE_SUB and INTERVAL. All at once :)

Filtering by yesterday

_TABLE_SUFFIX = FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Filtering by the last 7 days

_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 8 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Filtering by the last 30 days

_TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 31 DAY)) AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))

Let’s check it out:

The table returns only values compressed within 7 days. Tese functions work well for both event_date and event_timestamp and are extremely useful for reporting.

A good practice (although this is a bit more advanced sql level) is to automate the query to return completed events for a given time without rewriting the query. For instance, number of purchase event every 30 days.

Final Words

Dates and timestamps are automatically collected in GA4 tables and are associated with a unique event. Thanks to this, analysts can create reports and filter by specific dates in our reports.

When using BigQuery the event_date and event_timestamp are presented as raw numbers. We need to use functions to decipher them and transform them into values that are understandable and readable to the human eye. In this article we have seen basic functions like PARSE_DATE, FORMAT_DATE, EXTRACT and more complex ones like _TABLE_SUFFIX, DATE_SUB and INTERVAL .

Of course there are many other uses of these functions and I guess even better ones to return a similar value (…), although what I have tried to do is to keep it simple and practicable for entry level SQL analysts who use and are familiar with GA4.