A lot of Google Analytics 4 data can be derived straight from the tables without performing calculations on the data. With the help of
parse for dates or timestamps we can do the trick.
If you only need one dimension or metric, look at the
-- comments in the example query for names and definitions and copy the part you need from the
select clause. Make sure that you also add any additional conditions (i.e.
order by) that are necessary to calculate the results correctly.
Default items dimensions
Default items metrics
select -- item id (dimension | the id of the item) items.item_id, -- item name (dimension | the name of the item) items.item_name, -- item brand (dimension | the brand of the item) items.item_brand, -- item variant (dimension | the variant of the item) items.item_variant, -- item category (dimension | the category of the item) items.item_category, -- item category 2 (dimension | the sub category of the item) items.item_category2, -- item category 3 (dimension | the sub category of the item) items.item_category3, -- item category 4 (dimension | the sub category of the item) items.item_category4, -- item category 5 (dimension | the sub category of the item) items.item_category5, -- price in usd (metric | the price of the item, in usd with standard unit) items.price_in_usd, -- price (metric | the price of the item in local currency) items.price, -- quantity (metric | the quantity of the item) items.quantity, -- item revenue in usd (metric | the revenue of this item, calculated as price_in_usd * quantity, it is populated for purchase events only, in usd with standard unit) items.item_revenue_in_usd, -- item revenue (metric | the revenue of this item, calculated as price * quantity, it is populated for purchase events only, in local currency with standard unit) items.item_revenue, -- item refund in usd (metric | the refund value of this item, calculated as price_in_usd * quantity, it is populated for refund events only, in usd with standard unit) items.item_refund_in_usd, -- item refund (metric | the refund value of this item, calculated as price_in_usd * quantity, it is populated for refund events only, in local currency with standard unit) items.item_refund, -- coupon (dimension | coupon code applied to this item) items.coupon, -- affiliation (dimension | a product affiliation to designate a supplying company or brick and mortar store location) items.affiliation, -- location id (dimension | the location associated with the item) items.location_id, -- item list id (dimension | the id of the list in which the item was presented to the user) items.item_list_id, -- item list name (dimension | the name of the list in which the item was presented to the user) items.item_list_name, -- item list index (dimension | the position of the item in a list) items.item_list_index, -- promotion id (dimension | the id of a product promotion) items.promotion_id, -- promotion name (dimension | the name of a product promotion) items.promotion_name, -- creative name (dimension | the name of a creative used in a promotional spot) items.creative_name, -- creative slot (dimension | the name of a creative slot) items.creative_slot from -- change this to your google analytics 4 export location in bigquery `ga4bigquery.analytics_250794857.events_*`, unnest(items) as items where -- define static and/or dynamic start and end date _table_suffix between '20201101' and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
Enjoying this post? Subscribe to the GA4BigQuery newsletter!
You will receive regular updates (max 1x/week) straight in your inbox. You can unsubscribe anytime.