Case 06 · Automation · Operations · Marketing
Every report answers one specific person's question, in their own words.
Instead of a dashboard someone has to open, I designed reports and alerts that arrive by email at the useful moment: calculated live, with declared sources, without contradictory figures and with an action at the end. The golden rule for alerts is to stay quiet when everything is fine.
- Project
- Automated email reports and alerts
- Context
- Car dealership, ZoDesk, multi-country e-commerce and a service CRM
- By Antonio Duque
- Reviewed against the code and documentation on
01
Context and need
Leadership wanted certainty, not metrics; marketing needed to know which cars the ad budget went to; sales needed to know which leads were waiting for a person; and nobody noticed when an integration stopped working.
Platform numbers weren't reliable on their own either: they had to be checked against the forms actually received.
02
My responsibility
- I defined each report from its recipient's question: format, language, frequency, sources and validation. I directed the build and reviewed the results with the team.
03
Strategy and decisions
- One report per question and per person: leadership gets purchases versus sales without jargon; marketing gets spend and margin per vehicle.
- Count leads from real website forms, not from what the ad platform reports.
- Windows that respect how data closes: spend is reported up to yesterday, and the monthly close goes out on the 5th, once platforms finish adjusting.
- Alerts with memory: one notice per problem, a short window so old cases aren't revived, and a retry if the email fails.
04
What I built
- Ad spend split between buying and selling cars, with a daily version for me, a weekly one for leadership and a monthly campaign-by-campaign close.
- A weekly ROI report that matches ad spend per car with recorded sales to read margin after advertising.
- A plain-language weekly report for leadership, calculated live, and a purchasing status four times a day formatted to paste into WhatsApp.
- Weekly closes per salesperson, a cooling-leads radar by channel compared with the previous 30 days, and a list of website searches with no results.
- Per-car performance that recommends where to pay for featured placement based on view-to-click conversion, and flags cars with a pricing problem separately.
- Health alerts: catalog out of sync, cost per lead spiking, hot buyer with no human contact, aging stock, sales without a purchase cost, forms dropping to zero, and a watchdog that checks the data arrived, not just that jobs ran.
05
How it works
Trigger
Server scheduler with times set to Panama's time zone.
Live read
Ad platforms, CRM, forms, inventory and record sheets.
Validation
Source available, no double counting, optional dry run; if a source fails, the report says so.
Writing
Shared template with a headline figure, table, sources and a suggested action.
Sending with fallback
If one email transport fails it goes out through another; a probe watches each transport.
Memory
Each notice is marked so it isn't repeated, and retried if it failed.
Demo
What a report looks like
Weekly ad spend for leadership
Anonymized example · fictitious figures
Subject: Ad spend this week: buying vs selling cars
Monday, 7:00 a.m. · calculated live
To buy cars
$X.XXX
N people wrote
To sell cars
$X.XXX
N people wrote
Leads are counted from forms actually received on the website, not from the ad platform's figure.
Sources: Meta · TikTok · Google Ads · website forms. Data up to yesterday.
Alert: cost per lead spiking
Anonymized example · fictitious figures
Subject: ⚠️ Campaign A: cost per lead tripled
Only sent when something is off
Last 7 days: $XX per lead. Its own previous 30-day average: $X.
Rule: 3× its own average with a minimum weekly spend, or spend with zero leads.
Next step: check the form and the ad before raising budget.
It rings once per campaign and week; if it is still broken next week, it rings again.
ZoDesk collections summary for the office
Anonymized example · fictitious figures
Customers who owe
12 · $XX.XXX
7 validated customers received the reminder. 3 are pending review and 2 have no email.
- Customer A · AB-1234 · validated$X.XXX
- Customer B · pending review · no email$X.XXX
- Customer C · validated · email bounced: call them$X.XXX
It lists who to review, who to call and whether the campaign is switched off. It does not include automatic recommendations or period comparisons.
06
Tools and what they do
- Laravel Scheduler
- Scheduling and running reports and alerts.
- Meta, Google Ads and TikTok APIs
- Campaign spend and performance.
- Zoho CRM
- Sales statuses and direct links to records that need completing.
- Brevo and a Google relay
- Sending with a fallback transport.
- Resend
- Sending and delivery events in ZoDesk.
Available evidence
- The dealership server scheduler with each report's and alert's frequency.
- A shared alert template with dry run, test send and notice memory.
- ZoDesk's collections summary and the multi-country store's health alerts, documented with their first response step.
07
Current status
- In production
Ad, ROI, leadership, closes and demand reports
Scheduled on the dealership server, according to the reviewed scheduler copy.
- In production
Data and integration health alerts
Scheduled; they stay quiet when everything is fine.
- Built and tested
Office collections summary (ZoDesk)
Built and tested; no documented confirmation of real rounds.
- Pending
Multi-country store health alerts
Built and dry-run tested; recipients and external monitor still pending.
- Pending
Daily maintenance reminders (service CRM)
Function built; scheduling is described as a next step.
“In production” is only used when the project’s documentation confirms it. “Built and tested” means automated tests exist, not that it is in use. “Proposal” is a documented idea, not something built.
Next steps
- Turn on the multi-country store alerts with defined recipients.
- Bring the dealership reporting pattern into ZoDesk as a reusable module.
08
Lessons learned
“The job ran” doesn't mean “the data arrived”. The watchdog compares what processes claim against what is in the database.
Writing for the recipient changes the report: leadership asked for certainty and got one clear comparison.
Platforms can inflate leads; the received form is the reliable number.
Related services
Data analysis
Intelligent admin dashboard
Dashboards that surface critical metrics, expose bottlenecks and replace intuition with numbers.
Advertising profitability
Advertising ROI measurement
Connect spend, leads, sales and margin to see which campaigns actually return money.
Marketing connected to data and operations
Marketing and Sales Process Automation
I connect inventory, campaigns, CRM and email so what you advertise exists, what you measure is true and what breaks raises an alert.
Connected cases
Monitoring and action dashboards
Dashboards that don't stop at the number: every out-of-range indicator suggests a concrete action.
Email: follow-up, segmented sends and operations
Emails built on real data, with segments, human validation, duplicate protection and delivery tracking.
Inventory-driven dynamic marketing
Inventory as the single source for catalogs, content, conversion signals and ad decisions.