tylr · April 3, 2026 21:09
diff --git a/bid_optimizer_query.sql b/bid_optimizer_query.sql
 -- =============================================================================
 -- Profit-Maximizing Bid Optimizer — Google Ads + Taboola
 -- =============================================================================
 -- Uses log-log elasticity estimated from 90-day ArbDaily history,
 -- then grid-searches all valid cent-integer bids to find the bid
 -- that maximizes estimated daily profit per ad group.
 --
 -- Key columns in output:
 --   suggested_bid       — the profit-maximizing cent-integer bid
 --   implied_margin      — margin implied by that bid (1 - bid/vps)
 --   est_profit_delta    — estimated daily profit gain vs. current bid
 --   beta_is_measured    — TRUE = elasticity from data; FALSE = platform median fallback
 --   direction           — INCREASE / DECREASE / HOLD
 --
 -- Limitations:
 --   - β estimated from observed effective_cpc variation, which includes
 --     competitor bid changes (confounding). Treat as directional, not precise.
 --   - ad groups with constant bids (no CPC variance) use platform median β.
 --   - Run in shadow mode first; cap live bid changes at ±15%/day.
 -- =============================================================================

 WITH

 -- Step 1: Raw daily data (90-day window)
 raw AS (
  SELECT
    adset,
    platform,
    date,
    sessions,
    spend,
    totalValue,
    SAFE_DIVIDE(totalValue, sessions) AS vps,
    SAFE_DIVIDE(spend, sessions)      AS effective_cpc
  FROM `bdg-analytics.analytics.ArbDaily`
  WHERE
    platform IN ('Google', 'Taboola')
    AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sessions   >= 1
    AND spend      >  0
    AND totalValue >  0
 ),

 -- Step 2: Log-log elasticity (β) per ad group
 -- β = slope of log(sessions) ~ log(effective_cpc)
 -- Positive β = more bid → more sessions (normal behavior)
 elasticity AS (
  SELECT
    adset,
    platform,
    COUNT(*)      AS data_points,
    SUM(sessions) AS total_sessions,
    AVG(vps)      AS avg_vps,
    VAR_POP(LN(GREATEST(effective_cpc, 0.001))) AS cpc_variance,
    SAFE_DIVIDE(
      COVAR_POP(LN(GREATEST(effective_cpc, 0.001)), LN(GREATEST(sessions, 1))),
      VAR_POP(LN(GREATEST(effective_cpc, 0.001)))
    ) AS beta_raw
  FROM raw
  WHERE effective_cpc > 0
  GROUP BY adset, platform
  HAVING
    COUNT(*)      >= 5
    AND SUM(sessions) >= 20
 ),

 -- Step 3: Platform-level median β as fallback for flat-bid ad groups
 platform_median_beta AS (
  SELECT
    platform,
    PERCENTILE_CONT(GREATEST(0.1, LEAST(3.0, beta_raw)), 0.5)
      OVER (PARTITION BY platform) AS median_beta
  FROM elasticity
  WHERE beta_raw > 0
  QUALIFY ROW_NUMBER() OVER (PARTITION BY platform) = 1
 ),

 -- Step 4: Recent baseline — last 30 days for current bid + session rate
 baseline AS (
  SELECT
    adset,
    platform,
    AVG(effective_cpc) AS current_bid,
    AVG(sessions)      AS avg_daily_sessions,
    AVG(vps)           AS recent_vps
  FROM raw
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY adset, platform
  HAVING AVG(effective_cpc) > 0
 ),

 -- Step 5: Join elasticity + baseline, clamp β to [0.1, 3.0]
 -- Ad groups with no CPC variance fall back to platform median β
 ad_groups AS (
  SELECT
    e.adset,
    e.platform,
    COALESCE(b.recent_vps, e.avg_vps) AS vps,
    b.current_bid,
    b.avg_daily_sessions,
    CASE
      WHEN e.cpc_variance > 0 AND e.beta_raw > 0
        THEN GREATEST(0.1, LEAST(3.0, e.beta_raw))
      ELSE COALESCE(pmb.median_beta, 0.5)
    END AS beta,
    e.beta_raw,
    e.cpc_variance > 0 AND e.beta_raw > 0 AS beta_is_measured
  FROM elasticity e
  JOIN baseline b USING (adset, platform)
  LEFT JOIN platform_median_beta pmb USING (platform)
  WHERE
    b.current_bid > 0
    AND COALESCE(b.recent_vps, e.avg_vps) >= 0.02
 ),

 -- Step 6: Grid search — evaluate estimated profit at every valid cent-integer bid
 -- sessions(b) = sessions_baseline × (b / current_bid)^β  [power law model]
 -- profit(b)   = sessions(b) × (VPS - b)
 profit_grid AS (
  SELECT
    ag.adset,
    ag.platform,
    ag.vps,
    ag.current_bid,
    ag.avg_daily_sessions,
    ag.beta,
    ag.beta_raw,
    ag.beta_is_measured,
    bid_cents / 100.0 AS candidate_bid,
    ag.avg_daily_sessions
      * POWER(SAFE_DIVIDE(bid_cents / 100.0, ag.current_bid), ag.beta)
      AS est_sessions,
    ag.avg_daily_sessions
      * POWER(SAFE_DIVIDE(bid_cents / 100.0, ag.current_bid), ag.beta)
      * (ag.vps - bid_cents / 100.0)
      AS est_profit
  FROM ad_groups ag
  CROSS JOIN UNNEST(GENERATE_ARRAY(
    1,
    CAST(LEAST(
      CASE WHEN ag.platform = 'Taboola' THEN 40 ELSE 200 END,
      GREATEST(1, CAST(FLOOR(ag.vps * 90) AS INT64))
    ) AS INT64)
  )) AS bid_cents
 ),

 -- Step 7: Pick the max-profit cent-integer bid per ad group
 optimal AS (
  SELECT
    adset,
    platform,
    vps,
    current_bid,
    avg_daily_sessions,
    beta,
    beta_raw,
    beta_is_measured,
    candidate_bid                             AS optimal_bid,
    est_sessions                              AS projected_sessions,
    est_profit                                AS projected_profit,
    avg_daily_sessions * (vps - current_bid)  AS current_profit
  FROM profit_grid
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY adset, platform
    ORDER BY est_profit DESC
  ) = 1
 )

 -- Final output — sorted by biggest estimated profit gain first
 SELECT
  adset,
  platform,
  ROUND(vps,              4) AS vps,
  ROUND(current_bid,      2) AS current_bid,
  ROUND(optimal_bid,      2) AS suggested_bid,
  ROUND(1 - SAFE_DIVIDE(optimal_bid, vps), 3) AS implied_margin,
  ROUND(beta,             3) AS beta,
  beta_is_measured,
  ROUND(current_profit,   4) AS est_current_daily_profit,
  ROUND(projected_profit, 4) AS est_optimal_daily_profit,
  ROUND(projected_profit - current_profit, 4) AS est_profit_delta,
  ROUND(avg_daily_sessions, 1) AS avg_daily_sessions,
  ROUND(projected_sessions, 1) AS projected_sessions_at_optimal,
  ABS(optimal_bid - current_bid) >= 0.005 AS bid_change_recommended,
  CASE
    WHEN optimal_bid > current_bid THEN 'INCREASE'
    WHEN optimal_bid < current_bid THEN 'DECREASE'
    ELSE 'HOLD'
  END AS direction
 FROM optimal
 WHERE projected_profit > 0
 ORDER BY est_profit_delta DESC
	-- =============================================================================
	-- Profit-Maximizing Bid Optimizer — Google Ads + Taboola
	-- =============================================================================
	-- Uses log-log elasticity estimated from 90-day ArbDaily history,
	-- then grid-searches all valid cent-integer bids to find the bid
	-- that maximizes estimated daily profit per ad group.
	--
	-- Key columns in output:
	-- suggested_bid — the profit-maximizing cent-integer bid
	-- implied_margin — margin implied by that bid (1 - bid/vps)
	-- est_profit_delta — estimated daily profit gain vs. current bid
	-- beta_is_measured — TRUE = elasticity from data; FALSE = platform median fallback
	-- direction — INCREASE / DECREASE / HOLD
	--
	-- Limitations:
	-- - β estimated from observed effective_cpc variation, which includes
	-- competitor bid changes (confounding). Treat as directional, not precise.
	-- - ad groups with constant bids (no CPC variance) use platform median β.
	-- - Run in shadow mode first; cap live bid changes at ±15%/day.
	-- =============================================================================

	WITH

	-- Step 1: Raw daily data (90-day window)
	raw AS (
	SELECT
	adset,
	platform,
	date,
	sessions,
	spend,
	totalValue,
	SAFE_DIVIDE(totalValue, sessions) AS vps,
	SAFE_DIVIDE(spend, sessions) AS effective_cpc
	FROM `bdg-analytics.analytics.ArbDaily`
	WHERE
	platform IN ('Google', 'Taboola')
	AND date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
	AND sessions >= 1
	AND spend > 0
	AND totalValue > 0
	),

	-- Step 2: Log-log elasticity (β) per ad group
	-- β = slope of log(sessions) ~ log(effective_cpc)
	-- Positive β = more bid → more sessions (normal behavior)
	elasticity AS (
	SELECT
	adset,
	platform,
	COUNT(*) AS data_points,
	SUM(sessions) AS total_sessions,
	AVG(vps) AS avg_vps,
	VAR_POP(LN(GREATEST(effective_cpc, 0.001))) AS cpc_variance,
	SAFE_DIVIDE(
	COVAR_POP(LN(GREATEST(effective_cpc, 0.001)), LN(GREATEST(sessions, 1))),
	VAR_POP(LN(GREATEST(effective_cpc, 0.001)))
	) AS beta_raw
	FROM raw
	WHERE effective_cpc > 0
	GROUP BY adset, platform
	HAVING
	COUNT(*) >= 5
	AND SUM(sessions) >= 20
	),

	-- Step 3: Platform-level median β as fallback for flat-bid ad groups
	platform_median_beta AS (
	SELECT
	platform,
	PERCENTILE_CONT(GREATEST(0.1, LEAST(3.0, beta_raw)), 0.5)
	OVER (PARTITION BY platform) AS median_beta
	FROM elasticity
	WHERE beta_raw > 0
	QUALIFY ROW_NUMBER() OVER (PARTITION BY platform) = 1
	),

	-- Step 4: Recent baseline — last 30 days for current bid + session rate
	baseline AS (
	SELECT
	adset,
	platform,
	AVG(effective_cpc) AS current_bid,
	AVG(sessions) AS avg_daily_sessions,
	AVG(vps) AS recent_vps
	FROM raw
	WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
	GROUP BY adset, platform
	HAVING AVG(effective_cpc) > 0
	),

	-- Step 5: Join elasticity + baseline, clamp β to [0.1, 3.0]
	-- Ad groups with no CPC variance fall back to platform median β
	ad_groups AS (
	SELECT
	e.adset,
	e.platform,
	COALESCE(b.recent_vps, e.avg_vps) AS vps,
	b.current_bid,
	b.avg_daily_sessions,
	CASE
	WHEN e.cpc_variance > 0 AND e.beta_raw > 0
	THEN GREATEST(0.1, LEAST(3.0, e.beta_raw))
	ELSE COALESCE(pmb.median_beta, 0.5)
	END AS beta,
	e.beta_raw,
	e.cpc_variance > 0 AND e.beta_raw > 0 AS beta_is_measured
	FROM elasticity e
	JOIN baseline b USING (adset, platform)
	LEFT JOIN platform_median_beta pmb USING (platform)
	WHERE
	b.current_bid > 0
	AND COALESCE(b.recent_vps, e.avg_vps) >= 0.02
	),

	-- Step 6: Grid search — evaluate estimated profit at every valid cent-integer bid
	-- sessions(b) = sessions_baseline × (b / current_bid)^β [power law model]
	-- profit(b) = sessions(b) × (VPS - b)
	profit_grid AS (
	SELECT
	ag.adset,
	ag.platform,
	ag.vps,
	ag.current_bid,
	ag.avg_daily_sessions,
	ag.beta,
	ag.beta_raw,
	ag.beta_is_measured,
	bid_cents / 100.0 AS candidate_bid,
	ag.avg_daily_sessions
	* POWER(SAFE_DIVIDE(bid_cents / 100.0, ag.current_bid), ag.beta)
	AS est_sessions,
	ag.avg_daily_sessions
	* POWER(SAFE_DIVIDE(bid_cents / 100.0, ag.current_bid), ag.beta)
	* (ag.vps - bid_cents / 100.0)
	AS est_profit
	FROM ad_groups ag
	CROSS JOIN UNNEST(GENERATE_ARRAY(
	1,
	CAST(LEAST(
	CASE WHEN ag.platform = 'Taboola' THEN 40 ELSE 200 END,
	GREATEST(1, CAST(FLOOR(ag.vps * 90) AS INT64))
	) AS INT64)
	)) AS bid_cents
	),

	-- Step 7: Pick the max-profit cent-integer bid per ad group
	optimal AS (
	SELECT
	adset,
	platform,
	vps,
	current_bid,
	avg_daily_sessions,
	beta,
	beta_raw,
	beta_is_measured,
	candidate_bid AS optimal_bid,
	est_sessions AS projected_sessions,
	est_profit AS projected_profit,
	avg_daily_sessions * (vps - current_bid) AS current_profit
	FROM profit_grid
	QUALIFY ROW_NUMBER() OVER (
	PARTITION BY adset, platform
	ORDER BY est_profit DESC
	) = 1
	)

	-- Final output — sorted by biggest estimated profit gain first
	SELECT
	adset,
	platform,
	ROUND(vps, 4) AS vps,
	ROUND(current_bid, 2) AS current_bid,
	ROUND(optimal_bid, 2) AS suggested_bid,
	ROUND(1 - SAFE_DIVIDE(optimal_bid, vps), 3) AS implied_margin,
	ROUND(beta, 3) AS beta,
	beta_is_measured,
	ROUND(current_profit, 4) AS est_current_daily_profit,
	ROUND(projected_profit, 4) AS est_optimal_daily_profit,
	ROUND(projected_profit - current_profit, 4) AS est_profit_delta,
	ROUND(avg_daily_sessions, 1) AS avg_daily_sessions,
	ROUND(projected_sessions, 1) AS projected_sessions_at_optimal,
	ABS(optimal_bid - current_bid) >= 0.005 AS bid_change_recommended,
	CASE
	WHEN optimal_bid > current_bid THEN 'INCREASE'
	WHEN optimal_bid < current_bid THEN 'DECREASE'
	ELSE 'HOLD'
	END AS direction
	FROM optimal
	WHERE projected_profit > 0
	ORDER BY est_profit_delta DESC
No results found