E commerce funnel
Wed Jul 06 2022 11:04:24 GMT+0000 (Coordinated Universal Time)
Saved by @Matt_Stone #bigquery
WITH ecommerceProducts AS( SELECT --Item name item_name AS itemName, --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 --- Update the date fields currently last 30 days WHERE _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval 30 day)) and format_date('%Y%m%d',date_sub(current_date(), interval 1 day)) GROUP BY itemName) SELECT itemName, 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