Tallowtree's forms took 4,234 submissions last quarter. Sales felt leads were thin, and the working theory was the cut in ad spend. Nobody had followed the same lead from one system to the next yet, so I did, week by week.
Your file stays in this browser tab. Nothing gets uploaded or saved, and it's gone when you close the tab.
The file needs one row per week. Put the week's start date first, then a column naming the form or source if you have more than one, then a column for each system with that week's count, in the order a lead hits them. When in doubt, download the sample and copy its layout.
LeadsDropped out on purposeLeaked
I call it a leak when a hand-off drops below half its normal rate and stays down. A leak usually starts on a specific day, which helps, because the first thing to ask whoever owns that system is what changed that week. None of these take anyone being careless. Fields get renamed and logins expire, and the systems don't say a word when it happens.
Red cells are weeks where the hand-off broke. The usual share is the typical week outside the leak, so one odd week can't drag it around.
Weekly counts are great at catching something that suddenly stops. They're bad at two things: a leak that was already there before you started counting, and a leak spread thin across every form. Finding those takes the raw records, so I went through them too.
Why the pipes miss itIt's been dropping about 5% of qualified leads since day one of the quarter. It never changes week to week, so in the counts it just looks like the normal rate.
How I found itPulled every qualified lead with a sync error and grouped them by the error message.
FixMake Country required on the forms where it's optional, then fix the 56 rejected records and resync them.
Why the pipes miss itIt takes about 6% from every form. That's nowhere near enough to cut a single hand-off in half, so the pipes look fine.
How I found itMatched each lead's owner against the user list and kept the ones who'd been deactivated.
FixReassign the 73 stuck leads today. Then give Mountain an owner and set routing to fall back to a team queue whenever an owner gets deactivated, so this can't happen quietly again.
On August 26 the demo form was rebuilt, and the hidden field that stamps Lead Source didn't make the move. It's easy to miss, because nothing errors and the leads still arrive. Every demo request after that landed in the CRM with the source empty, 238 of them by the end of the quarter. They're real leads and sales got them. But any report that splits leads by source shows Website falling off a cliff.
All leads in the CRM Leads with a source
Nothing was lost here, so I don't count it as a leak. Put the hidden field back and backfill the source on those 238 leads. Until that's done, nobody should read a drop by source as a drop in demand.
The first explanation was lower ad spend and softer demand. It was a fair guess, since spend did fall. Before building on a number, I rebuild it from the same systems. One part held up and three didn't.
Reported 81%Rebuilt 81%Matches
Same report, same months. It counts leads by Lead Source, though, so the 238 leads with a blank source aren't in it.
Reported 25%Rebuilt 16%Doesn't match
The 25% came from the budget plan. The ad platform's own export has actual spend down 16%, starting September 8.
Reported Most of itRebuilt 24 of 419 leadsDoesn't hold up
Fewer ad fills cost about 24 leads. Paid social leads in the CRM went from 81 to zero, because the connector stopped working on August 19. After that date, spend wasn't the cause.
Reported YesRebuilt Submissions down 8%Doesn't hold up
People kept filling out forms at about the same rate. The leads disappeared after that.
The report had leads with a source going from 519 in July to 100 in September. Split by cause, those 419 leads break down like this.
| Cause | Leads | |
|---|---|---|
| Lead Source went blank (the leads still showed up) | 194 | |
| Broken hand-offs: webinar sync, ad connector, pricing button | 213 | |
| Lower ad spend | 24 | |
| Everything else (September is a day shorter, plus normal noise) | −12 | |
| Total drop in leads with a source, July to September | 419 |
Spend accounts for 24 of them. Nearly all the rest is a missing field and three broken hand-offs, and you could fix every one of those in a week. For the broken hand-offs, I assumed each form would have kept converting at the rate it ran before it broke.
A lost lead is worth roughly what leads like it turned into when nothing broke. I used each form's own conversion rate from the leads that went through cleanly last quarter, and Tallowtree's average deal of $28K. Treat these as estimates. Some of those people came back through another door.
| Leak | Leads | Opportunities | Pipeline | Fixed by |
|---|---|---|---|---|
| The "Talk to sales" button on the pricing page leads nowhere | 188 | 49 | $1.36M | Web team |
| Leads in the Mountain territory go to a rep who has left | 73 | 18 | $497K | Sales ops |
| Webinar sign-up form stopped syncing to marketing automation | 719 | 14 | $395K | Marketing ops |
| Qualified leads rejected by the CRM because Country is blank | 56 | 9 | $238K | Web team, then marketing ops |
| The ad platform connector stopped delivering lead-form fills | 253 | 7 | $194K | Marketing ops |
| All five | 1,289 | 98 | $2.68M |
For scale, the funnel still produced 272 opportunities. The leaks cost it roughly 98 more.
None of this needs special tools. Every finding comes from a query your team can rerun, and these three do most of the work.
-- Form submissions against leads that reached the CRM, by form and week
select f.form_name,
date_trunc('week', f.submitted_at) as week,
count(*) as submitted,
count(l.lead_id) as in_crm,
count(*) - count(l.lead_id) as missing
from form_submissions f
left join crm_leads l
on lower(l.email) = lower(f.email)
and l.created_at between f.submitted_at and f.submitted_at + interval '1 day'
group by 1, 2
order by week desc, missing desc;
-- Share of each form's leads with no Lead Source, by day
select form_name, created_date,
avg(iff(lead_source is null or lead_source = '', 1, 0)) as blank_share
from crm_leads
group by 1, 2
order by 1, 2;
-- Open leads owned by people who can no longer work them select l.territory, u.name as owner, count(*) as leads, min(l.created_date) as oldest from crm_leads l join users u on u.user_id = l.owner_id where u.is_active = false and l.status = 'Open' group by 1, 2;
If you want this run on your own data, I take on a few outside projects. I follow your leads through every system they touch until I find where they leak, then hand each fix to the team that owns it. $1,200 fixed, scoped in writing before I start. Hire me on Upwork.