Hi Everyone! am trying to get a ratio dimension (AOV) from GA4 using BigQuery connection and used this sql query:

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!!

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.

Thank you Luke!