Promotion Views

{
  "digitalData": {
    "page": {
      "category": {
        "pageType": "home"
      }
    },
    "event": [
      {
        "eventInfo": {
          "eventName": "view_promotion",
          "type": "promotion"
        },
        "attributes": {
          "trafficType": "external",
          "promotions": [
            {
              "promotionId": "PROMO-1",
              "promotionName": "promo",
              "creativeName": "banner",
              "creativeSlot": "slot-1"
            }
          ]
        }
      }
    ]
  }
}

{
  "event": "view_promotion",
  "promotion_id": "PROMO-1",
  "promotion_name": "promo",
  "creative_name": "banner",
  "creative_slot": "slot-1",
  "page_type": "home",
  "traffic_type": "external",
  "ecommerce": {
    "items": [
      {
        "item_id": "SKU-1",
        "item_name": "item",
        "promotion_id": "PROMO-1",
        "promotion_name": "promo"
      }
    ]
  }
}

{
  "event": "view_promotion",
  "eventInfo": {
    "path": "promotions"
  },
  "promotions": [
    {
      "promotionId": "PROMO-1",
      "promotionName": "promo",
      "creativeName": "banner",
      "creativeSlot": "slot-1"
    }
  ],
  "page": {
    "pageType": "home"
  },
  "trafficType": "external"
}

{
  "eventType": "view_promotion",
  "timestamp": "2026-01-01T00:00:00Z",
  "identityMap": {
    "ECID": [
      {
        "id": "ecid-1",
        "authenticatedState": "ambiguous",
        "primary": true
      }
    ]
  },
  "_tenantId": {
    "trafficType": "external",
    "promotions": [
      {
        "promotionId": "PROMO-1",
        "promotionName": "promo",
        "creativeName": "banner",
        "creativeSlot": "slot-1"
      }
    ]
  }
}

-- ==========================================
-- 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);

WITH events AS (
  -- Page views and promotion views in the period, internal traffic excluded, event-level promotion fields pivoted
  SELECT
    user_pseudo_id,
    event_timestamp,
    event_name,
    items,
    (SELECT NULLIF(ep.value.string_value, '') FROM UNNEST(event_params) AS ep WHERE ep.key = 'promotion_id') AS promotion_id,
    (SELECT NULLIF(ep.value.string_value, '') FROM UNNEST(event_params) AS ep WHERE ep.key = 'promotion_name') AS promotion_name
  FROM `your-project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name IN ('page_view', 'view_promotion')
    AND COALESCE((SELECT ep.value.string_value FROM UNNEST(event_params) AS ep WHERE ep.key = 'traffic_type'), '') != 'internal'
),
anchored AS (
  -- Tie each promotion view to the user's most recent page view, so re-renders on the same page share one page view key
  SELECT
    *,
    LAST_VALUE(IF(event_name = 'page_view', event_timestamp, NULL) IGNORE NULLS)
      OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS page_view_ts
  FROM events
),
promotions AS (
  -- One row per promotion listed on the event: promotion_id (item, then event level), else promotion_name, else (not set)
  SELECT
    a.user_pseudo_id,
    a.page_view_ts,
    COALESCE(NULLIF(i.promotion_id, ''), a.promotion_id, NULLIF(i.promotion_name, ''), a.promotion_name, '(not set)') AS promotion_key
  FROM anchored AS a
  LEFT JOIN UNNEST(a.items) AS i
  WHERE a.event_name = 'view_promotion'
)
-- Promotion Views: each distinct promotion counts once per page view
SELECT
  COUNT(DISTINCT CONCAT(user_pseudo_id, '|', COALESCE(CAST(page_view_ts AS STRING), 'none'), '|', promotion_key)) AS promotion_views
FROM promotions;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
WITH events AS (
  -- Page views and promotion views in the last 90 complete days, internal traffic excluded
  SELECT
    identityMap['ECID'][0].id AS ecid,
    timestamp,
    eventType,
    _tenantId.promotions AS promotions
  FROM aep_web_events_dataset
  WHERE timestamp >= date_sub(current_date(), 90)
    AND timestamp < current_date()
    AND eventType IN ('web.webpagedetails.pageViews', 'view_promotion')
    AND COALESCE(_tenantId.trafficType, '') != 'internal'
),
anchored AS (
  -- Tie each promotion view to the visitor's most recent page view, so re-renders on the same page share one page view key
  SELECT
    *,
    last(CASE WHEN eventType = 'web.webpagedetails.pageViews' THEN timestamp END, true)
      OVER (PARTITION BY ecid ORDER BY timestamp ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS page_view_ts
  FROM events
),
promotions AS (
  -- One row per promotion listed on the event: promotionId, else promotionName, else (not set)
  SELECT
    a.ecid,
    a.page_view_ts,
    COALESCE(NULLIF(p.promotionId, ''), NULLIF(p.promotionName, ''), '(not set)') AS promotion_key
  FROM anchored AS a
  LATERAL VIEW OUTER explode(a.promotions) AS p
  WHERE a.eventType = 'view_promotion'
)
-- Promotion Views: each distinct promotion counts once per page view
SELECT
  COUNT(DISTINCT CONCAT(ecid, '|', COALESCE(CAST(page_view_ts AS STRING), 'none'), '|', promotion_key)) AS promotion_views
FROM promotions;