Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 36 ยท Projects and Practice

Project: Weekly Report with Digest and Summary

Build an automated weekly report: collect events through the week, aggregate totals in a table, format a readable message, post it on schedule, and handle empty weeks and errors.

Reports are the automation that people feel most. A good weekly report arrives at the same time, says the same things in the same order, and nobody has to assemble it by hand on Friday afternoon. In this project you build a weekly team report: events are recorded during the week, a scheduled automation summarises them, and a formatted message goes to a chat channel and an email list.

Zapier, Make and n8n cannot run here. The guide describes the steps in words; adapt the names to your screens. The runnable parts are JavaScript stand-ins for the aggregation and formatting logic, so you can get the numbers and the message right before building.

The plan

Stage What happens Zapier building block
Collect Every sale, signup or ticket closed is logged A Zap per source writing to a table (or Digest)
Store One row per event with a date and a numeric value Zapier Table Events
Aggregate Totals per category for last week Code step, or table formulas and views
Format A readable summary with numbers and changes Formatter / Code step
Deliver Post at Monday 09:00 Schedule trigger, chat and email actions
Safety Empty week, errors, duplicates Filters, error Zap
flowchart LR
    S1["Sales Zap"] --> T["Events table"]
    S2["Signup Zap"] --> T
    S3["Support Zap"] --> T
    SCH["Schedule: Monday 09:00"] --> Q["Find records: last week"]
    T --> Q
    Q --> A["Aggregate totals"]
    A --> F["Format message"]
    F --> C["Chat + email"]

Many small writers, one scheduled reader.

Step 1: the Events table

Fields: event_id (unique), type (dropdown: sale, signup, ticket_closed), value (number: amount, or 1), occurred_at (date and time), source. Every collecting Zap uses Find or Create Record on event_id so replays never double count (Tables as Lookup, State and Dedupe). Keep type a dropdown so a typo never creates a new category.

Step 2: choose the reporting window

Reports break when the window is vague. Define it precisely: last week = Monday 00:00 up to, but not including, this Monday 00:00 in the team's time zone. Zapier's Schedule trigger and Formatter date tools let you pick a time zone; set it deliberately, because "Monday 09:00" in UTC and in New York are different times, and events near midnight fall into different weeks.

JS Stand-in: the reporting window

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Both calls report the same window, the week ending at Monday 00:00 of the run week.

Step 3: aggregate

A scheduled Zap runs Find Records with filters on occurred_at. The Table app may return many rows, so you either use a formula/view that already aggregates, or a Code step that sums them. Remember that one Find step may return a list as line items (Webhook Payloads, Nested Data and Line Items), and values may arrive as text, so convert.

JS Stand-in: aggregate events by type

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Note the half-open window (>= from, < to): an event at exactly Monday 00:00 belongs to exactly one week. Using "less than or equal" on both ends would count it twice across two reports.

Step 4: compare with the previous week

Numbers without context are weak. Keep last week's totals in a state record (a Table row report_state with a JSON text field), and show the change: "Sales 200.00 (+25%)". Update the state at the end of the run, after the message is sent. Handle division by zero when last week had none.

Step 5: format the message

Use plain, scannable text: a heading, one line per metric, the change, and a link to the Events view. For chat, markdown-like formatting is fine; for email, a simple list. Round money to two decimals and use a consistent currency.

JS Stand-in: build the report message

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The second output shows the empty-week branch. An empty report is a signal, often that a collecting Zap broke. Send it with a warning instead of staying silent, and consider alerting the owner too (Error Handling, Alerts and Replays).

Step 6: deliver and protect

  • Use a Schedule by Zapier trigger: Every Week, Monday, 09:00, in the right time zone.
  • Actions: a chat post, and an email to a list address (use a distribution list instead of mapping many individual recipients: fewer steps and easy to edit).
  • Add a Delay or check so a missed run (platform outage) is noticed: a watchdog Zap on Monday 10:00 can verify that the state record's last_report_at changed and alert you if not.
  • Count tasks: the weekly Zap itself is only a few tasks per week, but the collectors dominate (Task Usage and Cost Control). Consider a Digest for high-volume collectors.

Test checklist

  1. Fill the Events table with a known set (use the sample above) and run the Zap manually: do the numbers match your hand calculation?
  2. Insert an event on each side of the Monday boundary: each counted once, in the correct week.
  3. Duplicate an event id: it must not change totals.
  4. Empty the table: the empty-week message appears.
  5. Switch the time zone: the window moves as expected.

Check your understanding

Quick check ยท 3 questions

of 3 answered You scored / 3 ยท perfect!

  1. 1. Why use a half-open window (from inclusive, to exclusive)?

    Inclusive at both ends would count a boundary event in two consecutive weeks.
  2. 2. What should the report do when there were no events at all?

    Silence hides problems. An explicit empty report is a useful alarm.
  3. 3. Why convert value fields with Number() before summing?

    Adding text values concatenates them instead of summing.

Summary

  • Collect events into a table with unique ids; aggregate on a schedule.
  • Define the window precisely, half-open, in a chosen time zone.
  • Convert text to numbers, dedupe, compare with last week using a state record.
  • Format a short consistent message; handle empty weeks; add a watchdog so a missed report is noticed.

Next: Practice: Debugging Broken Automations