Explore GA4 Export Schema in BigQuery

In our ongoing Google Analytics 4 SQL Course, the ability to comprehend and navigate your data is essential. In this section, we will dive deep into understanding the Google Analytics 4 (GA4) export schema in BigQuery. By gaining insights into the structure of your data, you’ll be better equipped to write precise SQL queries and extract valuable insights. Let’s begin our exploration of the GA4 export schema in BigQuery!

The tree chart below shows the complete structure for each events_YYYYMMDD table:

GA4 Export Schema: Columns

Each column in the events_YYYYMMDD table represents an event-specific parameter, each serving a specific purpose. These columns can be categorized or grouped based on their scopes.

• Event: Columns within the “Event” scope provide detailed information about user interactions and events on your website or app. Event-related columns enable you to track and analyze user actions, helping you optimize your conversion funnels and user experience.

• User: The “User” scope contains columns related to user attributes. These columns provide information about the subject of the events.

• Device: Columns within the “Device” scope offer insights into the devices used by your audience. Understanding device preferences can aid in optimizing your website or app for specific platforms and screen sizes.

• Geo: The “Geo” scope encompasses columns that provide geographic information about your users. This data helps you tailor your marketing campaigns and content to target specific geographical regions effectively.

• App Info: Columns in the “App Info” scope reveal details about the user’s app or web environment. Understanding this information can assist in optimizing app performance and tracking the effectiveness of marketing campaigns.

• Collected Traffic Source: The “Collected Traffic Source” scope contains columns related to the source of traffic for the corresponding event.

• Traffic Source: Columns in the “Traffic Source” scope contains information about the traffic source that first acquired the user. This record is not populated in intraday tables. These columns enable you to dive deeper into your marketing attribution and understand which specific sources drive traffic and conversions.

• Stream: Columns within the “Stream” scope are particularly useful if you’re using multiple event streams within GA4, allowing you to differentiate and analyze data from various sources or apps.

• Platform: The “Platform” scope includes columns that specify the platform type and platform version. This information is crucial for ensuring your website or app is compatible with the platforms most commonly used by your audience.

• Ecommerce: If you run an e-commerce business, you’ll find valuable data in the “Ecommerce” scope. Columns here provide insights into transaction-related information. This data is essential for optimizing your online store and tracking revenue performance.

• Item: The “Item” scope encompasses columns related to individual items or products within e-commerce transactions. You can find details such as item name, SKU, and item price here. These columns are indispensable for tracking product-level performance and making data-driven merchandising decisions.

To simplify it even more so that it can be understood easily, we can summarize it into two broad scopes: Event and User.

• Event: All information about actions occurred on your product (What, When, Where, and How).
• User: All information about who performed an action on your product (Who).

GA4 Export Schema: Rows

Data for a single event might appear in one or more rows, depending on whether it includes repeated RECORDS. For instance, a page_view event featuring multiple event_params would resemble the table below. The initial row includes the event_date, event_name, event_timestamp, and other non-repeating data elements. The event_params RECORD is repeated for every parameter linked to the event. These repeated RECORDS are filled in subsequent rows positioned directly under the initial event row.