Generating GA4 Item/Product Performance Reports in BigQuery: A Step-by-Step Guide

Creating a GA4 (Google Analytics 4) item/product performance report in BigQuery involves several steps. Here’s a comprehensive guide:

Prerequisites

• GA4 Setup: Ensure you have GA4 set up for your website or app.
• BigQuery Project: Have a BigQuery project and dataset ready.
• Linked GA4 to BigQuery: Ensure GA4 is linked to BigQuery and data is being exported.

Steps to Create the Report

Access BigQuery:

Go to the BigQuery Console.

Select the Dataset:

In the navigation panel, select your project and the dataset where GA4 data is stored.

Understand the GA4 Data Schema:

GA4 data is stored in nested JSON format, which includes events_* tables.

Each events_* table contains event-level data with columns such as event_name, event_timestamp, event_params, items, etc.

Write the SQL Query:

Construct a query to extract and analyze item/product performance data. Here is a sample query:

WITH item_data AS (
  SELECT
    event_date,
    event_timestamp,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'currency') AS currency,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
    items
  FROM
    `your_project.your_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20240606' AND '20240716'  -- specify your date range
    AND event_name = 'purchase'  -- filter for purchase events
),
items_flat AS (
  SELECT
    event_date,
    event_timestamp,
    currency,
    transaction_id,
    item.item_id,
    item.item_name,
    item.price_in_usd,
    item.quantity
  FROM
    item_data,
    UNNEST(items) AS item
)
SELECT
  item_id,
  item_name,
  currency,
  COUNT(DISTINCT transaction_id) AS number_of_purchases,
  SUM(quantity) AS total_quantity_sold,
  SUM(price_in_usd * quantity) AS total_revenue
FROM
  items_flat
GROUP BY
  item_id,
  item_name,
  currency
ORDER BY
  total_revenue DESC
    

Explanation:

• The first CTE (item_data) filters for purchase events and extracts relevant fields.
• The second CTE (items_flat) unnests the items array to get individual item details.
• The final SELECT statement aggregates the data to show performance metrics per item.

Run the Query:

Click on the Run button to execute the query. Result:

Analyze and Visualize:

Once the query is executed, you can see the results in the BigQuery Console. You can also export the results to Google Sheets, Data Studio, or other visualization tools for further analysis.

Tips

• Date Range: Adjust the date range in the query to match your analysis period.
• Event Name: Ensure the event name (purchase in this case) matches the event names used in your GA4 setup.
• Currency Conversion: If you sell in multiple currencies, consider adding logic to convert to a single currency.

Example Query Breakdown

• WITH item_data AS: Defines a CTE to filter and extract initial data.
• event_params: Extracts specific event parameters like currency and transaction_id.
• UNNEST(items): Flattens the nested items array.
• SUM(price_in_usd * quantity): Calculates total revenue per item.

By following these steps and adjusting the query based on your specific needs, you can create a detailed GA4 item/product performance report in BigQuery.