Cart abandonment rate

{
  "event": [
    {
      "eventInfo": {
        "eventName": "add_to_cart"
      }
    }
  ],
  "cart": {
    "cartID": "cart_8f2a91",
    "price": {
      "basePrice": 29.99,
      "currency": "USD",
      "cartTotal": 29.99
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU_12345",
          "productName": "Sample Product"
        },
        "category": {
          "primaryCategory": "Apparel"
        },
        "quantity": 1,
        "price": {
          "basePrice": 29.99
        }
      }
    ]
  }
}

{
  "event": "add_to_cart",
  "ecommerce": {
    "currency": "USD",
    "value": 29.99,
    "items": [
      {
        "item_id": "SKU_12345",
        "item_name": "Sample Product",
        "item_category": "Apparel",
        "price": 29.99,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "scAdd",
  "eventInfo": {
    "eventName": "scAdd"
  },
  "cart": {
    "cartID": "cart_8f2a91",
    "currency": "USD",
    "total": 29.99,
    "items": [
      {
        "productID": "SKU_12345",
        "productName": "Sample Product",
        "category": "Apparel",
        "price": 29.99,
        "quantity": 1
      }
    ]
  }
}

{
  "eventType": "commerce.productListAdds",
  "commerce": {
    "productListAdds": {
      "value": 1
    },
    "cart": {
      "cartID": "cart_8f2a91"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU_12345",
      "name": "Sample Product",
      "priceTotal": 29.99,
      "quantity": 1,
      "currencyCode": "USD"
    }
  ]
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Cart abandonment rate = cart sessions (>= 1 add_to_cart) with no purchase at/after the first add / all cart sessions
WITH cart_events AS (
  SELECT
    user_pseudo_id,
    (SELECT MAX(IF(ep.key = 'ga_session_id', ep.value.int_value, NULL)) FROM UNNEST(event_params) AS ep) AS ga_session_id,
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    event_name,
    event_timestamp
  FROM `your-project.analytics_123456789.events_*`
  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))
    AND event_name IN ('add_to_cart', 'purchase')
    AND ARRAY_LENGTH(items) > 0
),
carts AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MIN(IF(event_name = 'add_to_cart', event_date, NULL)) AS cart_date,
    MIN(IF(event_name = 'add_to_cart', event_timestamp, NULL)) AS first_add_ts,
    MAX(IF(event_name = 'purchase', event_timestamp, NULL)) AS last_purchase_ts
  FROM cart_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
  HAVING MIN(IF(event_name = 'add_to_cart', event_timestamp, NULL)) IS NOT NULL
)
SELECT
  cart_date,
  COUNT(*) AS carts_created,
  COUNTIF(last_purchase_ts IS NULL OR last_purchase_ts < first_add_ts) AS abandoned_carts,
  ROUND(100 * SAFE_DIVIDE(COUNTIF(last_purchase_ts IS NULL OR last_purchase_ts < first_add_ts), COUNT(*)), 2) AS cart_abandonment_rate_pct
FROM carts
GROUP BY cart_date
ORDER BY cart_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Cart abandonment rate = cart sessions (>= 1 commerce.productListAdds) with no purchase at/after the first add / all cart sessions
WITH sessionized AS (
  SELECT
    endUserIDs._experience.ecid.id AS ecid,
    timestamp,
    eventType,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY endUserIDs._experience.ecid.id
      ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM commerce_experience_event_dataset
  WHERE timestamp >= date_sub(current_date, 30)
    AND timestamp < current_date
),
carts AS (
  SELECT
    ecid,
    session.num AS session_num,
    MIN(CASE WHEN eventType = 'commerce.productListAdds' THEN CAST(timestamp AS DATE) END) AS cart_date,
    MIN(CASE WHEN eventType = 'commerce.productListAdds' THEN timestamp END) AS first_add_ts,
    MAX(CASE WHEN eventType = 'commerce.purchases' THEN timestamp END) AS last_purchase_ts
  FROM sessionized
  WHERE eventType IN ('commerce.productListAdds', 'commerce.purchases')
  GROUP BY ecid, session.num
  HAVING MIN(CASE WHEN eventType = 'commerce.productListAdds' THEN timestamp END) IS NOT NULL
)
SELECT
  cart_date,
  COUNT(*) AS carts_created,
  SUM(CASE WHEN last_purchase_ts IS NULL OR last_purchase_ts < first_add_ts THEN 1 ELSE 0 END) AS abandoned_carts,
  ROUND(100.0 * SUM(CASE WHEN last_purchase_ts IS NULL OR last_purchase_ts < first_add_ts THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0), 2) AS cart_abandonment_rate_pct
FROM carts
GROUP BY cart_date
ORDER BY cart_date;