CORTEXFLOW · FREE FOREVER

Automation

Appointment Waitlist Backfill: Fill Cancelled Slots Automatically

↓ Download workflow JSON

Import in n8n: Workflows → ⋯ → Import from file, then add your credentials. Free download with a free account.

How it works

Your 24/7 receptionist for cancelled appointments — no slot ever goes to waste:

  1. STEP 1 — Cancellation intake + config — your booking system POSTs the freed slot (appointment_id, slot_time, service) to the waitlist-cancel webhook. The workflow loads your rules from CONFIG — Waitlist Rules, normalizes the payload, and logs it to the Offers tab with status NEW.
  2. STEP 2 — Deterministic scoring + ranking — the waitlist is read from Google Sheets and every candidate is scored by a Code node (no black-box AI): days waiting × your weight, minus a no-show penalty, plus loyalty and priority-flag bonuses. The top N candidates are logged with status RANKED.
  3. STEP 3 — AI offer copy + send — an AI Agent writes a warm, personalized offer message for each candidate (Structured Output, gpt-4o-mini), the reply instruction (“Reply TAKE within 120 minutes”) is appended deterministically, and the message goes out through the demo HTTP node pointed at your WhatsApp/SMS provider. Rows flip to OFFER_SENT and you get a Telegram summary of who was contacted.
  4. STEP 4 — Reply-window expiry sweep — a Wait node pauses for your configurable reply window, then the Offers tab is re-read: any still-open offer is marked EXPIRED and you get the window-closed summary on Telegram.
  5. STEP 5 — Reply handling (first TAKE wins) — a second webhook, waitlist-reply, receives {phone, message} from your messaging provider. The reply is normalized, matched to the latest still-open offer for that phone, the winner flips to BOOKED, and every other open offer for that slot flips to EXPIRED. You get a Telegram alert naming the winner.
  6. STEP 6 — Owner notifications — Telegram messages at every stage: offers sent, window closed, slot booked, and unmatched-reply alerts for messages that don’t match any open offer.

Status flow in Sheets: NEW → RANKED → OFFER_SENT → BOOKED / EXPIRED

Set up steps

You’ll need:

  • n8n (self-hosted or cloud)
  • A Google account — create a spreadsheet with two tabs (headers below), then n8n Credentials → Google Sheets OAuth2 API
  • A WhatsApp/SMS provider — Twilio (WhatsApp + SMS), 360dialog (WhatsApp Business API), or WATI
  • A Telegram bot — message @BotFather, send /newbot, copy the token; get your chat ID from @userinfobot
  • (Optional) An OpenAI API key — platform.openai.com → API keys, for the AI-written offer copy

Steps:

  1. Import the workflow — in n8n, Workflows → Import from File → select appointment-waitlist-backfill.json.
  2. Create the Google Sheet — two tabs with these exact headers:
    • Waitlist: Name | Phone | Service | Wait Since | No Shows | Visits | Priority (add your waitlisted clients; Wait Since as a date, Priority as YES/blank)
    • Offers: Appointment ID | Slot Time | Service | Name | Phone | Score | Status | Offer Sent At | Offer Expires At | Reply At (the workflow fills this; leave it empty)
  3. CONFIG — Waitlist Rules — open the Set node and fill in:
    • business_name — shown in the AI-written offer messages
    • spreadsheet_id — from your sheet’s URL; leave waitlist_tab / offers_tab as-is unless you renamed the tabs
    • offer_count — how many candidates get the offer (3 is a good start)
    • reply_window_minutes — how long a candidate has to reply (120 = 2 hours)
    • reply_keyword — the word that claims the slot (TAKE)
    • weight_wait_day, weight_no_show, weight_visit_loyalty, weight_priority_flag, min_score — tune the scoring to your business
    • message_provider_url, message_api_token, sender_id — your WhatsApp/SMS provider’s send-message endpoint and credentials
    • telegram_chat_id — where owner alerts go
  4. Credentials — select your Google Sheets OAuth2 credential in all eight Sheets nodes, your Telegram credential in the four Telegram nodes, and your OpenAI credential in OpenAI Chat Model — Offer Copy. Then activate the workflow and copy both webhook URLs (n8n shows the production URLs once active).
  5. Wire up your booking system — POST cancellations to the waitlist-cancel URL with JSON body: {"appointment_id": "APT-123", "slot_time": "2026-10-07 14:00", "service": "Dental Cleaning"}.
  6. Wire up replies — point your messaging provider’s inbound webhook at the waitlist-reply URL so incoming messages arrive as {"phone": "+919876543210", "message": "TAKE"}. (Twilio: Messaging Service → Inbound Settings; 360dialog/WATI: webhook URL in the dashboard.)

Customize it

  • Real provider payloads — HTTP — Send Offer (Demo Provider) is a clearly-marked placeholder: it POSTs {to, from, text} with a Bearer token. Swap the URL/body to match your provider’s API (e.g. Twilio’s Messages.json endpoint or 360dialog’s /v1/messages) — nothing downstream changes.
  • Scoring weights — a salon might crank weight_visit_loyalty to reward regulars; a clinic might raise weight_wait_day so the longest-waiting patient always wins. weight_no_show is negative on purpose: chronic no-shows sink to the bottom.
  • Service matching — candidates are only offered slots matching their Service column (blank = any service). Delete that filter in Code — Score & Rank Waitlist if your waitlist is service-agnostic.
  • Skip the AI copy — don’t want the OpenAI call? Delete the AI Agent and its two sub-nodes, and connect Sheets — Log Ranked (RANKED) straight to Code — Build Offer Message: the fallback template message still works.
  • Tighter reply windows — 120 minutes suits clinics and salons; for same-day slots, drop reply_window_minutes to 30. Note the cancellation webhook’s response waits for the window to close (responseMode lastNode) — use a short window while testing.
  • Escalation ladder — instead of offering all N candidates at once, chain the flow: offer candidate #1, wait 30 minutes, and only if unclaimed, offer candidate #2. Duplicate the STEP 3–4 block with offer_count = 1 and an IF on the BOOKED status.

Workflow nodes

This workflow is built from 33 nodes. Here's what each one does:

#NodeTypeWhat it does
1 Webhook — Cancellation Intake Webhook Entry point — receives incoming HTTP requests that trigger the workflow.
2 CONFIG — Waitlist Rules Set Holds editable configuration values — fill in your own settings here before activating.
3 Code — Normalize Cancellation Code Runs custom JavaScript to transform, filter, or compute data between steps.
4 Sheets — Log Cancellation (NEW) Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
5 Sheets — Read Waitlist Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
6 Code — Score & Rank Waitlist Code Runs custom JavaScript to transform, filter, or compute data between steps.
7 Sheets — Log Ranked (RANKED) Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
8 AI Agent — Write Offer Copy AI Agent The AI brain — reasons over the task and calls tools to get it done.
9 OpenAI Chat Model — Offer Copy OpenAI Chat Model The language model powering the AI agent.
10 Structured Output Parser — Offer Copy Structured Output Parser Forces the AI to return clean, structured JSON instead of free text.
11 Code — Build Offer Message Code Runs custom JavaScript to transform, filter, or compute data between steps.
12 HTTP — Send Offer (Demo Provider) HTTP Request Calls an external API or webhook endpoint.
13 Sheets — Mark OFFER_SENT Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
14 Code — Build Offers Alert Code Runs custom JavaScript to transform, filter, or compute data between steps.
15 Telegram — Owner: Offers Sent Telegram Sends you a Telegram message or alert.
16 Wait — Reply Window Wait Pauses the workflow until a condition or time is met.
17 Sheets — Re-read Offers Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
18 Code — Expire & Summarize Code Runs custom JavaScript to transform, filter, or compute data between steps.
19 IF — Any Expired? IF Splits the flow into branches based on a condition.
20 Sheets — Mark EXPIRED Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
21 Telegram — Owner: Window Closed Telegram Sends you a Telegram message or alert.
22 Webhook — Reply Intake Webhook Entry point — receives incoming HTTP requests that trigger the workflow.
23 Code — Normalize Reply Code Runs custom JavaScript to transform, filter, or compute data between steps.
24 Sheets — Read Open Offers Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
25 Code — Match Reply to Offer Code Runs custom JavaScript to transform, filter, or compute data between steps.
26 IF — Offer Found? IF Splits the flow into branches based on a condition.
27 Sheets — Mark BOOKED Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
28 Sheets — Read Appointment Offers Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
29 Code — Build Others to Expire Code Runs custom JavaScript to transform, filter, or compute data between steps.
30 Sheets — Mark Others EXPIRED Google Sheets Reads from or writes to a Google Sheet — this workflow's log and state store.
31 Code — Build Booking Alert Code Runs custom JavaScript to transform, filter, or compute data between steps.
32 Telegram — Owner: Slot Booked Telegram Sends you a Telegram message or alert.
33 Telegram — Owner: Unmatched Reply Telegram Sends you a Telegram message or alert.

Setup instructions

1. Add your credentials

In n8n go to Credentials → New, create each credential below, then open the named node and select it:

NodeCredential to add
Sheets — Log Cancellation (NEW) Google Sheets OAuth2
Sheets — Read Waitlist Google Sheets OAuth2
Sheets — Log Ranked (RANKED) Google Sheets OAuth2
OpenAI Chat Model — Offer Copy OpenAI API
Sheets — Mark OFFER_SENT Google Sheets OAuth2
Telegram — Owner: Offers Sent Telegram Bot API
Sheets — Re-read Offers Google Sheets OAuth2
Sheets — Mark EXPIRED Google Sheets OAuth2
Telegram — Owner: Window Closed Telegram Bot API
Sheets — Read Open Offers Google Sheets OAuth2
Sheets — Mark BOOKED Google Sheets OAuth2
Sheets — Read Appointment Offers Google Sheets OAuth2
Sheets — Mark Others EXPIRED Google Sheets OAuth2
Telegram — Owner: Slot Booked Telegram Bot API
Telegram — Owner: Unmatched Reply Telegram Bot API

2. Fill in your config values

Open the config node listed below and replace each placeholder with your own value before activating the workflow:

NodeFieldWhat to paste
CONFIG — Waitlist Rules spreadsheet_id PASTE_YOUR_SPREADSHEET_ID
Your spreadsheet id
CONFIG — Waitlist Rules message_provider_url PASTE_YOUR_PROVIDER
Your provider
CONFIG — Waitlist Rules message_api_token PASTE_YOUR_MESSAGE_API_TOKEN
Your message api token
CONFIG — Waitlist Rules sender_id PASTE_YOUR_SENDER_ID
Your sender id
CONFIG — Waitlist Rules telegram_chat_id PASTE_YOUR_TELEGRAM_CHAT_ID
Your telegram chat id

How this workflow starts

  • Webhook: Listens on path /waitlist-cancel — copy the production URL from the node after activating the workflow.
  • Webhook: Listens on path /waitlist-reply — copy the production URL from the node after activating the workflow.

Use cases

Every cancellation is revenue evaporating. This template is your 24/7 receptionist for freed appointment slots: your booking system POSTs the cancellation to the Webhook — Cancellation Intake, the workflow scores your waitlist deterministically in Code — Score & Rank Waitlist (days waiting × your weight, minus a no-show penalty, plus loyalty and priority-flag bonuses), logs the top candidates as RANKED, and an AI agent writes a warm, personalized offer message for each. The messages go out through the HTTP — Send Offer (Demo Provider) node to your WhatsApp or SMS provider with a "Reply TAKE within 120 minutes" instruction appended, rows flip to OFFER_SENT, and the Wait — Reply Window pause starts. Replies come back through the Webhook — Reply Intake, get matched to the latest open offer in Code — Match Reply to Offer, and it's first TAKE wins — the winner flips to BOOKED, everyone else flips to EXPIRED.

Dental clinic losing money on hygiene cancellations

A 3-dentist clinic sees 4–6 hygiene cancellations a week, and each empty 45-minute slot costs around $150. Wire your booking software to POST cancellations to the waitlist-cancel webhook with appointment_id, slot_time, and service. Patients on your Waitlist tab get scored automatically — long waiters float up, chronic no-shows sink — and the top 3 get the AI-written WhatsApp offer within seconds of the cancellation. The first reply of TAKE books the slot, the rest are expired automatically. Practical tip: set reply_window_minutes to 30 for same-day slots so the sweep in Sheets — Mark EXPIRED closes fast and you can try a second wave of candidates if the first wave stays silent.

Salon or spa filling same-day gaps

Salons know that a Thursday afternoon cancellation can still be filled if you act within the hour. Set offer_count to 3 in CONFIG — Waitlist Rules and let the AI Agent — Write Offer Copy (gpt-4o-mini with structured output) write offers that feel personal — mentioning the service, the stylist, the time — rather than a robotic blast. The deterministic reply keyword TAKE keeps the matching in Code — Match Reply to Offer reliable: no fuzzy parsing, no confusion. Variation: if you don't want the OpenAI call, delete the AI Agent and its two sub-nodes and connect Sheets — Log Ranked (RANKED) straight to Code — Build Offer Message — the fallback template message still gets the slot filled. And if two clients reply TAKE in the same minute, only the first match wins; the other offer expires, and your Telegram alert shows you exactly who replied.

Physiotherapy or medical practice with chronic no-shows

No-show penalties are built into the scoring on purpose. A clinic that flags repeat no-shows in the No Shows column automatically pushes them down the ranking, so your scarce freed slots go to reliable patients first — fairness you can defend to anyone who asks. Service matching means candidates only get offers for their service type (blank matches any), so a physio cancellation never gets offered to a podiatry waitlist by accident. Tip: raise weight_wait_day so the longest-waiting patient always wins the tie — in healthcare, perceived fairness matters as much as speed, and the score column in the Offers tab gives you an auditable record of why each person was chosen.

Auto service center or grooming studio with repeat regulars

A car service center or dog groomer lives on loyal repeat customers — exactly what the Visits column rewards. Crank weight_visit_loyalty in the config and your regulars get first dibs on cancelled Saturday slots, which is precisely the loyalty gesture that keeps them coming back. The full status flow in Sheets — NEW → RANKED → OFFER_SENT → BOOKED / EXPIRED — means at the end of the month you can count exactly how many slots you recovered and what they're worth. Practical variation: build the escalation ladder from the guide — offer candidate #1, wait 30 minutes, and only then offer candidate #2 — if you'd rather fill one slot reliably than race three candidates against each other.

Owner who wants oversight without being the bottleneck

You never have to watch the sheet. Telegram — Owner: Offers Sent tells you who got contacted, Telegram — Owner: Window Closed confirms the sweep finished, Telegram — Owner: Slot Booked names the winner, and Telegram — Owner: Unmatched Reply flags messages that matched no open offer (a mistyped keyword, a question about a different appointment) so you or your staff can handle them personally. Tip: while testing, keep the reply window short (15–30 minutes), because the cancellation webhook's response waits for the window to close in lastNode mode — long windows make testing painfully slow.

← All templates