ecomitems_flip

SELECT
    event_date,
    item_id,
    item_name,
    item_brand,
    item_category,
    item_battery,
    item_capacity,
    item_colour,
    item_condition,
    item_stock,
    SUM(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS total_items_viewed,
    SUM(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS total_adds_to_cart,
    SUM(CASE WHEN event_name = 'add_to_cart' THEN quantity ELSE 0 END) AS total_adds_to_cart_qty,
    SUM(CASE WHEN event_name = 'add_to_wishlist' THEN 1 ELSE 0 END) AS total_adds_to_wishlist,
    SUM(CASE WHEN event_name = 'view_cart' THEN 1 ELSE 0 END) AS total_carts_viewed,
    SUM(CASE WHEN event_name = 'remove_from_cart' THEN 1 ELSE 0 END) AS total_removes_from_cart,
    SUM(CASE WHEN event_name = 'remove_from_cart' THEN quantity ELSE 0 END) AS total_removes_from_cart_qty,
    SUM(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS total_checkouts_started,
    SUM(CASE WHEN event_name = 'begin_checkout' THEN quantity ELSE 0 END) AS total_checkouts_started_qty,
    SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS total_purchases,
    SUM(CASE WHEN event_name = 'purchase' THEN quantity ELSE 0 END) AS total_purchase_qty,
    SUM(CASE WHEN event_name = 'purchase' THEN item_revenue ELSE 0 END) AS total_purchase_revenue,
    SUM(CASE WHEN event_name = 'refund' THEN 1 ELSE 0 END) AS total_refunds
FROM
    `cogent-node-249607.dataform_output.ro_output_ga4_ecom`
WHERE
  event_name IN ('view_item', 'add_to_cart', 'add_to_wishlist', 'view_cart', 'remove_from_cart', 'begin_checkout', 'purchase', 'refund')
GROUP BY
    event_date,
    item_id,
    item_name,
    item_brand,
    item_category,
    item_battery,
    item_capacity,
    item_colour,
    item_condition,
    item_stock