Skip to content

Instantly share code, notes, and snippets.

@tylr

tylr/bid_optimizer_query.sql

Created April 3, 2026 21:09
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tylr/b39f03ff91a5e4a5e6bbd0ea47ec029d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tylr/b39f03ff91a5e4a5e6bbd0ea47ec029d to your computer and use it in GitHub Desktop.
Download ZIP
Profit-Maximizing Bid Optimizer — Google Ads + Taboola (BDG ArbDaily)
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment