What happens in the moment before a banner appears?
You open a news site, the page builds, and a banner for an e-bike appears next to the article. It looks as if the publisher simply put it there. In fact the space was sold while the page was loading, in an automated auction that took about a tenth of a second and that you never saw. This is programmatic display advertising: the buying and selling of banner impressions by software, one impression at a time.
For a marketer, the auction is where the money is actually spent, and its rules decide how much each impression costs. Two advertisers with the same budget, the same creative and the same targeting can pay very different prices if one of them understands the auction and the other does not. This free lesson explains who wins a display auction, what the winner pays under the two classic sets of rules, second price and first price, and how to work all of it out in Excel with IF, MAX and COUNTIF.
The example is Fernhollow Cycles, a fictional online retailer of e-bikes and cycling gear. All auctions, placements, bids and prices are invented for learning, and every benchmark is illustrative. You will use the same twelve auctions as in the lab above.
The players: publisher, SSP, exchange, DSP, advertiser
Five roles take part in every programmatic sale. The publisher owns the page and the ad slot. The publisher uses a supply-side platform (SSP) to offer the slot to buyers. The SSP runs, or connects to, an ad exchange, the marketplace where the auction takes place. On the other side the advertiser, or its agency, uses a demand-side platform (DSP) that decides which impressions to bid on and how much.
When a reader opens the page, the SSP sends a bid request to the connected DSPs. It describes the opportunity in general terms: the site, the size of the slot, the type of device, a rough location and, where the reader has consented, an identifier that lets a DSP recognise a browser it has seen before. Each DSP checks its campaigns, decides whether the impression fits and answers with a bid. The exchange compares the bids, picks the winner and tells its DSP to deliver the creative.
CPM: the unit every price is quoted in
Nobody quotes the price of a single banner impression, because it would be a fraction of a cent. Prices are quoted as CPM, the cost per 1,000 impressions (the M is the Roman numeral for thousand). A bid of $6.50 CPM means the advertiser is prepared to pay $6.50 for a thousand impressions of this kind, or $0.0065 for this one. To turn a CPM into the cost of one impression, divide by 1,000; to turn impressions and a CPM into spend, use impressions ÷ 1,000 × CPM.
CPM bids in an auction are still bids for exactly one impression. When this lesson says that Fernhollow Cycles bids $6.50 and a rival bids $4.20, both figures are CPMs for the same single ad slot on the same page view.
Two hurdles: the rival bid and the floor
To win, a bid has to clear two hurdles. The first is obvious: it has to be higher than the highest rival bid. The second is easy to forget: it has to be higher than the publisher's floor price. The floor is the minimum CPM the publisher accepts for the slot. Publishers set floors so that valuable inventory is not sold for almost nothing when few buyers show up, and many adjust them by placement, time of day and buyer.
Both hurdles can be put into one test: the bid wins when it is greater than the higher of the two, in Excel bid > MAX(rival bid, floor). If the bid beats every rival but stays below the floor, nobody wins the auction. The publisher then fills the slot some other way, for example with a house ad, or leaves it empty.
What the winner pays: second price
For many years almost all exchanges ran second-price auctions. The winner does not pay the own bid. The winner pays just enough to beat the next hurdle: the higher of the second-highest bid and the floor, plus a small increment, typically one cent. As a formula: paid CPM = MAX(rival bid, floor) + 0.01.
The attraction of this rule is that it makes bidding simple. If an impression is worth $6.50 to you, you can bid $6.50. When the next bid is $4.20 you pay $4.21 and keep the difference; when the next bid is $6.60 you lose an impression that would have cost more than it was worth. Bidding your true value is the best strategy, so buyers did not need to guess what others would bid.
What the winner pays: first price
In a first-price auction the rule is shorter: the winner pays the own bid. paid CPM = bid. Most open exchanges have moved to this rule over the past years. One reason was transparency. A typical impression passed through several auctions in a row, and second-price rules stacked on top of each other, together with fees and floors that buyers could not see, made it hard to tell what a bid actually bought. With first price, what you bid is what you pay.
The consequence for buyers is large. Bidding your true value in a first-price auction means paying your true value on every win and keeping nothing. The same $6.50 bid that cost $4.21 under second-price rules now costs $6.50, a difference of $2.29 per 1,000 impressions, or 54% more. A buyer who simply carried old bids over into a first-price world saw the average CPM jump without getting a single extra impression.
A fully worked example: twelve auctions of Fernhollow Cycles
Here are twelve auctions from one morning. For each one you see the bid of Fernhollow Cycles, the highest rival bid and the publisher's floor, all as CPM, followed by the three calculated columns from the lab: the result and the CPM paid under each rule. A lost auction costs nothing, so its paid CPM is $0.00.
| Auction | Placement | Our bid | Highest rival bid | Floor | Result | Paid, second price | Paid, first price |
|---|---|---|---|---|---|---|---|
| A-01 | Cycling news · 300x250 | $6.50 | $4.20 | $2.00 | Win | $4.21 | $6.50 |
| A-02 | Commuter blog · 728x90 | $5.00 | $5.80 | $2.00 | Lose | $0.00 | $0.00 |
| A-03 | Trail guide · 300x600 | $8.00 | $3.10 | $3.50 | Win | $3.51 | $8.00 |
| A-04 | Weather app · 320x50 | $4.50 | $4.40 | $1.50 | Win | $4.41 | $4.50 |
| A-05 | Gear reviews · 300x250 | $7.20 | $6.90 | $3.00 | Win | $6.91 | $7.20 |
| A-06 | City magazine · 970x250 | $3.80 | $2.50 | $4.00 | Lose | $0.00 | $0.00 |
| A-07 | E-bike forum · 300x600 | $9.00 | $5.25 | $2.50 | Win | $5.26 | $9.00 |
| A-08 | Sports results · 728x90 | $6.00 | $6.75 | $2.50 | Lose | $0.00 | $0.00 |
| A-09 | Route planner · 320x50 | $5.50 | $3.60 | $2.00 | Win | $3.61 | $5.50 |
| A-10 | Outdoor magazine · 970x250 | $10.00 | $7.40 | $5.00 | Win | $7.41 | $10.00 |
| A-11 | Local news · 300x250 | $4.00 | $1.90 | $2.80 | Win | $2.81 | $4.00 |
| A-12 | Fitness tracker app · 300x250 | $6.80 | $6.10 | $3.00 | Win | $6.11 | $6.80 |
| Sum | 9 wins | $44.24 | $61.50 |
These are the formulas of the three calculated columns and the summary. Each is shown for the first data row (row 4); fill it down as far as row 15:
- Result (F4):
=IF(C4>MAX(D4,E4),"Win","Lose").MAXpicks the higher hurdle,IFreturns one text when the test is true and the other when it is false. - Paid CPM, second price (G4):
=IF(C4>MAX(D4,E4),MAX(D4,E4)+$C$2,0). The increment of $0.01 sits in cell C2 and carries dollar signs ($C$2), which stops the reference from moving when the formula is filled down. - Paid CPM, first price (H4):
=IF(C4>MAX(D4,E4),C4,0). The winner pays the own bid from column C. - Auctions won (K4):
=COUNTIF(F4:F15,"Win")counts the cells in the result column that contain the word Win. - First-price premium (K7):
=K6/K5-1, where K5 and K6 hold=SUM(G4:G15)and=SUM(H4:H15).
Walking through the interesting rows
A-01 is the standard case. The bid of $6.50 is higher than the rival's $4.20 and the floor of $2.00, so the auction is won. Under second-price rules the price is $4.20 + $0.01 = $4.21; under first-price rules it is $6.50.
A-03 shows the floor at work. The rival bid is only $3.10, but the floor is $3.50. The winner cannot pay less than the floor, so the second-price CPM is $3.50 + $0.01 = $3.51, not $3.11. A formula that only looks at the rival bid gets this row wrong. The same happens in A-11, where the floor of $2.80 is higher than the rival's $1.90 and the price becomes $2.81.
A-06 is the trap. The bid of $3.80 beats the rival's $2.50 comfortably, and a formula that compares only the two bids reports a win. But the floor on this billboard placement is $4.00, and the bid stays below it. The auction is lost, and no impression is bought. In real campaigns this is a frequent reason for low win rates on premium placements: the competition is not too strong, the floor is too high for the bid.
A-02 and A-08 are ordinary losses: a rival bid more. A-04 is a narrow win, $4.50 against $4.40, and here the two rules give almost the same price, $4.41 and $4.50. When competition is close, first price and second price converge. When the winner bids far above everyone else, as in A-03 or A-07, they drift apart.
The result: what first price costs
Fernhollow Cycles wins 9 of the 12 auctions, a win rate of 75%. Under second-price rules the nine paid CPMs add up to $44.24, an average of $4.92 per win. Under first-price rules the same nine wins add up to $61.50, an average of $6.83. The first-price premium is $61.50 ÷ $44.24 − 1 = 39.0%.
Read the number carefully. It does not say that first-price auctions are 39% more expensive in general. It says that these bids, which were set as if the buyer would only pay the second price, cost 39% more when the buyer pays the own bid instead. In the lab's terms: the sums in K5 and K6 are sums of CPMs for single impressions, so the true dollar amounts are a thousandth of them, but the ratio is the same at any volume. If these twelve auctions were typical of a campaign that buys ten million impressions, the difference would be thousands of dollars.
From the premium to bid shading
The sensible reaction to first-price rules is to bid less than the impression is worth to you. This is called bid shading. Every dollar you take off the bid is a dollar saved on each win, but a lower bid also wins fewer auctions. In A-04 the bid could be lowered by at most nine cents before the rival wins; in A-03 it could be cut by more than half.
The art lies in not knowing the other bids in advance. DSPs estimate, from past auctions, how the probability of winning changes with the bid, the so-called win curve, and choose the bid with the best expected result. You will build exactly this calculation in lesson 7 of the course: the win rate per bid level, the expected surplus and the best bid with INDEX and MATCH. The premium you calculated here tells you how much is at stake.
Step by step in Excel
- Enter the auctions with one row each: your bid, the highest rival bid and the floor, all as CPM. Put the bid increment of 0.01 into its own cell (C2 in the lab).
- In the result column type
=IF(C4>MAX(D4,E4),"Win","Lose")and copy it down. Check one row where the floor is higher than the rival bid. - In the second-price column type
=IF(C4>MAX(D4,E4),MAX(D4,E4)+$C$2,0). Press F4 while the cursor is on C2 to add the dollar signs. - In the first-price column type
=IF(C4>MAX(D4,E4),C4,0). - Count the wins with
=COUNTIF(F4:F15,"Win")and add up both price columns withSUM. - Divide the first-price sum by the second-price sum and subtract 1. Format the cell as a percentage.
=IF(F4="Win",MAX(D4,E4)+$C$2,0). It is shorter, but it breaks when somebody changes the wording in column F, so the direct comparison is more robust.Common mistakes and how to spot them
- Ignoring the floor.
=IF(C4>D4,…)reports a win in A-06 although the bid is below the floor. If your win count is 10 instead of 9, this is the reason. - Second price without the floor.
D4+$C$2returns $3.11 for A-03, less than the floor of $3.50. No publisher sells below the floor; useMAX(D4,E4). - Using MIN instead of MAX. The lower hurdle is not the one that matters. A bid has to clear the higher one.
- Forgetting the dollar signs on the increment.
C2copied down becomes C3, C4 and so on, and the formula starts to add text or bids instead of one cent. - Reversing the premium.
=K5/K6-1gives −28.1%: how much cheaper second price is than first price. Both are correct statements, but the premium is defined on the second-price base. - Treating a CPM as dollars. A paid CPM of $6.50 for one impression costs $0.0065. Divide by 1,000 before you add auction prices to a budget.
How to read the numbers as a buyer
Three habits follow from this lesson. First, always look at the win rate together with the price. A high win rate at first price usually means the bids are higher than they need to be. Second, when a placement refuses to deliver, check the floor before you blame the competition; the DSP's loss reasons often show bids below the floor. Third, remember that the auction price is only the beginning of the cost: fees, unviewable impressions and invalid traffic all sit on top of it.
It also helps to keep the two rules apart when you read older material. Advice such as bid your true value comes from the second-price era and is wrong at first price. Conversely, the fear that a high maximum bid will always be charged in full does not apply where a DSP shades the bid for you. The spreadsheet makes the difference visible in two columns.
Quick reference: functions in this lesson
| Function | What it does here | Example |
|---|---|---|
MAX | Returns the higher of rival bid and floor, the hurdle to clear | =MAX(D4,E4) |
IF | Returns one result when the bid clears the hurdle and another when it does not | =IF(C4>MAX(D4,E4),"Win","Lose") |
$C$2 | Absolute reference: stays on the increment cell when the formula is copied | =MAX(D4,E4)+$C$2 |
COUNTIF | Counts the cells that contain a given text | =COUNTIF(F4:F15,"Win") |
SUM | Adds the paid CPMs of a column | =SUM(G4:G15) |
Where the other nineteen labs go from here
Lesson 1 is free. The other nineteen labs follow the money from the auction to the result, each with its own live sheet and a different data set of Fernhollow Cycles.
- Lessons 2 to 5: price and quality. Win rate and clearing CPM from a DSP report, viewability and vCPM, the fee stack from budget to working media, and the cost per 1,000 quality impressions (qCPM).
- Lessons 6 to 10: where to buy. Open auction against private marketplaces, preferred and guaranteed deals; bid shading along a win curve; brand safety and invalid traffic; supply-path optimisation; an authorised-sellers audit.
- Lessons 11 to 15: delivery and proof. Reach and frequency capping, budget pacing with a forecast, a regression of win rate on bid, a significance test for viewability differences, and incrementality with holdout groups.
- Lessons 16 to 20: decisions. Whether a data segment earns its fee, size-by-device grids, a what-if grid of vCPM, walk-away prices for guaranteed deals and a final partner scorecard.
After the twentieth lab a 20-question exam leads to the certificate. The arithmetic you practise is the durable part of the subject: platforms rename features and change fees, but a CPM, a win rate and a floor work the same way everywhere.
Frequently asked questions
What is the difference between a first-price and a second-price auction in programmatic advertising?
In a second-price auction the winner pays the higher of the second-highest bid and the floor plus a small increment, usually one cent. In a first-price auction the winner pays exactly the own bid. With the same bids, first price is more expensive unless the buyer lowers the bids.
How do I calculate the second-price clearing price in Excel?
Use =IF(bid>MAX(rival_bid,floor),MAX(rival_bid,floor)+0.01,0). MAX picks the higher hurdle, the increment is added on top, and a lost auction costs 0. Keep the increment in its own cell and lock it with dollar signs.
What is a floor price in a display auction?
The floor is the minimum CPM a publisher accepts for an ad slot. A bid below the floor cannot win, even if it is the highest bid, and in a second-price auction the winner never pays less than the floor.
Why did exchanges move from second-price to first-price auctions?
Mainly for transparency. Impressions often passed through several chained auctions with hidden fees and floors, so buyers could not tell what a second-price bid really bought. In a first-price auction the bid is the price.
What is bid shading?
Bid shading means bidding below the value an impression has for you in a first-price auction, to keep part of the surplus. DSPs estimate how the chance of winning changes with the bid and choose the bid with the best expected result.
Is a CPM bid the price of one impression?
No. CPM is the cost per 1,000 impressions. A bid of 6.50 dollars CPM for one impression means 0.0065 dollars. Divide a CPM by 1,000 to get the cost of a single impression.
Keep going: Programmatic Display Advertising: Auction Mechanics, vCPM & Viewability, Tech Fees & Working Media, Deals vs Open Auction, Brand Safety & Supply Paths
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- From Bid Requests to Impressions: Win Rate, Clearing CPM and the Gap Between Bid and Price per Line Item
- Viewability and vCPM: Measured Rate, Viewability Rate and the Cost per 1,000 Viewable Impressions by Placement
- Tech Fees and Working Media: Agency and DSP Percentages, Data and Verification CPMs, SSP Take Rate and What the Publisher Receives
- All-In CPM and qCPM: Adding Fees, Removing Unviewable, Invalid and Unsuitable Impressions and Ranking Supply Sources
- Deals vs Open Auction: Comparing Open Exchange, Private Marketplace, Preferred Deals and Programmatic Guaranteed on CPM and vCPM
- Bid Shading at First Price: The Win Curve, Expected Surplus per Bid Level and the Best Bid with INDEX and MATCH
- Brand Safety and Invalid Traffic: Unsuitable and IVT Rates, Wasted Spend and Block, Review or Keep Rules per Domain
- Supply-Path Optimisation: Take Rates per Path, the Best Route to Each Publisher with MINIFS and the Money Recovered by Cutting Resellers
- Authorised Sellers Audit: Matching Bid-Stream Sellers to Publisher Lists with Keys, COUNTIFS and XLOOKUP, and the Direct Share of Spend
- Reach, Frequency and Capping: Impressions per Frequency Group, Average Frequency and the Budget Freed by a Frequency Cap
- Budget Pacing: Ideal Cumulative Spend, Pacing Index, a FORECAST.LINEAR Projection to the End of the Flight and the Required Daily Spend
- Modelling the Win Curve: SLOPE, INTERCEPT and R Squared of Win Rate on Bid and the Bid That Reaches a Target Win Rate
- Is the Deal Really More Viewable? A Two-Proportion z-Test with Pooled Rate, z Value, p-Value and a Verdict for Eight Size Tests
- View-Through vs Incremental: Holdout Rates, Incremental Conversions and the True Cost per Conversion by Audience
- Is the Data Segment Worth Its Fee? Data CPM, Cost per Conversion, Lift and Break-Even Lift for Ten Audience Segments
- Size by Device: Two-Way SUMIFS Grids with Mixed References for Spend and vCPM, the Cheapest Combination and the In-App Share
- What-If Grid: All-In vCPM by Media CPM and Viewability, the Green Zone Below a Target and the Highest CPM You Can Afford
- Negotiating Guaranteed Deals: Deal qCPM, the Walk-Away CPM Against the Open-Auction Benchmark, Headroom and an Accept, Negotiate or Decline Verdict
- Final Project: Quarterly Programmatic Scorecard by Exchange Partner with Quality Impressions, qCPM, Working Media Share, a Combo Chart and a Flag