Impact in 10 Seconds
Solving Geo-Targeted Billboards on Any Budget
Integrating traffic data, geographic coverage, and budget constraints into an optimized Excel Solver impressions model.
CONSIDERATIONS
Fixed total budget
Total duration of the campaign
Maximum number of runs per billboard
Geographic diversity requirements
Ability to quickly recalcuate options depending on revolving inventory
THE CHALLENGE
Grow our brand presence in the Columbus Ohio northern suburbs by optimizing a billboard media campaign that maximizes total impressions.
BUILDING THE EXCEL SOLVER OPTIMIZATION MODEL
Inventory and pricing data from multiple billboard providers were merged with publicly available traffic data to define six high-traffic, mandatory geographic zones. This dataset formed the baseline input for the optimization model.
THE CONCEPT
Create a comprehensive list of all available billboard inventory in our mandatory zones.
Maximize total projected impressions by solving for optimal mix of product and number of runs per product.
Constraint the solution:
At least one run occurs in each of the six mandatory zones
Total Spend is <= Allocated Budget
Max Run Time is 4 runs. Each run is 4 weeks.
At least one run is non-digital.
TRADITIONAL COMPLEMENTING DIGITAL
Campaigns that include both out-of-home (billboard/DOOH) and digital advertising see up to a 27% higher ROI compared with campaigns that run digital alone.
MFour Data Research, Inc. Measuring the Effectiveness of Out-of-Home Advertising. May 2025
CREATING THE SIX HIGH TRAFFIC ZONES.
Using proprietary inventory data from leading billboard providers, we mapped the locations of available billboards across the region. By cross-referencing this with Ohio Department of Transportation traffic data, we defined six high-traffic zones with comparable traffic indices.
This map served as foundation for ensuring geographic diversity in our optimzation model.
Additionally, the Delaware, Ohio region (Blue) was designated as a mandatory inclusion in the campaign due to its proximity to the business’ corporate office.
An example of the resulting map is shown below, with proprietary details for each billboard anonymized for confidentiality.
| Vendor Name | Zone | Inventory # | Location | Billboard Type | IMP 18+ 4 WEEKS | Net Amount Per Period | Production Cost | CPM | Periods (4 week increments)(int) | Selected (bin) | Total Impressions | Media Cost | Total Cost |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| VENDOR 1 | 1 | 10001AA | 40.34898, -83.07369 | POSTER | 245168 | $990.00 | $288.00 | $5.21 | 1 | 1 | 980672 | $3,960.00 | $4,248.00 |
| VENDOR 3 | 5 | 5003 | 40.04946, -83.09501 | DIGITAL | 358120 | $1,040.00 | $200.00 | $3.46 | 4 | 0 | 1432480 | $4,160.00 | $4,360.00 |
| VENDOR 2 | 3 | 3007 | 40.00009, -82.98516 | BULLETIN | 401780 | $1,650.00 | $382.00 | $5.06 | 3 | 1 | 1607120 | $6,600.00 | $6,982.00 |
BUILDING THE SOLVER
Here’s an example of the data collected for table created for the solver. Numbers have been changed for confidentiality.
Billboard 1
- Inventory ID: 1001
- Zone: 1
- Type: Poster
- Location: 40.34898, -83.07369
- Selected: Yes=1, No=0
- 4-Week Periods: 1,2,3, or 4
Performance
- Impressions (18+ / 4 weeks): 245,168
- Total Impressions: 980,672
- CPM: $5.21
Cost
- Net Media / Period: $990.00
- Production: $288.00
- Media Cost: $3,960.00
- Total Cost: $4,248.00
Billboard 2
- Inventory ID: 5003
- Zone: 5
- Type: Digital
- Location: 40.04946, -83.09501
- Selected: Yes=1, No=0
- 4-Week Periods: 1,2,3, or 4
Performance
- Impressions (18+ / 4 weeks): 358,120
- Total Impressions: 1,432,480
- CPM: $3.46
Cost
- Net Media / Period: 1,040.00
- Production: $200.00
- Media Cost: $4,160.00
- Total Cost: $4,360.00
Billboard 2
- Inventory ID: 3007
- Zone: 3
- Type: Digital
- Location: 40.00009, -82.98516
- Selected: Yes=1, No=0
- 4-Week Periods: 1,2,3, or 4
Performance
- Impressions (18+ / 4 weeks): 401,780
- Total Impressions: 1,607,120
- CPM: $5.06
Cost
- Net Media / Period: $1,650.00
- Production: $382.00
- Media Cost: $6,600.00
- Total Cost: $6,982.00
VARIABLE CELLS
The Solver will manipulate the Variable Cells to optimize. The Variable Cells are Periods (Column J) and Selected (Column K).
Selected - Relays if the billboard was chosen = 1 or not chosen = 2
Periods - Determines how many times the billboard will run. Minimum is 4 weeks. Maximum is 16 weeks.
CONSTRAINTS
Total Cost <= Budget Allocated
Zones (Column B) Zones 1-6 must appear at least once
Periods (Column J) must be an integer
Selected (Column K) is binary
EXAMPLE SOLUTION WITH BUDGET SET TO $40,000.00
| Vendor Name | Zone | Inventory # | Billboard Type | Periods | Total Cost | Impressions | CPM |
|---|---|---|---|---|---|---|---|
| VENDOR 1 | 1 | 1001 | POSTER | 1 | $1240.00 | 358,120 | $3.46 |
| VENDOR 3 | 5 | 5003 | DIGITAL | 3 | $8970.00 | 3,360,780 | $2.67 |
| VENDOR 2 | 3 | 3007 | BULLETIN | 1 | $3430.00 | 1,343,692 | $2.55 |
| VENDOR 2 | 4 | 4009 | BULLETIN | 4 | $13,720.00 | 7,843,072 | $1.75 |
| VENDOR 1 | 6 | 6011 | DIGITAL | 1 | $3,300.00 | 359,812 | $9.17 |
| VENDOR 1 | 2 | 2005 | DIGITAL | 1 | $1350.00 | 317,932 | $4.25 |
| TOTALS | 11 | $39,874.00 | 13,583,408 | $2.94 |
SOLVER SOLUTIONS
| Vendor Name | Zone | Inventory # | Billboard Type | Periods | Total Cost | Impressions | CPM |
|---|---|---|---|---|---|---|---|
| VENDOR 1 | 1 | 1001 | POSTER | 1 | $1240.00 | 358,120 | $3.46 |
| VENDOR 3 | 5 | 5003 | DIGITAL | 3 | $8970.00 | 3,360,780 | $2.67 |
| VENDOR 2 | 3 | 3007 | BULLETIN | 1 | $3430.00 | 1,343,692 | $2.55 |
| VENDOR 2 | 4 | 4009 | BULLETIN | 4 | $13,720.00 | 7,843,072 | $1.75 |
| VENDOR 1 | 6 | 6011 | DIGITAL | 1 | $3,300.00 | 359,812 | $9.17 |
| VENDOR 1 | 2 | 2005 | DIGITAL | 1 | $1350.00 | 317,932 | $4.25 |
| TOTALS | 11 | $39,874.00 | 13,583,408 | $2.94 |
Example Output with Budget = $40,000.00
The output of the solver includes:
Billboards Selected for Campaign
Number of 4 week Increments per Selected Billboard
Number of Appearances by Zone
Total Impressions
Total Cost
Total CPM
Here is a solution example where budget is set to $40,000.
Inventory ID - 1001
- Zone: 1
- Billboard Type: Poster
- 4-Week Periods: 1
Performance
- Impressions: 358,120
- CPM: $3.46
Total Cost: $1,240
Inventory ID - 5003
- Zone: 5
- Billboard Type: Digital
- 4-Week Periods: 3
Performance
- Impressions: 3,360,780
- CPM: $2.67
Total Cost: $8,970.00
Inventory ID - 3007
- Zone: 3
- Billboard Type: Bulletin
- 4-Week Periods: 1
Performance
- Impressions: 1,343,692
- CPM: $2.55
Total Cost: $3,430.00
Inventory ID - 4009
- Zone: 4
- Billboard Type: Bulletin
- 4-Week Periods: 4
Performance
- Impressions: 7,843,072
- CPM: $1.75
Total Cost $13,720.00
Inventory ID - 6011
- Zone: 6
- Billboard Type: Digital
- 4-Week Periods: 1
Performance
- Impressions: 359,812
- CPM: $9.17
Total Cost: $3,300.00
Inventory ID - 2005
- Zone: 2
- Billboard Type: Digital
- 4-Week Periods: 1
Performance
- Impressions: 317,932
- CPM: $4.25
Total Cost: $1,350.00
Campaign Totals
- Total 4-Week Periods: 11
- Total Impressions: 13,583,408
- Total Cost: $39,874.00
- Overall CPM: $2.94
Even on a limited budget. The solver can find the optimum mix where all zones are represented and impressions are maximized.
KEY TAKEAWAY
This tool demonstrates how optimization modeling can significantly improve media efficiency while still respecting real-world planning constraints.
In practice, I’ve encountered executives and stakeholders often having strong preferences about where billboards should be placed, including near new developments, competitors, or areas personal to the stakeholder.
This solver is designed to accommodate those inputs without sacrificing performance.
By allowing planners to apply geographic, inventory, and budget constraints, the model ensures recommendations remain realistic and actionable. At the same time, it enables scenario testing. This solver can show how incremental budget increases can unlock disproportionate gains in reach and impressions; helping teams make more confident, data-driven investment decisions.
TOOLS USED FOR THIS CASE STUDY
EXCEL SOLVER
GOOGLE CUSTOM MAPS
PROPRIETARY INVENTORY & PRICING DATA
PUBLIC OHIO DEPARTMENT OF TRANSPORTATION TRAFFIC MONITORING DATA