Tabellab

Break-Even Bids: Margin per Order, Break-Even CPC, Margin per Click and a Verdict for Every Ad Group

Lesson 1 of 20Lab

All lessons
  1. Break-Even Bids: Margin per Order, Break-Even CPC, Margin per Click and a Verdict for Every Ad GroupLabFree
  2. Break-Even CPA and ROAS: What an Order May Cost, the ROAS You Need and a Target with Profit Built InLab Locked
  3. Manual CPC: Turning a Target CPA into Keyword Bids with Conversion Rates, Capped Steps and ROUNDLab Locked
  4. Manual CPC vs Target CPA vs Target ROAS: Spend, CPA, ROAS and Share of Spend per Bid Strategy with SUMIFSLab Locked
  5. Target ROAS: Value per Click, Tiered ROAS Targets with XLOOKUP and the Max CPC a Target ImpliesLab Locked
  6. Day-Parting: Conversion Rate by Hour Block, an Index Against the Account and Capped Bid ModifiersLab Locked
  7. Device Modifiers: Value per Click by Device, the Campaign Baseline with SUMIFS and Bids in 5 % StepsLab Locked
  8. Geo Modifiers: CPA by Region, a Minimum-Data Rule, Capped Location Modifiers and a RankLab Locked
  9. Stacked Modifiers: How Device, Region and Time Adjustments Multiply, the Effective Bid and a Bid CeilingLab Locked
  10. Monthly Pacing: Cumulative Spend, the Ideal Line, Pacing in Per Cent and the Daily Spend LeftLab Locked
  11. Month-End Forecasts: Run Rate vs Recent Trend with DAY and EOMONTH, Variance to Budget and a Status with IFSLab Locked
  12. Weekday-Weighted Pacing: Weekday Weights, WEEKDAY and XLOOKUP on Dates and a Daily Plan for the Rest of the MonthLab Locked
  13. Impression Share: Lost to Budget or Lost to Rank, Missed Clicks, the Spend to Capture Them and a DiagnosisLab Locked
  14. Marginal CPA: What the Last Euro Buys, a Ranking of Campaigns and Where to Move BudgetLab Locked
  15. Bid Landscapes: Marginal CPC, Profit After Ads at Every Bid Level and the Profit-Maximising Bid with INDEX/MATCHLab Locked
  16. Ready for Automated Bidding? Conversion Volume, Budget-to-Target Ratio, Days to Enough Data and a VerdictLab Locked
  17. What-If Grid: Profit After Ads for Every Target ROAS and Margin with Mixed References, the Best Target and a Heat MapLab Locked
  18. Seasonal Budget Plan: A Seasonal Index, the Annual Budget Split by Month with ROUND and Expected ConversionsLab Locked
  19. Testing a Bid Strategy: A Split Experiment, the Change in CPA, a Paired T.TEST and a VerdictLab Locked
  20. Final Project: Bidding & Pacing Dashboard with Month-End Projection, Profit Index, an Action per Campaign and Profit by StrategyLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

Why every bid needs a ceiling

Pay-per-click advertising looks simple from the outside. You choose keywords or products, you say how much a click may cost, and visitors arrive. The difficult part is the number in the middle. Bid too little and competitors take the clicks. Bid too much and every order you win costs more than it earns, while the dashboards still show healthy traffic and growing revenue.

The way out is to stop asking what does a click cost? and to ask what is a click worth? Once you know what one click is worth to your business on average, you have a ceiling for every bid: the break-even CPC. Below it a click adds profit. Above it a click destroys profit, however good the click-through rate looks.

This free lesson shows how to calculate that ceiling in Excel from three numbers you already have, how to compare it with what you really pay and how to flag the ad groups that need a lower bid. The example company is Fernwick Cycle Supply, a fictional online shop for bicycle parts. All ad groups, people, campaigns and numbers are invented for learning, and every benchmark in this course is illustrative.

The three numbers behind a break-even bid

A break-even bid needs no forecast and no special tool. It is built from three inputs that sit in your shop system and your ad account.

  • Average order value (AOV). Revenue ÷ orders for the ad group. A brake-pad order at Fernwick is small, €38 on average. An order from the bike-computer ad group is large, €240.
  • Gross margin. The share of the order value that is left after product cost, shipping, packaging and payment fees, before advertising. This is the money that has to pay for the ads and still leave a profit. Margins differ a lot: 48% on bike lights, only 22% on bike computers.
  • Conversion rate. Orders ÷ clicks. It tells you how many clicks you have to buy for one order. At 4.6% you need about 22 clicks per order, at 1.2% about 83.
Use the gross margin before advertising. If you subtract ad cost first and then compare the result with ad cost again, you count the ads twice and every bid looks too high.

Notice what is not on the list: impressions, click-through rate, average position, quality score. They all influence how many clicks you get and what you pay for them. None of them changes what a click is worth once the visitor is on your site.

The formula in one line

Start with one order. Margin per order = average order value × gross margin. For the Road tyres ad group that is €92 × 31% = €28.52. If you could buy orders directly, €28.52 is the most you could pay for one. That figure has a name of its own, the break-even CPA, and lesson 2 of the course builds on it.

But you buy clicks, and most clicks do not become orders. With a conversion rate of 2.9%, one click carries only 2.9% of an order. So one click is worth, on average, €28.52 × 2.9% = €0.83. That is the break-even CPC. In words: break-even CPC = average order value × gross margin × conversion rate.

From an order to the value of one click: Road tyresAverage order value€92.00Margin per order€28.52Break-even CPC€0.83Average CPC paid€0.88× 31% margin× 2.9% conv. ratecompareMargin per click after CPC = €0.83 − €0.88 = −€0.05Every click on this ad group costs about 5 cents more than it earns.Fictional figures. Gross margin is before advertising.
The chain behind a break-even bid: order value, margin per order, value of one click. For Road tyres the click is worth €0.83 but costs €0.88.

The last step is a comparison. Fernwick pays €0.88 per click on Road tyres. Margin per click after CPC = break-even CPC − average CPC = €0.83 − €0.88 = −€0.05. The minus sign is the whole message: each click loses about 5 cents, which is €53 per 1,000 clicks. The ad group may show a respectable ROAS in the ad platform and still shrink the bank balance.

A fully worked example: ten ad groups at Fernwick Cycle Supply

Here is the data from the lab above: ten search ad groups with their figures for the last 90 days. Columns B to E are given; you calculate the rest.

Ad groupAverage order valueGross marginConversion rateAverage CPC
Brake pads€3842%4.6%€0.52
Chains & cassettes€6438%3.4%€0.71
Road tyres€9231%2.9%€0.88
MTB tyres€10433%2.7%€0.83
Helmets€11844%2.1%€0.96
Bike lights€4648%3.8%€0.58
Pedals€7236%2.5%€0.74
Saddles€8641%1.9%€0.79
Bike computers€24022%1.2%€0.92
Workshop tools€5445%3.1%€0.49

Look at the raw data first and try to guess which ad groups have a problem. Most people pick the ones with the highest CPC, Helmets at €0.96 and Bike computers at €0.92. One of those guesses is right and one is wrong, and the reason is the point of the lesson: a CPC is only high or low relative to what the click earns.

Step by step in Excel

The lab asks for four columns and one summary cell. You type each formula once in row 4 and fill it down as far as row 13.

  1. Margin per order (F4): =B4*C4. Order value times margin. For Brake pads: €38 × 42% = €15.96.
  2. Break-even CPC (G4): =F4*D4, or in one step =B4*C4*D4. For Brake pads: €15.96 × 4.6% = €0.73.
  3. Margin per click after CPC (H4): =G4-E4. For Brake pads: €0.73 − €0.52 = €0.21. Positive, so each click adds about 21 cents.
  4. Verdict (I4): =IF(E4>G4,"Lower bid","OK"). IF tests whether the CPC is above the break-even CPC and returns one of two texts.
  5. Count the problem ad groups (E15): =COUNTIF(I4:I13,"Lower bid"), or straight from the data without helper columns: =SUMPRODUCT((E4:E13>B4:B13*C4:C13*D4:D13)*1).
Format the break-even CPC with at least two decimals, better three while you are checking. Bids are set in cents, and the difference between a profitable and an unprofitable ad group is often only five or ten of them.

The SUMPRODUCT version deserves a second look, because the same pattern returns throughout the course. The comparison E4:E13>B4:B13*C4:C13*D4:D13 produces ten TRUE or FALSE values, one per ad group. Multiplying by 1 turns them into ones and zeros, and SUMPRODUCT adds them up. The result does not depend on any helper column, so it stays correct even if someone deletes column I.

Reading the result

With all formulas in place the sheet looks like this.

Ad groupMargin per order (F)Break-even CPC (G)Average CPC (E)Margin per click after CPC (H)Verdict (I)
Brake pads€15.96€0.73€0.52€0.21OK
Chains & cassettes€24.32€0.83€0.71€0.12OK
Road tyres€28.52€0.83€0.88−€0.05Lower bid
MTB tyres€34.32€0.93€0.83€0.10OK
Helmets€51.92€1.09€0.96€0.13OK
Bike lights€22.08€0.84€0.58€0.26OK
Pedals€25.92€0.65€0.74−€0.09Lower bid
Saddles€35.26€0.67€0.79−€0.12Lower bid
Bike computers€52.80€0.63€0.92−€0.29Lower bid
Workshop tools€24.30€0.75€0.49€0.26OK

Four of the ten ad groups pay more for a click than the click earns: Road tyres, Pedals, Saddles, Bike computers. The Helmets guess from above was wrong. Helmets has the highest CPC in the table, €0.96, but also the highest break-even CPC, €1.09, because a €118 order at 44% margin leaves €51.92 per order. It is one of the healthiest ad groups in the account.

Bike computers is the opposite case. The order value of €240 is by far the largest, which makes the ad group look attractive. But the margin is only 22% and only 1.2% of clicks convert, so a click is worth €0.63 while it costs €0.92. With −€0.29 per click it is the most expensive mistake on the sheet: €286 lost per 1,000 clicks.

Break-even CPC vs average CPC paidBreak-even CPC (CPC is below it)Break-even CPC (CPC is above it)Average CPC paidBrake pads€0.73 vs €0.52Chains & cassettes€0.83 vs €0.71Road tyres€0.83 vs €0.88MTB tyres€0.93 vs €0.83Helmets€1.09 vs €0.96Bike lights€0.84 vs €0.58Pedals€0.65 vs €0.74Saddles€0.67 vs €0.79Bike computers€0.63 vs €0.92Workshop tools€0.75 vs €0.49€0.00€0.20€0.40€0.60€0.80€1.00€1.20€1.40Euro per click, last 90 days (fictional ad groups)
Break-even CPC per ad group (bar) and the average CPC actually paid (black marker). Where the marker sits to the right of the bar, a click costs more than it earns: Road tyres, Pedals, Saddles, Bike computers.

The chart makes the pattern visible at a glance. Wherever the black marker lies to the right of the bar, the bid is above the ceiling. The best ad group by margin per click is Workshop tools with €0.26 per click; Brake pads and Bike lights follow closely. These are the ad groups where a higher bid could be tested, because there is room between what a click costs and what it earns.

What to do with an ad group above break-even

"Lower bid" is the obvious verdict, and often the right first move. It is not the only lever. The break-even CPC is a product of three numbers, and each of them can be improved.

  • Lower the bid to below the break-even CPC. You will lose some clicks, mostly the most expensive ones. Lesson 3 shows how to move bids in capped steps so that one noisy period cannot wreck a keyword.
  • Raise the conversion rate. A better landing page, clearer delivery information or tighter keywords lift the value of every click. For Road tyres the conversion rate would have to rise from 2.9% to about 3.09% to break even at today's CPC.
  • Raise the order value. Bundles, accessories and free-shipping thresholds increase the margin per order without touching the bid.
  • Cut waste inside the ad group. An average hides a mix. A few search terms with many clicks and no orders can pull a good ad group below break-even. Negative keywords repair the average.
  • Check the margin. Sometimes the honest answer is that the product cannot carry paid clicks at current prices. That is useful to know before the budget is spent.

How sensitive is the break-even CPC?

Because the three inputs are multiplied, a change of 10% in any one of them moves the break-even CPC by 10%. The conversion rate is usually the least certain of the three, so it pays to see how much rests on it. For Road tyres, with a margin per order of €28.52:

Conversion rateBreak-even CPCAgainst a CPC of €0.88
2.5%€0.71loses money
2.9%€0.83loses money
3.2%€0.91profitable
3.5%€1.00profitable

A shift of three tenths of a percentage point in the conversion rate turns a losing ad group into a profitable one. That is a small difference, well within the normal variation of a month with a few dozen orders. Two conclusions follow. First, use enough data: 90 days are better than 14, and an ad group with eight orders does not have a reliable conversion rate at all. Second, do not treat a verdict as final when the margin per click is within a few cents of zero. Pedals at −€0.09 and Saddles at −€0.12 are clear cases; an ad group at −€0.01 is not.

Break-even is a ceiling, not a target

A common misreading is to set every bid exactly at its break-even CPC. At that bid the ad group earns nothing: all of the margin goes to the ad platform. Break-even marks the point where you stop losing money, not the point where you make the most.

The profit-maximising bid lies below the ceiling, and how far below depends on how quickly clicks get more expensive as you raise the bid. Lesson 15 of the course works through a bid landscape and finds that point with MAX, INDEX and MATCH. As a working rule until then: decide which share of the margin you want to keep, for example 30%, and bid at most the remaining 70% of the break-even CPC. For Helmets that would be €1.09 × 70% = €0.76.

There is one legitimate reason to bid at or even above break-even: when a first order reliably leads to repeat orders, the value of a click includes that future margin. Then calculate with a customer value you can prove, not with a hopeful guess.

Common mistakes and how to spot them

The arithmetic is short, which makes the typical errors easy to recognise.

  • Using revenue instead of margin. =B4*D4 gives revenue per click. For Road tyres that is €2.67, more than three times the true ceiling of €0.83. If your break-even CPCs look generous, check whether column C is in the formula.
  • Dividing by the conversion rate. =B4*C4/D4 returns hundreds of euros per click. A break-even CPC is always smaller than the margin per order.
  • Percentages typed as whole numbers. A conversion rate entered as 2.9 instead of 2.9% makes every result a hundred times too large. Format the cells as percentages before typing.
  • A reversed comparison. =IF(E4<G4,"Lower bid","OK") flags exactly the healthy ad groups. Read the formula aloud: lower the bid if the CPC is greater than break-even.
  • Mixing time periods. Order value from last year, conversion rate from last week and CPC from yesterday do not belong in one row. Use the same period for all columns.
  • Account-wide averages. One conversion rate and one margin for the whole account produce one break-even CPC for everything. The table shows why that fails: the true ceilings range from €0.63 to €1.09.

From break-even CPC to target CPA and target ROAS

Manual bidding works with a CPC. Automated bid strategies ask for a different input, a target CPA or a target ROAS, but they rest on the same three numbers.

The margin per order in column F is the break-even CPA: Road tyres can pay at most €28.52 in ads per order. And the gross margin alone gives the break-even ROAS, 1 ÷ margin: at 31% margin Road tyres needs €3.23 of revenue per euro of ad spend, Bike computers at 22% needs €4.55. Whichever strategy you use, the platform optimises towards the number you give it. If that number is on the wrong side of break-even, automation simply loses money more efficiently.

Quick reference: formulas in this lesson

QuantityFormulaIn the sheet
Margin per orderorder value × gross margin=B4*C4
Break-even CPCorder value × gross margin × conversion rate=B4*C4*D4
Margin per click after CPCbreak-even CPC − average CPC=G4-E4
VerdictCPC above break-even?=IF(E4>G4,"Lower bid","OK")
Count above break-evennumber of ad groups with CPC above break-even=COUNTIF(I4:I13,"Lower bid")
Break-even CPAorder value × gross marginsame as margin per order
Break-even ROAS1 ÷ gross margin=1/C4

Where the numbers come from in practice

To build this sheet for your own account, export a report by ad group (or by campaign, product group or keyword) for a period long enough to contain at least a few dozen orders per row. You need clicks, cost, conversions and conversion value. Average CPC is cost ÷ clicks, conversion rate is conversions ÷ clicks, and average order value is conversion value ÷ conversions.

The gross margin is the one figure the ad platform does not know. Take it from your shop system or your accountant, per product category if you can. Be strict about what counts as cost: purchase price, inbound freight, packaging, outbound shipping you do not charge for, payment fees and the cost of returns. A margin that is five points too optimistic raises every break-even CPC by the same proportion.

What you will build in the rest of this course

This first lesson is open to everyone. The nineteen labs that follow carry the break-even logic through bid strategies, bid modifiers and budget pacing to a complete review dashboard.

  • Break-even CPA and ROAS, manual bids and strategy comparison (lessons 2 to 5). You calculate break-even CPA and ROAS with profit built into the target, turn a target CPA into keyword bids with capped steps, roll campaigns up by bid strategy with SUMIFS and derive the max CPC that a tiered ROAS target implies.
  • Bid modifiers (lessons 6 to 9). You build day-parting modifiers from hourly conversion rates, device modifiers from value per click and geo modifiers with a minimum-data rule, then see how the three multiply into one effective bid and why a ceiling is needed.
  • Pacing and forecasts (lessons 10 to 12). You track cumulative spend against an ideal line, calculate the daily spend left, forecast month-end spend from the run rate and from the last seven days with DAY and EOMONTH, and replace the even plan with weekday weights.
  • Budget decisions (lessons 13 to 16). You separate impression share lost to budget from share lost to rank, estimate marginal CPA to decide where the next euro goes, find the profit-maximising bid on a bid landscape and check which campaigns have enough data and budget for automated bidding.
  • Planning, testing and the final project (lessons 17 to 20). You build a target ROAS what-if grid with mixed references and a heat map, split an annual budget by a seasonal index, evaluate a bid strategy experiment with a paired t-test and finish with a bidding and pacing dashboard that assigns an action to every campaign.

Summary

A break-even bid answers the most basic question in paid search: what may a click cost? Multiply the average order value by the gross margin to get the margin per order, multiply by the conversion rate to get the break-even CPC, and subtract the CPC you pay. In Excel that is =B4*C4, =B4*C4*D4 and =G4-E4, with =IF(E4>G4,"Lower bid","OK") for the verdict.

At Fernwick Cycle Supply the calculation shows that four of ten ad groups bid above their ceiling, that the ad group with the highest CPC is one of the most profitable and that the one with the largest orders loses the most per click. None of that is visible in the CPC column alone.

The lab on this page lets you work through the ten ad groups yourself in roughly a quarter of an hour. Every formula is checked as you enter it, and the finished sheet doubles as a template for your own ad groups.

Frequently asked questions

What is a break-even CPC?

It is the highest cost per click at which an ad neither makes nor loses money. Break-even CPC = average order value × gross margin × conversion rate. Above it every click costs more than the margin it brings in.

How do I calculate a break-even bid in Excel?

With the order value in B4, the gross margin in C4 and the conversion rate in D4, type =B4*C4*D4. Compare it with the average CPC in E4 using =IF(E4>G4,"Lower bid","OK") when the break-even CPC is in G4.

Should I bid exactly at the break-even CPC?

No. At break-even the whole margin is spent on ads and nothing is left as profit. Treat it as a ceiling and bid below it, for example at 70% of the break-even CPC if you want to keep 30% of the margin.

Which margin should I use for break-even calculations?

Use the gross margin before advertising: order value minus product cost, shipping, packaging, payment fees and the cost of returns. Do not subtract ad cost, otherwise you count the ads twice.

What is the difference between break-even CPC, break-even CPA and break-even ROAS?

They are the same limit seen from three sides. Break-even CPA is the margin of one order (order value × margin). Break-even CPC is that margin × conversion rate. Break-even ROAS is 1 ÷ gross margin, for example 4.00 at a 25% margin.

How much data do I need for a reliable break-even CPC?

Enough for a stable conversion rate. A few dozen orders per ad group is a sensible minimum; with fewer, lengthen the period or combine similar ad groups. Small changes in the conversion rate move the break-even CPC in the same proportion.

Keep going: PPC Bidding & Budget Pacing: Break-Even Bids, Manual CPC vs Target CPA vs Target ROAS, Bid Modifiers, Monthly Pacing & Spend Forecasts

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.

  1. Break-Even CPA and ROAS: What an Order May Cost, the ROAS You Need and a Target with Profit Built In
  2. Manual CPC: Turning a Target CPA into Keyword Bids with Conversion Rates, Capped Steps and ROUND
  3. Manual CPC vs Target CPA vs Target ROAS: Spend, CPA, ROAS and Share of Spend per Bid Strategy with SUMIFS
  4. Target ROAS: Value per Click, Tiered ROAS Targets with XLOOKUP and the Max CPC a Target Implies
  5. Day-Parting: Conversion Rate by Hour Block, an Index Against the Account and Capped Bid Modifiers
  6. Device Modifiers: Value per Click by Device, the Campaign Baseline with SUMIFS and Bids in 5 % Steps
  7. Geo Modifiers: CPA by Region, a Minimum-Data Rule, Capped Location Modifiers and a Rank
  8. Stacked Modifiers: How Device, Region and Time Adjustments Multiply, the Effective Bid and a Bid Ceiling
  9. Monthly Pacing: Cumulative Spend, the Ideal Line, Pacing in Per Cent and the Daily Spend Left
  10. Month-End Forecasts: Run Rate vs Recent Trend with DAY and EOMONTH, Variance to Budget and a Status with IFS
  11. Weekday-Weighted Pacing: Weekday Weights, WEEKDAY and XLOOKUP on Dates and a Daily Plan for the Rest of the Month
  12. Impression Share: Lost to Budget or Lost to Rank, Missed Clicks, the Spend to Capture Them and a Diagnosis
  13. Marginal CPA: What the Last Euro Buys, a Ranking of Campaigns and Where to Move Budget
  14. Bid Landscapes: Marginal CPC, Profit After Ads at Every Bid Level and the Profit-Maximising Bid with INDEX/MATCH
  15. Ready for Automated Bidding? Conversion Volume, Budget-to-Target Ratio, Days to Enough Data and a Verdict
  16. What-If Grid: Profit After Ads for Every Target ROAS and Margin with Mixed References, the Best Target and a Heat Map
  17. Seasonal Budget Plan: A Seasonal Index, the Annual Budget Split by Month with ROUND and Expected Conversions
  18. Testing a Bid Strategy: A Split Experiment, the Change in CPA, a Paired T.TEST and a Verdict
  19. Final Project: Bidding & Pacing Dashboard with Month-End Projection, Profit Index, an Action per Campaign and Profit by Strategy

Buy course — $9.90 See the full course