Add to cart

{
  "page": {
    "pageInfo": {
      "pageID": "pdp:SKU-12345",
      "pageName": "product detail: classic running shoe"
    },
    "category": {
      "primaryCategory": "Footwear",
      "pageType": "product detail"
    }
  },
  "cart": {
    "cartID": "cart_98765",
    "price": {
      "basePrice": 79.98,
      "currency": "USD",
      "cartTotal": 79.98
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU-12345",
          "sku": "SKU-12345-BLU-10",
          "productName": "Classic Running Shoe",
          "manufacturer": "Acme",
          "color": "Blue",
          "size": "10"
        },
        "category": {
          "primaryCategory": "Footwear"
        },
        "quantity": 2,
        "price": {
          "basePrice": 39.99,
          "currency": "USD"
        }
      }
    ]
  },
  "event": [
    {
      "eventInfo": {
        "eventName": "add_to_cart",
        "eventAction": "scAdd",
        "eventID": "a1f3c9e2-7b4d-4e8a-9c21-5d6f0b8e1a47",
        "type": "commerce",
        "timeStamp": "2026-09-18T14:32:05.123Z"
      },
      "category": {
        "primaryCategory": "cart",
        "subCategory1": "product detail"
      },
      "attributes": {
        "addMethod": "pdp_button",
        "itemListName": "Running Shoes",
        "cartID": "cart_98765",
        "lineCount": 1,
        "unitCount": 2
      }
    }
  ]
}

{
  "event": "add_to_cart",
  "event_id": "a1f3c9e2-7b4d-4e8a-9c21-5d6f0b8e1a47",
  "cart_id": "cart_98765",
  "add_method": "pdp_button",
  "page_type": "product detail",
  "ecommerce": {
    "currency": "USD",
    "value": 79.98,
    "items": [
      {
        "item_id": "SKU-12345",
        "item_name": "Classic Running Shoe",
        "item_brand": "Acme",
        "item_category": "Footwear",
        "item_category2": "Running",
        "item_variant": "Blue-10",
        "item_list_id": "plp_running_shoes",
        "item_list_name": "Running Shoes",
        "index": 0,
        "price": 39.99,
        "quantity": 2
      }
    ]
  }
}

{
  "event": "add_to_cart",
  "eventInfo": {
    "eventId": "a1f3c9e2-7b4d-4e8a-9c21-5d6f0b8e1a47",
    "eventName": "scAdd",
    "addMethod": "pdp_button",
    "pageType": "product detail"
  },
  "cart": {
    "cartID": "cart_98765",
    "currencyCode": "USD"
  },
  "product": [
    {
      "SKU": "SKU-12345",
      "name": "Classic Running Shoe",
      "brand": "Acme",
      "category": "Footwear",
      "variant": "Blue-10",
      "listName": "Running Shoes",
      "quantity": 2,
      "price": 39.99,
      "priceTotal": 79.98,
      "currencyCode": "USD"
    }
  ]
}

{
  "_id": "a1f3c9e2-7b4d-4e8a-9c21-5d6f0b8e1a47",
  "eventType": "commerce.productListAdds",
  "timestamp": "2026-09-18T14:32:05.123Z",
  "commerce": {
    "productListAdds": {
      "value": 1
    },
    "cart": {
      "cartID": "cart_98765"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-12345",
      "name": "Classic Running Shoe",
      "quantity": 2,
      "priceTotal": 79.98,
      "currencyCode": "USD",
      "productAddMethod": "pdp_button"
    }
  ],
  "web": {
    "webPageDetails": {
      "name": "product detail: classic running shoe"
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Add to cart: one count per deduplicated add_to_cart event, independent of units or SKUs.
-- Events are counted at event grain; items are only aggregated inside a scalar subquery, never joined.
WITH atc_events AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS event_date,
    user_pseudo_id,
    event_timestamp,
    (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'event_id') AS event_id,
    (SELECT ep.value.int_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'ga_session_id') AS ga_session_id,
    (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'traffic_type') AS traffic_type,
    (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'add_method') AS add_method,
    (SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'page_location') AS page_location,
    device.web_info.hostname AS hostname,
    (SELECT STRING_AGG(CONCAT(IFNULL(i.item_id, ''), ':', CAST(COALESCE(NULLIF(i.quantity, 0), 1) AS STRING)), '|' ORDER BY i.item_id)
       FROM UNNEST(items) AS i) AS items_signature,
    (SELECT IFNULL(SUM(COALESCE(NULLIF(i.quantity, 0), 1)), 0) FROM UNNEST(items) AS i) AS units_added
  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 = 'add_to_cart'
),
filtered AS (
  SELECT
    event_date,
    CONCAT(IFNULL(user_pseudo_id, 'na'), '.', IFNULL(CAST(ga_session_id AS STRING), 'na')) AS session_key,
    -- Dedupe on event_id; fallback key wraps every nullable part so NULLs never collapse distinct events.
    COALESCE(
      NULLIF(event_id, ''),
      CONCAT(IFNULL(user_pseudo_id, 'na'), '.', IFNULL(CAST(ga_session_id AS STRING), 'na'), '.',
             IFNULL(CAST(event_timestamp AS STRING), 'na'), '.',
             TO_HEX(MD5(IFNULL(items_signature, ''))))
    ) AS dedupe_key,
    event_timestamp,
    units_added
  FROM atc_events
  WHERE IFNULL(traffic_type, '') NOT IN ('internal', 'developer', 'test')
    -- Non-shopper adds: login rehydration, cart merges, saved-cart restoration, promotion auto-injected gifts.
    AND LOWER(IFNULL(add_method, '')) NOT IN ('login_rehydration', 'cart_merge', 'saved_cart_restore', 'promo_auto_gift')
    -- QA / staging / dev hostnames (hostname from device.web_info, falling back to the page_location host).
    AND NOT REGEXP_CONTAINS(
      LOWER(COALESCE(hostname, NET.HOST(page_location), '')),
      r'(^|[.-])(qa|staging|stage|dev|test|uat|preview)([.-]|$)|localhost'
    )
),
deduped AS (
  SELECT *
  FROM filtered
  WHERE TRUE
  QUALIFY ROW_NUMBER() OVER (PARTITION BY dedupe_key ORDER BY event_timestamp) = 1
)
SELECT
  event_date,
  COUNT(*) AS add_to_cart,
  COUNT(DISTINCT session_key) AS sessions_with_add_to_cart,
  SUM(units_added) AS items_added_to_cart
FROM deduped
GROUP BY event_date
ORDER BY event_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Add to cart: one count per deduplicated commerce.productListAdds experience event (_id), independent of units.
-- Sessions are rebuilt over all web activity per ECID with a 30-minute inactivity timeout, then filtered to adds.
WITH web_events AS (
  SELECT
    _id,
    timestamp,
    eventType,
    identityMap['ECID'][0].id AS ecid,
    productListItems
  FROM aep_web_events_dataset
  WHERE timestamp >= date_sub(current_date(), 30)
    AND timestamp < current_date()
    AND identityMap['ECID'][0].id IS NOT NULL
    AND NOT (LOWER(COALESCE(environment.browserDetails.userAgent, '')) RLIKE 'bot|crawler|spider|headless|slurp|monitor')
    -- Internal traffic flagged at collection (office IP ranges on environment.ipV4 are mapped to trafficType upstream).
    AND COALESCE(_tenant.trafficType, '') <> 'internal'
    -- QA / staging / dev hostnames.
    AND NOT (LOWER(COALESCE(parse_url(web.webPageDetails.URL, 'HOST'), '')) RLIKE '(^|[.-])(qa|staging|stage|dev|test|uat|preview)([.-]|$)|localhost')
),
sessionized AS (
  SELECT
    _id,
    timestamp,
    eventType,
    ecid,
    productListItems,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY ecid ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session
  FROM web_events
),
atc_ranked AS (
  SELECT
    to_date(timestamp) AS event_date,
    CONCAT(ecid, '.', CAST(session.num AS STRING)) AS session_key,
    COALESCE(aggregate(
      productListItems,
      CAST(0 AS BIGINT),
      (acc, x) -> acc + CAST(COALESCE(NULLIF(x.quantity, 0), 1) AS BIGINT)
    ), CAST(0 AS BIGINT)) AS units_added,
    ROW_NUMBER() OVER (PARTITION BY _id ORDER BY timestamp) AS rn
  FROM sessionized
  WHERE eventType = 'commerce.productListAdds'
    -- Non-shopper adds: login rehydration, cart merges, saved-cart restoration, promotion auto-injected gifts.
    AND (productListItems IS NULL OR NOT exists(
      productListItems,
      x -> LOWER(COALESCE(x.productAddMethod, '')) IN ('login_rehydration', 'cart_merge', 'saved_cart_restore', 'promo_auto_gift')
    ))
)
SELECT
  event_date,
  COUNT(*) AS add_to_cart,
  COUNT(DISTINCT session_key) AS sessions_with_add_to_cart,
  SUM(units_added) AS items_added_to_cart
FROM atc_ranked
WHERE rn = 1
GROUP BY event_date
ORDER BY event_date;