Leads come in. Some close, most don't, and you couldn't say with confidence which sources actually pay off. So you keep spending on all of them, or you cut the wrong one based on a hunch. This guide gives you a no-cost way to fix that: a simple spreadsheet that tells you how fast you respond, which sources produce real customers, and where to put your effort next.
You do not need a CRM, an attribution platform, or any paid software. A spreadsheet you maintain in five minutes a day will outperform an expensive tool you ignore.
The blind spot: leads in, but no idea which sources are worth it
Most small businesses track lead volume and stop there. You know roughly how many inquiries came in last month. What you usually cannot answer:
- How long does a lead wait before you respond?
- Which sources send leads that actually buy, versus leads that waste an hour and vanish?
- How much business comes from free referrals you are not crediting anywhere?
Without those answers, every decision about marketing spend is a guess. You might pour money into a paid channel that produces lots of leads and few customers, while underinvesting in the word-of-mouth that quietly closes at a high rate.
The fix is not more data. It is the right four columns, filled in consistently, plus three calculations you run once a month. That is the whole system.
This post sits under the operations and measurement guide, which covers the broader habit of measuring what makes customer experience better. Here we focus on one job: knowing which leads are worth your time.
Build the lead log: source, first-response timestamp, qualified Y/N, outcome
The tracker is one row per lead. Resist the urge to add twenty columns. A tracker nobody maintains tells you nothing. Here are the columns that earn their place.
| Column | What goes in it | Why it matters |
|---|---|---|
| Date received | When the lead arrived | Anchors response time |
| Name / company | Identifier | So you can find the record |
| Source | Where it came from (one value from a fixed list) | The whole point of the system |
| First-response timestamp | Exact time you first replied | Powers median response time |
| Qualified? (Y/N) | Was this a real fit, not spam or a tire-kicker? | Separates "leads" from "real leads" |
| Outcome | Won / Lost / Open | Powers close rate |
| Value (optional) | Deal size if won | Lets you weight by revenue later |
Pick a fixed source list
The single most common mistake is freehand source labels. One row says "referral," the next says "word of mouth," a third says "friend of a client." Those are the same thing, but a spreadsheet treats them as three. Decide on a short list up front and only use those values. For example:
- Referral (free / word-of-mouth)
- Google organic search
- Google paid / ads
- Social
- Directory or marketplace listing
- Repeat customer
Use a dropdown (data validation in your spreadsheet) so you physically cannot type a new variant.
Define "qualified" before you start
"Qualified" needs a one-line rule you apply the same way every time. A reasonable rule: the lead is in your service area, has a real need you handle, and is not obviously spam or a price-only shopper you would decline. Write your definition at the top of the sheet so future-you and anyone else stays consistent.
Record the first-response timestamp honestly
This is the timestamp of your first real reply, not when you marked it "in progress." If a lead came in at 9:10am and you emailed back at 11:45am, that is the number. Be honest here; the value of the whole tracker depends on it.
The three calculations
Once you have a few weeks of rows, three numbers do the heavy lifting. All three run on the columns above. No add-ons.
1. Median response time
In plain language: sort all your first-response gaps from fastest to slowest and take the middle one.
For each lead, compute the gap between "Date received" (use the actual time, not just the date) and "First-response timestamp." Then take the median, not the average. Median is more honest here because one lead you forgot for three days will drag an average way up and hide the fact that you usually reply in twenty minutes. The median tells you what a typical lead actually experiences.
Why response time leads the list: a fast first reply is one of the clearest signals that you are easy to buy from. It is also something you control today, for free.
2. Source-to-close rate
In plain language: for each source, what share of qualified leads turned into customers?
For one source: count the won deals from that source, divide by the number of qualified leads from that source. Use qualified leads as the denominator, not all leads, so a source that sends a lot of spam is not unfairly punished and a source that sends a few perfect-fit leads gets proper credit.
Source-to-close rate = (Won leads from source) / (Qualified leads from source)
Do this per source and you can finally compare channels on the thing that matters: do they produce customers, not just inquiries.
3. Cost-free referral attribution
In plain language: count the business that arrived through referrals and repeat customers, which cost you nothing in ad spend.
Filter to the "Referral" and "Repeat customer" rows, count the wins, and (if you filled in the optional Value column) sum the revenue. This number is easy to overlook precisely because no invoice from an ad platform reminds you it exists. Once you see how much of your won business is free, you may decide the smartest move is to do more for the customers who refer you, rather than buying more cold leads.
For how these lead metrics fit alongside the rest of your dashboard, see the CX metrics to track.
Reading the numbers to decide where to spend effort
A filled-in tracker is only useful if you act on it. Here is how two different businesses might read the same three calculations.
Example A: a small agency comparing referral, paid, and organic
Imagine a four-person marketing agency. After a quarter, their tracker shows (these numbers are illustrative, not benchmarks):
| Source | Qualified leads | Won | Source-to-close | Notes |
|---|---|---|---|---|
| Referral | 8 | 5 | High | Slow to arrive, but closes well |
| Google paid | 30 | 3 | Low | High volume, few buyers |
| Google organic | 12 | 4 | Solid | Steady, fits well |
The read: paid is doing its job at the top (lots of qualified leads) but converting poorly, while referral and organic are quietly carrying the won business. Before the agency cuts paid, they should ask why those paid leads stall. Often the leads are fine and the handoff is the problem. More on that in the routing rule below.
Example B: a home-service business comparing Google vs. word-of-mouth
Picture a two-van plumbing business. Their tracker shows Google leads arriving in big numbers and word-of-mouth arriving in a trickle. But:
- Median response time on Google leads is over an hour, because they come in during jobs.
- Word-of-mouth leads close at a much higher rate.
The read: the Google channel is not bad, but the slow first response is leaking it. The fix is operational (a faster way to acknowledge inquiries on site) before it is a spend decision. The tracker pointed at a behavior they could change for free.
In both cases, the spreadsheet did not just rank channels. It separated "the source is weak" from "we are mishandling a good source." That distinction is where the routing rule comes in.
When a strong source converts poorly: route up to the website pillar
Here is the rule worth memorizing:
A high-intent source plus a poor close rate usually means the page is the problem, not the source.
If a source sends genuinely qualified leads (people actively searching for what you sell, or warm referrals) and they still do not convert, the leak is rarely the channel. It is more often what happens after the click: a confusing service page, an unclear offer, a form that asks for too much, or a slow first response.
Use this quick routing map:
| What the tracker shows | Likely cause | Where to look |
|---|---|---|
| Low qualified-lead count from a source | Targeting / the source itself | Reconsider spend on that source |
| Good qualified leads, slow median response | Your follow-up process | Tighten response workflow |
| Good qualified leads, fast response, still low close | The page or offer | Your website experience |
When you land in that bottom row, stop blaming the channel and look at the page. Walk through the conversion diagnostic tool to pressure-test the page that high-intent traffic lands on, and read how to diagnose why conversion dropped step by step. The tracker tells you which page deserves the scrutiny; the diagnostic tells you what to fix on it.
Download and run the lead-tracking spreadsheet
The template is a single sheet with the columns above pre-built, a source dropdown, a "qualified" definition box, and the three calculations already wired so they update as you add rows.
Download the lead-tracking spreadsheet from the Templates library, then run it like this.
Walk one row end to end
Say a lead arrives Tuesday at 9:10am. You fill in:
- Date received: Tue 9:10am
- Name / company: Riverside Cafe (illustrative)
- Source: Google paid (chosen from the dropdown)
- First-response timestamp: Tue 9:34am (your actual first reply)
- Qualified?: Y (in your area, real need, not spam)
- Outcome: starts as Open; you change it to Won three days later
- Value: filled in when won
That single row now feeds all three calculations: its 24-minute gap goes into the median, its Won status counts toward Google paid's source-to-close rate, and because it is not a referral it does not touch the free-referral total. Add a row per lead, and at month's end the three numbers read themselves.
Keep it maintainable
The reason to stay in a spreadsheet is not just cost. It is survival. A tracker you can update in the gap between calls is a tracker you will actually keep. If you find yourself wanting fifteen new columns, that is a signal to stop, not to expand. Ship the four core columns first, live with them for a month, and only add complexity if a real decision demands it.
In Plain English
This is a lightweight, spreadsheet-only system for answering three questions every small business should be able to answer: how fast do we respond to leads, which sources produce real customers, and how much of our business is free referrals.
- What it means: one row per lead, four core columns, and three monthly calculations (median response time, source-to-close rate, and free-referral attribution).
- Who it helps: any owner or operator who gets inquiries from more than one place and is guessing about where to spend. Agencies, home-service businesses, clinics, consultants, e-commerce, and SaaS all use the same structure.
- When to use it: as soon as you have leads coming from two or more sources and any meaningful marketing effort behind them.
- What to do next: download the spreadsheet, set your fixed source list and your "qualified" definition, and start logging today. After one month, read the three numbers. If a high-intent source closes poorly, route the problem up to your page, not your budget.
The most useful insight this system surfaces is usually not "cut channel X." It is "this good channel is being wasted by something we control." That is the kind of finding that helps you outclimb competitors who are still guessing.
For the bigger picture on building measurement habits that stick, return to the operations and measurement guide. To put your lead metrics in context with everything else worth watching, see the CX metrics to track.