Carts Created

{
  "event": [
    {
      "eventInfo": {
        "eventName": "add_to_cart",
        "add_method": "button",
        "add_source": "product_page",
        "page_type": "product_detail",
        "item_list_id": "related_products",
        "item_list_name": "Related Products",
        "traffic_type": "external"
      }
    }
  ],
  "cart": {
    "cartID": "cart_8841273",
    "price": {
      "cartTotal": "49.99",
      "currency": "USD"
    },
    "item": [
      {
        "productInfo": {
          "productID": "SKU_12345",
          "productName": "Wireless Mouse"
        },
        "price": {
          "unitPrice": "49.99"
        },
        "quantity": "1"
      }
    ]
  },
  "user": [
    {
      "profile": [
        {
          "profileInfo": {
            "profileID": "u_582910"
          }
        }
      ]
    }
  ]
}

{
  "event": "add_to_cart",
  "ecommerce": {
    "currency": "USD",
    "value": 49.99,
    "cart_id": "cart_8841273",
    "items": [
      {
        "item_id": "SKU_12345",
        "item_name": "Wireless Mouse",
        "price": 49.99,
        "quantity": 1
      }
    ]
  },
  "user_id": "u_582910",
  "traffic_type": "external",
  "add_method": "button",
  "page_type": "product_detail",
  "add_source": "product_page",
  "item_list_id": "related_products",
  "item_list_name": "Related Products"
}

{
  "event": "scAdd",
  "cart": {
    "cartId": "cart_8841273",
    "currency": "USD",
    "value": 49.99
  },
  "product": [
    {
      "items": {
        "SKU_12345": {
          "price": 49.99,
          "quantity": 1
        }
      }
    }
  ],
  "user": {
    "userId": "u_582910",
    "trafficType": "external"
  },
  "page": {
    "pageType": "product_detail"
  },
  "addMethod": "button",
  "addSource": "product_page",
  "itemListId": "related_products",
  "itemListName": "Related Products"
}

{
  "eventType": "commerce.productListAdds",
  "timestamp": "2026-09-26T14:22:00Z",
  "commerce": {
    "cart": {
      "cartID": "cart_8841273"
    },
    "productListAdds": {
      "value": 1,
      "currencyCode": "USD"
    }
  },
  "productListItems": [
    {
      "SKU": "SKU_12345",
      "name": "Wireless Mouse",
      "priceTotal": 49.99,
      "quantity": 1
    }
  ],
  "_tenant": {
    "trafficType": "external"
  },
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar_add_method": "button",
          "eVar_add_source": "product_page",
          "eVar_item_list_id": "related_products",
          "eVar_item_list_name": "Related Products"
        }
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Each cart_id counts as its own cart; a guest cart merged into an account cart at login may count twice.
-- add_to_cart events up to end_date (no lower bound, so a cart's true first add is found), params pivoted with UNNEST
WITH adds AS (
  SELECT
    event_date,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'cart_id') AS cart_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX <= FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'add_to_cart'
),
-- drop null/empty cart_id and internal traffic, then date each cart to its first add_to_cart
first_adds AS (
  SELECT cart_id, PARSE_DATE('%Y%m%d', MIN(event_date)) AS first_add_date
  FROM adds
  WHERE NULLIF(TRIM(cart_id), '') IS NOT NULL
    AND COALESCE(traffic_type, '') != 'internal'
  GROUP BY cart_id
)
-- carts whose first add falls in the reporting window
SELECT COUNT(*) AS carts_created
FROM first_adds
WHERE first_add_date BETWEEN start_date AND end_date;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Each cartID counts as its own cart; a guest cart merged into an account cart at login may count twice.
-- reporting window: the last 90 complete days
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date, date_sub(current_date(), 1) AS end_date
),
-- productListAdds events up to end_date (no lower bound, so a cart's true first add is found)
adds AS (
  SELECT
    e.timestamp,
    e.commerce.cart.cartID AS cart_id,
    e._tenant.trafficType AS traffic_type
  FROM aep_web_events_dataset e
  CROSS JOIN params p
  WHERE e.eventType = 'commerce.productListAdds'
    AND to_date(e.timestamp) <= p.end_date
),
-- drop null/empty cartID and internal traffic, then date each cart to its first add
first_adds AS (
  SELECT cart_id, to_date(MIN(timestamp)) AS first_add_date
  FROM adds
  WHERE NULLIF(TRIM(cart_id), '') IS NOT NULL
    AND COALESCE(traffic_type, '') != 'internal'
  GROUP BY cart_id
)
-- carts whose first add falls in the reporting window
SELECT COUNT(*) AS carts_created
FROM first_adds f
CROSS JOIN params p
WHERE f.first_add_date BETWEEN p.start_date AND p.end_date;