Budget Pacing Workflow

The budget pacing system compares quarterly budgets (maintained in Google Sheets by the customer) against actual ad spend from the unified performance view. It calculates pacing metrics to help the paid media team track over/underspend in real time.

Architecture

Google Sheet (6 tabs)
  │  Platform_Budget    (wide: platform × quarters)
  │  Region_Budget      (wide: region × quarters)
  │  Quarter_Config     (quarter start/end dates)
  │  Product_Mapping    (reference for Phase 2)
  │  2026-Q1_Planned    (granular media plan: platform × region × product)
  │  2026-Q2_Planned    (granular media plan: platform × region × product)
  │
  ├── BigQuery External Tables (federated query)
  │     suse_budget_platform_staging_v2
  │     suse_budget_region_staging_v2
  │     suse_budget_config_staging_v2
  │     suse_budget_planned_q1_staging_v2
  │     suse_budget_planned_q2_staging_v2
  │
  ├── Daily Scheduled Query (Budget Refresh, 5 sections)
  │     1-3: UNPIVOT wide → long for platform/region/config
  │     4: Planned quarterly (multi-region split + CONCAT " Ads")
  │     5: Planned monthly (UNPIVOT months + multi-region split)
  │
  ├── Final Tables (long format)
  │     suse_budget_platform_final_v2   (Platform, Quarter, QuarterBudget)
  │     suse_budget_region_final_v2     (Region, Quarter, QuarterBudget)
  │     suse_budget_config_v2           (Quarter, StartDate, EndDate)
  │     suse_budget_planned_final_v2    (Platform, Region, Product, Quarter, PlannedBudget)
  │     suse_budget_planned_monthly_v2  (Platform, Region, Product, Month, PlannedBudget)
  │
  └── Pacing Views (JOIN budget + actual spend)
        budget_pacing_platform_v2   (+PlannedBudget, UnplannedMargin, PctPlanned)
        budget_pacing_region_v2     (+PlannedBudget, UnplannedMargin, PctPlanned)
        budget_pacing_summary_v2    (UNION ALL → Looker Studio)
        budget_pacing_monthly_v2    (monthly planned vs actual → Looker Studio)

Google Sheet

Sheet: Budget Tracking for BigQuery

The customer maintains budget data in a Google Sheet with 4 tabs:

Tab: Platform_Budget

Platform	2026-Q1	2026-Q2	2026-Q3	2026-Q4
6sense Ads	50000	50000	...	...
Google Ads	350000	500000	...	...

Rules:

• Plain numbers, no $ signs or commas
• Platform names must match unified view exactly (with " Ads" suffix)
• Data validation dropdowns enforce correct platform names
• TOTAL row at bottom is for reference only (filtered out by BigQuery)

Tab: Region_Budget

Same format with regions: EMEA, NA, APAC, LATAM, Global, EMEA APAC LATAM.

Tab: Quarter_Config

Quarter	StartDate	EndDate
2026-Q1	2025-11-01	2026-01-31
2026-Q2	2026-02-01	2026-04-30

SUSE fiscal year starts November 1. Add new rows for future quarters.

Tab: Product_Mapping

Reference table mapping ProductLine → ProductGroup. Used in Phase 2 for product-level pacing.

Daily Refresh

The Budget Refresh scheduled query runs daily and:

1. Reads from external tables (Google Sheets)
2. UNPIVOTs wide format (Platform, Q1, Q2...) into long format (Platform, Quarter, Budget)
3. Filters out TOTAL rows, note rows, and null values
4. Full-replaces the final tables (DELETE WHERE TRUE + INSERT)
5. Logs results to suse_budget_refresh_log_v2

Each section is wrapped in BEGIN...EXCEPTION...END for fault isolation.

Adding New Quarters

When a new fiscal year starts:

1. Add new quarter columns to the sheet (e.g., "2027-Q1")
2. Add corresponding rows to Quarter_Config tab
3. Update the refresh query's UNPIVOT list to include the new column names
4. The pacing views automatically pick up new quarters via the config table JOIN

Planned Budget (Media Plan)

The planned budget feature adds granular media plan data at the Platform × Region × Product level. This enables comparison of what was budgeted (top-down) vs what was planned (bottom-up media plan).

Multi-Region Splitting

Sheet rows may have multi-region values like "NA + EMEA" or "EMEA + APAC + LATAM". The refresh logic splits these evenly:

Sheet Region	Split Into	Budget Each
"NA + EMEA"	NA, EMEA	50% each
"EMEA + APAC + LATAM"	EMEA, APAC, LATAM	33.3% each
"APAC"	APAC	100%

Monthly Pacing View

The budget_pacing_monthly_v2 view joins monthly planned budgets with actual spend from unified_performance_v2, grouped by month. It provides Platform and Region scopes (use PacingScope to filter).

Pacing Metrics

Metric	Formula	Interpretation
PctBudgetConsumed	QTD_Spend / QuarterBudget	How much of the budget is used
PctTimeElapsed	DaysElapsed / DaysInQuarter	How much time has passed
PacingRatio	PctBudgetConsumed / PctTimeElapsed	1.0 = on track
ProjectedSpend	(QTD_Spend / DaysElapsed) × DaysInQuarter	Linear extrapolation
ProjectedVariance	ProjectedSpend - QuarterBudget	Negative = under budget

Verification

-- Check budget refresh log
SELECT * FROM `customer_ads_suse.suse_budget_refresh_log_v2`
ORDER BY run_timestamp DESC LIMIT 10;

-- Check platform pacing
SELECT Platform, Quarter, ROUND(PacingRatio, 2) AS Pacing
FROM `customer_ads_suse.budget_pacing_platform_v2`
WHERE Quarter = '2026-Q2';

Future (Phase 2)

• Product Line pacing: Budget split % per product × quarter × region
• Campaign Objective pacing: Brand Awareness / Lead Gen / Funnel Accel / ABM splits
• get_campaign_objective() UDF: Auto-tag campaigns with their objective