BSP Calculator

Bid Strategy Portfolio - ROAS Adjustment Workflow

1
API
Google Ads API
Query bid strategy performance
Last 30 days metrics
|
2
SHEET
BSP Calculator Sheet
Write to rows 13-230
~218 bid strategies
Cost, Conv, ROAS per strategy
|
3
L1-L4
ROAS Ladder Calculation
L1: Original ROAS
L2: Adjusted ROAS
L3: Achieved % Change
L4: Desired % Change
|
4
?
Validate: L3 >= L4?
Achieved change must meet or exceed desired change
|
OK
Valid: Duplicate Sheet
Create snapshot
"[PULL|PUSH] MM/DD" naming
+/-10
Invalid: Adjust ROAS
Increase/decrease L2 by 10
Recalculate L3
Google Ads API
Google Sheet
Calculation
Validation
User Action

The ROAS Ladder (L1 - L4)

Level 1
L1: Original ROAS
Current target in Google Ads
Cell B4
Level 2
L2: Adjusted ROAS
New target to apply
Cell B5 (editable)
Level 3
L3: Achieved %
Actual change from L1 to L2
(L2 - L1) / L1 * 100
Level 4
L4: Desired %
Target change from detector
Cell B8 (from AI rec)

Validation Logic

L3 >= L4 ?
-->
YES
Ready to duplicate
|
NO
Adjust L2 by +/-10

Data Flow Details

L30D Import

  • Date range: Last 30 days
  • Filters: Enabled strategies only
  • Excludes: Brand strategies (_BR_)
  • Expected: ~218 rows

PUSH vs PULL

  • PUSH: Reduce ROAS target = spend more
  • PULL: Increase ROAS target = spend less
  • Direction determines L2 adjustment
  • Sheet name includes direction + date

BSP Sheet Structure

  • Row 4-8: ROAS ladder values
  • Row 12: Headers
  • Rows 13-230: Strategy data
  • Columns: Name, Status, Cost, Conv, ROAS

Duplicate Sheet

  • Created after validation passes
  • Naming: "PULL 12/24" or "PUSH 12/24"
  • Preserves snapshot for audit trail
  • Original sheet resets for next cycle