A solo acupuncture practitioner was tracking her business across three separate Airtable tables: appointments and revenue in a Business Tracker, ad spend by channel in an Ads Tracker, and content in a Social Media Posts table.
Every Monday morning she'd open all three, manually calculate week-over-week revenue change, work out her revenue per hour, check whether her ad spend was delivering clients, and try to spot any concerning trends — a process that regularly took 45–60 minutes and still produced no written summary she could refer back to.
More critically, the manual process meant anomalies were often noticed too late. A 20%+ revenue drop in a single week, an ad channel spending with zero client bookings, or a week with no appointments logged — these needed immediate attention, not a Monday retrospective.
A scheduled n8n workflow that runs every Monday at 8 AM Eastern, pulls all three Airtable tables in parallel, calculates the full KPI set in JavaScript, generates 3–5 AI insights using Claude Sonnet via OpenRouter, and delivers a fully branded HTML report to her inbox — with automatic alert emails if any threshold is breached.
n8n fires every Monday at 8:00 AM Eastern. Three Airtable fetch operations run simultaneously — Business Tracker (appointments, revenue, payment methods, session types), Ads Tracker (spend by channel), and Social Media Posts (posts by platform). A Merge node synchronises all three branches before any calculation begins. If no business data is found for the week, a 'no data' reminder email fires and the workflow stops cleanly.
A Code node handles all date filtering and aggregation in JavaScript — no Airtable formula dependencies. Records are split into two 7-day windows (last week and the prior week) enabling true week-over-week comparisons in a single pass. Calculated metrics include: total revenue, total clients, hours worked, revenue per hour, revenue change %, and breakdowns by payment method, appointment type, session type, ad channel, and social platform.
The same Code node checks four alert conditions: revenue drop greater than 20% versus the prior week, no clients logged for the week (possible data entry gap), ad spend recorded with zero client bookings (tracking mismatch), and revenue per hour below the $50/hr benchmark. Any triggered alert is flagged in the output for the downstream IF node.
The calculated KPI object is passed to Claude Sonnet 4.6 via OpenRouter with a prompt that instructs it to produce 3–5 specific, actionable business insights based on that week's exact numbers — not generic advice. Each insight is tagged as positive, neutral, or warning, and formatted for direct inclusion in the email.
A Code node renders a fully branded HTML email — KPI summary cards with colour-coded week-over-week arrows, AI insight cards with visual type indicators, and breakdown tables for all tracked dimensions. The report is sent via Gmail OAuth2. Simultaneously, a summary record is written back to a Weekly Snapshots Airtable table, building a historical performance log. If any alerts were triggered, a separate plain-text alert email fires immediately after the main report.