training.sql

-- =====================================================================
-- Purchase propensity training
-- Schedule: every Sunday 00:01
-- Inputs:   GA4 export dataset
-- Output:   ga4_ml.purchase_propensity (BQML BOOSTED_TREE_CLASSIFIER)
--
-- Target: Predict 'purchase' in the next 30 days.
--
-- Features: Full standard GA4 ecommerce funnel including begin_checkout
--           and add_payment_info. These are NOT data leakage:
--             - Feature window: 210 to 31 days ago (180 days)
--             - Label window:   30 days ago to yesterday
--           The windows are non-overlapping, so an event in the feature
--           window cannot leak the label. These are the strongest legitimate
--           predictors in any ecommerce propensity model.
--
-- Timezone: Europe/Stockholm — must match GA4 property reporting timezone
--           and the GA4_TIMEZONE env var in the Cloud Function.
--
-- To deploy in a new project, replace:
--   GCP_PROJECT        → your GCP project ID
--   GA4_EXPORT_DATASET → e.g. analytics_123456789
--   ML_DATASET         → e.g. ga4_ml
-- =====================================================================

CREATE OR REPLACE MODEL `GCP_PROJECT.ML_DATASET.purchase_propensity`
OPTIONS(
  model_type               = 'BOOSTED_TREE_CLASSIFIER',
  input_label_cols         = ['will_convert'],
  auto_class_weights       = TRUE,
  enable_global_explain    = TRUE,
  -- HP tuning requires a non-empty eval set; NO_SPLIT would error.
  data_split_method        = 'RANDOM',
  data_split_eval_fraction = 0.2,
  num_trials               = 20,
  max_parallel_trials      = 4,
  hparam_tuning_objectives = ['ROC_AUC'],
  learn_rate               = HPARAM_RANGE(0.05, 0.3),
  max_tree_depth           = HPARAM_RANGE(3, 8),
  subsample                = HPARAM_RANGE(0.6, 1.0),
  l2_reg                   = HPARAM_RANGE(0.0, 1.0),
  max_iterations           = 100,
  early_stop               = TRUE,
  min_rel_progress         = 0.005
) AS
WITH
  windows AS (
    SELECT
      FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Europe/Stockholm'), INTERVAL 210 DAY)) AS feat_start,
      FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Europe/Stockholm'), INTERVAL  31 DAY)) AS feat_end,
      FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Europe/Stockholm'), INTERVAL  30 DAY)) AS lbl_start,
      FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE('Europe/Stockholm'), INTERVAL   1 DAY)) AS lbl_end,
      DATE_SUB(CURRENT_DATE('Europe/Stockholm'), INTERVAL 38 DAY) AS recent_7d_start,
      DATE_SUB(CURRENT_DATE('Europe/Stockholm'), INTERVAL 61 DAY) AS recent_30d_start
  ),

  -- Exclude users who already purchased in the feature window. Training on
  -- them would teach "buyers buy again" and flood `high` with current customers.
  recent_buyers AS (
    SELECT DISTINCT user_pseudo_id
    FROM `GCP_PROJECT.GA4_EXPORT_DATASET.events_*`, windows
    WHERE _TABLE_SUFFIX BETWEEN feat_start AND feat_end
      AND event_name = 'purchase'
  ),

  -- Per-session aggregates enable avg/max session features.
  -- (user_pseudo_id, ga_session_id) is the unique session key; ga_session_id
  -- alone is a session-start unix timestamp and can collide across users.
  sessions AS (
    SELECT
      user_pseudo_id,
      (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') AS session_id,
      MAX(IFNULL((SELECT value.int_value FROM UNNEST(event_params) WHERE key='session_engaged'),0)) AS engaged,
      SUM(IFNULL((SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec'),0))/1000 AS session_engagement_seconds,
      COUNT(*) AS events_in_session
    FROM `GCP_PROJECT.GA4_EXPORT_DATASET.events_*`, windows
    WHERE _TABLE_SUFFIX BETWEEN feat_start AND feat_end
      AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id') IS NOT NULL
    GROUP BY user_pseudo_id, session_id
  ),

  user_sessions AS (
    SELECT
      user_pseudo_id,
      COUNT(*) AS sessions_total,
      COUNTIF(engaged = 1) AS engaged_sessions,
      SAFE_DIVIDE(COUNTIF(engaged = 1), COUNT(*)) AS engaged_session_rate,
      AVG(events_in_session) AS avg_session_depth,
      AVG(session_engagement_seconds) AS avg_session_engagement_seconds,
      MAX(session_engagement_seconds) AS max_session_engagement_seconds
    FROM sessions
    GROUP BY user_pseudo_id
  ),

  -- Per-user event aggregates including recency-windowed engagement
  user_events AS (
    SELECT
      user_pseudo_id,

      -- Engagement: total, last 30d, last 7d
      SUM(IFNULL((SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec'),0))/1000 AS total_engagement_seconds,
      SUM(IF(DATE(TIMESTAMP_MICROS(event_timestamp), 'Europe/Stockholm') >= (SELECT recent_7d_start FROM windows),
             IFNULL((SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec'),0),
             0))/1000 AS engagement_last_7d_seconds,
      SUM(IF(DATE(TIMESTAMP_MICROS(event_timestamp), 'Europe/Stockholm') >= (SELECT recent_30d_start FROM windows),
             IFNULL((SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec'),0),
             0))/1000 AS engagement_last_30d_seconds,

      -- Categoricals with NULL sentinel for stable encoding
      COALESCE(ANY_VALUE(device.category), '(none)')         AS device_category,
      COALESCE(ANY_VALUE(device.operating_system), '(none)') AS os,
      COALESCE(ANY_VALUE(geo.country), '(none)')             AS country,
      COALESCE(ANY_VALUE(traffic_source.medium), '(none)')   AS traffic_medium,
      COALESCE(ANY_VALUE(traffic_source.source), '(none)')   AS traffic_source,

      -- Full standard GA4 ecommerce funnel.
      -- begin_checkout and add_payment_info are NOT leakage — they're in the
      -- 180-day feature window, the label is in a separate 30-day future window.
      -- These are the strongest legitimate predictors in ecommerce models.
      COUNTIF(event_name='view_item_list')    AS view_item_list_count,
      COUNTIF(event_name='select_item')       AS select_item_count,
      COUNTIF(event_name='view_item')         AS view_item_count,
      COUNTIF(event_name='add_to_wishlist')   AS add_to_wishlist_count,
      COUNTIF(event_name='add_to_cart')       AS add_to_cart_count,
      COUNTIF(event_name='view_cart')         AS view_cart_count,
      COUNTIF(event_name='remove_from_cart')  AS remove_from_cart_count,
      COUNTIF(event_name='begin_checkout')    AS begin_checkout_count,
      COUNTIF(event_name='add_shipping_info') AS add_shipping_info_count,
      COUNTIF(event_name='add_payment_info')  AS add_payment_info_count,

      -- Page-level activity
      COUNTIF(event_name='page_view') AS page_view_count,
      COUNT(*) AS total_events,

      -- Recency
      DATE_DIFF(CURRENT_DATE('Europe/Stockholm'),
                MAX(DATE(TIMESTAMP_MICROS(event_timestamp), 'Europe/Stockholm')),
                DAY) AS days_since_last_visit

    FROM `GCP_PROJECT.GA4_EXPORT_DATASET.events_*`, windows
    WHERE _TABLE_SUFFIX BETWEEN feat_start AND feat_end
      AND user_pseudo_id NOT IN (SELECT user_pseudo_id FROM recent_buyers)
    GROUP BY user_pseudo_id
  ),

  -- Funnel ratios — usually the strongest single predictors because they
  -- normalize for activity volume and capture pure intent.
  feats AS (
    SELECT
      ue.* EXCEPT(user_pseudo_id),
      us.sessions_total,
      us.engaged_sessions,
      us.engaged_session_rate,
      us.avg_session_depth,
      us.avg_session_engagement_seconds,
      us.max_session_engagement_seconds,
      SAFE_DIVIDE(ue.view_item_count,         NULLIF(ue.view_item_list_count, 0))  AS list_to_view_ratio,
      SAFE_DIVIDE(ue.add_to_cart_count,       NULLIF(ue.view_item_count, 0))       AS view_to_cart_ratio,
      SAFE_DIVIDE(ue.begin_checkout_count,    NULLIF(ue.add_to_cart_count, 0))     AS cart_to_checkout_ratio,
      SAFE_DIVIDE(ue.add_payment_info_count,  NULLIF(ue.begin_checkout_count, 0))  AS checkout_to_payment_ratio,
      SAFE_DIVIDE(ue.remove_from_cart_count,  NULLIF(ue.add_to_cart_count, 0))     AS cart_abandon_ratio,
      ue.user_pseudo_id
    FROM user_events ue
    LEFT JOIN user_sessions us USING(user_pseudo_id)
  ),

  -- Label: strictly 'purchase' in the next 30 days.
  labels AS (
    SELECT
      user_pseudo_id,
      MAX(IF(event_name = 'purchase', 1, 0)) AS will_convert
    FROM `GCP_PROJECT.GA4_EXPORT_DATASET.events_*`, windows
    WHERE _TABLE_SUFFIX BETWEEN lbl_start AND lbl_end
    GROUP BY user_pseudo_id
  )

SELECT f.* EXCEPT(user_pseudo_id), IFNULL(l.will_convert, 0) AS will_convert
FROM feats f
LEFT JOIN labels l USING(user_pseudo_id);