I Built a Weekly Report Automation with Zapier — The Exact Setup
Every Monday used to start the same way.
Open the spreadsheet. Pull last week's numbers. Open the email tool. Check the stats. Open the CRM. Count the new leads. Copy everything into a document. Format it. Send it to myself so I could review it in the week.
One hour. Every Monday. Without fail.
That hour is gone now. Zapier does it automatically every Sunday night and sends me a formatted summary before 7am on Monday.
Here's the exact setup.
What the Automation Does
Every Sunday at 11pm, Zapier:
- Pulls new leads added in the past 7 days from my CRM
- Gets the email open rate from my email tool for the week's campaigns
- Counts the number of new subscribers from a Google Sheet I keep updated
- Compiles all this into a formatted summary
- Sends it to my Gmail so it's waiting when I start Monday
The whole thing runs without me touching anything. The report is consistent, complete, and ready before I've made coffee.
Why This Matters for Small Business Owners
Reporting is one of those tasks that is both important and genuinely miserable.
Important because knowing your numbers weekly keeps you from being surprised at the end of a quarter. Miserable because pulling data manually from multiple places is tedious, repetitive, and exactly the kind of work that erodes the mental energy you need for real decisions.
Automation doesn't make the data more insightful. But it removes the friction of collecting it so you actually do it every week instead of every time you remember.
The Exact Zapier Setup (Step by Step)
Before you start: You'll need a free or paid Zapier account, and access to the apps you want to pull data from.
Zap 1: The Weekly Trigger
Trigger: Schedule by Zapier → Every Week → Sunday → 11:00 PM
This is the simplest trigger in Zapier. No app connection needed. It fires at the time you set, every week.
Step 2: Get New Leads
Action: Your CRM (HubSpot, Pipedrive, etc.) → Find Records → Filter by date created: last 7 days
This pulls a count of new leads added in the past week. If your CRM isn't connected to Zapier, a Google Sheet where you manually log leads works just as well — Zapier reads the new rows added in the past 7 days.
Step 3: Get Email Stats
Action: Your email tool (Mailchimp, Brevo, ConvertKit, etc.) → Get Campaign Stats → Last Campaign
This pulls the open rate, click rate, and subscriber count from your most recent email campaign.
Step 4: Format the Summary
Action: Formatter by Zapier → Text → Compose
This is where the magic happens. Zapier's Formatter lets you create a text template with dynamic fields. Mine looks like this:
Weekly Business Summary — [Week of Sunday Date]
NEW LEADS: [Count from CRM]
EMAIL OPEN RATE: [Open rate from email tool]
NEW SUBSCRIBERS: [Count from Google Sheet]
Generated automatically by Zapier.Zapier fills in the fields automatically from the previous steps.
Step 5: Send the Email
Action: Gmail → Send Email → To: your email address → Subject: Weekly Summary [Date] → Body: the formatted text from Step 4
Done.
The Results After 3 Months
I've been running this for about three months. Some observations:
It runs every week without fail. I don't have to remember to do it, create it for a holiday, or catch up on a week I skipped.
The consistency is unexpectedly valuable. Because the format is exactly the same every week, I can spot trends immediately. Last week's numbers look wrong? I notice because I'm comparing to an identical format from the previous 12 weeks.
I look at it every Monday. When I was creating it manually, I sometimes skipped it — the friction of creation made me rationalise that "I already know roughly how things are going." Now that it arrives in my inbox, I always read it.
It evolved. I added a step after the first month: a note field where I manually type one thing I want to focus on this week, which Zapier adds to the report. That takes me 60 seconds and makes the report feel more useful.
Common Questions
What if I don't have a CRM? Start with Google Sheets. Log your leads manually (or use a Zapier automation to add them from your contact form). The weekly report Zap reads from Sheets just as easily.
What if my email tool isn't on Zapier? Zapier integrates with over 7,000 apps. Check zapier.com/apps to find yours. If it's not there, export your weekly stats manually to Google Sheets and pull from there.
What if the numbers are wrong? Check each Zap step individually — Zapier lets you run test steps to see what each one returns. Almost every data error comes from a filter being set up incorrectly.
Can I make it more complex? Yes. You can add conditional logic ("if leads > 20, add a congratulations line"), pull from more data sources, send to Slack instead of email, or create a Google Doc instead of an email. Start simple, then add.
The Surprising Discovery
The value of this automation wasn't the hour I got back on Monday mornings.
It was the habit it created.
Because the report arrives reliably every week, I developed a weekly review habit I'd never managed to build manually. The automation created the consistency that created the habit.
That's the underrated value of business automation. It's not just efficiency. It's making good habits easier than bad ones.
Build Yours This Week
The basic version of this automation takes about 45 minutes to set up. The time to build it is repaid in the first Monday it runs.
Start with the simplest version: schedule trigger → one data source → send email. Add complexity once the basic version is working.
Watch me build this Zapier automation live on the Automate Lane YouTube channel: Automate Lane on YouTube
Automate Lane shows small business owners how to automate the repetitive work so you can focus on what actually matters. Subscribe for weekly automation guides.
Affiliate Disclosure
This article contains affiliate links. If you purchase through these links, Automate Lane may earn a small commission at no extra cost to you. We only recommend tools we have personally tested and use ourselves.