详细内容
Announcing
GA4 Auditor- the ultimate
Google Analytics 4 audit tool, built by the creator of GA4 SQL.
Dismiss
Google Analytics 4
Google Search Console
Table Name
Date Range
YesterdayLast WeekLast monthMonth to yesterdayLast 7 daysLast 14 daysLast 30 daysYear to yesterdayLast yearCustom date range
Start Date
End Date
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.
Filters
Select FieldSession Channel Sessions OperatorEQUALS NOT EQUALS CONTAINS NOT CONTAINS GREATER THAN LESS THAN IS NULL IS NOT NULL
Sorty by
Select FieldSession Channel Sessions
AscendingAsc
DescendingDesc
Custom Metrics
Custom metrics are event-scoped. You can count a specific
event by providing its name, such as 'adding_shipping_info'.
You can also filter this event by adding parameter values,
such as 'page_location contains 'bigquery'.' However, it's
important to note that the event must contain this parameter
for the filter to work effectively
Event Params
Custom Metric
Custom Dimensions
Custom dimensions are defined by event parameters. The scope
of the custom dimensions is determined by the metrics you
incorporate with them.
For instance, if you include an event
parameter such as 'shipping_tier' and a metric like
'sessions', the custom dimension will be session-scoped.
If
you want to make the custom dimension user-scoped, you need
to include a user-scoped metric, such as 'users'.
if you
want to make the custom dimension event-scoped, you need to
include an event-scoped metric, such as 'event_count'. You
can filter the custom dimension for a specific event by
adding a filter with the event name.
Say you have an event called 'menu_click' and you want to
understand which menu items are clicked the most. You can
create a custom dimension called 'menu_item' and filter it
by 'event_name = 'menu_click'. Then, you can add a metric
like 'event_count' to see the number of times each menu item
was clicked.
Custom Dimension 1
Generate Query
131420
Total Generated Queries
/* 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.
If you’re looking to audit additional marketing platforms beyond GA4, be
sure to check out our new
Marketing Auditor
tool.
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.
If you're looking for a more advanced solution, you can use
Markifact
which is a powerful tool that allows you to build automated marketing workflows
using AI.
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.
Looking for a source to stay updated on the latest GA4
features and analytics industry? Check out
Swipe Insight. Your go-to source for GA4 and analytics industry updates.
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.
