I build n8n automations for small salons and clinics. The series so far
covered preventing no-shows, refilling cancelled slots, recovering fees,
and turning finished visits into reviews and rebookings. This build is
the one that tells you whether any of that worked: a Monday morning
digest with last week's real numbers, one message, no dashboard. Complete
workflow JSON at the end, runs on n8n alone.
The loop nobody measures
Here is the honest failure mode of automation projects: the reminders
ship, the waitlist fires, everyone feels busier, and nobody can say
whether the no-show rate moved. The numbers live in a booking tool
behind four clicks, so nobody looks. Six weeks later the workflows are
still running on faith.
The fix costs one message a week. Every Monday at 08:00, before the
first customer walks in, the owner reads: how many appointments, what
share showed up, no-show rate, cancellations, revenue, and which booking
source brought in the most completed visits. Ten seconds of reading,
and every other workflow in the loop becomes measurable.
What the digest says
One text block, built for a phone screen:
Week of 2026-09-28: 4/6 kept (67%, up 4 pts vs last week), 1 no-shows
(17%), revenue EUR 230 (EUR +40 vs last week). Top source: repeat (3).
The deltas are the part that does the work. A raw number needs a
baseline to mean anything; "up 4 pts" is an answer, "67%" is a lookup.
The workflow computes both weeks from the same rows, so the comparison
is always apples to apples.
Bad rows must not skew the numbers
A KPI digest that divides by garbage is worse than no digest, because
it looks authoritative. The workflow validates every row before it
counts: starts_at must parse, status must be one of
completed, no_show, cancelled, revenue_eur must be numeric.
Rows that fail carry an exact reason (unknown status: noshow) and
route to an error log branch instead of the aggregation. In production
that branch writes to a kpi_errors sheet; you fix the source once and
the digest self-corrects.
The empty-week trap
The subtle one. n8n IF nodes evaluate per item. If the source returns
zero rows one Monday (sheet moved, filter too narrow), an IF downstream
never fires and the flow stops silently. No digest, no error, nothing.
You would discover it weeks later.
The validation node handles this with a sentinel: when zero items come
in, it emits one { empty_week: true } item. The empty-week branch then
sends an honest line instead of silence:
No completed appointment data found for this week. Check that the
source sheet filled correctly.
Silent failure converted into a self-reporting failure. That is the
whole trick, and it applies to any scheduled n8n workflow you run.
The aggregation code
Both weeks from one pass over the rows:
const items = $input.all();
const weekMs = 7 * 24 * 3600 * 1000;
const now = Date.now();
function agg(rows) {
const total = rows.length;
const completed = rows.filter((i) => i.json.status === 'completed').length;
const noShow = rows.filter((i) => i.json.status === 'no_show').length;
const cancelled = rows.filter((i) => i.json.status === 'cancelled').length;
const revenue = rows.reduce((sum, i) => sum + (i.json.revenue_eur || 0), 0);
const sources = {};
for (const i of rows) {
if (i.json.status !== 'completed') continue;
sources[i.json.source || 'unknown'] = (sources[i.json.source || 'unknown'] || 0) + 1;
}
const top = Object.entries(sources).sort((a, b) => b[1] - a[1])[0];
return {
total: total, completed: completed, no_shows: noShow, cancelled: cancelled,
revenue_eur: revenue,
show_up_rate_pct: total ? Math.round((completed / total) * 100) : 0,
no_show_rate_pct: total ? Math.round((noShow / total) * 100) : 0,
top_source: top ? top[0] + ' (' + top[1] + ')' : 'none'
};
}
const thisWeek = agg(items.filter((i) => i.json.starts_ts > now - weekMs));
const lastWeek = agg(items.filter((i) => i.json.starts_ts <= now - weekMs && i.json.starts_ts > now - 2 * weekMs));
return [{ json: { week_of: new Date(now - weekMs).toISOString().slice(0, 10), ...thisWeek, prev: lastWeek } }];
Two details worth copying. The top-source ranking counts only completed
visits, because a source that books customers who never show up should
not win the ranking. And the previous week comes from the same rows,
split by timestamp, so you never need a second source or a stored
snapshot to say "vs last week".
Test it without sending anything
The JSON below has demo rows wired in. Import it into n8n, open the
demo source node, execute, and read the digest text in the send node's
output. No message leaves: the send node is a placeholder by design,
stamped delivered: 'demo'.
The complete workflow JSON
Import into n8n (Workflows, Import from File), disable the demo node,
and point the first code node at your Google Sheet of appointment rows.
Required columns: appointment_id, starts_at (ISO), status
(completed, no_show, or cancelled), revenue_eur, source. Put
your delivery node (Gmail, WhatsApp to yourself, Slack) in place of the
placeholder:
{
"name": "Summarize weekly appointment KPIs into a Monday digest",
"nodes": [
{
"parameters": {
"rule": {
"interval": [
{
"field": "weeks",
"weeksInterval": 1,
"triggerAtDay": [
1
],
"triggerAtHour": 8
}
]
}
},
"id": "91000000-0000-4000-8000-000000000001",
"name": "Mondays 08:00",
"type": "n8n-nodes-base.scheduleTrigger",
"typeVersion": 1.2,
"position": [
-280,
0
]
},
{
"parameters": {
"jsCode": "// DEMO SOURCE so the template runs immediately. For production,\n// disable this node and read last week's rows from Google Sheets.\n// Required columns: appointment_id, starts_at (ISO), status\n// (completed|no_show|cancelled), revenue_eur, source\nconst demoRows = [\n { appointment_id: 'APT-5001', starts_at: new Date(Date.now() - 2 * 24 * 3600 * 1000).toISOString(), status: 'completed', revenue_eur: 65, source: 'repeat' },\n { appointment_id: 'APT-5002', starts_at: new Date(Date.now() - 3 * 24 * 3600 * 1000).toISOString(), status: 'no_show', revenue_eur: 0, source: 'walkin' },\n { appointment_id: 'APT-5003', starts_at: new Date(Date.now() - 4 * 24 * 3600 * 1000).toISOString(), status: 'completed', revenue_eur: 120, source: 'instagram' },\n { appointment_id: 'APT-5004', starts_at: new Date(Date.now() - 5 * 24 * 3600 * 1000).toISOString(), status: 'cancelled', revenue_eur: 0, source: 'repeat' },\n { appointment_id: 'APT-5005', starts_at: new Date(Date.now() - 6 * 24 * 3600 * 1000).toISOString(), status: 'completed', revenue_eur: 45, source: 'repeat' },\n { appointment_id: 'APT-5006', starts_at: new Date(Date.now() - 6 * 24 * 3600 * 1000).toISOString(), status: 'completed', revenue_eur: 85, source: 'waitlist' }\n];\nreturn demoRows.map((row) => ({ json: row }));"
},
"id": "91000000-0000-4000-8000-000000000002",
"name": "Last week's rows (demo data)",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
-60,
0
]
},
{
"parameters": {
"jsCode": "// Validate each row before aggregation: a broken status or a non-numeric\n// revenue would silently skew the KPIs. Exact reasons are attached and bad\n// rows are routed to the error log. If the source returns nothing at all,\n// one sentinel item is emitted so the empty-week branch can fire (an IF\n// node evaluates per item, and zero items would stop the flow silently).\nconst items = $input.all();\nconst out = [];\nconst STATUSES = ['completed', 'no_show', 'cancelled'];\nfor (const item of items) {\n const r = item.json;\n const t = r.starts_at ? new Date(r.starts_at).getTime() : NaN;\n let rowError = null;\n if (!r.appointment_id) rowError = 'missing appointment_id';\n else if (isNaN(t)) rowError = 'starts_at not parseable';\n else if (r.status && !STATUSES.includes(r.status)) rowError = 'unknown status: ' + r.status;\n else if (r.revenue_eur !== undefined && isNaN(Number(r.revenue_eur))) rowError = 'revenue_eur not numeric';\n out.push({\n json: {\n ...r,\n revenue_eur: r.revenue_eur === undefined ? undefined : Number(r.revenue_eur),\n starts_ts: isNaN(t) ? null : t,\n row_valid: !rowError,\n row_error: rowError\n }\n });\n}\nif (out.length === 0) {\n out.push({ json: { empty_week: true, row_valid: true } });\n}\nreturn out;"
},
"id": "dddd0000-0000-4000-8000-000000000201",
"name": "Prepare + validate rows",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
140,
0
]
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "c1",
"leftValue": "={{ $json.row_valid }}",
"rightValue": true,
"operator": {
"type": "boolean",
"operation": "true"
}
}
],
"combinator": "and"
},
"options": {}
},
"id": "dddd0000-0000-4000-8000-000000000202",
"name": "Row valid?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
360,
0
]
},
{
"parameters": {
"jsCode": "// Error path for source-data problems: exact reason per row. In production,\n// write to a 'kpi_errors' sheet; the digest then runs on clean rows only.\nconst items = $input.all();\nreturn items.map((item) => ({\n json: {\n error: 'invalid KPI row',\n appointment_id: item.json.appointment_id || null,\n reason: item.json.row_error || 'unknown',\n seen_at: new Date().toISOString()\n }\n}));"
},
"id": "dddd0000-0000-4000-8000-000000000203",
"name": "Log invalid rows",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
360,
220
]
},
{
"parameters": {
"conditions": {
"options": {
"caseSensitive": true,
"leftValue": "",
"typeValidation": "strict",
"version": 2
},
"conditions": [
{
"id": "e1",
"leftValue": "={{ $json.empty_week }}",
"rightValue": false,
"operator": {
"type": "boolean",
"operation": "false"
}
}
],
"combinator": "and"
},
"options": {}
},
"id": "dddd0000-0000-4000-8000-000000000205",
"name": "Any rows?",
"type": "n8n-nodes-base.if",
"typeVersion": 2.2,
"position": [
580,
0
]
},
{
"parameters": {
"jsCode": "// Quiet-week branch: no valid rows made it through, so no ratios are\n// computed (no divide-by-zero) and the owner still gets one honest line.\nreturn [{ json: {\n week_of: new Date(Date.now() - 7 * 24 * 3600 * 1000).toISOString().slice(0, 10),\n digest: 'No completed appointment data found for this week. Check that the source sheet filled correctly.'\n} }];"
},
"id": "dddd0000-0000-4000-8000-000000000206",
"name": "Digest: empty week",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
800,
-100
]
},
{
"parameters": {
"jsCode": "// Compare this week against last week so the digest says something useful\n// on quiet weeks too: deltas on show-up rate and revenue.\nconst w = $input.first().json;\nconst p = w.prev || {};\nconst dShow = w.show_up_rate_pct - (p.show_up_rate_pct || 0);\nconst dRev = w.revenue_eur - (p.revenue_eur || 0);\nconst dir = dShow >= 0 ? 'up' : 'down';\nconst digest = 'Week of ' + w.week_of + ': ' + w.completed + '/' + w.total + ' kept (' + w.show_up_rate_pct + '%, ' + dir + ' ' + Math.abs(dShow) + ' pts vs last week), ' + w.no_shows + ' no-shows (' + w.no_show_rate_pct + '%), revenue EUR ' + w.revenue_eur + ' (EUR ' + (dRev >= 0 ? '+' : '') + dRev + ' vs last week). Top source: ' + w.top_source + '.';\nreturn [{ json: { ...w, delta_show_up_pts: dShow, delta_revenue_eur: dRev, digest } }];"
},
"id": "dddd0000-0000-4000-8000-000000000204",
"name": "Trend vs last week",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1020,
-100
]
},
{
"parameters": {
"jsCode": "// Aggregate the clean rows into the numbers an owner reads: show-up rate,\n// no-show rate, revenue, top booking source. Also computes the same numbers\n// for the previous week (rows carry starts_ts, so weeks are split by date).\nconst items = $input.all();\nconst weekMs = 7 * 24 * 3600 * 1000;\nconst now = Date.now();\nfunction agg(rows) {\n const total = rows.length;\n const completed = rows.filter((i) => i.json.status === 'completed').length;\n const noShow = rows.filter((i) => i.json.status === 'no_show').length;\n const cancelled = rows.filter((i) => i.json.status === 'cancelled').length;\n const revenue = rows.reduce((sum, i) => sum + (i.json.revenue_eur || 0), 0);\n const sources = {};\n for (const i of rows) {\n if (i.json.status !== 'completed') continue;\n sources[i.json.source || 'unknown'] = (sources[i.json.source || 'unknown'] || 0) + 1;\n }\n const top = Object.entries(sources).sort((a, b) => b[1] - a[1])[0];\n return {\n total: total, completed: completed, no_shows: noShow, cancelled: cancelled,\n revenue_eur: revenue,\n show_up_rate_pct: total ? Math.round((completed / total) * 100) : 0,\n no_show_rate_pct: total ? Math.round((noShow / total) * 100) : 0,\n top_source: top ? top[0] + ' (' + top[1] + ')' : 'none'\n };\n}\nconst thisWeek = agg(items.filter((i) => i.json.starts_ts > now - weekMs));\nconst lastWeek = agg(items.filter((i) => i.json.starts_ts <= now - weekMs && i.json.starts_ts > now - 2 * weekMs));\nreturn [{ json: { week_of: new Date(now - weekMs).toISOString().slice(0, 10), ...thisWeek, prev: lastWeek } }];"
},
"id": "91000000-0000-4000-8000-000000000003",
"name": "Aggregate week",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
800,
100
]
},
{
"parameters": {
"jsCode": "// Placeholder for the delivery node (Gmail / WhatsApp to the owner /\n// Slack). The digest text is in .digest; the numbers are fields.\nconst item = $input.first().json;\nreturn [{ json: { ...item, delivered: 'demo', digest_subject: 'Weekly numbers - ' + item.week_of } }];"
},
"id": "91000000-0000-4000-8000-000000000004",
"name": "Send digest (replace me)",
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
1240,
-100
]
},
{
"parameters": {
"content": "## Summarize weekly appointment KPIs into a Monday digest\n\n### How it works\n\nEvery Monday at 08:00 the workflow reads last week's appointment rows and aggregates them into the numbers an owner actually reads: total appointments, kept rate, no-show rate, cancellations, revenue, and the booking source that brought in most completed visits. One text block is built for delivery, so a single WhatsApp message, email, or Slack post is enough. Demo data is included, so it runs immediately.\n\n### Setup steps\n\n- Disable the demo node and connect your Google Sheets source. Required columns are listed in that node.\n- Put your delivery node (Gmail, WhatsApp, or Slack) in place of the placeholder send node.\n- Activate the workflow.\n\n### Requirements\n\n- A sheet or other source with one row per appointment: starts_at, status (completed, no_show, or cancelled), revenue, and source.\n\n### Customization\n\nChange the trigger day and hour, the digest text, or the source filter (currently only completed visits count toward the top-source ranking).",
"width": 600,
"height": 700
},
"id": "cccc0000-0000-4000-8000-0000000000f1",
"name": "Sticky Note 00f1",
"type": "n8n-nodes-base.stickyNote",
"typeVersion": 1,
"position": [
-960,
-200
]
},
{
"parameters": {
"content": "## 1. Read + validate\nMonday 08:00. Demo source stands in for your sheet; a code node parses dates and checks statuses, and an IF gate routes bad rows to an error log.",
"color": 7,
"width": 785,
"height": 560
},
"id": "cccc0000-0000-4000-8000-0000000000f2",
"name": "Sticky Note 00f2",
"type": "n8n-nodes-base.stickyNote",
"typeVersion": 1,
"position": [
-320,
-200
]
},
{
"parameters": {
"content": "## 2. Aggregate + trend + send\nValid rows become show-up rate, no-show rate, revenue and top source, compared against the previous week. An empty week skips the ratios; the placeholder send node ships the digest text as-is.",
"color": 7,
"width": 965,
"height": 560
},
"id": "cccc0000-0000-4000-8000-0000000000f3",
"name": "Sticky Note 00f3",
"type": "n8n-nodes-base.stickyNote",
"typeVersion": 1,
"position": [
505,
-200
]
}
],
"connections": {
"Mondays 08:00": {
"main": [
[
{
"node": "Last week's rows (demo data)",
"type": "main",
"index": 0
}
]
]
},
"Last week's rows (demo data)": {
"main": [
[
{
"node": "Prepare + validate rows",
"type": "main",
"index": 0
}
]
]
},
"Prepare + validate rows": {
"main": [
[
{
"node": "Row valid?",
"type": "main",
"index": 0
}
]
]
},
"Row valid?": {
"main": [
[
{
"node": "Any rows?",
"type": "main",
"index": 0
}
],
[
{
"node": "Log invalid rows",
"type": "main",
"index": 0
}
]
]
},
"Any rows?": {
"main": [
[
{
"node": "Aggregate week",
"type": "main",
"index": 0
}
],
[
{
"node": "Digest: empty week",
"type": "main",
"index": 0
}
]
]
},
"Digest: empty week": {
"main": [
[
{
"node": "Send digest (replace me)",
"type": "main",
"index": 0
}
]
]
},
"Aggregate week": {
"main": [
[
{
"node": "Trend vs last week",
"type": "main",
"index": 0
}
]
]
},
"Trend vs last week": {
"main": [
[
{
"node": "Send digest (replace me)",
"type": "main",
"index": 0
}
]
]
}
}
}
Where this fits
This is the fifth full build in the series.
Appointment reminders with a no-double-send guard,
waitlist backfill that fills cancelled slots automatically,
fee recovery with a polite escalation ladder,
and post-visit follow-up for reviews and rebookings
came before. This one closes the loop: prevent, refill, recover, grow,
measure. The Dutch-language versions plus the surrounding pack (booking
intake, no-show rescue) are in one download:
https://vlotstream.gumroad.com/l/rhmxw
Free companion tools
Want the numbers before the workflow? I put the math in two free
calculators (EN/NL, no signup, run in your browser):
no-show cost calculator
and waitlist fill-rate calculator.
Top comments (0)