GA4 SQL: The Easy Way to Generate GA4 BigQuery Queries
GA4 SQL: The Easy Way to Generate GA4 BigQuery Queries 图标

GA4 SQL: The Easy Way to Generate GA4 BigQuery Queries

ga4sql.com

GA4 BigQuery Query Builder allows you to generate queries quickly via user interface. Stop writing complex BigQuery queries by hand and try GA4 SQL today!

ga4 bigqueryga4 bigquery query builderga4 bigquery queryga4 bigquery sqlga4 bigquery query generatorga4 bigquery query toolga4bigquery
收录于 1 年前更新于 1 年前正常收录
GA4 SQL: The Easy Way to Generate GA4 BigQuery Queries 网站预览

详细内容

Announcing
GA4 Auditor- the ultimate
Google Analytics 4 audit tool, built by the creator of GA4 SQL.

Try it Now →

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.

网站信息

域名
ga4sql.com
收录路径
ga4sql