Wishlist Additions

{
  "digitalData": {
    "page": {
      "pageInfo": {
        "pageName": "pdp:SKU-10482",
        "pageType": "product detail"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "add_to_wishlist",
          "eventAction": "save",
          "type": "wishlist",
          "timeStamp": "2026-09-14T00:59:00Z"
        },
        "attributes": {
          "currency": "USD",
          "value": 89.99,
          "listName": "product_detail"
        }
      }
    ],
    "product": [
      {
        "productInfo": {
          "productID": "SKU-10482",
          "productName": "Trail Running Shoe",
          "manufacturer": "Northpeak"
        },
        "category": {
          "primaryCategory": "Footwear"
        },
        "price": {
          "basePrice": 89.99,
          "currency": "USD"
        },
        "quantity": 1
      }
    ]
  }
}

{
  "event": "add_to_wishlist",
  "ecommerce": {
    "currency": "USD",
    "value": 89.99,
    "items": [
      {
        "item_id": "SKU-10482",
        "item_name": "Trail Running Shoe",
        "item_brand": "Northpeak",
        "item_category": "Footwear",
        "item_list_name": "product_detail",
        "price": 89.99,
        "quantity": 1
      }
    ]
  }
}

{
  "event": "addToWishlist",
  "eventInfo": {
    "path": "component.wishlist-button",
    "linkName": "Add to Wishlist",
    "linkType": "o"
  },
  "commerce": {
    "currency": "USD",
    "value": 89.99,
    "listName": "product_detail",
    "products": [
      {
        "sku": "SKU-10482",
        "name": "Trail Running Shoe",
        "brand": "Northpeak",
        "category": "Footwear",
        "price": 89.99,
        "quantity": 1
      }
    ]
  }
}

{
  "eventType": "commerce.saveForLaters",
  "commerce": {
    "saveForLaters": {
      "value": 1
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-10482",
      "name": "Trail Running Shoe",
      "productCategories": [
        {
          "categoryName": "Footwear"
        }
      ],
      "priceTotal": 89.99,
      "quantity": 1,
      "currencyCode": "USD"
    }
  ],
  "_experience": {
    "analytics": {
      "customDimensions": {
        "eVars": {
          "eVar1": "product_detail"
        }
      }
    }
  },
  "web": {
    "webInteraction": {
      "name": "Add to Wishlist",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- Wishlist Additions: confirmed add_to_wishlist events per day (event grain) and saved items with a non-null item_id (item grain),
-- split by save context (product discovery vs cart save-for-later). Intent value uses event_value_in_usd and is never revenue.
-- Add-to-List Rate (discovery context only) = discovery wishlist additions / all view_item events for the same day.
-- Exclusions: internal traffic, staging / QA / preview / dev hostnames, double-fires (same user, same sorted item_id set, within 2 seconds).
-- Known bots are removed by GA4 before export, so no bot filter is needed here.
-- event_date is already in the reporting-property timezone; the last 3 days are flagged provisional.
WITH base AS (
  SELECT
    PARSE_DATE('%Y%m%d', e.event_date) AS event_date,
    e.event_name,
    e.user_pseudo_id,
    e.event_timestamp,
    e.event_value_in_usd,
    e.items,
    LOWER(COALESCE(e.device.web_info.hostname, '')) AS hostname,
    (SELECT ep.value.int_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'ga_session_id') AS ga_session_id,
    (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'traffic_type') AS traffic_type,
    (SELECT ep.value.string_value FROM UNNEST(e.event_params) AS ep WHERE ep.key = 'item_list_name') AS param_item_list_name,
    (SELECT MAX(it.item_list_name) FROM UNNEST(e.items) AS it) AS items_item_list_name,
    (SELECT COUNT(1) FROM UNNEST(e.items) AS it WHERE it.item_id IS NOT NULL) AS item_count,
    (SELECT STRING_AGG(it.item_id, '|' ORDER BY it.item_id) FROM UNNEST(e.items) AS it WHERE it.item_id IS NOT NULL) AS item_signature
  FROM `your-project.analytics_123456789.events_*` AS e
  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 e.event_name IN ('add_to_wishlist', 'view_item')
),
filtered AS (
  SELECT
    b.*,
    CASE
      WHEN LOWER(COALESCE(b.items_item_list_name, b.param_item_list_name, '')) IN ('cart', 'shopping_cart', 'mini_cart', 'cart_save_for_later')
        THEN 'cart_save_for_later'
      ELSE 'product_discovery'
    END AS save_context
  FROM base AS b
  WHERE COALESCE(b.traffic_type, '') <> 'internal'
    AND NOT REGEXP_CONTAINS(b.hostname, r'^(staging|stage|qa|uat|preview|dev)[.-]|localhost')
),
wishlist_sequenced AS (
  SELECT
    f.*,
    LAG(f.event_timestamp) OVER (
      PARTITION BY f.user_pseudo_id, COALESCE(f.item_signature, '(no_items)')
      ORDER BY f.event_timestamp
    ) AS prev_event_timestamp
  FROM filtered AS f
  WHERE f.event_name = 'add_to_wishlist'
),
wishlist_confirmed AS (
  SELECT *
  FROM wishlist_sequenced
  WHERE prev_event_timestamp IS NULL
     OR event_timestamp - prev_event_timestamp > 2000000
),
wishlist_daily AS (
  SELECT
    event_date,
    save_context,
    COUNT(*) AS wishlist_additions,
    SUM(item_count) AS wishlist_item_additions,
    COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST(ga_session_id AS STRING))) AS sessions_with_wishlist_addition,
    COUNTIF(item_count = 0) AS events_missing_items,
    ROUND(SUM(COALESCE(event_value_in_usd, 0)), 2) AS wishlist_intent_value_usd
  FROM wishlist_confirmed
  GROUP BY event_date, save_context
),
product_views_daily AS (
  SELECT
    event_date,
    COUNT(*) AS view_item_events
  FROM filtered
  WHERE event_name = 'view_item'
  GROUP BY event_date
)
SELECT
  w.event_date,
  w.save_context,
  w.wishlist_additions,
  w.wishlist_item_additions,
  w.sessions_with_wishlist_addition,
  w.wishlist_intent_value_usd,
  w.events_missing_items,
  SAFE_DIVIDE(w.events_missing_items, w.wishlist_additions) AS missing_items_share,
  v.view_item_events,
  CASE
    WHEN w.save_context = 'product_discovery' THEN SAFE_DIVIDE(w.wishlist_additions, v.view_item_events)
  END AS add_to_list_rate,
  w.event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AS is_provisional
FROM wishlist_daily AS w
LEFT JOIN product_views_daily AS v
  ON v.event_date = w.event_date
ORDER BY w.event_date DESC, w.save_context;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Wishlist Additions: confirmed commerce.saveForLaters ExperienceEvents per day (event grain) and saved productListItems with a SKU (item grain),
-- split by save context from eVar1 list name (product discovery vs cart save-for-later).
-- Add-to-List Rate (discovery context only) = discovery wishlist additions / all commerce.productViews events for the same day.
-- Intent value = sum of productListItems.priceTotal converted to USD at the event_date rate from fx_daily_rates_dataset (required input); never added to revenue.
-- Non-USD saves without a rate keep a NULL value (excluded from the sum) and are counted in fx_rate_missing_events.
-- Exclusions: bot / spider user agents, staging / QA / preview / dev hosts, double-fires (same ECID, same SKU set, within 2 seconds).
-- Dates use the reporting timezone; the last 3 days are flagged provisional.
WITH base AS (
  SELECT
    to_date(from_utc_timestamp(e.timestamp, 'America/New_York')) AS event_date,
    e.timestamp AS event_ts,
    e.eventType AS event_type,
    COALESCE(e.endUserIDs._experience.ecid.id, e.identityMap['ECID'][0].id) AS ecid,
    e._experience.analytics.session.num AS visit_num,
    lower(COALESCE(e._experience.analytics.customDimensions.eVars.eVar1, '')) AS list_name,
    CASE WHEN e.productListItems IS NULL THEN 0
         ELSE size(filter(e.productListItems, x -> x.SKU IS NOT NULL)) END AS item_count,
    CASE WHEN e.productListItems IS NULL THEN NULL
         ELSE NULLIF(array_join(array_sort(transform(filter(e.productListItems, x -> x.SKU IS NOT NULL), x -> x.SKU)), '|'), '') END AS item_signature,
    CASE WHEN e.productListItems IS NULL THEN CAST(0 AS DOUBLE)
         ELSE aggregate(e.productListItems, CAST(0 AS DOUBLE), (acc, x) -> acc + COALESCE(CAST(x.priceTotal AS DOUBLE), CAST(0 AS DOUBLE))) END AS intent_value_local,
    upper(element_at(e.productListItems, 1).currencyCode) AS currency_code
  FROM web_experience_events_dataset AS e
  WHERE e.timestamp >= date_sub(current_date(), 31)
    AND e.timestamp < current_date()
    AND (
          (e.eventType = 'commerce.saveForLaters' AND COALESCE(e.commerce.saveForLaters.value, 0) > 0)
       OR (e.eventType = 'commerce.productViews')
        )
    AND lower(COALESCE(e.web.webPageDetails.URL, '')) NOT RLIKE '^https?://(staging|stage|qa|uat|preview|dev)[.-]|localhost'
    AND lower(COALESCE(e.environment.browserDetails.userAgent, '')) NOT RLIKE 'bot|spider|crawler|headless'
),
classified AS (
  SELECT
    b.*,
    CASE
      WHEN b.list_name IN ('cart', 'shopping_cart', 'mini_cart', 'cart_save_for_later') THEN 'cart_save_for_later'
      ELSE 'product_discovery'
    END AS save_context
  FROM base AS b
  WHERE b.event_date >= date_sub(current_date(), 30)
),
wishlist_sequenced AS (
  SELECT
    c.*,
    LAG(c.event_ts) OVER (
      PARTITION BY c.ecid, COALESCE(c.item_signature, '(no_items)')
      ORDER BY c.event_ts
    ) AS prev_event_ts
  FROM classified AS c
  WHERE c.event_type = 'commerce.saveForLaters'
),
wishlist_confirmed AS (
  SELECT *
  FROM wishlist_sequenced
  WHERE prev_event_ts IS NULL
     OR CAST(event_ts AS DOUBLE) - CAST(prev_event_ts AS DOUBLE) > 2
),
wishlist_daily AS (
  SELECT
    w.event_date,
    w.save_context,
    COUNT(*) AS wishlist_additions,
    SUM(w.item_count) AS wishlist_item_additions,
    COUNT(DISTINCT concat(w.ecid, '-', CAST(w.visit_num AS STRING))) AS visits_with_wishlist_addition,
    SUM(CASE WHEN w.item_count = 0 THEN 1 ELSE 0 END) AS events_missing_items,
    ROUND(SUM(w.intent_value_local * CASE WHEN w.currency_code = 'USD' THEN CAST(1.0 AS DOUBLE) ELSE fx.rate_to_usd END), 2) AS wishlist_intent_value_usd,
    SUM(CASE WHEN COALESCE(w.currency_code, '') <> 'USD' AND fx.rate_to_usd IS NULL THEN 1 ELSE 0 END) AS fx_rate_missing_events
  FROM wishlist_confirmed AS w
  LEFT JOIN fx_daily_rates_dataset AS fx
    ON fx.rate_date = w.event_date
   AND upper(fx.currency_code) = w.currency_code
  GROUP BY w.event_date, w.save_context
),
product_views_daily AS (
  SELECT
    event_date,
    COUNT(*) AS product_view_events
  FROM classified
  WHERE event_type = 'commerce.productViews'
  GROUP BY event_date
)
SELECT
  w.event_date,
  w.save_context,
  w.wishlist_additions,
  w.wishlist_item_additions,
  w.visits_with_wishlist_addition,
  w.wishlist_intent_value_usd,
  w.fx_rate_missing_events,
  w.events_missing_items,
  CAST(w.events_missing_items AS DOUBLE) / NULLIF(w.wishlist_additions, 0) AS missing_items_share,
  v.product_view_events,
  CASE
    WHEN w.save_context = 'product_discovery'
      THEN CAST(w.wishlist_additions AS DOUBLE) / NULLIF(v.product_view_events, 0)
  END AS add_to_list_rate,
  w.event_date >= date_sub(current_date(), 3) AS is_provisional
FROM wishlist_daily AS w
LEFT JOIN product_views_daily AS v
  ON v.event_date = w.event_date
ORDER BY w.event_date DESC, w.save_context;