Text new enquiries a booking link, then chase once

Every hour, the workflow looks for enquiries added to your Airtable table in the last few days and texts each one your Cal.com booking link. If they still have not booked after the waiting period you choose, it sends exactly one reminder, then leaves them alone. Texts only go out between the hours you set (9:00 to 19:00 unless you change them), so nobody hears from you at 2am, and enquiries already in your table when you connect it are never texted.

Nobody who has booked is chased: before every text, Cal.com is checked for an upcoming booking under their email address. A row is marked 'Link sent' or 'Reminded' only once Twilio has accepted the text, so nobody is texted twice. A row with no usable mobile number or email address is marked 'Needs attention' for you to look at instead of being texted at a guess. If Twilio refuses a text, Cal.com cannot be checked or a row cannot be updated, the run is marked as failed and names the people affected, so a problem never looks like a quiet day.

You need Airtable, Cal.com and Twilio connected, and an Airtable table with 'Name', 'Phone', 'Email', 'Follow-up Status' and 'Follow-up Date' columns. Fill in the base and table, your Cal.com booking link, the Twilio number to text from, your business name and the country your customers' numbers are from.

Run every hour.Run every hour.

Set airtableBaseId to blank.Set airtableBaseId to blank.

Set airtableTableName to blank.Set airtableTableName to blank.

Set bookingLink to blank.Set bookingLink to blank.

Set twilioFromNumber to blank.Set twilioFromNumber to blank.

Set businessName to blank.Set businessName to blank.

Set phoneRegion to US.Set phoneRegion to US.

Set daysToWaitBeforeReminder to 3.Set daysToWaitBeforeReminder to 3.

Set maxTextsPerRun to 12.Set maxTextsPerRun to 12.

Set calcomEventTypeId to blank.Set calcomEventTypeId to blank.

Set enquiriesToCheck to 100.Set enquiriesToCheck to 100.

Set textingHoursStart to 9.Set textingHoursStart to 9.

Set textingHoursEnd to 19.Set textingHoursEnd to 19.

Set onlyTextEnquiriesFromLastDays to 3.Set onlyTextEnquiriesFromLastDays to 3.

Get today's date and save it as today.Get today's date and save it as today.

Get the current date and time and save it as rightNow.Get the current date and time and save it as rightNow.

Get the hour of rightNow and save it as hourNow.Get the hour of rightNow and save it as hourNow.

Set withinTextingHours to false.Set withinTextingHours to false.

If hourNow is greater than or equal to textingHoursStart multiplied by 1 and…If hourNow is greater than or equal to textingHoursStart multiplied by 1 and…

Yes

Change withinTextingHours to true.Change withinTextingHours to true.

No

No steps

Set invitesSent to 0.Set invitesSent to 0.

Set remindersSent to 0.Set remindersSent to 0.

Set alreadyBooked to 0.Set alreadyBooked to 0.

Set stillWaiting to 0.Set stillWaiting to 0.

Set skippedNoPhone to 0.Set skippedNoPhone to 0.

Set skippedNoEmail to 0.Set skippedNoEmail to 0.

Set skippedProblem to 0.Set skippedProblem to 0.

Set textsSent to 0.Set textsSent to 0.

Set hitTextLimit to false.Set hitTextLimit to false.

Set waitingForTextingHours to 0.Set waitingForTextingHours to 0.

Set textsTried to 0.Set textsTried to 0.

Set refusedIds to an empty list.Set refusedIds to an empty list.

Set notes to an empty list.Set notes to an empty list.

AirtableList records in airtableTableName. Save it as enquiryPage.List records in airtableTableName. Save it as enquiryPage.

Set enquiries to enquiryPage.records.Set enquiries to enquiryPage.records.

For each enquiry in enquiries:For each enquiry in enquiries:

If textsTried is greater than or equal to maxTextsPerRun multiplied by 1If textsTried is greater than or equal to maxTextsPerRun multiplied by 1

Yes

Change hitTextLimit to true.Change hitTextLimit to true.

Stop repeating.Stop repeating.

No

No steps

Set fields to enquiry.fields.Set fields to enquiry.fields.

Set followUpStatus to blank.Set followUpStatus to blank.

If a step below fails, save the error as statusUnreadableIf a step below fails, save the error as statusUnreadable

Try

Trim fields["Follow-up Status"] and save it as followUpStatus.Trim fields["Follow-up Status"] and save it as followUpStatus.

On error

Change followUpStatus to blank.Change followUpStatus to blank.

If followUpStatus isn't blank and followUpStatus is not Link sentIf followUpStatus isn't blank and followUpStatus is not Link sent

Yes

Skip to the next item.Skip to the next item.

No

No steps

Set personName to there.Set personName to there.

If a step below fails, save the error as nameUnreadableIf a step below fails, save the error as nameUnreadable

Try

Trim fields.Name and save it as personName.Trim fields.Name and save it as personName.

On error

Change personName to there.Change personName to there.

If personName is emptyIf personName is empty

Yes

Change personName to there.Change personName to there.

No

No steps

Split personName by a space and save it as nameParts.Split personName by a space and save it as nameParts.

Set mobile to blank.Set mobile to blank.

Set mobileUsable to false.Set mobileUsable to false.

If a step below fails, save the error as mobileUnreadableIf a step below fails, save the error as mobileUnreadable

Try

Trim fields.Phone and save it as mobile.Trim fields.Phone and save it as mobile.

Check whether mobile is a valid phone number and save it as mobileUsable.Check whether mobile is a valid phone number and save it as mobileUsable.

On error

Change mobileUsable to false.Change mobileUsable to false.

Set emailAddress to blank.Set emailAddress to blank.

Set emailUsable to false.Set emailUsable to false.

If a step below fails, save the error as emailUnreadableIf a step below fails, save the error as emailUnreadable

Try

Trim fields.Email and save it as emailAddress.Trim fields.Email and save it as emailAddress.

Check whether emailAddress is a valid email address and save it as emailUsable.Check whether emailAddress is a valid email address and save it as emailUsable.

On error

Change emailUsable to false.Change emailUsable to false.

Set unusableReason to blank.Set unusableReason to blank.

If mobileUsable is noIf mobileUsable is no

Yes

Add 1 to skippedNoPhone.Add 1 to skippedNoPhone.

Change unusableReason to no usable mobile number.Change unusableReason to no usable mobile number.

Else if @emailUsable is no

Add 1 to skippedNoEmail.Add 1 to skippedNoEmail.

Change unusableReason to no usable email address.Change unusableReason to no usable email address.

Otherwise

No steps

If unusableReason isn't blankIf unusableReason isn't blank

Yes

If a step below fails, save the error as markProblemIf a step below fails, save the error as markProblem

Try

AirtableUpdate enquiry in airtableTableName.Update enquiry in airtableTableName.

Add Marked 'Needs attention': personName - unusableReason to the end of notes and save it as notes.Add Marked 'Needs attention': personName - unusableReason to the end of notes and save it as notes.

On error

Add 1 to skippedProblem.Add 1 to skippedProblem.

Add Could not mark personName 'Needs attention' (unusableReason): markProblem.message to the end of notes and save it as notes.Add Could not mark personName 'Needs attention' (unusableReason): markProblem.message to the end of notes and save it as notes.

Skip to the next item.Skip to the next item.

No

No steps

Format the phone number mobile and save it as textTo.Format the phone number mobile and save it as textTo.

Build the text Hi @nameParts[0], and save it as greeting.Build the text Hi @nameParts[0], and save it as greeting.

Set inviteOpening to greetingthanks for getting in touch with businessName.Set inviteOpening to greetingthanks for getting in touch with businessName.

Set inviteText to inviteOpening. Pick a time that suits you here: bookingLink Reply STOP to opt out.Set inviteText to inviteOpening. Pick a time that suits you here: bookingLink Reply STOP to opt out.

Set reminderOpening to greetingwe have not got you booked in at businessName.Set reminderOpening to greetingwe have not got you booked in at businessName.

Set reminderText to reminderOpening yet. Here is the link again: bookingLink Reply STOP to opt out.Set reminderText to reminderOpening yet. Here is the link again: bookingLink Reply STOP to opt out.

Set textToSend to blank.Set textToSend to blank.

Set statusAfterText to blank.Set statusAfterText to blank.

If a step below fails, save the error as enquiryProblemIf a step below fails, save the error as enquiryProblem

Try

Cal.comList bookings. Save it as bookingLookup.List bookings. Save it as bookingLookup.

If bookingLookup.bookings isn't emptyIf bookingLookup.bookings isn't empty

Yes

Add 1 to alreadyBooked.Add 1 to alreadyBooked.

AirtableUpdate enquiry in airtableTableName.Update enquiry in airtableTableName.

Else if @followUpStatus is "Link sent"

Trim fields["Follow-up Date"] and save it as linkSentOn.Trim fields["Follow-up Date"] and save it as linkSentOn.

Count the days from linkSentOn to today and save it as daysWaited.Count the days from linkSentOn to today and save it as daysWaited.

If daysWaited is greater than or equal to daysToWaitBeforeReminder multiplied by…If daysWaited is greater than or equal to daysToWaitBeforeReminder multiplied by…

Yes

Change textToSend to reminderText.Change textToSend to reminderText.

Change statusAfterText to Reminded.Change statusAfterText to Reminded.

No

Add 1 to stillWaiting.Add 1 to stillWaiting.

Otherwise

Change textToSend to inviteText.Change textToSend to inviteText.

Change statusAfterText to Link sent.Change statusAfterText to Link sent.

On error

Add 1 to skippedProblem.Add 1 to skippedProblem.

Add Not checked, tried again next run: personName - enquiryProblem.message to the end of notes and save it as notes.Add Not checked, tried again next run: personName - enquiryProblem.message to the end of notes and save it as notes.

If textToSend is blankIf textToSend is blank

Yes

Skip to the next item.Skip to the next item.

No

No steps

If withinTextingHours is noIf withinTextingHours is no

Yes

Add 1 to waitingForTextingHours.Add 1 to waitingForTextingHours.

Skip to the next item.Skip to the next item.

No

No steps

Add 1 to textsTried.Add 1 to textsTried.

Set textWentOut to false.Set textWentOut to false.

If a step below fails, save the error as sendProblemIf a step below fails, save the error as sendProblem

Try

TwilioSend a text message to textTo. Save it as sentText.Send a text message to textTo. Save it as sentText.textToSend

Change textWentOut to true.Change textWentOut to true.

On error

Add enquiry.id to the end of refusedIds and save it as refusedIds.Add enquiry.id to the end of refusedIds and save it as refusedIds.

Add Twilio refused the text to personName (textTo): sendProblem.message to the end of notes and save it as notes.Add Twilio refused the text to personName (textTo): sendProblem.message to the end of notes and save it as notes.

If textWentOut is noIf textWentOut is no

Yes

Skip to the next item.Skip to the next item.

No

No steps

Add 1 to textsSent.Add 1 to textsSent.

If statusAfterText is RemindedIf statusAfterText is Reminded

Yes

Add 1 to remindersSent.Add 1 to remindersSent.

No

Add 1 to invitesSent.Add 1 to invitesSent.

If a step below fails, save the error as recordProblemIf a step below fails, save the error as recordProblem

Try

AirtableUpdate enquiry in airtableTableName.Update enquiry in airtableTableName.

On error

Stop and report a problem: Texted personName (textTo) but could not mark their row '@statusAfterText' in Airtable: recordProblem.message. Set their Follow-up Status to '@statusAfterText' by hand before the next run, or they will be texted again. Stopped here so nobody else is texted without a record.Stop and report a problem: Texted personName (textTo) but could not mark their row '@statusAfterText' in Airtable: recordProblem.message. Set their Follow-up Status to '@statusAfterText' by hand before the next run, or they will be texted again. Stopped here so nobody else is texted without a record.

Set refusedNote to blank.Set refusedNote to blank.

If refusedIds isn't emptyIf refusedIds isn't empty

Yes

If textsSent is greater than 0If textsSent is greater than 0

Yes

For each refusedId in refusedIds:For each refusedId in refusedIds:

If a step below fails, save the error as refusedMarkProblem If a step below fails, save the error as refusedMarkProblem

Try

AirtableUpdate refusedId in airtableTableName.Update refusedId in airtableTableName.

On error

Add Could not mark a refused text 'Needs attention': refusedMarkProblem.message to the end of notes and save it as notes.Add Could not mark a refused text 'Needs attention': refusedMarkProblem.message to the end of notes and save it as notes.

Change refusedNote to:Change refusedNote to:Other texts went out, so each refused number is marked 'Needs attention' - usually a landline, or someone who replied STOP. Fix the row and set Follow-up Status back to empty (or 'Link sent' for a reminder) to try again.

No

Change refusedNote to:Change refusedNote to:No text went out this run, which usually means a problem with your Twilio account or the 'twilioFromNumber' step. Nothing was marked, so the next run tries again.

No

No steps

Set summaryLines to [Booking links texted: invitesSent, Reminders texted: remindersSent, Already booked, marked Booked: alreadyBooked, Still inside the waiting period: stillWaiting, Due a text, held until texting hours: waitingForTextingHours, Marked 'Needs attention', no usable mobile number: skippedNoPhone, Marked 'Needs attention', no usable email address: skippedNoEmail, Texts Twilio refused: the length of refusedIds, Could not be checked: skippedProblem].Set summaryLines to [Booking links texted: invitesSent, Reminders texted: remindersSent, Already booked, marked Booked: alreadyBooked, Still inside the waiting period: stillWaiting, Due a text, held until texting hours: waitingForTextingHours, Marked 'Needs attention', no usable mobile number: skippedNoPhone, Marked 'Needs attention', no usable email address: skippedNoEmail, Texts Twilio refused: the length of refusedIds, Could not be checked: skippedProblem].

Join summaryLines with a new line and save it as summary.Join summaryLines with a new line and save it as summary.

Set limitNote to blank.Set limitNote to blank.

If hitTextLimit is yesIf hitTextLimit is yes

Yes

Change limitNote to Change limitNote to Stopped early at this run's limit of maxTextsPerRun texts. The rest are picked up next run.

No

No steps

Join notes with a new line and save it as noteList.Join notes with a new line and save it as noteList.

Build the text Enquiry follow-up finished. @summary@limitNote @noteList@refusedNote and save it as report.Build the text Enquiry follow-up finished. @summary@limitNote @noteList@refusedNote and save it as report.

If refusedIds isn't empty or skippedProblem is greater than 0If refusedIds isn't empty or skippedProblem is greater than 0

Yes

Stop and report a problem: report.Stop and report a problem: report.

No

No steps

Show in the output: report.Show in the output: report.