WITH CpcEvents AS (
  SELECT
    DATE(TIMESTAMP_MICROS(event_timestamp)) AS event_date,
    geo.country AS country,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign') AS campaign
  FROM
    `dein-bq-projekt-und-dataset.events_*`
  WHERE
    -- Filtere auf session_start Events, da diese die eingehenden Klicks repräsentieren
    event_name = 'session_start'
    -- Zeitraum anpassen nach Bedarf
    AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    -- Filtere auf CPC Medium
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') = 'cpc'
    -- Filtere DACH-Länder aus (DE = Deutschland, AT = Österreich, CH = Schweiz)
    AND geo.country NOT IN ('Germany', 'Austria', 'Switzerland')
)

-- Summiere Klicks nach Land
SELECT
  country,
  COUNT(*) AS clicks,
  COUNT(DISTINCT event_date) AS days_with_clicks,
  MIN(event_date) AS first_click_date,
  MAX(event_date) AS last_click_date,
  ARRAY_AGG(DISTINCT source ORDER BY source LIMIT 20) AS sources,
  ARRAY_AGG(DISTINCT campaign ORDER BY campaign LIMIT 20) AS campaigns
FROM
  CpcEvents
GROUP BY
  country
ORDER BY
  clicks DESC;