Discrepancy in ecommerce metrics attribution between BigQuery and Google Analytics UI

Ecom
1

I’m having trouble getting accurate ecommerce data from BigQuery that matches what I see in the Google Analytics UI. My main issue is with product revenue. In BigQuery, all revenue is showing up as ‘(not set)’ for the product list, but the GA UI shows it correctly assigned to different product lists.

I’ve also noticed that only product list impressions and clicks are linked to product lists in BigQuery. Other important events like product detail views, checkouts, and purchases aren’t connected to product lists, unlike in the GA UI.

Here’s a sample query I’m using:

SELECT
  product.list AS ProductList,
  SUM(product.revenue)/1000000 AS TotalRevenue
FROM
  `myproject.mydataset.ga_sessions_*`, UNNEST(hits) AS hits, UNNEST(hits.product) AS product
WHERE
  _TABLE_SUFFIX BETWEEN '20230101' AND '20230131'
GROUP BY
  ProductList
ORDER BY TotalRevenue DESC

Can anyone help me figure out how to get BigQuery results that match the GA UI? I’m really stuck on this one.

2

eyyy BoldPainter45, i feel ur pain! :tired_face: BigQuery can be a real pain sometimes. have u checked if ur using the right event parameters? maybe try something like this:

SELECT
params.value.string_value AS ProductList,
SUM(ecommerce.purchase_revenue) AS TotalRevenue
FROM project.dataset.events_*
WHERE event_name = ‘purchase’
AND params.key = ‘item_list_name’
GROUP BY ProductList

this might help u get closer to wat u see in GA. lmk if it works!

3

I’ve encountered similar issues with BigQuery and GA4 data discrepancies. One key thing to remember is that BigQuery raw data doesn’t always align perfectly with the processed data in the GA UI. For ecommerce metrics, you might want to focus on the ‘purchase’ event specifically. Try modifying your query to use the ecommerce object:

SELECT
(SELECT value.string_value FROM UNNEST(event_params) WHERE key = ‘item_list_name’) AS ProductList,
SUM(ecommerce.purchase_revenue) AS TotalRevenue
FROM myproject.mydataset.events_*
WHERE event_name = ‘purchase’
AND _TABLE_SUFFIX BETWEEN ‘20230101’ AND ‘20230131’
GROUP BY ProductList
ORDER BY TotalRevenue DESC

This approach should give you a more accurate representation of revenue per product list. Remember to adjust the project, dataset, and date range as needed. If you’re still seeing discrepancies, it might be worth checking your data collection setup to ensure all necessary parameters are being passed correctly.

4

Hey there, BoldPainter45! :man_detective:

I’ve run into similar headaches with BigQuery and GA before. It’s like they’re speaking different languages sometimes, right?

Have you tried using the ecommerce.purchase_item event instead of the product schema? It might give you a clearer picture of revenue per product list. Something like:

SELECT
  ecommerce.purchase_item.item_list_name AS ProductList,
  SUM(ecommerce.purchase_item.price) AS TotalRevenue
FROM
  `myproject.mydataset.events_*`
WHERE
  event_name = 'purchase'
  AND _TABLE_SUFFIX BETWEEN '20230101' AND '20230131'
GROUP BY
  ProductList
ORDER BY TotalRevenue DESC

Just a thought! :thinking:

Also, curious - are you using standard GA4 or a custom implementation? Sometimes custom setups can cause weird discrepancies.

Let me know if that helps or if you’ve already tried something similar. Always happy to brainstorm more ideas!