AI.KEY_DRIVERS(
    { TABLE TABLE_NAME | (QUERY_STATEMENT) },
    metric_col => 'METRIC_COL',
    dimension_cols => DIMENSION_COLS,
    interest_label_col => 'INTEREST_LABEL_COL'
    [, min_apriori_support => MIN_APRIORI_SUPPORT]
    [, top_k => TOP_K]
    [, enable_pruning => ENABLE_PRUNING]
)

CREATE TEMP FUNCTION date_from() RETURNS STRING AS ('20260801');
CREATE TEMP FUNCTION date_to()   RETURNS STRING AS ('20260930');

-- =============================================================================
-- 一時関数（TEMP FUNCTION）の定義
-- 指定基準日（target_date）から過去 N 週分の同一曜日（当日含む）を構造体配列で返す
-- =============================================================================
CREATE TEMP FUNCTION get_past_same_weekdays(
  target_date DATE,
  weeks_back INT64
)
RETURNS ARRAY<STRUCT<past_date DATE, table_suffix STRING, weeks_ago INT64>>
AS (
  ARRAY(
    SELECT AS STRUCT
      past_date,
      FORMAT_DATE('%Y%m%d', past_date) AS table_suffix,
      weeks_ago
    FROM
      UNNEST(
        GENERATE_DATE_ARRAY(
          DATE_SUB(target_date, INTERVAL weeks_back * 7 DAY),
          target_date,
          INTERVAL 7 DAY
        )
      ) AS past_date
      WITH OFFSET AS pos,
      UNNEST([weeks_back - pos]) AS weeks_ago
    ORDER BY
      past_date DESC
  )
);

-- =============================================================================
-- メインクエリ：GA4データ集計と AI.KEY_DRIVERS による要因分析
-- =============================================================================

-- 1. 一時関数を呼び出して行展開し、比較対象日（9/8 当日 + 過去3週の火曜日）を準備
WITH 
  TargetDays AS (
    SELECT
      past_date,
      table_suffix,
      weeks_ago,
      (weeks_ago = 0) AS is_target_date,
      COUNTIF(weeks_ago > 0) OVER() AS ref_weeks_count
    FROM
      UNNEST(get_past_same_weekdays(DATE('2026-09-08'), 3))
  ),

  -- 2. GA4生ログから対象日のセッション数をディメンション別に集計
  DailyDimensionSessions AS (
    SELECT
      e.event_date,
      t.is_target_date,
      t.ref_weeks_count, 
      IFNULL(e.device.category, '(unknown)') AS device_category,
      IFNULL(e.session_traffic_source_last_click.cross_channel_campaign.source, '(direct)') AS source,
      IFNULL(e.session_traffic_source_last_click.cross_channel_campaign.medium, '(none)') AS medium,
      IFNULL(e.session_traffic_source_last_click.cross_channel_campaign.campaign_name, '(none)') AS campaign,
      IFNULL(e.geo.country, '(unknown)') AS country,
      COUNT(DISTINCT CONCAT(user_pseudo_id, '-', CAST((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS STRING))) AS _ss
    FROM
      `<project>.<dataset>.events_*` AS e
    INNER JOIN
      TargetDays AS t
      ON e._TABLE_SUFFIX  = t.table_suffix
    WHERE
      e.event_name = 'session_start'
      AND e._TABLE_SUFFIX  BETWEEN date_from() AND date_to()
    GROUP BY ALL
  ),

  -- 3. 「9月8日当日」と「過去3週分の1日あたり平均値」をユニオンオール（正規化）
  PreparedData AS (
    SELECT
      device_category,
      source,
      medium,
      campaign,
      country,
      _ss AS session_metric,
      TRUE AS is_target_date
    FROM
      DailyDimensionSessions
    WHERE
      is_target_date = TRUE
    UNION ALL
    SELECT
      device_category,
      source,
      medium,
      campaign,
      country,
      SUM(_ss) / ANY_VALUE(ref_weeks_count) AS session_metric,
      FALSE AS is_target_date
    FROM
      DailyDimensionSessions
    WHERE
      is_target_date = FALSE
      
    GROUP BY
      device_category,
      source,
      medium,
      campaign,
      country
  ),

  Calc AS (
    SELECT
      drivers,
      ROUND(metric_reference, 1) AS avg_past_sessions,
      ROUND(metric_interest, 1) AS target_day_sessions,
      ROUND(difference, 1) AS session_diff,
      ROUND(relative_difference, 3) AS rel_diff,
      ROUND(unexpected_difference, 1) AS unexpected_diff,
      apriori_support,
      contribution
    FROM
      AI.KEY_DRIVERS(
        TABLE PreparedData,
        metric_col => 'session_metric',
        interest_label_col => 'is_target_date',
        dimension_cols => ['device_category', 'source', 'medium', 'campaign', 'country'],
        top_k => 10
      )
    ORDER BY
      unexpected_difference DESC
  )

SELECT
  *
FROM
  Calc