Win back no-show and cancelled appointments every morning

Every weekday at 10am I want an agent to recover the appointments that fell through in my LeadConnector sub-account. Run it against a single sub-account (locationId) that I configure once, and use that sub-account's own timezone when working out what counts as yesterday.

Start by pulling yesterday's appointments with List Calendar Events across every calendar in the sub-account, covering the full day from midnight to midnight. Keep only the events whose appointment status came back as no-show or cancelled. Appointment status lives on the calendar event itself, so this is a status filter on the events you already pulled rather than a separate lookup. If nothing fell through, post a one line Slack note saying so and stop.

For each missed appointment, pull the person with Get Contact so you have their name, phone, email, tags and assigned user. Then read what was already said: Search Conversations scoped to that contact, and Get Messages by Conversation on their most recent thread, so you know the history and the tone before you write.

Before writing anything, check the upcoming calendar events for the next 30 days with List Calendar Events and skip anyone who has already rebooked. Also enforce a cap of one recovery message per contact per week: skip a contact who is already carrying the no-show-recovery tag from a recent run, and cross check the last seven days of rows in the tracker with Get Values so repeat no-shows do not get spammed.

For everyone left, draft a short and specific rebooking message. Name the actual service or meeting they missed and the day it was booked for, offer to get them back on the calendar, and match the tone of the existing thread, so a casual thread gets a casual message and a formal one stays formal. Keep it to a couple of sentences with one clear question, and never invent details you did not read in the thread or on the appointment. Send it with Send Message as SMS when a mobile number is on file, and as email otherwise with a plain subject line. If a contact has neither a mobile number nor an email address, skip them and call it out in the summary.

After a message goes out, tag the contact with Add Tags to Contact using no-show-recovery, and create a follow-up task due in two days for the rep assigned to the appointment with Create Contact Task. Use List Users to turn the assigned user id on the calendar event into a rep name for the task and for the tracker row.

Append one row per missed appointment to a Google Sheets tracker with Append Values: date, contact, calendar, rep, whether it was a no-show or a cancellation, the channel used, and the message that was sent. Append rather than overwrite so the sheet builds a no-show rate trend over time.

Finish by posting a short Slack summary with Send a Message: how many appointments fell through, the no-show rate versus last week (read the earlier rows out of the tracker with Get Values to work that out), how many people you reached out to and on which channel, who you skipped and why, and the highest value people you contacted so the team knows who to watch for a reply.

LeadConnector calls need the Version header and a locationId. Never send more than one recovery message to the same person in a single run, and if a send fails, report the failure in the Slack summary rather than retrying in a loop.