Count Where Last Year's Jobs Actually Came From

What it does

One worksheet, Sources. It takes a year of finished work, sorted into the places that work came from, and prices each place: what it cost, what it closed, and how much of the year it paid for. The question it answers is the one most owners can only guess at — where does my work actually come from, and what am I paying for each piece of it.

The log is rows 7 through 26, twenty sources. Columns A through E are yellow: that is where you type. Columns F through J are gray: they work themselves out, and you never type in them. The answer block sits at rows 30 through 41 — C30 is the guard, C31 through C40 are the answers, green except for the gray guard, and C41 is the one-line verdict. Row 3 is the banner that says the same thing. Rows 43 through 50 are the closing prose, each one merged across A:J.

The five yellow columns:

One source per job, and it is the first one. A referral who then searched your name and called the ad is a referral: the source is whatever first put that name in your hands. Split one job across two sources and you have counted its revenue twice, both channels read better than they are, and the shares in column J stop adding to a hundred.

Column B is the cost of making the phone ring, not the cost of doing the work. Ad spend, lead service fees, the printing and the dropping. Not labor, not material, not the truck. Repeat customers and referrals normally sit at $0, and that zero is the whole point of the sheet: it is what lets a free channel and a bought channel stand next to each other. When B is 0 the sheet prints free in the two cost columns instead of dividing by nothing.

Column C is leads, not jobs. Every call, form, knock and message that source produced, whether you ever got on the roof or not. This is the column shops fudge, and it is the one that carries the most weight: it sets the cost per lead and the close rate. If your phone log genuinely cannot tell you the number for a channel, type the closest honest count you have and write on the sheet that it is a count you made — a guessed lead number wrecks two answers at once and nothing in here can see it.

The five gray columns, all wrapped in =IF($A7="","",…) so an empty row stays empty:

How to use it

Pull last year's closed jobs — a full year, so the slow months are in it. Put one source on each one. Then fill one row per source: the spend, the leads, the jobs, the revenue. Twenty rows is more than most shops need; five or six is normal.

Read it in this order. First C30, the guard: anything but 0 and nothing below it will print. Then C40, the share of the year that cost you nothing — this is the number owners guess wrong, usually low. Then column G, cost per job, which is the only honest way to line up a channel that sends a flood of cheap tire-kickers against one that sends four calls and closes three of them. Column H is that same channel's close rate; compare it against C34, your own average, not against a number somebody quoted you at a trade show.

Then change one thing. Turn a channel up, turn one off, or give the free channel an owner. Change two and the next count cannot tell you which one moved.

The example

The five yellow rows shipped in the file are an example so you can watch the sheet run: search ads, repeat customers, referrals, a lead service app and door hangers, across a made-up year. None of those numbers is a figure from anywhere and none of them is a benchmark. Cost per lead, close rate and average job size swing hard by market, by season and by what you sell — a shop three states over runs numbers that would look like a typo next to yours. They are there to show the shape of the answer, not the size of it. Select A7:E11, press Delete, and put your own year in.

What the example is built to show: two channels with $0 spend carry 47% of the revenue, and the verdict line at C41 points straight at them — because in most shops nobody is responsible for that half of the business.

The guard, term by term

C30 counts problems, not bad rows, so one half-typed row can raise it by more than 1:

• a source typed in A with a blank in B, C, D or E — four separate counts, one per column
• a number typed in B, C, D or E on a row with no source in A — the totals cannot see that row
• more jobs than leads on a named row (this also catches jobs with zero leads)
• jobs closed on a named row with no revenue against them
• revenue on a named row with no jobs against it
• any minus number in B through E

Anything but 0 and every green cell prints Fix the rows that don't line up instead of an answer. A total taken off a broken row is worse than no total, because you will act on it.

What the guard cannot catch: a wrong but believable number. Type 40 leads where the phone log said 90 and everything recalculates happily and lies to you. Same with a job filed under the wrong source, a channel you forgot to give a row, revenue that quietly includes a job you have not been paid for, and the year you meant to count being ten months long. The guard checks that a row hangs together, not that it is true.

Adding rows

Type into the empty yellow rows between row 7 and row 26. The gray columns F through J are already on every one of those rows and start working the moment you type a source, so there is nothing to drag — and never clear them: clear one on a named row and part of that source stops reaching the totals. Nothing needs a total typed anywhere; the answer block does that. Do not type below row 26 — the log ends there, and anything under it is invisible to every number in the sheet.

What it does not do

It does not tell you whether those jobs made money. Revenue is not profit, and a channel can look rich in column I and still lose money once the roof is built — Module 6, Play 19 grades a finished job. It does not price anything, it does not forecast, and it cannot tell you what a channel would do with more money in it; that only comes from turning one up and counting again next year. And it does not follow a lead through your shop — it starts at the source and ends at the closed job.