Cart Removals

window.digitalData.event.push({
  eventInfo: {
    eventName: "remove_from_cart",
    eventAction: "remove",
    eventType: "cart",
    timeStamp: new Date().toISOString()
  },
  category: {
    primaryCategory: "ecommerce",
    subCategory1: "cart",
    subCategory2: "remove_from_cart"
  },
  attributes: {
    currency: "USD",
    value: 29.99
  },
  product: [
    {
      productInfo: {
        productID: "SKU-12345",
        productName: "Classic T-Shirt",
        sku: "SKU-12345",
        brand: "Acme"
      },
      category: {
        primaryCategory: "Apparel"
      },
      attributes: {
        variant: "Blue / M"
      },
      price: {
        sellingPrice: 29.99,
        currency: "USD"
      },
      quantity: 1
    }
  ]
});

window.dataLayer.push({
  event: "remove_from_cart",
  ecommerce: {
    currency: "USD",
    value: 29.99,
    items: [
      {
        item_id: "SKU-12345",
        item_name: "Classic T-Shirt",
        item_brand: "Acme",
        item_category: "Apparel",
        item_variant: "Blue / M",
        price: 29.99,
        quantity: 1
      }
    ]
  }
});

window.adobeDataLayer.push({
  event: "commerce:cartItemRemoved",

  commerce: {
    cart: {
      cartID: "CART-98765",
      currencyCode: "USD",
      priceTotal: 29.99,

      productListItems: [
        {
          SKU: "SKU-12345",
          name: "Classic T-Shirt",
          currencyCode: "USD",
          priceTotal: 29.99,
          quantity: 1
        }
      ]
    }
  }
});

alloy("sendEvent", {
  xdm: {
    eventType: "commerce.productListRemovals",

    commerce: {
      productListRemovals: {
        value: 1,
        id: "generated_or_source_event_id"
      }
    },

    productListItems: [
      {
        SKU: "SKU-12345",
        name: "Classic T-Shirt",
        quantity: 1,
        priceTotal: 29.99,
        currencyCode: "USD"
      }
    ]
  }
});

WITH base AS ( SELECT PARSE_DATE('%Y%m%d', e.event_date) AS metric_date, e.event_timestamp, e.event_name, e.user_pseudo_id, ( SELECT ep.value.int_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'ga_session_id' ) AS ga_session_id, CONCAT( COALESCE(e.user_pseudo_id, 'unknown'), '-', CAST(e.event_timestamp AS STRING), '-', CAST(e.event_bundle_sequence_id AS STRING), '-', CAST(e.batch_event_index AS STRING) ) AS event_key, item.item_id AS item_id, item.item_name AS item_name, COALESCE(item.quantity, 1) AS quantity FROM `project.dataset.events_*` AS e LEFT JOIN UNNEST(e.items) AS item WHERE e.event_name = 'remove_from_cart' ) SELECT metric_date, COUNT(DISTINCT event_key) AS remove_from_cart_events, COUNTIF(item_id IS NOT NULL OR item_name IS NOT NULL) AS item_rows_removed, SUM( CASE WHEN item_id IS NOT NULL OR item_name IS NOT NULL THEN COALESCE(quantity, 1) ELSE 0 END ) AS units_removed, COUNT(DISTINCT user_pseudo_id) AS users_with_cart_removals, COUNT(DISTINCT CONCAT( COALESCE(user_pseudo_id, 'unknown'), '-', CAST(ga_session_id AS STRING) )) AS sessions_with_cart_removals FROM base GROUP BY metric_date ORDER BY metric_date;

WITH event_base AS ( SELECT id AS event_id, DATE(timestamp) AS metric_date, timestamp, eventType, identityMap['ECID'][0].id AS ecid, commerce.productListRemovals.value AS cart_removal_value, commerce.productListRemovals.id AS cart_removal_id, productListItems FROM your_websdk_experience_event_dataset WHERE eventType = 'commerce.productListRemovals' ), item_base AS ( SELECT event_id, productItem.SKU AS sku, productItem.name AS product_name, COALESCE(productItem.quantity, 1) AS quantity, productItem.priceTotal AS price_total FROM ( SELECT event_id, explode(productListItems) AS productItem FROM event_base ) ), event_agg AS ( SELECT metric_date, COUNT(DISTINCT event_id) AS remove_from_cart_events, COUNT(DISTINCT ecid) AS users_with_cart_removals FROM event_base GROUP BY metric_date ), item_agg AS ( SELECT eb.metric_date, COUNT(ib.sku) AS product_rows_removed, SUM(COALESCE(ib.quantity, 0)) AS units_removed, SUM(COALESCE(ib.price_total, 0)) AS removed_product_value FROM event_base eb LEFT JOIN item_base ib ON eb.event_id = ib.event_id GROUP BY eb.metric_date ) SELECT ea.metric_date, ea.remove_from_cart_events, ia.product_rows_removed, ia.units_removed, ea.users_with_cart_removals, ia.removed_product_value FROM event_agg ea LEFT JOIN item_agg ia ON ea.metric_date = ia.metric_date ORDER BY ea.metric_date;