-- ========================================== -- 2. ADOBE AEP QUERY SERVICE SQL -- ========================================== -- Abandoned Cart Recovery Rate = recovered orders credited to a recovery touch / all abandoned carts * 100 -- Cohort anchor is the ABANDONMENT timestamp (not the order date). Cohort period: 2026-08-01 to 2026-08-31, reporting timezone America/New_York. -- The ExperienceEvent scan runs from 2026-08-01 00:00 to the end of 2026-09-08 in America/New_York (bounds converted to UTC with to_utc_timestamp): -- cohort end + the fixed 7-day recovery window + a one-day buffer, so the last cohort day (2026-08-31) can still see every order it may claim. -- Voids: cancelled orders and FULL refunds only (refunded amount = order priceTotal); partial refunds keep the order. -- Abandonment is inferred: commerce.productListAdds / commerce.checkouts with no commerce.purchases for that cart before -- GREATEST(last cart activity + 30-minute session inactivity timeout, end of the same calendar day). One record per cart identifier (eVar21). -- Purchases inside the window with no recovery touch are ORGANIC RETURNS and are reported on their own line, never folded into the numerator. WITH base_events AS ( SELECT timestamp AS event_ts, eventType AS event_type, identityMap['CRMID'][0].id AS crm_contact_key, identityMap['ECID'][0].id AS ecid, commerce.order.purchaseID AS transaction_id, commerce.order.priceTotal AS order_value, aggregate(productListItems, CAST(0 AS DOUBLE), (acc, x) -> acc + COALESCE(CAST(x.priceTotal AS DOUBLE), 0)) AS item_price_total, LOWER(COALESCE(consents.marketing.email.val, '')) AS consent_email_val, commerce.order.currencyCode AS currency_code, commerce.order.payments[0].paymentType AS payment_type, commerce.purchases.value AS purchase_flag, commerce.productListAdds.value AS cart_add_flag, commerce.checkouts.value AS checkout_flag, directMarketing.emailOpened.value AS email_opened, directMarketing.emailClicked.value AS email_clicked, marketing.campaignID AS campaign_id, marketing.campaignName AS campaign_name, marketing.campaignGroup AS campaign_group, marketing.trackingCode AS tracking_code, LOWER(COALESCE(channel.mediaType, '')) AS channel_media_type, LOWER(COALESCE(channel.typeAtSource, '')) AS channel_type_at_source, _experience.analytics.customDimensions.eVars.eVar21 AS cart_id, _experience.analytics.customDimensions.eVars.eVar22 AS checkout_id, _experience.analytics.customDimensions.eVars.eVar23 AS abandoned_ts_context, _experience.analytics.customDimensions.eVars.eVar24 AS touch_context, _experience.analytics.customDimensions.eVars.eVar25 AS window_context, _experience.analytics.customDimensions.eVars.eVar26 AS checkout_step_context, LOWER(COALESCE(_experience.analytics.customDimensions.props.prop21, '')) AS order_context, LOWER(COALESCE(_experience.analytics.customDimensions.props.prop22, '')) AS touch_event, web.webPageDetails.name AS page_name, web.webPageDetails.siteSection AS site_section, LOWER(COALESCE(web.webPageDetails.URL, '')) AS page_url, LOWER(COALESCE(web.webReferrer.URL, '')) AS referrer_url, LOWER(COALESCE(environment.browserDetails.userAgent, '')) AS user_agent, LOWER(COALESCE(device.type, '')) AS device_category FROM aep_web_experience_event_dataset WHERE timestamp >= to_utc_timestamp(TIMESTAMP '2026-08-01 00:00:00', 'America/New_York') AND timestamp < to_utc_timestamp(TIMESTAMP '2026-09-09 00:00:00', 'America/New_York') AND eventType IN ( 'commerce.productListAdds', 'commerce.checkouts', 'commerce.purchases', 'commerce.backofficeOrderCancelled', 'commerce.backofficeOrderItemsRefunded', 'directMarketing.emailOpened', 'directMarketing.emailClicked', 'web.webinteraction.linkClicks' ) ), clean_events AS ( SELECT b.*, COALESCE(b.crm_contact_key, b.ecid) AS identity_key, CASE WHEN b.consent_email_val = 'y' THEN 1 WHEN b.consent_email_val IN ('n', 'dn') THEN 0 ELSE NULL END AS consent_flag, split(COALESCE(b.touch_context, ''), '\\|')[0] AS touch_channel_context, split(COALESCE(b.touch_context, ''), '\\|')[1] AS touch_message_type, split(COALESCE(b.touch_context, ''), '\\|')[2] AS touch_campaign_id, split(COALESCE(b.window_context, ''), '\\|')[0] AS attribution_model, CAST(regexp_extract(COALESCE(b.checkout_step_context, ''), 'checkout_step_([0-9]+)', 1) AS INT) AS checkout_step FROM base_events AS b WHERE NOT (b.user_agent RLIKE '(bot|crawler|spider|headless|monitoring)') AND NOT (b.page_url RLIKE '(staging|preview|qa\\.|loadtest)') AND NOT (LOWER(COALESCE(b.tracking_code, '')) RLIKE '(qa_|test_|seed_)') ), cart_activity AS ( SELECT cart_id, MAX(event_ts) AS last_cart_activity_ts, MIN(event_ts) AS first_cart_activity_ts, SUM(CASE WHEN event_type = 'commerce.checkouts' THEN 1 ELSE 0 END) AS checkout_events, MAX(COALESCE(checkout_step, CASE WHEN event_type = 'commerce.checkouts' THEN 1 ELSE 0 END)) AS checkout_step_at_abandonment, COALESCE( MAX(CASE WHEN event_type = 'commerce.checkouts' THEN item_price_total END), SUM(CASE WHEN event_type = 'commerce.productListAdds' THEN COALESCE(item_price_total, 0) ELSE 0 END) ) AS cart_value, MIN(consent_flag) AS consent_flag, MAX(identity_key) AS identity_key, MAX(crm_contact_key) AS crm_contact_key, MAX(ecid) AS ecid, MAX(checkout_id) AS checkout_id, MAX(device_category) AS device_category, MAX(currency_code) AS currency_code FROM clean_events WHERE event_type IN ('commerce.productListAdds', 'commerce.checkouts') AND cart_id IS NOT NULL AND cart_id <> '' GROUP BY cart_id ), purchase_orders AS ( SELECT transaction_id, MIN(event_ts) AS purchase_ts, MAX(COALESCE(order_value, 0)) AS purchase_revenue, MAX(cart_id) AS cart_id, MAX(identity_key) AS identity_key, MAX(crm_contact_key) AS crm_contact_key, MAX(ecid) AS ecid, MAX(payment_type) AS payment_type FROM clean_events WHERE event_type = 'commerce.purchases' AND COALESCE(purchase_flag, 0) > 0 AND transaction_id IS NOT NULL AND transaction_id <> '' GROUP BY transaction_id ), order_refunds AS ( SELECT transaction_id, SUM(COALESCE(order_value, item_price_total, 0)) AS refunded_amount FROM clean_events WHERE event_type = 'commerce.backofficeOrderItemsRefunded' AND transaction_id IS NOT NULL AND transaction_id <> '' GROUP BY transaction_id ), voided_orders AS ( SELECT DISTINCT transaction_id FROM clean_events WHERE event_type = 'commerce.backofficeOrderCancelled' AND transaction_id IS NOT NULL AND transaction_id <> '' UNION SELECT r.transaction_id FROM order_refunds AS r JOIN purchase_orders AS o ON o.transaction_id = r.transaction_id WHERE r.refunded_amount >= o.purchase_revenue ), valid_orders AS ( SELECT o.* FROM purchase_orders AS o LEFT JOIN voided_orders AS v ON v.transaction_id = o.transaction_id WHERE v.transaction_id IS NULL AND o.purchase_revenue > 0 ), cart_first_purchase AS ( SELECT cart_id, MIN(purchase_ts) AS first_cart_purchase_ts FROM purchase_orders WHERE cart_id IS NOT NULL AND cart_id <> '' GROUP BY cart_id ), cart_deadlines AS ( SELECT a.*, to_date(from_utc_timestamp(a.last_cart_activity_ts, 'America/New_York')) AS abandonment_date, greatest( CAST(from_unixtime(unix_timestamp(a.last_cart_activity_ts) + 1800) AS TIMESTAMP), to_utc_timestamp( CAST(date_add(to_date(from_utc_timestamp(a.last_cart_activity_ts, 'America/New_York')), 1) AS TIMESTAMP), 'America/New_York' ) ) AS abandonment_deadline_ts, CAST(from_unixtime(unix_timestamp(a.last_cart_activity_ts) + 7 * 86400) AS TIMESTAMP) AS recovery_window_end_ts FROM cart_activity AS a ), abandoned_carts AS ( SELECT c.cart_id, c.checkout_id, c.last_cart_activity_ts AS abandonment_ts, c.abandonment_date, c.abandonment_deadline_ts, c.recovery_window_end_ts, c.identity_key, c.crm_contact_key, c.ecid, c.checkout_step_at_abandonment, c.cart_value, c.device_category, c.currency_code, CASE WHEN c.consent_flag = 1 THEN 'granted' WHEN c.consent_flag = 0 THEN 'denied' ELSE 'unknown' END AS marketing_consent FROM cart_deadlines AS c LEFT JOIN cart_first_purchase AS p ON p.cart_id = c.cart_id WHERE c.abandonment_date BETWEEN DATE '2026-08-01' AND DATE '2026-08-31' AND ( p.first_cart_purchase_ts IS NULL OR p.first_cart_purchase_ts > c.abandonment_deadline_ts ) ), recovery_touches AS ( SELECT event_ts AS touch_ts, cart_id, identity_key, crm_contact_key, ecid, CASE WHEN touch_channel_context IN ('email', 'sms', 'push', 'retargeting') THEN touch_channel_context WHEN channel_media_type IN ('email', 'e-mail') THEN 'email' WHEN channel_media_type IN ('sms', 'mms', 'text') THEN 'sms' WHEN channel_media_type IN ('push', 'mobilepush', 'app_push') THEN 'push' WHEN channel_media_type IN ('display', 'social', 'advertising') THEN 'retargeting' ELSE 'other paid touch' END AS recovery_channel, COALESCE(campaign_id, touch_campaign_id) AS campaign_id, COALESCE(campaign_name, campaign_id, touch_campaign_id, '(not set)') AS campaign_name, campaign_group, tracking_code FROM clean_events WHERE event_type <> 'commerce.purchases' AND COALESCE(campaign_id, touch_campaign_id) IS NOT NULL AND ( COALESCE(email_opened, 0) > 0 OR COALESCE(email_clicked, 0) > 0 OR touch_event = 'notification_open' OR event_type IN ('directMarketing.emailOpened', 'directMarketing.emailClicked') OR (event_type = 'web.webinteraction.linkClicks' AND channel_media_type <> '') ) AND ( touch_message_type = 'abandoned_cart' OR channel_type_at_source RLIKE '(abandon|cart_recovery|winback)' OR LOWER(COALESCE(campaign_name, '')) RLIKE '(abandon|cart[_ -]?recovery|checkout[_ -]?recovery|winback)' ) ), window_orders AS ( SELECT a.cart_id, a.abandonment_date, a.abandonment_ts, a.identity_key, a.crm_contact_key, a.ecid, o.transaction_id, o.purchase_ts, o.purchase_revenue, o.payment_type, CASE WHEN o.cart_id = a.cart_id THEN 1 ELSE 2 END AS match_rank FROM abandoned_carts AS a JOIN valid_orders AS o ON o.purchase_ts > a.abandonment_deadline_ts AND o.purchase_ts <= a.recovery_window_end_ts AND ( o.cart_id = a.cart_id OR (a.crm_contact_key IS NOT NULL AND o.crm_contact_key = a.crm_contact_key) OR (a.identity_key IS NOT NULL AND o.identity_key = a.identity_key) ) ), window_orders_touched AS ( SELECT w.cart_id, w.abandonment_date, w.abandonment_ts, w.transaction_id, w.purchase_ts, w.purchase_revenue, w.payment_type, w.match_rank, t.recovery_channel, t.campaign_id, t.campaign_name, t.touch_ts, ROW_NUMBER() OVER ( PARTITION BY w.cart_id, w.transaction_id ORDER BY t.touch_ts DESC NULLS LAST ) AS touch_rn FROM window_orders AS w LEFT JOIN recovery_touches AS t ON t.touch_ts >= w.abandonment_ts AND t.touch_ts <= w.purchase_ts AND ( t.cart_id = w.cart_id OR (w.crm_contact_key IS NOT NULL AND t.crm_contact_key = w.crm_contact_key) OR (w.ecid IS NOT NULL AND t.ecid = w.ecid) ) ), last_non_direct_touch AS ( SELECT cart_id, abandonment_date, abandonment_ts, transaction_id, purchase_ts, purchase_revenue, payment_type, match_rank, recovery_channel, campaign_id, campaign_name, touch_ts, ROW_NUMBER() OVER ( PARTITION BY transaction_id ORDER BY match_rank, abandonment_ts DESC ) AS order_rn FROM window_orders_touched WHERE touch_rn = 1 ), order_credited_once AS ( SELECT t.*, ROW_NUMBER() OVER ( PARTITION BY t.cart_id ORDER BY CASE WHEN t.recovery_channel IS NULL THEN 1 ELSE 0 END, t.match_rank, t.purchase_ts ) AS cart_rn FROM last_non_direct_touch AS t WHERE t.order_rn = 1 ), cart_outcome AS ( SELECT * FROM order_credited_once WHERE cart_rn = 1 ), cart_fact AS ( SELECT a.abandonment_date, a.cart_id, CASE WHEN a.crm_contact_key IS NOT NULL THEN 1 ELSE 0 END AS is_identified, a.marketing_consent, a.checkout_step_at_abandonment, a.device_category, a.cart_value, o.transaction_id, o.purchase_revenue, o.payment_type, CASE WHEN o.recovery_channel IS NOT NULL THEN 1 ELSE 0 END AS is_recovered, CASE WHEN o.transaction_id IS NOT NULL AND o.recovery_channel IS NULL THEN 1 ELSE 0 END AS is_organic_return, CASE WHEN o.recovery_channel IS NOT NULL THEN o.recovery_channel WHEN o.transaction_id IS NOT NULL THEN 'organic return (no recovery touch)' ELSE 'not recovered' END AS recovery_channel, CASE WHEN o.recovery_channel IS NOT NULL THEN COALESCE(o.campaign_name, '(not set)') WHEN o.transaction_id IS NOT NULL THEN '(organic - no campaign)' ELSE '(none)' END AS campaign_name, CASE WHEN to_utc_timestamp(CAST(date_add(a.abandonment_date, 8) AS TIMESTAMP), 'America/New_York') <= current_timestamp() THEN 1 ELSE 0 END AS cohort_matured FROM abandoned_carts AS a LEFT JOIN cart_outcome AS o ON o.cart_id = a.cart_id ), daily_base AS ( SELECT abandonment_date, COUNT(*) AS abandoned_carts, SUM(CASE WHEN is_identified = 0 THEN 1 ELSE 0 END) AS anonymous_abandoned_carts, SUM(CASE WHEN marketing_consent <> 'granted' THEN 1 ELSE 0 END) AS consent_suppressed_carts, ROUND(SUM(cart_value), 2) AS abandoned_cart_value, MIN(cohort_matured) AS cohort_matured FROM cart_fact GROUP BY abandonment_date ), breakdown AS ( SELECT abandonment_date, recovery_channel, campaign_name, SUM(is_recovered) AS recovered_orders, SUM(is_organic_return) AS organic_return_orders, ROUND(SUM(CASE WHEN is_recovered = 1 THEN COALESCE(purchase_revenue, 0) ELSE 0 END), 2) AS recovered_revenue, ROUND(SUM(CASE WHEN is_organic_return = 1 THEN COALESCE(purchase_revenue, 0) ELSE 0 END), 2) AS organic_return_revenue FROM cart_fact GROUP BY GROUPING SETS ( (abandonment_date), (abandonment_date, recovery_channel), (abandonment_date, recovery_channel, campaign_name) ) ) SELECT b.abandonment_date, CASE WHEN b.recovery_channel IS NULL THEN 'total' WHEN b.campaign_name IS NULL THEN 'recovery channel' ELSE 'recovery channel x campaign' END AS breakdown_level, COALESCE(b.recovery_channel, '(all channels)') AS recovery_channel, COALESCE(b.campaign_name, '(all campaigns)') AS campaign_name, d.abandoned_carts, b.recovered_orders, ROUND(100.0 * b.recovered_orders / NULLIF(d.abandoned_carts, 0), 2) AS abandoned_cart_recovery_rate_pct, b.organic_return_orders, ROUND(100.0 * b.organic_return_orders / NULLIF(d.abandoned_carts, 0), 2) AS organic_return_rate_pct, b.recovered_revenue, ROUND(b.recovered_revenue / NULLIF(b.recovered_orders, 0), 2) AS recovered_aov, b.organic_return_revenue, d.abandoned_cart_value, d.anonymous_abandoned_carts, ROUND(100.0 * d.anonymous_abandoned_carts / NULLIF(d.abandoned_carts, 0), 2) AS unrecoverable_anonymous_share_pct, d.consent_suppressed_carts, ROUND(100.0 * d.consent_suppressed_carts / NULLIF(d.abandoned_carts, 0), 2) AS consent_suppressed_share_pct, CASE WHEN d.cohort_matured = 1 THEN 'final' ELSE 'provisional - 7-day recovery window still open, restate daily' END AS cohort_status FROM breakdown AS b JOIN daily_base AS d ON d.abandonment_date = b.abandonment_date ORDER BY b.abandonment_date, breakdown_level, recovery_channel, campaign_name;