If you run more than one marketing channel, you already know your monthly spend and your monthly revenue. What you probably cannot say is which channel produced which dollar. You do not need dedicated attribution software to track marketing ROI. You need four small tables, one attribution rule you apply consistently, and about thirty minutes a week.
Everything below is operational: the exact columns, one worked example, decision rules for what to do when a channel looks bad, and the point where a spreadsheet genuinely stops being the right tool.
The Four Numbers You Need to Track Marketing ROI
ROI reporting usually breaks for one reason: people record spend and revenue but nothing in between. When the two ends do not line up, there is no way to tell whether the problem is the channel, the offer, or your follow-up.
Record four things, per channel, per month:
- Spend — cash out, plus your own time at an hourly rate you set once and reuse everywhere.
- Leads — anyone who gave you contact details and a reason to reply.
- Opportunities — leads who reached a stage you define, such as a booked call or a sent proposal.
- Revenue — cash actually collected, net of refunds, tagged back to the lead that produced it.
Write your definitions of lead and opportunity into the spreadsheet itself. If a sent proposal counts as an opportunity in March and a discovery call counts in April, your trend lines mean nothing. Definitions can be crude. They cannot move.
Pick One Attribution Rule and Write It Down
Attribution is a choice, not a fact. Three rules are workable by hand:
- First touch. Credit the channel that produced the first contact. Best when buyers find you once and decide quickly.
- Last touch. Credit the channel that immediately preceded the inquiry. Easiest to collect from form data, but it over-credits branded search and direct visits.
- Manual. Ask the buyer how they found you and record the answer verbatim in one field. Least elegant, most useful for service businesses.
Decision rule: if your typical sale closes in under a month, use first touch. If sales cycles run several months and buyers see you repeatedly before reaching out, use manual with one primary source field and a free-text notes field for everything else. Never mix rules inside one report, and never change the rule mid-quarter.
Put the rule in a labeled cell at the top of your summary tab, with the date you adopted it. When a number looks strange six months from now, that cell is what saves the analysis.
Build It: Four Tabs and One Join Key
The whole system is four tabs joined by a short source code such as seo-local, referral, paid-social or newsletter. Use lowercase, no spaces, and keep a list of allowed codes on a fifth tab so nobody invents new ones.
Tab 1, Spend log. Columns: Month, Channel, Source code, Cash spend, Hours worked, Hourly rate, Total spend (cash plus hours times rate).
Tab 2, Lead log. Columns: Lead ID, Date received, Name, Source code, Attribution note, Current stage, Date became opportunity, Date closed or lost.
Tab 3, Revenue log. Columns: Date collected, Lead ID, Amount collected, Refunds, Net revenue. One row per payment, not per client, so installments and retainers stay accurate.
Tab 4, Summary. One row per source code per month. Every cell is a SUMIFS or COUNTIFS against the first three tabs. Nothing is typed twice. Calculate five metrics: cost per lead, lead-to-opportunity rate, opportunity-to-won rate, revenue per dollar spent, and days from lead to first payment.
Revenue per dollar spent is the number you act on. Net revenue divided by total spend. A value of 1.0 means the channel returned its cost and nothing more. If you would rather start from something prebuilt than wire the formula layer yourself, the Marketing ROI and Lead Source Dashboard exists for exactly this job.
A Worked Example
The figures below are invented to demonstrate the arithmetic. They are not results from any real business and should not be read as benchmarks. Assume a small bookkeeping studio reviewing a 90-day window across four channels, using manual attribution.
- Local SEO and content. Spend $600. Leads 12. Opportunities 5. Closed-won deals 3. Net revenue $10,800.
- Referral thank-you program. Spend $150. Leads 5. Opportunities 4. Closed-won deals 2. Net revenue $9,000.
- Paid social. Spend $900. Leads 20. Opportunities 4. Closed-won deals 1. Net revenue $2,400.
- Email newsletter. Spend $240 ($40 in tools plus 4 hours at $50). Leads 11. Opportunities 3. Closed-won deals 0. Net revenue $0.
Cost per lead lands at $50, $30, $45 and $22. Judged on that metric alone, the newsletter looks like the best channel and local SEO looks expensive. Revenue per dollar spent tells the opposite story: 18.0, 60.0, 2.7 and 0.0.
Now read the middle of the funnel. Paid social generated the most leads and the worst lead-to-opportunity rate at 20 percent, which points at lead quality or the intake conversation, not at the ad budget. The referral program converted 4 of 5 leads into opportunities, which is the pattern that justifies spending more time asking for referrals. The newsletter produced opportunities but no closes inside the window, which is a reason to extend the observation period rather than to shut it down.
That is the entire value of the exercise. Cost per lead ranked the channels backwards. The four-table view did not.
Decision Rules for Acting on the Numbers
- Never judge a channel on cost per lead alone. Use revenue per dollar spent, with the funnel rates as the diagnostic.
- Set a minimum before you decide. Wait for at least 10 leads from a channel or one full sales cycle, whichever is longer. Killing a channel on three data points is guessing.
- Below 1.0 after two full sales cycles: cut the spend or rebuild the offer behind it. Do not keep paying for a channel because it feels necessary.
- Between 1.0 and 2.0: leave the budget alone and fix the weakest funnel step instead. Usually it is response time or the opportunity stage, not traffic.
- Above 3.0: increase spend gradually, not more than about 30 percent per month, and recheck after each step. Returns on small channels rarely hold when you scale them quickly.
- Unknown source above 20 percent of leads: stop drawing conclusions and fix intake first.
What to Do When It Goes Wrong
Most leads have no source. Add one required question to your form and one scripted question to your intake call. Backfilling from memory is worse than leaving the field blank.
Long sales cycles distort the months. Report by lead cohort, not by revenue month. A deal from a February lead belongs to February spend even if it pays in June. Keep both views if you need cash-basis reporting.
Shared costs. Split a retainer or tool cost across channels by time spent, document the split, and reuse it. Consistency matters more than precision.
One channel is really two. If organic search brings both branded and problem-led traffic, split the source codes. Aggregated channels hide the useful half.
Where a Spreadsheet Stops Being Enough
Be honest about the ceiling. Manual entry breaks down somewhere around a few hundred leads a month, or as soon as more than two people edit the file. You should move to a CRM plus a reporting layer when you need multi-touch credit across many sessions, ad-level or keyword-level detail, subscription cohorts and lifetime value, or an audit trail because someone else spends the budget.
Until then, the spreadsheet wins on a point that software cannot give you: you know exactly how every number was produced. Most owners who track marketing ROI badly are not missing a tool. They are missing the middle two columns.
If you want the structure without building it from scratch, the lead source dashboard is the direct next step, and you can browse the rest of the Cursiqa workbooks or read more operational walkthroughs on the blog first. Either way, start the spend log this week. The system only becomes useful once it has a quarter of honest data in it.