GA4 SQL: The Easy Way to Generate GA4 BigQuery Queries

Google Analytics 4

Google Search Console

Table Name

Date Range

Metrics

Sessions

Engaged Sessions
These sessions are the ones that have triggered the engaged_session event.
Engagement Rate
The percentage of engaged sessions (Engaged sessions divided by Sessions).
Sessions Per User
This is calculated by dividing the number of sessions by the number of users.
Average Session Duration
The is calculated by dividing the total session duration by the number of sessions.
Average Engagement Time Per Session
The average duration (in seconds) of users' sessions with at least one event.
Bounce Rate
The percentage of sessions that were not engaged ((Sessions Minus Engaged sessions) divided by Sessions). This metric is returned as a fraction; for example, 0.2761 means 27.61% of sessions were bounces.
Total Users
The number of distinct users who have logged at least one event, regardless of whether the site or app was in use when that event was logged.
Active Users
The number of distinct users who have logged at least one event in the selected time period.
New Users
The number of users who interacted with your site or launched your app for the first time (event triggered: first_open or first_visit).
Returning Users
The number of users who interacted with your site or launched your app for the second time.
First Time Purchasers
The number of users who made their first purchase.
Total Purchasers
The number of users that logged purchase events for the time period selected.
Average Engagement Time Per User
The average time users spent on your site or app.
Screen Page Views
The number of times a user visits a page / screen on your website or app
Screen Page Views Per Session
The average number of times a user visits a page / screen on your website or app per session
Screen Page Views Per User
The average number of times a user visits a page / screen on your website or app per user
Event Count
The number of events that occurred. Use it with event_name to get the number of events of a specific type.
Event Value
The total value of all events that occurred. Use it with event_name to get the total value of events of a specific type.
Events Per User
The average number of events that occurred per user.
Events Per Session
The average number of events that occurred per session.
Purchases
The number of purchases. For example, 19
Purchase Revenue
The total revenue. For example, 29.20.
Purchase Revenue USD
The total revenue USD. For example, 29.20.
Tax Amount
Tax amount associated with a transaction. Populated by the 'tax' event parameter.
Tax Amount USD
Tax amount associated with a transaction in USD. Populated by the 'tax' event parameter.
Shipping Amount
Shipping amount associated with a transaction. Populated by the 'shipping' event parameter.
Shipping Amount USD
Shipping amount associated with a transaction in USD. Populated by the 'shipping' event parameter.
Refund Amount
The total refunded transaction revenues. Refund amount sums refunded revenue for the 'refund' and 'app_store_refund' events.
Refund Amount USD
The total refunded transaction revenues in USD. Refund amount sums refunded revenue for the 'refund' and 'app_store_refund' events.
Add To Carts
The number of add to cart events. For example, 19
View Cart
The number of view cart events. For example, 19
Promotion Views
The number of times an item promotion was viewed. This metric counts the occurrence of the 'view_promotion' event.
Promotion Clicks
The number of times an item promotion was clicked. This metric counts the occurrence of the 'select_promotion' event.
Add To Wishlist
The number of add to wishlist events. For example, 19
Checkouts
The number of begin checkout events. For example, 19
Add Shipping Info
The number of add shipping info events. For example, 19
Add Payment Info
The number of add payment info events. For example, 19
Item List View Events
The number of times the item list was viewed. This metric counts the occurrence of the 'view_item_list' event.
Item List Click Events
The number of times users clicked an item when it appeared in a list. This metric counts the occurrence of the 'select_item' event.
Item View Events
The number of times the item details were viewed. The metric counts the occurrence of the 'view_item' event.
Item Quantity
The quantity of the item. For example, 4.
Item Revenue
The revenue of the item. For example, 29.20.
Item Revenue USD
The revenue of the item USD. For example, 29.20.
Item Refund
The refund of the item. For example, 29.20.
Item Refund USD
The refund of the item USD. For example, 29.20.
Items Viewed In List
The number of units viewed in list for a single item. This metric counts the quantity of items in 'view_item_list' events.
Items Clicked In List
The number of units clicked in list for a single item. This metric counts the quantity of items in 'select_item' events.
Items Viewed In Promotion
The number of units viewed in promotion for a single item. This metric counts the quantity of items in 'view_promotion' events.
Items Clicked In Promotion
The number of units clicked in promotion for a single item. This metric counts the quantity of items in 'select_promotion' events.
Items Viewed
The number of units viewed for a single item. This metric counts the quantity of items in 'view_item' events.
Items Added To Cart
The number of units added to cart for a single item. This metric counts the quantity of items in 'add_to_cart' events.
Items Checked Out
The number of units checked out for a single item. This metric counts the quantity of items in 'begin_checkout' events.
Items Purchased
The number of units for a single item included in purchase events. This metric counts the quantity of items in purchase events.

Dimensions

Session Channel

Session Source Medium
The combined values of sessionSource & sessionMedium that initiated a session on your website or app.
Session Source
The session source
Session Medium
The medium that initiated a session on your website or app
Session Campaign Name
The marketing campaign name for a session. Includes Google Ads Campaigns, Manual Campaigns, & other Campaigns.
First User Channel
The default channel grouping that first acquired the user to your website or app
First User Source
The source that first acquired the user to your website or app
First User Medium
The medium that first acquired the user to your website or app
First User Source Medium
The source / medium that first acquired the user to your website or app
First User Campaign Name
The marketing campaign name that first acquired the user to your website or app
User Pseudo ID
The user pseudo ID
User ID
The user ID
Landing Page
The first page a user visits on your website or app
Landing Page Title
The title of the first page a user visits on your website or app
Page Location
The page a user visits on your website or app
Page Title
The title of the page a user visits on your website or app
Host Name
The hostname of the page a user visits on your website or app
Platform
The platform on which your app or website ran; for example, web, iOS, or Android. To determine a stream's type in a report, use both platform and streamID
Stream ID
The stream ID of the stream that collected the data. To determine a stream's type in a report, use both platform and streamID
Device Category
The device category of the user's device
Mobile Device Branding
Manufacturer or branded name (examples: Samsung, HTC, Verizon, T-Mobile).
Mobile Device Model
The mobile device model name (examples: iPhone X or SM-G950F)
Mobile Device Marketing Name
The branded device name (examples: Galaxy S10 or P30 Pro)
Operating System
The operating systems used by visitors to your app or website. Includes desktop and mobile operating systems such as Windows and Android.
Operating System Version
The operating system versions used by visitors to your website or app. For example, Android 10's version is 10, and iOS 13.5.1's version is 13.5.1.
Browser
The browsers used by visitors to your website or app. Includes desktop and mobile browsers such as Chrome and Safari.
Browser Version
The browser versions used by visitors to your website or app. For example, Chrome 83.0.4103.61's version is 83.0.4103.61, and Safari 13.1.1's version is 13.1.1.
Language
The language used by visitors to your website or app. For example, en-US, en-GB, or fr-FR.
Advertising ID
The advertising ID of the user's device. This is the ID used by Google and Apple to track users across apps and websites.
Vendor ID
The vendor ID of the user's device. This is the ID used by Apple to track users across apps and websites.
App Version
The version of your app that visitors are using. For example, 1.0.0.
App ID
The ID of your app. For example, com.example.app.
App Install Store
The store where your app was installed. For example, Google Play or the App Store.
Firebase App ID
The Firebase App ID of your app. For example, 1:1234567890:android:1234567890.
Continent
The continent where visitors are located. For example, North America, Europe, or Asia.
Sub Continent
The sub-continent where visitors are located. For example, Northern America, Western Europe, or Eastern Asia.
Country
The country where visitors are located. For example, United States, United Kingdom, or France.
Region
The region where visitors are located. For example, California, England, or Île-de-France.
City
The city where visitors are located. For example, San Francisco, London, or Paris.
Metro
The metro where visitors are located. For example, San Francisco, London, or Paris.
Minute
The minute of the hour that the event was logged. This dimension ranges from 0-59 and is reported in your property's timezone.
Hour
The two-digit hour of the day that the event was logged. This dimension ranges from 0-23 and is reported in your property's timezone.
Date
The date when the event occurred. For example, 2023-01-01.
Week
The week of the event, a two-digit number from 01 to 53. Each week starts on Sunday.
Month
The combined values of year and month. Example values include 202312 or 202301.
Year
The year when the event occurred. For example, 2023.
Event Name
The name of the event. For example, purchase, app_update, or app_remove.
Transaction ID
The unique identifier of the transaction. For example, 1234.
Item ID
The unique identifier of the item. For example, 1234.
Item Name
The name of the item. For example, T-Shirt.
Item Brand
The brand of the item. For example, Google.
Item Variant
The variant of the item. For example, Black.
Item Category
The category of the item. For example, Clothing.
Item Category2
The second category of the item. For example, T-Shirts.
Item Category3
The third category of the item. For example, Blue.
Item Category4
The fourth category of the item. For example, Small.
Item Category5
The fifth category of the item. For example, Summer.
Item List Name
The name of the list in which the item was presented to the user. For example, Search Results.
Item List ID
The ID of the list in which the item was presented to the user. For example, Search Results.
Item List Position
The index of the item in the list. For example, 1.
Item Promotion Name
The name of the promotion associated with the item. For example, Summer Sale.
Item Promotion ID
The ID of the promotion associated with the item. For example, Summer Sale.
Item Coupon
The coupon code associated with the item. For example, SUMMER_SALE13.
Item Promotion Creative Name
The name of the creative associated with the item. For example, Summer Sale Banner.
Item Promotion Creative Slot
The slot of the creative associated with the item. For example, 1.
Item Price
The price of the item. For example, 29.20.

80636

Total Generated Queries

sql

/* This query is generated by ga4Sql.com */ 

SELECT * FROM analytics_12345.events_*

Frequently Asked Questions

What is GA4 SQL?

GA4 SQL is a free and user-friendly tool that simplifies the querying process for Google Analytics 4 data in BigQuery. It enables users to generate GA4 BigQuery queries without requiring any SQL knowledge. By selecting the desired metrics, dimensions, date range, and the name of the BigQuery table to query, the tool automatically generates a SQL query to retrieve the relevant data from BigQuery. Best of all, GA4 SQL is free to use.

How can I ensure my GA4 data is accurate?

Making decisions based on inaccurate data can have costly consequences. To ensure the accuracy of your GA4 data, we've built GA4 Auditor an advanced GA4 audit tool that can help you identify data quality issues in your analytics account. Try it out now for free.

Why are sub-queries used instead of CTEs in query building?

Developing a universal query template for all metrics and dimensions using CTEs (Common Table Expressions) with the syntax 'WITH table AS (...)' can be challenging, as it can lead to longer queries with multiple joins. While sub-queries may be less readable, we decided to prioritize functionality over readability. It's worth noting that there is no difference in performance between CTEs and sub-queries.

How can I customize the generated queries?

To customize the generated queries, it is beneficial to have some SQL knowledge. A valuable resource for learning SQL is GA4BigQuery.com check it out!
If you don't feel like learning SQL, you can use our Custom GPT on ChatGPT and ask any questions you have about the queries.

Why does BigQuery data differ from GA4 UI data for some metrics?

The data in the GA4 UI goes through several processing stages, including value additions like Google Signals, modeling, traffic attribution, and prediction, before it is displayed. However, the data exported to BigQuery is typically collected data and does not contain these value additions. You can find more information about this on the Google Analytics developer website.

Why can't I select some metrics or dimensions?

In some cases, certain metrics or dimensions may be disabled and cannot be selected because they are not compatible with each other. This is to ensure that the generated query provides accurate results. If you encounter this issue, try selecting different combinations of metrics and dimensions that are compatible with each other.

How can I provide feedback on the queries or contact you?

We welcome any feedback or questions you may have about the queries generated by GA4 SQL. If you have any issues with a generated query or suggestions on how we can improve our query builder, you can contact us by filling out this contact form.

Where can I find the definitions of metrics and dimensions?

If you're unsure about the definitions of any metrics or dimensions in GA4 SQL, simply hover over the help icon next to each field to view its definition. This can help you better understand what data is being pulled and how it relates to your analysis.

How do I use GA4 SQL?

To use GA4 SQL, you need to have a Google Analytics 4 property connected to your Google Cloud project. You can connect your GA4 property to your Google Cloud project by following the steps in this to link GA4 to BigQuery.

Is GA4 SQL free to use?

Yes, GA4 SQL is completely free to use. We may introduce paid features in the future, but for now, users can generate an unlimited number of queries at no cost.