Lead Distribution Automation

Overview

Every morning the sales team needs fresh leads in each agent's list. A planner writes the day's plan in a Google Sheet: which CRM filter to pull from, which agents or team get the leads, and how many each. I built the Apps Script that takes that plan from there, sends the leads through the CRM, and tells the team how it went.

The problem

Built by hand, every morning. Someone opened the CRM's Bulk Distribute window, picked each filter, typed the leads per agent, and chose the role and teams, copying it all from the plan.
Then a 2.5+ hour queue. Pressing Distribute put the job in a queue, so the leads reached agents 2.5 hours or more later.
It couldn't start early. Agents have to be logged in to receive leads, and the workday starts at 10 am, so the queue ate the first hours of the selling day.
Easy to get wrong. A copy-paste slip sent leads to the wrong team, and nobody had one place to see which runs worked and which failed.

The solution

The script finds today's plan sheet by its date, reads every row, and runs it through the CRM. Three kinds of distribution run on their own each day:

Special, at 10:25. Leads go to named agents, a few each, from the filters the planner picked.
Regular, at 10:30. Leads go to whole agent groups. Both start after agents are logged in at 10, so every lead lands with someone who can call it.
Autodial, at 2 pm. Leads from one filter go into the afternoon Autodial campaign. It skips Fridays.

The distribution report email — The report, a few minutes after each run. How many distributions ran, which went out, which failed and why, and which were skipped. It also goes to Telegram, and if there is no plan for the day, the team gets a “No plan” email instead of silence.

How it grew

It started as one script for one job and grew a step at a time, each step because the last one left a gap.

1
One job: special leads
The first script took over the morning's main task: read the plan, send leads to named agents, and email a report of what went out.
2
Whole teams, one scheduler
Regular distribution to agent groups came next, and one scheduler started running both at their own times, so nobody had to start anything.
3
Minutes, not a queue
Both runs moved to the CRM's direct bulk update instead of the queue behind the Bulk Distribute window, so leads land in minutes, not 2.5+ hours. The script rebuilds each saved filter's conditions itself, so the leads sent are exactly the ones the planner sees.
4
Autodial and pulling leads back
The afternoon Autodial campaign joined, plus a script that pulls leads back from agents into the shared pool, filter by filter. A 6 am sync copies every CRM filter into the sheet, so plans can use filter names.
5
It heals itself
If the CRM hiccups, the run tries again in 15 minutes, up to 3 times a day, and repeats only the rows that failed. The email and Telegram report show when the next try is.

Decisions and trade-offs

Keep the plan in Google Sheets

Planners already wrote the plan there. The script reads their sheet as it is, so nobody had to learn a new tool.

Exact times that renew themselves

Apps Script's daily triggers only promise a time within an hour. Each run sets up the next day's run at the exact minute, so leads land right after agents log in, not some time later.

Retry only what can succeed

A network blip, a timeout or a CRM server error is retried later the same day. A wrong filter or too few leads would fail the same way again, so it goes in the report instead.

Skip a bad row, not the whole run

A row with no valid agent IDs is skipped and named in the report, and every other row still goes out.

What I learned

Automating the task was the easy part. The real work was the report: when a job runs itself, people trust it only if it tells them plainly what happened.

Back to sample work