big-breakfast-9029610/10/2023, 5:04 AM
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!!
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_*` )
helpful-application-710710/10/2023, 2:18 PM
big-breakfast-9029610/10/2023, 11:00 PM