Checking for Unsubs via Shop Pay

shop-pay-unsub-check.sql

-- A SQL query to find customers that may have unintentionally unsubscribed from their e-mail.
-- This is related to a Shop Pay bug where if customers come from a Headless shop, they're unsubscribed.


SELECT T1.metadata__email "Email", C.accepts_marketing "Subscribed", C.accepts_marketing_updated_at "Unsubscribe Date", T2.created_at "Transaction Date", O.created_at "Order Date"
FROM ca_ecomm.transactions__receipt__charges__data T1
LEFT JOIN ca_ecomm.transactions T2
ON 
T1.metadata__order_transaction_id::VARCHAR = T2.id::VARCHAR
LEFT JOIN ca_ecomm.customers C
ON T1.metadata__email = C.email
LEFT JOIN ca_ecomm.orders O
ON T2.order_id = O.id
WHERE 
T1.metadata__email IS NOT NULL AND 
T1.payment_method_details__card__checks__cvc_check IS NULL AND 
T1.payment_method_details__card__wallet__type IS NULL AND
T2.gateway = 'shopify_payments' AND
C.accepts_marketing = FALSE AND
C.accepts_marketing_updated_at::DATE = T2.created_at::DATE