Hi Everyone!
am trying to get a ratio dimension (AOV) from GA4 using BigQuery connection and used this sql query:
Copy code
SELECT
revenue AS value,
user_pseudo_id AS anonymous_id,
TIMESTAMP_MICROS(event_timestamp) as timestamp
FROM
(
SELECT
COALESCE(ecommerce.purchase_revenue,0) AS revenue,
user_pseudo_id,
event_timestamp
FROM
`project.dataset.events_*`
)
Used SUM(value) as aggregation and 'number of purchase' as denominator
in the metric preview I get Average much smaller than actual AOV. I have ($4) whereas actual AOV is around ($300)
The problem is that revenue is divided by count of records instead of the denominator (count of purchase)
Is this a valid sql and has anyone encountered issues like this?
Thank you!!
h
helpful-application-7107
10/10/2023, 2:18 PM
Hi Babak, this is a problem with our metric preview, but it should be computed correctly in the context of the experiment. You should see a note in the metric preview that we don't join to the denominator and are just showing you a time series of the numerator.