Where the leads leak

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.

Download the sample file

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.

Each pipe is a form. The wider the water, the more leads, and every pipe uses the same scale. Gray is leads dropping out on purpose, like guide downloads that were never going to qualify. Red is a leak, where one system kept taking leads in and the next one quit receiving them. Tap any pipe for its week-by-week numbers.

LeadsDropped out on purposeLeaked

The leaks

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.

Week by week

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.

Two leaks the weekly numbers miss

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.

Qualified leads rejected by the CRM because Country is blank

56 leadsabout 9 opportunitiesall quarterFixed by: Web team, then marketing ops

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.

Leads in the Mountain territory go to a rep who has left

73 leadsabout 18 opportunitiessince July 15Fixed by: Sales ops

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.

The Lead Source field went missing

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.

Leads reaching the CRM each week vs. the ones with a Lead Source
075150Jul 1Jul 22Aug 12Sep 2Sep 23source blank from Aug 26 All leads in the CRM, week of Jul 1: 116All leads in the CRM, week of Jul 8: 125All leads in the CRM, week of Jul 15: 115All leads in the CRM, week of Jul 22: 105All leads in the CRM, week of Jul 29: 104All leads in the CRM, week of Aug 5: 89All leads in the CRM, week of Aug 12: 90All leads in the CRM, week of Aug 19: 73All leads in the CRM, week of Aug 26: 70All leads in the CRM, week of Sep 2: 63All leads in the CRM, week of Sep 9: 86All leads in the CRM, week of Sep 16: 69All leads in the CRM, week of Sep 23: 66Leads with a source, week of Jul 1: 116Leads with a source, week of Jul 8: 125Leads with a source, week of Jul 15: 115Leads with a source, week of Jul 22: 105Leads with a source, week of Jul 29: 104Leads with a source, week of Aug 5: 89Leads with a source, week of Aug 12: 90Leads with a source, week of Aug 19: 73Leads with a source, week of Aug 26: 19Leads with a source, week of Sep 2: 29Leads with a source, week of Sep 9: 25Leads with a source, week of Sep 16: 28Leads with a source, week of Sep 23: 15

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.

Checking the first explanation

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.

Leads fell 81% from July to September

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.

Paid social spend fell 25%

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.

Lower spend explains most of it

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.

Demand is softening

Reported YesRebuilt Submissions down 8%Doesn't hold up

People kept filling out forms at about the same rate. The leads disappeared after that.

Where the drop actually came from

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.

CauseLeads
Lead Source went blank (the leads still showed up)194
Broken hand-offs: webinar sync, ad connector, pricing button213
Lower ad spend24
Everything else (September is a day shorter, plus normal noise)−12
Total drop in leads with a source, July to September419

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.

What the leaks cost

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.

LeakLeadsOpportunitiesPipelineFixed by
The "Talk to sales" button on the pricing page leads nowhere18849$1.36MWeb team
Leads in the Mountain territory go to a rep who has left7318$497KSales ops
Webinar sign-up form stopped syncing to marketing automation71914$395KMarketing ops
Qualified leads rejected by the CRM because Country is blank569$238KWeb team, then marketing ops
The ad platform connector stopped delivering lead-form fills2537$194KMarketing ops
All five1,28998$2.68M

For scale, the funnel still produced 272 opportunities. The leaks cost it roughly 98 more.

The checks, as queries

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;

What you'd get

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.