How to automate a daily lead report
A daily lead report can be taken off someone's plate completely. Make pulls new leads from the CRM into a Google Sheet every day, a formula assigns each lead its source, and a script builds a weekly report by day, course and source, with a plan and completion percentage.
An online academy's marketer spent 30–35 minutes a day on the report: going through CRM pipelines, filtering the day's leads and counting them by source. And again for each of 8 courses.
Now Make moves leads to Google Sheets every day, each lead's source is set automatically, and every Monday a script builds a new week with the plan and completion percentage. The marketer got back 130+ hours a year.
A manual report is always late
While someone filters the CRM and counts leads by hand, half an hour a day goes by, and ad decisions are made on yesterday's numbers or at the end of the month.
You need this if…
- Someone counts leads by hand daily or weekly
- You have several courses, pipelines or products
- You want to see plan vs actual for leads every day
- It's unclear which channel is behind plan
What an automated report affects
- Team time. 30–35 minutes a day go back to more important work.
- Ad budget. You see which channel is behind plan and adjust ads the same day.
- Accuracy. No manual counting mistakes or forgotten filters.
- History. Weeks are kept one below another for easy comparison.
Pitfalls
- Leads without tags. If a request has no UTM, the lead fits no source. Show these as a separate row instead of losing them.
- Messy source names. “fb”, “facebook” and “Meta” should count as one source. You need a clear classification table.
- Several pipelines. With many courses, each CRM pipeline must land in its own section of the report.
- A report that breaks on edits. If formulas build the week and someone deletes one, the report fails. It's more reliable when a script creates the week.
How the report is built
Solution options
Reports inside the CRMfast but limited
Most CRMs show deal counts but rarely give a source, course and plan-vs-actual breakdown in one view.
A BI dashboardpowerful but complex
Looker Studio or Power BI visualise data well but need separate setup and maintenance.
Make + Google Sheets + scriptthe sweet spot
Leads land in the sheet daily, the source is set automatically, and a script builds the weekly report with a plan. Transparent and flexible.
130+ hours a year back to the marketer
For an online academy I automated daily lead reporting: Make moves leads from the CRM to a sheet every day, each lead gets one of 17 sources automatically, and every Monday a script creates a new week broken down by 8 courses with plan and completion percentage.
Read the full case study →FAQ
How do I automatically export CRM leads to Google Sheets?
With Make: a scheduled scenario pulls new leads from the CRM and writes them to the sheet with date, source, course and UTM tags.
How do I assign the lead source automatically?
By UTM tags and request fields: a formula or script matches them against a rules table. Leads without tags are shown separately.
Can I see plan vs actual for leads every day?
Yes. A plan per course is added to the report, and the completion percentage updates with every refresh.
Do I need a developer for this?
It takes technical setup of Make, formulas and a Google Apps Script. I build it end to end for your CRM and structure.
What if my CRM has several pipelines?
Set up a separate scenario or filter for each pipeline so leads land in their own section of the report.
Want the report to build itself?
Describe your funnel and the result you want. I'll tell you how to build it with the tools you already use.
Discuss your task