From 50 people and spreadsheets to a self-running lead platform

Summary

Lead distribution was run by 40–50 people moving data through Google Sheets, one client at a time. Each additional client added more manual work. I reframed the problem from "make spreadsheets faster" to "let the business grow clients without growing headcount" and led the build of a Lead Management System (LMS). Ops teams set up a client's mapping once, choose real-time or scheduled delivery, and the system handles the rest, with masking, deduplication, exception tracking and audit logs built in.

Context

KollegeApply generates student leads (website, Meta Ads, Google Ads, and external datasets of 5 lakh+ raw records a month) and delivers them to colleges and education partners. Every client has its own field mapping, course rules, daily caps and campaign limits, delivered via API or Google Sheets.

Admissions are seasonal. In April–May, volume and client demand peak at the same time. That is exactly when a manual process breaks.

The problem

Working with the operations team, I mapped the workflow end to end: export → clean → filter → map → send → verify → report, repeated for every client. One person could handle roughly five clients. That gave me seven problems, which grouped into four themes:

1. Scale. Capacity grew linearly with people. With 400–500 regular clients, that meant 40–50 people.
2. Risk. Names, phone numbers and emails sat unmasked in shared sheets. Anyone with the link could see them.
3. No traceability. Nobody could answer "where did this lead come from, who received it, and why did delivery fail?" When an employee left, knowledge of their clients left with them.
4. Slow everything else. Reports were built from formula-heavy sheets that froze at scale. Communication (email, SMS, WhatsApp) caused duplicate sends. Even changing a website banner needed an engineering ticket and took 2–3 days.

Options considered

• Optimize the spreadsheet workflow (scripts, macros, better templates)

  Cheapest and fastest to start. But it kept humans in the loop for every delivery, did nothing for data security, and Sheets was already freezing at our volumes. Rejected: it would buy months, not years.

• Buy an off-the-shelf CRM / lead-distribution tool

  I evaluated ready-made CRM and lead-distribution tools first. Our per-client course mappings, campaign caps and Sheets-based delivery for smaller colleges didn't fit standard tools without heavy customization, and per-seat/per-lead pricing scaled badly at our volume. Rejected.

• Build a configuration-led internal platform

  Chosen

  Higher upfront cost with a 4-engineer team, but it encoded our specific rules, let us own the data and security model, and made the marginal cost of a new client close to zero. Chosen.

What we built

I owned problem discovery, requirements, prioritization, workflow design, QA sign-off and release planning. Engineering owned the architecture and implementation.

1. Configure once, run continuously. Ops sets a client's mapping and chooses real-time or scheduled delivery. After that, the system distributes leads on its own. The ops job changed from doing the delivery to checking it, about 10 minutes of monitoring a day.

2. Masking by default. PII is masked in the interface (t************l, ******7111). Being able to open a record no longer means seeing the customer's details.

3. A full lead journey. Every record carries its status history: received → validated → mapped → distribution attempt → outcome. User-level logs record who did what and when.

4. Exceptions as first-class states. Instead of "sent / not sent", the system distinguishes 10 states: success, duplicate, pending, failed, error, unknown, killed, daily limit exhausted, campaign limit exceeded, and course mapping error. Each state needs a different fix, and error alerts go out by email.

5. Communication tracking. Previous sends and blocked contacts are recorded in the workflow, which reduced duplicate messages.

6. Self-serve website inventory. Banners, sponsored listings and featured listings moved into the CMS, so ops publishes them directly.

Trade-offs

Execution

1. Sep 2025

   Discovery

   Mapped the 7-step manual workflow, sized the problem (≈5 clients per person, 1–2h per delivery), and evaluated ready-made tools.

2. Sep 2025 – Jan 2026

   Core platform

   Centralized distribution (sources in, API and Sheets out), configurable client mapping, PII masking, status history and audit logs.

3. By Jan 2026

   Exception management

   10 status types, duplicate detection, email error alerts.

4. Feb – Mar 2026

   Full rollout

   Remaining workflows, reporting and self-serve CMS inventory; the platform was largely complete by March.

5. Apr – May 2026

   Peak season

   12.4M+ distribution records in May alone, across 39K+ batches, with no added headcount.

6. Ongoing

   Advanced features

   Enhancements and advanced functionality on top of the core platform.

The engineering org had about 12 developers spread across 7 products (website, CMS, LMS, FMS, EnrollBee, B2B systems, DevOps). The LMS had at most 4 of them at a time, so most of my work was ruthless sequencing and aligning sales and ops on what would ship this season versus next.

Results

• 78.3M+ lead-distribution records across 268,536 batches. One lead sent to several clients counts as several records.
• 37.6M+ records successfully delivered.
• 23M+ duplicate distributions caught and blocked, so clients never paid for or chased repeat leads.
• 12.4M+ records in May, the peak admission month, with no added headcount.
• Reporting moved from manual spreadsheet work to system records, and manual report-building effort for the automated reports dropped to near zero.
• ~80% of clients retained across admission cycles.

What I'd do differently

• Define success metrics on day one. I had volume and time metrics, but no baseline for lead quality (contact rate and conversion at the client). That would have let me prove value to clients, not just to ops.
• Investigate "unknown" and "pending" earlier. About 3.7M records sat in these ambiguous states. Every unexplained state is a support ticket waiting to happen.
• Build client self-serve reporting sooner. Clients still asked our team for numbers they could have pulled themselves.

If I built this today with AI

The rules engine would stay deterministic, because routing millions of leads needs predictable behavior. I would add AI where people still spend judgment:

1. Mapping assistant. An LLM proposes the field and course mapping for a new client from their sample file. A human approves it, and the system validates it against the schema. Target: onboarding drops from hours to minutes.
2. Exception triage. Classify and summarize error clusters ("72% of today's mapping errors come from one client's renamed course field") and suggest the fix.
3. Lead-quality scoring. Predict a lead's likelihood of conversion per client from historical outcomes.

How I'd evaluate it: mapping accuracy against a labeled set of past client configurations (target ≥95% field-level accuracy before auto-suggesting), false-positive rate for triage alerts, cost per onboarding, and a human-approval gate until accuracy is proven. Failure modes I'd watch: confidently wrong course mappings, and PII leaking into prompts. Masking happens before any model call.