- GA4 BigQuery For Marketing Analytics
- Posts
- Lesson 5: GA4 Events Table Schema in BigQuery
Lesson 5: GA4 Events Table Schema in BigQuery
Learn BigQuery with GA4
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:
Event
User
Devices
Geo Location (geo)
App Info (app_info)
Collected Traffic Source (collected_traffic_source) - The traffic source of the event
Traffic Source (traffic_source) - The traffic source of the first touch of the user
Stream and Platform Info
eCommerce
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.
Reply