Discrepancy in ecommerce metrics attribution between BigQuery and Google Analytics UI

Ecom
1

I’m having trouble with my BigQuery data not matching what I see in the Google Analytics interface. I’m trying to get product revenue for each product list. But in my query, all the revenue is showing up as ‘(not set)’ for the product list.

It’s not just revenue. I noticed that product list impressions and clicks are assigned correctly, but other stuff like product views, checkouts, and purchases aren’t linked to the right lists in BigQuery. This is different from what I see in the GA interface.

Here’s a simplified version of my query:

SELECT
  product_list AS ListName,
  SUM(revenue) / 1000000 AS TotalRevenue
FROM
  ecommerce_data
WHERE
  date = '2018-11-03'
GROUP BY
  ListName
ORDER BY
  TotalRevenue DESC

Does anyone know why this is happening or how I can fix it? I really need to get accurate revenue data for each product list in BigQuery. Any help would be awesome!

2

hey flyingeagle, i’ve faced similar issues. the problem might be with how ga tracks events across sessions. try using the (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'list_name') approach to extract list names from event parameters. this could solve ur attribution problem. good luck!

3

I’ve encountered this issue before. The discrepancy likely stems from how GA processes and attributes data across sessions. In BigQuery, events are stored individually, while GA’s interface aggregates them.

To resolve this, try modifying your query to use the last non-null list name for each session. This approach often aligns better with GA’s attribution model:

WITH session_list AS (
  SELECT
    session_id,
    LAST_VALUE(product_list IGNORE NULLS) OVER (
      PARTITION BY session_id ORDER BY event_timestamp
    ) AS last_list
  FROM ecommerce_data
  WHERE date = '2018-11-03'
)
SELECT
  s.last_list AS ListName,
  SUM(e.revenue) / 1000000 AS TotalRevenue
FROM ecommerce_data e
JOIN session_list s ON e.session_id = s.session_id
WHERE e.date = '2018-11-03'
GROUP BY ListName
ORDER BY TotalRevenue DESC

This should yield results more consistent with the GA interface. Let me know if that helps.

4

Hey FlyingEagle, interesting problem you’ve got there! :thinking:

I’m curious, have you checked if this discrepancy happens for all date ranges or just specific ones? Sometimes GA can be a bit quirky with real-time data vs. processed data.

Also, have you tried comparing the raw event data in BigQuery with what GA is showing? It might give some clues about where things are getting mixed up.

Have you considered using the ga4 schema instead? I’ve heard it handles ecommerce attribution a bit differently, so it could be worth a shot if you’re open to experimenting.

Let us know what you find out! This kind of stuff can be a real head-scratcher sometimes. :sweat_smile: