Skip to content

Instantly share code, notes, and snippets.

@stanasiukcom

stanasiukcom/ga4_cart_abandoners.sql

Last active May 24, 2022 20:37
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save stanasiukcom/37875f9a07741c621fa5016a9ed11ef5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save stanasiukcom/37875f9a07741c621fa5016a9ed11ef5 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ga4_cart_abandoners.sql
WITH user_cart_journeys AS (
SELECT
user_pseudo_id
,event_timestamp
,event_name
,RANK() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS rank
FROM
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
AND
event_name IN ('add_to_cart', 'purchase')
)
SELECT
DISTINCT user_email
FROM
user_cart_journeys AS ucj
JOIN
`defuseddata-sandbox.hightouch_presentation.user_emails`
USING (user_pseudo_id)
WHERE NOT ucj.user_pseudo_id IN (
SELECT
DISTINCT user_pseudo_id
FROM
user_cart_journeys
WHERE
event_name = 'purchase'
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment