GA4 E-Commerce Data in Google Sheets: Why You Need BigQuery for Product-Level Detail

If you have connected GA4 to Google Sheets and tried to pull product-level e-commerce data (individual SKUs, quantities sold, per-item revenue) you have run into a wall. The GA4 Data API returns aggregate metrics like total revenue and transaction count. It does not return the items array that contains product-level detail from each transaction.

This is not a configuration issue. It is an architectural limitation of the API itself. Understanding it before you spend hours trying to work around it will save significant time.

What the GA4 Data API Actually Returns

The GA4 Data API. The same API that powers the Google Analytics add-on, third-party connectors, and Apps Script integrations: exposes a predefined set of dimensions and metrics. For e-commerce, the standard metrics include:

• ecommercePurchases, number of completed purchases
• purchaseRevenue, total revenue from purchases
• transactions, number of transactions
• averagePurchaseRevenue, average revenue per purchase
• itemsPurchased, total units sold across all items

The dimensions include:

• transactionId, unique identifier per transaction
• itemName, name of a product (see note below)
• itemId. SKU or product ID
• itemCategory, itemCategory2, itemBrand, product classification fields

Here is the critical point: these item dimensions work by flattening each product into a separate row in the report. If you query date + itemName + itemRevenue, you will get a row per date per item showing aggregate revenue for that item on that date. This is useful for trending and comparison.

What you cannot get from the Data API:

• The list of items in a specific transaction (the items array).
• The quantity and price of each item within a given order.
• Event-level parameters that were attached to the purchase event but are not in the standard dimension/metric schema.
• Custom item parameters (e.g., a size or color parameter on an add_to_cart event).

The Data API was designed for aggregate reporting. It is not an event database.

What You Need BigQuery For

When you enable the GA4 BigQuery export, Google streams your GA4 event data into a BigQuery dataset: one row per event, with nested JSON containing all event parameters, user properties, and item arrays.

The BigQuery table schema stores e-commerce data in nested repeated fields. Each purchase event has an items array that looks like this:

event_name: "purchase"
event_params:
  - key: transaction_id, value: "TXN-20250615-00847"
  - key: value, value: 89.97
items:
  - item_id: "SKU-042"
    item_name: "Merino Wool Sweater"
    quantity: 1
    price: 59.99
  - item_id: "SKU-091"
    item_name: "Wool Socks"
    quantity: 2
    price: 14.99

To extract this in BigQuery, you use the UNNEST() function to flatten the nested items array:

SELECT
  event_date,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'transaction_id') AS transaction_id,
  item.item_id,
  item.item_name,
  item.quantity,
  item.price
FROM
  `your_project.analytics_XXXXXXXXX.events_*`
  CROSS JOIN UNNEST(items) AS item
WHERE
  event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20250601' AND '20250630'

This query returns one row per item per transaction, which is exactly what you need for product-level analysis in a spreadsheet.

You can then connect Google Sheets to this BigQuery query using Connected Sheets, and the product-level data appears directly in your sheet.

API vs. BigQuery: Side-by-Side Comparison

The Data API is the right tool for campaign reporting, traffic analysis, and conversion rate monitoring. BigQuery is the right tool for product analytics, order-level reconciliation, and any use case that requires the event stream rather than aggregated summaries.

Setting Up the GA4 to BigQuery Export

The export is configured in GA4 Admin and requires a Google Cloud project.

Prerequisites

• A Google Analytics 4 property with Editor or Administrator access.
• A Google Cloud project with billing enabled.
• The BigQuery API enabled in that GCP project.

Step-by-step setup

1. In GA4, go to Admin (gear icon at bottom left) > Product links > BigQuery links.
2. Click Link.
3. Select your GCP project from the list. If it does not appear, ensure the BigQuery API is enabled and you have the BigQuery Admin role in that project.
4. Choose export settings:

• Daily export: Exports all events from the previous day once per day, typically in the early morning hours UTC. Data appears in date-partitioned tables named events_YYYYMMDD.
• Streaming export: Exports events within minutes of collection to a events_intraday_YYYYMMDD table that is replaced by the daily table at end of day. Streaming costs more per GB than standard storage.

1. Select the frequency (daily, streaming, or both) and the region for your BigQuery dataset.
2. Click Submit.

After linking, data appears in BigQuery within 24 hours. Historical data is not backfilled. The export starts from the day you enable it.

Dataset structure

Your dataset will contain:

• events_YYYYMMDD, one table per day, permanent record.
• events_intraday_YYYYMMDD, today's streaming data (if streaming is enabled), overwritten by the daily table.

Querying BigQuery from Google Sheets with Connected Sheets

Connected Sheets is a Google Sheets feature that lets you write SQL against BigQuery and display the results as a sheet range. It is available on Google Workspace Business Standard and above.

Setup

1. In Google Sheets, go to Data > Data connectors > Connect to BigQuery.
2. Select your GCP project and the dataset created by the GA4 export.
3. In the query editor, write your SQL (see the example in the previous section).
4. Click Apply. The results populate the connected sheet range.
5. To schedule refresh: click the Connected Sheets panel > gear icon > set a refresh interval (hourly, daily, etc.).

Considerations

• Connected Sheets processes the query using BigQuery's infrastructure. Each refresh runs the full SQL query and billable bytes are consumed.
• For product-level queries spanning 30+ days on a large GA4 property, a single query can process several GB. Cache your queries (BigQuery caches identical queries for 24 hours at no charge) to reduce costs.
• Connected Sheets has a row limit of 50,000 rows per connected range. For properties with very high transaction volumes, you need to aggregate in the SQL rather than pulling raw rows.

Cost Considerations

Two components drive BigQuery costs for this use case.

Storage

GA4 BigQuery export data is stored in BigQuery. Storage costs approximately $0.02 per GB per month for active storage, and $0.01/GB/month for long-term storage (tables not modified in 90+ days).

A typical mid-size e-commerce property might generate 1–5 GB of event data per month. Annual storage for two years of data: 24–120 GB, or $0.50–$2.40/month.

Storage is almost never the significant cost.

Query processing

BigQuery charges for query processing at $5 per TB scanned (on-demand pricing). The events_* tables are date-partitioned: always filter by _TABLE_SUFFIX or event_date to limit the partition range scanned.

Without partition filtering, a query might scan 30+ GB for a 30-day date range. With filtering, the same query might scan 3–5 GB.

At $5/TB, scanning 5 GB costs $0.025 per query run. At 24 hourly refreshes per day, that is $0.60/day, or about $18/month: for one query refreshing hourly. Adjust the refresh frequency based on how current your product data needs to be.

Tips to reduce costs:

• Use daily refresh rather than hourly if the data is used for trend analysis rather than operational monitoring.
• Create summary tables in BigQuery (using scheduled queries) that pre-aggregate product data by day. Query the summary table from Sheets instead of the raw event table.
• Use BigQuery's free tier (1 TB of query processing per month), most small to mid-size e-commerce properties will stay within or near this limit.

When a Third-Party Connector Can Simplify the Pipeline

The GA4 → BigQuery → Sheets path involves three products, billing in two places, and SQL knowledge. For teams that want product-level e-commerce data without managing that stack, third-party connectors offer an alternative: with caveats.

What connectors can do

Some connectors (including brooked.io) can connect to BigQuery as a data source and write the results to Sheets, effectively acting as a scheduled query runner. You write the SQL once in the connector configuration, and the connector handles the BigQuery API authentication, query execution, and Sheets write on a schedule.

This eliminates the Connected Sheets Workspace tier requirement and centralizes your GA4 data pipeline in one tool. The same connector that might also be pulling your Stripe and HubSpot data.

What connectors cannot change

If your GA4 property does not have the BigQuery export enabled, no connector can get product-level item data from the Data API alone. The architectural limitation is in GA4, not in the connector. BigQuery export is a prerequisite for item-level e-commerce data regardless of which tool you use to move it to Sheets.

The practical recommendation

If you are already using a connector for other GA4 data (sessions, campaign metrics) and just need to add product-level e-commerce data, check whether your connector supports BigQuery as an additional source. brooked.io supports BigQuery connections alongside GA4 Data API connections, which means you can have aggregate marketing metrics from the GA4 API and product-level e-commerce data from BigQuery, all feeding into the same workbook on separate tabs.

Comparison Table: Methods for GA4 E-Commerce Data in Sheets

Troubleshooting

The BigQuery export is linked but no tables appear in the dataset. The first daily export typically takes 24 hours after linking. Streaming export takes a few hours to begin. If it has been more than 48 hours, check that billing is active on the GCP project: a billing issue silently stops the export.

My SQL query returns no rows for recent dates. GA4 event dates use the event_date field in YYYYMMDD format and _TABLE_SUFFIX for partition selection. If your date filter uses BETWEEN '2025-06-01' AND '2025-06-30', confirm the string format matches exactly. A common mistake is using dashes in the partition suffix filter: use '20250601' not '2025-06-01'.

**Product names appear in BigQuery but the items array is empty for some events.** Only events that include an items array in the GA4 dataLayer push will have item data in BigQuery. If your purchase event fires but the items array is not included in the gtag('event', 'purchase', {...}) call, the BigQuery record will have an empty items array. Audit your measurement implementation: specifically whether the e-commerce items parameter is being passed correctly.

Connected Sheets shows a row limit warning. The 50,000-row limit is per connected range. Add aggregation to your SQL: use GROUP BY item_name and SUM(quantity) to summarize rather than returning raw event rows. This also reduces query cost.

The BigQuery query processes more data than expected. Add AND _TABLE_SUFFIX BETWEEN '20250601' AND '20250630' to your query. Without this partition filter, BigQuery may scan all tables in the dataset. The events_* wildcard without a suffix filter reads every table.

Bottom Line

The GA4 Data API is excellent for what it does: aggregate metrics, campaign performance, funnel analysis, and conversion reporting. For e-commerce teams that need product-level data, which SKUs are driving revenue, what quantities are being purchased, which products appear most often in multi-item orders. The Data API is the wrong tool. It simply does not expose the items array.

BigQuery export is not optional for this use case. Once it is set up, Connected Sheets or a connector like brooked.io makes querying it from a spreadsheet straightforward. The complexity is front-loaded in the setup; ongoing reporting is just SQL and a refresh schedule.

For teams on Google Workspace Business Standard or above, Connected Sheets is the most native path. For teams on other tiers or those who want to consolidate their data tools, a connector that supports both the GA4 Data API and BigQuery as sources keeps the entire pipeline in one place.

Get Product-Level GA4 Data into Your Sheets

brooked.io connects to both the GA4 Data API and BigQuery, writing aggregate metrics and item-level e-commerce data into separate tabs of the same workbook. Start your free trial at brooked.io, no BigQuery SQL expertise required for the setup.

Related guides on brooked.io:

• GA4 in Google Sheets: How to Get Hourly Refresh (Not Just Daily)
• The Hidden Google Sheets GA4 Connector Nobody Talks About (And Its Catch)
• How to Combine Stripe, HubSpot, and Google Analytics Data in One Google Sheet

Troubleshooting quick reference

Frequently asked questions