Lesson 5: GA4 Events Table Schema in BigQuery

GA4 Events Table Schema in BigQuery

The events table is at the core of all the tables in BigQuery. This table contains every event that you choose to send to BigQuery.  There is a 1 million event limit so keep that in mind.

As mentioned in the previous post, there are possibly two events tables that get created, the daily events table and the intraday events table.

In this post, we will look at the data contained in these tables. 

To view the columns/data contained in these tables, click on the three dots next to the table and click “Open”

This will open up the structure of the table and show you the names of various columns in the table.

The data in a table is stored in rows and columns, just like the data you see in an Excel or Google sheet.

The structure, described below, is for both daily events tables and events_intraday tables.

The data in this table can be grouped into a few major buckets, as listed below:

1. Event

2. User

3. Devices

4. Geo Location (geo)

5. App Info (app_info)

6. Collected Traffic Source (collected_traffic_source) - The traffic source of the event

7. Traffic Source (traffic_source) - The traffic source of the first touch of the user

8. Stream and Platform Info

9. eCommerce

10. Items (eCommerce items)

Events Data

Events data is contained in the following fields:

• event_date, String (data type)

  The date when the event was logged. The format of this data is YYYYMMDD and is in the time zone of your GA4 property.

• event_timestamp, INTEGER

  The time when the event was logged on the client. This is Unix date and time in UTC (Universal Time Coordinated)

• event_previous_timestamp, INTEGER

  The time when the event was previously logged on the client. The format is UTC.

• event_name, STRING

  The name of the event.

• event_value_in_usd, FLOAT

  The value of the event converted in USD.

• event_bundle_sequence_id, INTEGER

  The sequential ID of the bundle in which these events were uploaded.

• event_server_timestamp_offset, INTEGER

  Timestamp offset between collection time and upload time in micros.

• event_params, RECORD

  Record of all the event parameters associated with that event.

User Data

User data is contained in the following fields:

• is_active_user, BOOLEAN

  Whether the user was active (True) or inactive (False) at any point in the calendar day. Included in only the daily tables.

• user_id, STRING

  This is set using GA4 user ID integration.  If it is not set then it will be null.

• user_pseudo_id, STRING

  The pseudonymous ID (e.g., app instance ID) for the user.

• user_first_touch_timestamp, INTEGER

  The time at which the user first opened the app or visited the site. This is in Microseconds,

• privacy_info.ads_storage, STRING

  Indicates if ad targeting is enabled for a user or not. You can have Yes, No, and Unset as values.

• privacy_info.analytics_storage, STRING

  Indicates if analytics storage is enabled for the user or not. You can have Yes, No, and Unset as values.

• privacy_info.uses_transient_token, STRING

  Indicates if the user has denied Analytics storage and the developer has enabled measurement without cookies based on transient tokens in server data. You can have Yes, No, and Unset as values.

• user_properties, RECORD

  Contains key-value pairs of all user properties.

• user_ltv, RECORD

  Contains the user's LTV and the currency used for that LTV. Not available in events_intra day table.

Stream and Platform info

• stream_id, STRING

  The ID of the data stream from which the event was logged

• platform, STRING

  The data stream platform, Web, IOS, or Android, from which the event was logged

Device, Geo, App Info, Collected Traffic Source, Traffic Source, eCommerce and Items

All of this data is captured in the device, geo, app_info, collected_traffic_source, e-commerce, and items fields in the events tables.

They are all of record type and contain various types of data associated with these records.  See below the general information about record type data.

Record Type Data in BigQuery Import of GA4

Many of the fields, as mentioned above, are Record type fields. This field contains the entire record in one column. Think of it as a table (record) within a table. 

Each record has a key value containing the name of the value (e.g. event_params.key) and the value containing the actual value.

Actual values are contained in a column that matches the data type of the value.  There are four possible data types, String, Integer, Float, or Double, and hence you will see four columns but only one of the columns contains the value while the other columns are null.

For example, for the event parameters, there is a column called, event_params.key. Let’s say this key value is session_number. This means the value column corresponding to this event parameter will contain the session number of the user, which is an integer value.  So event_params.value.int_value column is populated with the value while other columns are empty.

Don’t worry if this concept is a little difficult to understand. It will become clear in future posts.  However, if you want to speed up your learning then join my BigQuery for Marketing

If you have any questions then do not hesitate to reach out to me. I will start answering some of the questions in future editions.

Help me promote this newsletter, invite your friends and colleagues, and earn rewards.