WITH ecommerceProducts AS(
SELECT 
--Item name
item_name AS itemName,
items.item_category2 AS PAYMENT_METHOD,
Country_look.value.string_value AS Country,


--begiin_checkout 
COUNT(CASE WHEN event_name = 'begin_checkout' THEN CONCAT(event_timestamp, CAST(user_pseudo_id AS STRING)) ELSE NULL END) AS begin_checkout,
--add_shipping_info
COUNT(CASE WHEN event_name = 'add_shipping_info' THEN CONCAT(event_timestamp, CAST(user_pseudo_id AS STRING)) ELSE NULL END) AS add_shipping_info,
--add_payment_info
COUNT(CASE WHEN event_name = 'add_payment_info' THEN CONCAT(event_timestamp, CAST(user_pseudo_id AS STRING)) ELSE NULL END) AS add_payment_info,

--Cart_to_shipping_rate,
(CASE WHEN COUNT(CASE WHEN event_name = 'begin_checkout' THEN  user_pseudo_id ELSE NULL END) = 0 THEN 0
ELSE COUNT(DISTINCT CASE WHEN event_name = 'add_shipping_info' THEN user_pseudo_id  ELSE NULL END) /
COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id  ELSE NULL END) END  * 100)AS Cart_to_shipping_rate,

--add_payment_info_rate,
(CASE WHEN COUNT(CASE WHEN event_name = 'begin_checkout' THEN  user_pseudo_id ELSE NULL END) = 0 THEN 0
ELSE COUNT(DISTINCT CASE WHEN event_name = 'add_payment_info' THEN user_pseudo_id  ELSE NULL END) /
COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id  ELSE NULL END) END  * 100)AS add_payment_info_rate,

--Ecommerce Purchases
COUNT(CASE WHEN event_name = 'purchase' THEN ecommerce.transaction_id ELSE NULL END) AS ecommercePurchases, 
--Purchase-to-begun checkoutrate
(CASE WHEN COUNT(CASE WHEN event_name = 'begin_checkout' THEN  user_pseudo_id ELSE NULL END) = 0 THEN 0
ELSE COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_pseudo_id  ELSE NULL END) /
COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_pseudo_id  ELSE NULL END) END  * 100) AS purchaseToViewRate,
--Item purchase quantity
SUM(CASE WHEN event_name = 'purchase' THEN items.quantity  ELSE NULL END) AS itemPurchaseQuantity,
--Item revenue
SUM(item_revenue) AS itemRevenue
FROM 
`bigquery-341716.analytics_278788286.events_*`,
UNNEST(items) AS items,
UNNEST(user_properties) AS Country_look

--- Update the date fields currently last 30 days
WHERE _table_suffix ='20220614'
GROUP BY itemName,PAYMENT_METHOD,Country) 

SELECT Country,itemName,ecommerceProducts.PAYMENT_METHOD, begin_checkout, add_shipping_info,add_payment_info,ecommercePurchases,ROUND(Cart_to_shipping_rate,2) as Checked_to_shipping_rate,ROUND(add_payment_info_rate,2) as Checked_to_Payment_info_rate,
   ROUND(purchaseToViewRate,2) as purchase_to_checkout, itemPurchaseQuantity, itemRevenue
FROM ecommerceProducts
WHERE begin_checkout > 0 OR itemRevenue > 0
ORDER BY begin_checkout DESC