I sent you this chapter. Reply on LinkedIn or by email.
The formulas behind the four calculators, a spreadsheet layout for the pipeline, and one query for counting objections in support tickets.
| For | Formula | Notes |
|---|---|---|
| Cost to win, by conversation | (v + h × w) / c | v: cost to find a prospect. h: hours per conversation. w: cost of an hour. c: conversation close rate. |
| Cost to win, by page | v / p | p: page close rate for the same kind of prospect. |
| Gain per prospect talked to | (c − p) × g − h × w | g: first-year gross profit per won customer. |
| Break-even deal size | h × w / (c − p) | In first-year gross profit. Only defined when c > p. |
| Conversations you can hold | min(N, floor(S / h)) | N: prospects a month. S: selling hours a month. |
| Reply rate on touch k | rk = r1 × dk−1 | d: each follow-up’s rate as a share of the one before. |
| Keep following up while | rk × V > C | V: value of a reply. C: cost of one follow-up. |
| First conversations needed | ceil(T / A) / (q1 × q2 × q3) | T: target. A: average first-year deal. q: stage rates. Divide by weeks for the weekly number. |
| Page coverage | Σ(f × a / 2) / Σ f | f: how often it’s heard. a: 0, 1 or 2. Gap score: f × (2 − a) / 2. |
| Retail margin | (retail price − wholesale price) / retail price | So wholesale = retail × (1 − margin). |
ONE ROW PER DEAL
deal_id | account | kind (retail / distributor / partner / investor / customer)
first_conversation_date | qualified_date | proposal_date | closed_date
outcome (won / lost / open) | lost_reason | first_year_value
source (inbound / outbound / referral / event) | owner
last_step (advance / continuation / no) | next_step | next_step_date
SUMMARY TAB, LAST 20 CLOSED DEALS
qualify rate = COUNT(qualified_date not blank) / COUNT(all)
proposal rate = COUNT(proposal_date not blank) / COUNT(qualified_date not blank)
win rate = COUNT(outcome = won) / COUNT(proposal_date not blank)
average deal = AVERAGE(first_year_value where outcome = won)
weekly need = CEILING(target / average deal)
/ (qualify rate * proposal rate * win rate) / weeks
advance share = COUNT(last_step = advance or no) / COUNT(all conversations)
Count only closed deals in the rates. Mark a deal lost after a set time with no advance, such as sixty days, rather than leaving it open forever.
-- pre-purchase questions by objection tag, last 90 days
-- tickets: one row per ticket, with id, created_at, customer_id, tag
-- orders: one row per order, with customer_id, ordered_at
SELECT t.tag,
COUNT(*) AS tickets,
COUNT(*) FILTER (WHERE o.first_order IS NULL
OR o.first_order > t.created_at) AS before_first_order
FROM tickets t
LEFT JOIN (SELECT customer_id, MIN(ordered_at) AS first_order
FROM orders GROUP BY customer_id) o
ON o.customer_id = t.customer_id
WHERE t.created_at >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY t.tag
ORDER BY before_first_order DESC;
Tag tickets with the objection families from chapter 5. Questions asked before a first order are objections the page didn’t answer; each tag’s share of the total, times ten, is its “heard in 10” figure for the page scorer. Postgres syntax; in BigQuery, use COUNTIF(...).
Want to answer the note that sent you here? Reply on LinkedIn or by email.
If this is your store
The free Klaviyo audit reads your own account and scores it. Start with your store’s address. A read-only key gets the full report.
Written by Andrew Lauchner, a growth and retention operator for consumer brands. The paid work is one ninety-day Sprint.
This is one chapter of One to Many, which is free and readable in full on a single page with no form in front of it.