What would have to change for this channel to pay?
A channel that loses $5,303 a month, worked backwards two ways. It needs a 1.67 percent conversion rate or a $1.97 click, and knowing which is achievable is the decision.
Words on this sheet
- Contribution: What is left of the income after the costs that come with it, before the fixed costs are paid.
Marketing Starter Goal Seek free
After you install, this is the model to open.
What Conversion Rate Makes This Channel Work?
- In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
- Click Start from a template and put that name in the search box.
- Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.
The answer
- The channel today
- -$5,303 a month, at a 1.40% click-to-lead rate
- Fix it with conversion
- 1.67% 19% better: a landing-page and targeting job
- Or fix the click price
- $1.97 16% below the $2.35 you pay: a bidding job
- A real profit center
- 2.28% the rate $12,000 a month of contribution demands
The sheet opens on what the channel is actually doing: 14,000 clicks at $2.35 is $32,900 a month, a 1.4 percent click to lead rate gives 196 leads, 22 percent of those become 43.1 customers, and at $640 of first-year gross profit each that is $27,597 against $32,900 of spend. The channel loses $5,303 a month. Everyone already knows that. The useful question is what would have to change, and Goal Seek answers it exactly.
Click Run and it returns a click to lead rate of 1.669 percent. So the channel needs to convert 19 percent better than it does, which is a landing page question and a targeting question and is at least arguable. Now run it the other way, which is the run that makes this template worth having. Keep the Set cell on contribution after media spend with a target of 0, and change the cost a click instead: the answer is $1.97.
The channel also works if you can buy clicks 16 percent cheaper, which is a bidding and match-type question and a completely different job for a completely different person. Two levers, two teams, one sheet, and now you know how far each one has to move. A third run tells you what winning looks like: set the target to 12000 and change the click to lead rate again, and you need 2.278 percent, which is 63 percent better than today and is probably the honest answer that this channel is not going to become a profit center.
Notice which numbers the sheet does not solve for. Cost per lead and cost per customer are the two figures this channel gets judged on in every meeting, and neither of them is the one being driven to zero, because neither of them knows what a customer is worth. What the model leaves out on purpose is lifetime value: put a second-year figure into the gross profit a customer row and almost any channel works, which is true and is also how people talk themselves into unprofitable acquisition.
Replace the clicks, the cost a click and the two conversion rates with your own last quarter, and use the same sheet for any channel by changing nothing but those four numbers.
The model
It arrives on a tab called Template: What This Channel Needs, carrying these columns:
- What this channel needs
with the model computed beside the data:
| Monthly spend ($) | 32,900 |
| Leads | 196 |
| Customers | 43.12 |
| Gross profit ($) | 27,596.8 |
| Contribution after media spend ($) | -5,303.2 |
| Cost per lead ($) | 167.9 |
| Cost per customer ($) | 763 |
Once it is in your sheet
- The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
- Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
- Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.
Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.
Next question
- The winner got more opens. Did it get more clicks?Which Subject Line Won?
- Is a pitch you probably lose still worth entering?Should We Pitch for This Account?
- Does the price cut earn back the margin it gives away?Will the Promotion Pay For Itself?
- How many invitations does sixty replies take?Pull the People to Survey
- How much is riding on your one big channel?What If the Ad Platform Changes the Rules?
- Did the campaign really move the brand tracker?Two Big Samples, One Real Difference?
Every model like this one, and the method behind them: What-if analysis in Google Sheets.