Promotion Clicks

{
  "digitalData": {
    "event": [
      {
        "eventInfo": {
          "eventName": "select_promotion",
          "eventAction": "click"
        },
        "attributes": {
          "promotion_id": "PROMO-1",
          "promotion_name": "promo",
          "creative_name": "banner",
          "creative_slot": "slot-1",
          "traffic_type": "external"
        }
      }
    ],
    "page": {
      "category": {
        "pageType": "home"
      }
    }
  }
}

{
  "event": "select_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",
        "creative_slot": "slot-1"
      }
    ]
  }
}

{
  "event": "select_promotion",
  "eventInfo": {
    "promotion_id": "PROMO-1",
    "promotion_name": "promo",
    "creative_name": "banner",
    "creative_slot": "slot-1",
    "page_type": "home",
    "traffic_type": "external"
  },
  "productListItems": [
    {
      "SKU": "SKU-1",
      "promotion_id": "PROMO-1",
      "promotion_name": "promo",
      "creative_slot": "slot-1"
    }
  ]
}

{
  "eventType": "web.webinteraction.linkClicks",
  "web": {
    "webInteraction": {
      "name": "select_promotion",
      "type": "other",
      "linkClicks": {
        "value": 1
      }
    }
  },
  "productListItems": [
    {
      "SKU": "SKU-1",
      "_tenantId": {
        "promotionID": "PROMO-1",
        "promotionName": "promo",
        "creativeSlot": "slot-1"
      }
    }
  ],
  "_tenantId": {
    "trafficType": "external",
    "pageType": "home",
    "promotion": {
      "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);

-- One row per select_promotion event (not per item); promotion fields resolve item-level first, then event-level, else (not set)
WITH clicks AS (
  SELECT
    user_pseudo_id,
    event_timestamp,
    COALESCE(
      (SELECT MAX(i.promotion_id) FROM UNNEST(items) AS i),
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'promotion_id'),
      '(not set)') AS promotion_id,
    COALESCE(
      (SELECT MAX(i.promotion_name) FROM UNNEST(items) AS i),
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'promotion_name'),
      '(not set)') AS promotion_name,
    COALESCE(
      (SELECT MAX(i.creative_slot) FROM UNNEST(items) AS i),
      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'creative_slot'),
      '(not set)') AS creative_slot,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'traffic_type') AS traffic_type
  FROM `your-project.analytics_123456789.events_*`
  -- event_date suffix is already in the property time zone
  WHERE _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', start_date) AND FORMAT_DATE('%Y%m%d', end_date)
    AND event_name = 'select_promotion'
),

-- Drop internal and bot traffic, and duplicate hits with the same user, timestamp and promotion_id
deduped AS (
  SELECT *
  FROM clicks
  WHERE IFNULL(traffic_type, '') NOT IN ('internal', 'bot')
  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_pseudo_id, event_timestamp, promotion_id ORDER BY event_timestamp) = 1
)

-- Promotion Clicks: every remaining click counts, repeat clicks included
SELECT
  promotion_id,
  promotion_name,
  creative_slot,
  COUNT(*) AS promotion_clicks
FROM deduped
GROUP BY promotion_id, promotion_name, creative_slot
ORDER BY promotion_clicks DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- Relative reporting window: last 90 complete days
WITH params AS (
  SELECT date_sub(current_date(), 90) AS start_date,
         date_sub(current_date(), 1) AS end_date
),

-- One row per select_promotion event (not per item); promotion fields resolve productListItems first, then event-level, else (not set)
clicks AS (
  SELECT
    identityMap['ECID'][0].id AS ecid,
    timestamp,
    COALESCE(
      array_max(transform(productListItems, i -> i._tenantId.promotionID)),
      _tenantId.promotion.promotionID,
      '(not set)') AS promotion_id,
    COALESCE(
      array_max(transform(productListItems, i -> i._tenantId.promotionName)),
      _tenantId.promotion.promotionName,
      '(not set)') AS promotion_name,
    COALESCE(
      array_max(transform(productListItems, i -> i._tenantId.creativeSlot)),
      _tenantId.promotion.creativeSlot,
      '(not set)') AS creative_slot,
    _tenantId.trafficType AS traffic_type
  FROM aep_web_events, params
  WHERE web.webInteraction.name = 'select_promotion'
    -- dated in the property time zone (set to the reporting time zone)
    AND to_date(from_utc_timestamp(timestamp, 'America/New_York')) BETWEEN params.start_date AND params.end_date
),

-- Drop internal and bot traffic, and duplicate hits with the same user, timestamp and promotion_id
deduped AS (
  SELECT *
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY ecid, timestamp, promotion_id ORDER BY timestamp) AS rn
    FROM clicks
    WHERE COALESCE(traffic_type, '') NOT IN ('internal', 'bot')
  ) t
  WHERE rn = 1
)

-- Promotion Clicks: every remaining click counts, repeat clicks included
SELECT
  promotion_id,
  promotion_name,
  creative_slot,
  COUNT(*) AS promotion_clicks
FROM deduped
GROUP BY promotion_id, promotion_name, creative_slot
ORDER BY promotion_clicks DESC;