Average Abandoned Cart Value

{
  "cart": {
    "cartID": "cart_8841203",
    "price": {
      "cartTotal": 89.97,
      "currency": "USD"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU_1042",
          "productName": "Wool Coat"
        },
        "category": {
          "primaryCategory": "Outerwear"
        },
        "quantity": 1,
        "price": {
          "basePrice": 89.97
        }
      }
    ]
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "u_556291"
          }
        }
      ]
    }
  ],
  "event": [
    {
      "eventInfo": {
        "eventName": "add_to_cart"
      },
      "cart": {
        "cartID": "cart_8841203",
        "price": {
          "cartTotal": 114.96,
          "currency": "USD"
        },
        "item": [
          {
            "productInfo": {
              "productID": "SKU_1042",
              "productName": "Wool Coat"
            },
            "category": {
              "primaryCategory": "Outerwear"
            },
            "quantity": 1,
            "price": {
              "basePrice": 89.97
            }
          },
          {
            "productInfo": {
              "productID": "SKU_2098",
              "productName": "Wool Scarf"
            },
            "category": {
              "primaryCategory": "Accessories"
            },
            "quantity": 1,
            "price": {
              "basePrice": 24.99
            }
          }
        ]
      },
      "traffic_type": "organic"
    },
    {
      "eventInfo": {
        "eventName": "remove_from_cart"
      },
      "cart": {
        "cartID": "cart_8841203",
        "price": {
          "cartTotal": 89.97,
          "currency": "USD"
        },
        "item": [
          {
            "productInfo": {
              "productID": "SKU_2098",
              "productName": "Wool Scarf"
            },
            "category": {
              "primaryCategory": "Accessories"
            },
            "quantity": 1,
            "price": {
              "basePrice": 24.99
            }
          }
        ]
      },
      "traffic_type": "organic"
    }
  ]
}

[
  {
    "event": "add_to_cart",
    "cart_id": "cart_8841203",
    "currency": "USD",
    "value": 114.96,
    "user_id": "u_556291",
    "traffic_type": "organic",
    "items": [
      {
        "item_id": "SKU_1042",
        "item_name": "Wool Coat",
        "item_category": "Outerwear",
        "price": 89.97,
        "quantity": 1
      },
      {
        "item_id": "SKU_2098",
        "item_name": "Wool Scarf",
        "item_category": "Accessories",
        "price": 24.99,
        "quantity": 1
      }
    ]
  },
  {
    "event": "remove_from_cart",
    "cart_id": "cart_8841203",
    "currency": "USD",
    "value": 24.99,
    "user_id": "u_556291",
    "traffic_type": "organic",
    "items": [
      {
        "item_id": "SKU_2098",
        "item_name": "Wool Scarf",
        "item_category": "Accessories",
        "price": 24.99,
        "quantity": 1
      }
    ]
  }
]

[
  {
    "event": "scAdd",
    "cart_id": "cart_8841203",
    "currency": "USD",
    "value": 114.96,
    "user_id": "u_556291",
    "traffic_type": "organic",
    "items": [
      {
        "item_id": "SKU_1042",
        "item_name": "Wool Coat",
        "item_category": "Outerwear",
        "price": 89.97,
        "quantity": 1
      },
      {
        "item_id": "SKU_2098",
        "item_name": "Wool Scarf",
        "item_category": "Accessories",
        "price": 24.99,
        "quantity": 1
      }
    ]
  },
  {
    "event": "scRemove",
    "cart_id": "cart_8841203",
    "currency": "USD",
    "value": 24.99,
    "user_id": "u_556291",
    "traffic_type": "organic",
    "items": [
      {
        "item_id": "SKU_2098",
        "item_name": "Wool Scarf",
        "item_category": "Accessories",
        "price": 24.99,
        "quantity": 1
      }
    ]
  }
]

[
  {
    "commerce": {
      "cart": {
        "cartID": "cart_8841203"
      },
      "productListAdds": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "SKU_1042",
        "name": "Wool Coat",
        "priceTotal": 89.97,
        "quantity": 1,
        "currencyCode": "USD"
      },
      {
        "SKU": "SKU_2098",
        "name": "Wool Scarf",
        "priceTotal": 24.99,
        "quantity": 1,
        "currencyCode": "USD"
      }
    ],
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar5": "traffic_type"
          }
        }
      }
    }
  },
  {
    "commerce": {
      "cart": {
        "cartID": "cart_8841203"
      },
      "productListRemovals": {
        "value": 1
      }
    },
    "productListItems": [
      {
        "SKU": "SKU_2098",
        "name": "Wool Scarf",
        "priceTotal": 24.99,
        "quantity": 1,
        "currencyCode": "USD"
      }
    ],
    "_experience": {
      "analytics": {
        "customDimensions": {
          "eVars": {
            "eVar5": "traffic_type"
          }
        }
      }
    }
  }
]

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Reporting window on cart creation date; hits are read 7 extra days so every purchase window can close
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Known bot traffic is removed by GA4 at collection; the export has no bot field to filter on.
-- Cart and purchase hits with their parameters pivoted, internal traffic excluded
WITH hits AS (
  SELECT
    event_name,
    TIMESTAMP_MICROS(event_timestamp) AS event_ts,
    user_pseudo_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'cart_id') AS cart_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'currency') AS currency,
    (SELECT COALESCE(value.double_value, CAST(value.int_value AS FLOAT64)) FROM UNNEST(event_params) WHERE key = 'value') AS value
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', DATE_ADD(end_date, INTERVAL 7 DAY))
    AND event_name IN ('add_to_cart', 'remove_from_cart', 'purchase')
    AND COALESCE((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type'), '') != 'internal'
),
-- Cart key (cart_id, else user_pseudo_id + session) and signed value: adds positive, removals negative
cart_events AS (
  SELECT
    COALESCE(cart_id, CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING))) AS cart_key,
    user_pseudo_id,
    event_name,
    event_ts,
    currency,
    IF(event_name = 'add_to_cart', value, -value) AS signed_value
  FROM hits
  WHERE event_name IN ('add_to_cart', 'remove_from_cart')
),
-- One row per cart: first add_to_cart (creation time, shopper, currency) and net merchandise value
carts AS (
  SELECT cart_key, user_pseudo_id, currency, event_ts AS created_ts, net_value
  FROM (
    SELECT
      *,
      SUM(signed_value) OVER (PARTITION BY cart_key) AS net_value,
      ROW_NUMBER() OVER (PARTITION BY cart_key ORDER BY IF(event_name = 'add_to_cart', 0, 1), event_ts) AS rn
    FROM cart_events
  )
  WHERE rn = 1 AND event_name = 'add_to_cart'
),
-- Abandoned carts created in the window: net value > 0, no purchase by the shopper within 7 days, converted at the creation-date rate
abandoned AS (
  SELECT
    DATE(c.created_ts) AS cart_date,
    c.net_value * fx.rate_to_reporting AS net_value_reporting
  FROM carts AS c
  JOIN `your-project.reference.fx_daily_rates` AS fx
    ON fx.rate_date = DATE(c.created_ts) AND fx.currency = c.currency
  WHERE DATE(c.created_ts) BETWEEN start_date AND end_date
    AND c.net_value > 0
    AND NOT EXISTS (
      SELECT 1 FROM hits AS p
      WHERE p.event_name = 'purchase'
        AND p.user_pseudo_id = c.user_pseudo_id
        AND p.event_ts BETWEEN c.created_ts AND TIMESTAMP_ADD(c.created_ts, INTERVAL 7 DAY)
    )
)
-- Mean and median abandoned cart value per creation date; the last 7 days are provisional
SELECT
  cart_date,
  cart_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AS is_provisional,
  ROUND(SUM(net_value_reporting) / NULLIF(COUNT(*), 0), 2) AS average_abandoned_cart_value,
  ROUND(APPROX_QUANTILES(net_value_reporting, 100)[OFFSET(50)], 2) AS median_abandoned_cart_value
FROM abandoned
GROUP BY cart_date
ORDER BY cart_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Reporting window on cart creation date; hits are read 7 extra days so every purchase window can close
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
-- Known bot traffic is removed by Adobe bot rules at collection; the dataset has no bot field to filter on.
-- Cart and purchase hits with cart ID, shopper, session, currency and item value, internal traffic excluded
hits AS (
  SELECT
    CASE
      WHEN e.commerce.productListAdds.value > 0 THEN 'add_to_cart'
      WHEN e.commerce.productListRemovals.value > 0 THEN 'remove_from_cart'
      ELSE 'purchase'
    END AS event_name,
    e.timestamp AS event_ts,
    e.endUserIDs._experience.ecid.id AS visitor_id,
    e.commerce.cart.cartID AS cart_id,
    SESS_TIMEOUT(e.timestamp, 60 * 30) OVER (
      PARTITION BY e.endUserIDs._experience.ecid.id ORDER BY e.timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ).num AS session_num,
    e.productListItems[0].currencyCode AS currency,
    aggregate(e.productListItems, CAST(0 AS DOUBLE), (acc, x) -> acc + COALESCE(x.priceTotal, 0)) AS value
  FROM aep_web_events_dataset AS e
  CROSS JOIN params AS p
  WHERE (e.commerce.productListAdds.value > 0 OR e.commerce.productListRemovals.value > 0 OR e.commerce.purchases.value > 0)
    AND to_date(e.timestamp) BETWEEN p.start_date AND date_add(p.end_date, 7)
    AND COALESCE(LOWER(e._experience.analytics.customDimensions.eVars.eVar5), '') != 'internal'
),
-- Cart key (cart ID, else visitor + session) and signed value: adds positive, removals negative
cart_events AS (
  SELECT
    COALESCE(cart_id, CONCAT(visitor_id, '-', CAST(session_num AS STRING))) AS cart_key,
    visitor_id,
    event_name,
    event_ts,
    currency,
    CASE WHEN event_name = 'add_to_cart' THEN value ELSE -value END AS signed_value
  FROM hits
  WHERE event_name IN ('add_to_cart', 'remove_from_cart')
),
-- One row per cart: first add (creation time, shopper, currency) and net merchandise value
carts AS (
  SELECT cart_key, visitor_id, currency, event_ts AS created_ts, net_value
  FROM (
    SELECT
      *,
      SUM(signed_value) OVER (PARTITION BY cart_key) AS net_value,
      ROW_NUMBER() OVER (PARTITION BY cart_key ORDER BY CASE WHEN event_name = 'add_to_cart' THEN 0 ELSE 1 END, event_ts) AS rn
    FROM cart_events
  ) AS ranked
  WHERE rn = 1 AND event_name = 'add_to_cart'
),
-- Abandoned carts created in the window: net value > 0, no purchase by the shopper within 7 days, converted at the creation-date rate
abandoned AS (
  SELECT
    to_date(c.created_ts) AS cart_date,
    c.net_value * fx.rate_to_reporting AS net_value_reporting
  FROM carts AS c
  CROSS JOIN params AS p
  JOIN fx_daily_rates AS fx
    ON fx.rate_date = to_date(c.created_ts) AND fx.currency = c.currency
  WHERE to_date(c.created_ts) BETWEEN p.start_date AND p.end_date
    AND c.net_value > 0
    AND NOT EXISTS (
      SELECT 1 FROM hits AS h
      WHERE h.event_name = 'purchase'
        AND h.visitor_id = c.visitor_id
        AND h.event_ts BETWEEN c.created_ts AND c.created_ts + INTERVAL 7 DAYS
    )
)
-- Mean and median abandoned cart value per creation date; the last 7 days are provisional
SELECT
  cart_date,
  cart_date >= date_sub(current_date(), 7) AS is_provisional,
  ROUND(SUM(net_value_reporting) / NULLIF(COUNT(*), 0), 2) AS average_abandoned_cart_value,
  ROUND(percentile_approx(net_value_reporting, 0.5), 2) AS median_abandoned_cart_value
FROM abandoned
GROUP BY cart_date
ORDER BY cart_date;