How to Send Personalized SMS Campaigns at Scale

The Goal

You want to send a segmented, personalized SMS to a list of contacts — first name, custom offer, location-aware copy — without messages going to spam, without violating opt-in laws, and without sitting at your desk for two hours hitting send. You also want to capture replies and track who opted out.

The Tools

• Twilio — SMS infrastructure (~$0.0083 per outbound message, $1.15/mo per phone number)
• Google Sheets — your contact list and merge data
• Google Apps Script — to loop through rows and call the Twilio API
• A2P 10DLC registration — required for US-bound SMS, takes 2–6 weeks
• A documented opt-in record for every recipient (this is non-negotiable)

Step 1: Provision Twilio

Open a Twilio account, fund it with $20, buy a US local number, and complete A2P 10DLC brand and campaign registration. Brand registration is $4 one-time. Campaign registration is $10 plus $1.50/mo. Approval takes 2–6 weeks. Without this, your throughput is capped at ~1 message per second and most carriers will silently filter your messages within days.

Step 2: Prepare Your Contact List

In a Google Sheet, set up columns for phone, first name, last name, segment tag, custom field 1, custom field 2, opt-in date, opt-in source, and opt-out flag. Phone numbers must be in E.164 format (+15555551234). Anything else and Twilio rejects the request.

Filter out any row where opt-out flag is true. Yes, every time. Forgetting this once will earn you a stern letter from the FCC.

Step 3: Write the Apps Script

The script reads your sheet, loops through rows, builds the personalized message via string interpolation, and POSTs to the Twilio API for each contact:

1. Authenticate with your Twilio Account SID and Auth Token (store these in Script Properties, never in the script body).
2. Read the active sheet's range, skip the header row.
3. For each row, build the message: const msg = template.replace('{{firstName}}', row.firstName).replace('{{offer}}', row.offer);
4. POST to https://api.twilio.com/2010-04-01/Accounts/{SID}/Messages.json with form-encoded body.
5. Sleep for 1100ms between sends to stay under the 1 msg/sec rate limit (10 msg/sec post-A2P).
6. Log success or failure to a status column on the sheet.

Step 4: Handle Replies and Opt-Outs

Configure a Twilio webhook URL pointing at a deployed Apps Script web endpoint. Every inbound message hits your endpoint with the sender's phone and message body. Your script needs to:

• Look up the sender's row in your sheet.
• Log the reply text to a "last reply" column.
• If the message body matches STOP, UNSUBSCRIBE, END, QUIT, or CANCEL, set the opt-out flag to true (Twilio also auto-blocks but you must mirror this in your sheet).
• Optionally email you a notification so you can respond personally.

Step 5: Compliance Boilerplate

Every initial campaign message must include your business name and an opt-out instruction. Something like: "[Acme Co] Hey {{firstName}}, ... Reply STOP to opt out." Skipping this is a TCPA violation even with consent on file.

Quiet hours matter too. Many states prohibit marketing SMS before 8am or after 9pm local time. Your script needs to know each contact's timezone and respect it — or you batch sends only during 10am–6pm Eastern and accept that you are missing the West Coast window.

Step 6: Segmentation

Real campaigns are not blasts. They are sliced by behavior, source, geography, and lifecycle stage. With Sheets, segmentation is done by filtering rows before the script runs. With manual filtering, you can run maybe 5–10 segments per campaign before the operational complexity makes you stop bothering — which is when SMS becomes a generic blast and your reply rate collapses.

Realistic Time Investment

• Twilio setup and A2P: 2–3 hours active plus 4–6 weeks of waiting
• Apps Script send loop: 8–12 hours including testing
• Reply webhook and opt-out handling: 6–10 hours
• Per-campaign prep (segmentation, copy, testing): 2–4 hours
• Per-campaign send and monitoring: 1–2 hours
• Compliance audit: ongoing

Where This Breaks

Three places, in order of how often we have seen it. First, a sheet-edit mistake re-sends to opted-out contacts and triggers a TCPA complaint. Second, the Apps Script execution time limit (6 minutes for free Workspace, 30 minutes for paid) kills a large send mid-batch and you have to reverse-engineer where it stopped. Third, your A2P registration gets flagged for "promotional content" you did not realize was promotional, and your throughput drops to 200 messages per day until you re-register.

None of these are fatal individually. Together, they are the reason most small businesses give up on DIY SMS within six months and either go silent or start spamming from their personal phone (which gets that number blocked by carriers within weeks).

SMS is the most powerful marketing channel you can run. It is also the most regulated. The free version costs you in compliance risk and operator hours.