ecommerce with country
Wed Jul 06 2022 16:36:32 GMT+0000 (Coordinated Universal Time)
Saved by @Matt_Stone #bigquery
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
Comments