Automate Client Reports for Your Agency in a Weekend

What you're building

You're building a script that runs every Monday morning, pulls last week's data from the tools your clients care about, formats it into a branded PDF or HTML email, and sends it to each client. No more screenshotting Google Analytics on Sunday night. No more copy-pasting numbers into a Notion doc. The script reads a clients.json file that tells it who gets what, runs once a week on a schedule, and logs the result so you can prove the report went out when the client asks. The deliverable looks at least as good as the one you make by hand, and arrives more reliably.

This is one of the highest-leverage automations an agency can build. Three clients times an hour of report work each is fifteen hours a month back. At a hundred and fifty dollars an hour, that's twenty-seven hundred dollars of recovered time. The build itself is small enough to fit in a Saturday and a Sunday. And once it works for three clients, it works for thirty with almost no extra code, because each new client is a config file rather than new logic.

What you need before you start

You don't need to be a developer to follow this, but you do need to be comfortable on the command line. You need a laptop with Node installed, a code editor, and API access to whatever tools you're reporting on. Most agencies need Google Analytics, Google Search Console, Meta Ads, and maybe a CRM. Each one has an API, and Claude Code knows how to talk to all of them. The auth setup for Google's APIs is the most tedious step, so budget an hour on Saturday morning for OAuth consent screens, service accounts, and scope approvals.

• Node 20 or later and a code editor like Cursor or VS Code
• Claude Code installed and authenticated
• Read-only API tokens for the platforms you report on
• A Resend, Postmark, or SendGrid account for sending email
• A logo and one brand color per client
• A sample report you've sent recently as the template

Saturday morning: the data layer

Start by asking Claude Code to scaffold a Node project with TypeScript, dotenv, and an index.ts entry point. Then build the data fetchers one at a time. For each platform, create a file under src/sources, give Claude the API docs URL and your last week's date range, and let it write the fetch function. The function should return a plain object with the metrics you care about. Don't over-engineer the shape. A flat object with five to ten fields per source is plenty. You can always nest later, but flat is easier to render and easier to debug.

Test each source by itself before moving on. Run it from the command line, see the JSON, confirm the numbers match what you see in the dashboard. If they don't match, fix the time zone. Time zones are where ninety percent of report bugs live. Set every API call to your client's local time zone, not UTC, and write it down in the config. A client in Sydney reading a report that ends at midnight UTC is reading data that ended at 10am their time, which is wrong in the way that erodes trust quietly.

Save every raw API response to a data/raw/{client}/{date}/{source}.json file before any transformation. Disk is cheap and reruns are free. When the report looks weird three weeks from now and you want to know whether the source data changed or the rendering logic changed, you'll have the receipts.

Saturday afternoon: the template

Pick one of two paths. Path A is HTML email with inline CSS, which renders inside Gmail and Outlook and is enough for most clients. Path B is a PDF generated with Playwright or Puppeteer, which looks more like a deliverable and is what bigger clients expect. Path A is faster. Path B is more impressive. You can build A this weekend and add B next month.

Ask Claude to write a render function that takes the client config and the fetched data and returns a string of HTML. Include a header with the client's logo, a summary section with three to five headline numbers, a section per platform with a small chart, and a footer with your agency branding. For charts, use QuickChart.io, which returns a PNG from a URL and works in both email and PDF. Skip interactive charts. Email clients block JavaScript, and clients open reports on their phones nine times out of ten.

Keep the design minimal. White background, dark text, one accent color for headers and the chart bars. Resist gradients, drop shadows, and round-cornered cards stacked on round-cornered cards. The report is a document, not a dashboard, and it should read like a one-page memo from someone who respects the client's time.

Choices to make along the way

Resend versus Postmark versus SendGrid: Resend is the easiest to set up, has a React-friendly API, and works fine for the volumes an agency sends. Postmark is the most reliable for transactional mail and has better deliverability if you're worried about spam folders. SendGrid is the legacy choice and has more knobs than you need.

Cron on your laptop versus GitHub Actions versus a tiny server: laptop cron is fine while you have one or two clients but breaks the first time you go on vacation. GitHub Actions on a schedule is free, runs in the cloud, and is the right answer for ten or fewer clients. A tiny Hetzner or Fly server with node-cron is the right answer above that. Start with GitHub Actions.

Sunday morning: client configs and personalization

Create a clients.json or, better, a clients/ folder with one file per client. Each file holds the client name, logo URL, brand color, the platforms to include, the recipient emails, and any custom commentary you want at the top of the report. Have Claude Code read this folder on each run, loop through every client, fetch their data, render their report, and send it. One script handles all of them, and onboarding a new client is a five-minute job of copying an existing config and editing five lines.

How to test it

Send the first three runs to yourself, not to clients. Spot check every number against the source dashboards. Send the next three runs to clients with a manual subject-line warning that the report is new. Ask for feedback on what's missing and what's confusing. Adjust. Once a client tells you the new report is better than the old one, you're done testing that client and you can move to set-and-forget. A good signal you're done: the client forwards the report to someone else with a one-line note instead of replying with questions.

How to ship it

Push the repo to a private GitHub. Add a workflow file at .github/workflows/weekly-report.yml that runs on a Monday morning cron. Set all your API tokens as repo secrets, not environment files. Add a manual workflow_dispatch trigger too, so you can run the report on demand when a client asks. The whole pipeline costs zero dollars a month at agency volume. The club at claudecodeclub.ai has a working starter template and a Slack channel of other agency owners running the same playbook, which is the fastest way to skip the early mistakes.

Set the cron to run at six in the morning client time, not yours, and stagger clients in different time zones by an hour each to avoid hitting API rate limits all at once. Add a heartbeat: if the workflow doesn't complete by eight, you get a Slack ping. Silent cron failure is the worst class of failure because the report just doesn't go out and nobody notices until the client emails on Tuesday.

Track delivery status. The script should write a row to a deliveries log every Monday with the client, the timestamp, the email message id from Resend, and the status. When a client says they didn't get the report, you can answer with the exact send time and the bounce reason. This builds trust faster than the report itself.

How to extend it

After v1, add a comparison to the previous week so the report shows deltas in green and red. Add an AI-generated insight at the top of each section, written by Claude from the raw numbers. Add a dashboard at reports.youragency.com where clients can see historical reports on demand. Add a CSV export. Each of those is a half-day project, not a week. The big one to ship eventually is a client-facing dashboard where the same data renders live, because it shifts the support conversation from 'where's my report' to 'I checked the dashboard, here's my question.'

Common gotchas

Time zones are the first gotcha and the second gotcha. API rate limits are the third, especially Meta Ads, which is stingy and slow. Always cache the raw fetch results to a JSON file before rendering, so a render bug doesn't force you to re-fetch and burn quota. The last gotcha is silently failing. If a source returns zero rows, the script should email you, not the client. A blank report is worse than no report.

Token refresh on Google APIs is the gotcha you only meet on week three. Access tokens expire after an hour, refresh tokens expire after six months of inactivity, and the script that worked perfectly for a quarter suddenly fails one Monday morning. Build a token refresh helper from day one and store refresh tokens in a place that's easy to rotate, not hardcoded in env vars you have to track down.

Email rendering is the gotcha that bites every time you add a new chart. Outlook renders HTML differently from every other client, and CSS Grid is unsupported there. Stick to tables for layout in the email template, even though it feels like 2005. Use a service like Litmus or PutsMail to preview across clients before shipping a new template, and screenshot the result in three viewports as a sanity check.