Ad Content

{
  "campaign": {
    "campaignID": "spring_sale_2026",
    "campaignName": "Spring Sale 2026",
    "mediaID": "banner_300x250_blue_v2",
    "mediaName": "banner_300x250_blue_v2"
  }
}

{
  "event": "session_start",
  "source": "google",
  "medium": "cpc",
  "campaign": "spring_sale_2026",
  "content": "banner_300x250_blue_v2"
}

{
  "event": "sessionStart",
  "campaign": {
    "source": "google",
    "medium": "cpc",
    "name": "spring_sale_2026",
    "content": "banner_300x250_blue_v2"
  }
}

{
  "marketing": {
    "campaignName": "spring_sale_2026",
    "trackingCode": "banner_300x250_blue_v2"
  }
}

-- ==========================================
-- 1. GOOGLE BIGQUERY SQL
-- ==========================================
-- ESP subscriber IDs follow no common pattern and cannot be detected; only email addresses (plain or %40-encoded) are rejected
DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- Session manual ad content (GA4 last non-direct click), lower-cased and trimmed, on every event of the period
WITH session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    NULLIF(LOWER(TRIM(session_traffic_source_last_click.manual_campaign.manual_content)), '') AS ad_content
  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)
),
-- One row per session
sessions AS (
  SELECT user_pseudo_id, ga_session_id, MAX(ad_content) AS ad_content
  FROM session_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
)
-- Blank, missing or email-bearing values report as (not set); sessions per ad content
SELECT
  CASE
    WHEN ad_content IS NULL
      OR REGEXP_CONTAINS(ad_content, r'[a-z0-9._%+-]+(@|%40)[a-z0-9-]+\.[a-z0-9.-]+') THEN '(not set)'
    ELSE ad_content
  END AS ad_content,
  COUNT(*) AS sessions
FROM sessions
GROUP BY 1
ORDER BY sessions DESC;

-- ==========================================
-- 2. ADOBE AEP QUERY SERVICE SQL
-- ==========================================
-- ESP subscriber IDs follow no common pattern and cannot be detected; only email addresses (plain or %40-encoded) are rejected
WITH params AS (
  -- Reporting period: last 90 days up to yesterday
  SELECT DATE_SUB(CURRENT_DATE, 90) AS start_date, DATE_SUB(CURRENT_DATE, 1) AS end_date
),
-- Events of the period with lower-cased, trimmed utm_content (marketing.trackingCode) and a 30-minute-inactivity session per visitor
events AS (
  SELECT
    endUserIDs._experience.ecid.id AS ecid,
    timestamp,
    NULLIF(LOWER(TRIM(marketing.trackingCode)), '') AS ad_content,
    NULLIF(web.webReferrer.URL, '') AS referrer,
    SESS_TIMEOUT(timestamp, 60 * 30) OVER (
      PARTITION BY endUserIDs._experience.ecid.id ORDER BY timestamp
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session
  FROM aep_web_events_dataset
  CROSS JOIN params
  WHERE timestamp >= CAST(params.start_date AS TIMESTAMP)
    AND timestamp < CAST(DATE_ADD(params.end_date, 1) AS TIMESTAMP)
),
-- One row per session: its first ad content and entry referrer; no tracking code and no referrer = direct
sessions AS (
  SELECT
    ecid,
    MIN(timestamp) AS session_start,
    MIN(IF(ad_content IS NOT NULL, NAMED_STRUCT('ts', timestamp, 'content', ad_content), NULL)).content AS entry_content,
    MIN(NAMED_STRUCT('ts', timestamp, 'referrer', referrer)).referrer AS entry_referrer
  FROM events
  GROUP BY ecid, session.num
),
-- Last non-direct click: a direct session takes the ad content of the visitor's latest earlier non-direct session
attributed AS (
  SELECT
    CASE
      WHEN entry_content IS NULL AND entry_referrer IS NULL THEN NULLIF(LAST(
        IF(entry_content IS NULL AND entry_referrer IS NULL, NULL, NAMED_STRUCT('content', COALESCE(entry_content, ''))), TRUE)
        OVER (PARTITION BY ecid ORDER BY session_start ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING).content, '')
      ELSE entry_content
    END AS ad_content
  FROM sessions
)
-- Blank, missing or email-bearing values report as (not set); sessions per ad content
SELECT
  CASE
    WHEN ad_content IS NULL
      OR ad_content RLIKE '[a-z0-9._%+-]+(@|%40)[a-z0-9-]+\\.[a-z0-9.-]+' THEN '(not set)'
    ELSE ad_content
  END AS ad_content,
  COUNT(*) AS sessions
FROM attributed
GROUP BY 1
ORDER BY sessions DESC;