Voter Survey Workflow

Production-ready Node.js workflow for managing voter surveys with Airtable, Twilio SMS, and Typeform integration.

Features

✅ CSV Import & Cleaning

• Normalizes phone numbers to E.164 format
• Removes duplicates (by phone and VANID)
• Generates unique UTM tracking IDs
• Creates personalized survey links

✅ Airtable Integration

• Single table design for all data and tracking
• Automatic create/update (no duplicates)
• Real-time SMS status tracking
• Survey completion tracking

✅ Twilio SMS Sending

• Rate-limited batch processing
• Personalized messages
• Automatic status updates
• Error handling and retry logic

✅ Typeform Webhook Support

• Real-time survey completion tracking
• Vote choice capture
• Automatic Airtable updates

Setup

1. Install Dependencies

npm install

2. Configure Airtable

Create a table called Voter Survey with these fields:

Field Name	Field Type	Notes
Zipcode	Single line text
VANID	Single line text	Primary identifier
FirstName	Single line text
LastName	Single line text
Sex	Single line text
Age	Number	Integer
Address	Single line text
City	Single line text
State	Single line text
Email	Email
Phone	Phone number	E.164 format
UTM_ID	Single line text	Unique tracking ID
UTM_Campaign	Single line text	Campaign identifier
Survey Link	URL	Personalized survey URL
Message Template	Long text	SMS message
SMS Sent At	Date	ISO timestamp
SMS Status	Single select	Options: Sent, Delivered, Failed
Survey Completed At	Date	ISO timestamp
Vote Choice	Single line text	Survey response
Sent By	Single line text	Optional
Notes	Long text	Optional

3. Configure Environment Variables

Copy .env.example to .env and fill in your credentials:

cp .env.example .env

Required variables:

• AIRTABLE_API_KEY: From https://airtable.com/account
• AIRTABLE_BASE_ID: From your Airtable base URL
• TWILIO_ACCOUNT_SID: From https://console.twilio.com/
• TWILIO_AUTH_TOKEN: From Twilio console
• TWILIO_FROM_NUMBER: Your Twilio phone number (E.164 format)

Optional variables:

• SURVEY_BASE_URL: Your survey landing page
• UTM_CAMPAIGN: Default campaign name
• PORT: Webhook server port (default: 3000)

4. Configure Typeform

In your Typeform survey:

1. Add a Hidden Field called utm_id
2. Add a Hidden Field called utm_campaign
3. Create your vote choice question with reference vote_choice
4. Go to Connect → Webhooks
5. Add webhook URL: http://your-server:3000/webhook/typeform

Usage

Import CSV and Sync to Airtable

# Import only (no SMS)
node voter-survey-workflow.js import voters.csv

# Import and send SMS immediately
node voter-survey-workflow.js import voters.csv --send-sms

Send SMS to Unsent Records

# Send SMS to all records without SMS status
node voter-survey-workflow.js send-sms

# Or use npm script
npm run send-sms

Start Webhook Server

# Start webhook server for Typeform responses
node voter-survey-workflow.js webhook

# Or use npm script
npm run webhook

# For development with auto-reload
npm run dev

Workflow Details

Step 1: CSV Cleaning

The script processes your voter CSV and:

1. Chooses phone number: Prioritizes Home Phone, falls back to LandlinePhone
2. Normalizes phones: Converts to E.164 format (+15551234567)
3. Validates phones: Uses libphonenumber-js for robust validation
4. Removes duplicates: By phone number and VANID
5. Generates tracking: Creates unique 8-character UTM_ID
6. Builds survey links: Personalized URLs with tracking parameters
7. Creates messages: Dynamic SMS templates with first name

Step 2: Airtable Sync

The script syncs data to Airtable:

1. Fetches existing records: Checks for existing VANIDs
2. Updates existing: Updates records that already exist
3. Creates new: Adds new voter records
4. Batch processing: Handles Airtable's 10-record batch limit
5. Error handling: Retries failed operations

Step 3: SMS Sending

The script sends SMS via Twilio:

1. Rate limiting: 10 messages/second (configurable)
2. Status tracking: Updates Airtable with Sent/Failed status
3. Timestamp logging: Records exact send time
4. Error handling: Catches and logs failed sends
5. Progress reporting: Console output for each message

Step 4: Survey Tracking

Typeform webhook updates Airtable:

1. Receives webhook: Typeform sends response data
2. Extracts UTM_ID: From hidden fields
3. Captures vote choice: From survey answer
4. Updates Airtable: Sets completion timestamp and choice
5. Returns success: Acknowledges webhook

Analytics & Reporting

Airtable Views

Create these views for easy analysis:

SMS Performance

• Filter: SMS Status is not empty
• Group by: SMS Status
• Shows: Sent, Delivered, Failed counts

Survey Responses

• Filter: Survey Completed At is not empty
• Group by: Vote Choice
• Shows: Response breakdown by candidate

Response Rate

• Formula field: IF({Survey Completed At}, "Completed", "Not Started")
• Group by: Response status
• Shows: Completion rate

Campaign Performance

• Group by: UTM_Campaign
• Shows: Results by campaign

Sample Analytics Queries

// Get response rate
const totalSent = records.filter((r) => r.get("SMS Status") === "Sent").length;
const totalCompleted = records.filter((r) =>
  r.get("Survey Completed At")
).length;
const responseRate = ((totalCompleted / totalSent) * 100).toFixed(2);

// Get vote breakdown
const voteCounts = records.reduce((acc, r) => {
  const choice = r.get("Vote Choice");
  if (choice) acc[choice] = (acc[choice] || 0) + 1;
  return acc;
}, {});

Production Deployment

Option 1: Deploy Webhook Server

For webhook functionality, deploy to a server:

# Using PM2 for process management
npm install -g pm2
pm2 start voter-survey-workflow.js --name voter-webhook -- webhook
pm2 save
pm2 startup

Option 2: Serverless (AWS Lambda, Google Cloud Functions)

Export the webhook handler:

exports.handler = async (event) => {
  const body = JSON.parse(event.body);
  // Process webhook...
  return { statusCode: 200, body: JSON.stringify({ success: true }) };
};

Option 3: Scheduled Imports

Use cron or task scheduler:

# Run daily at 9 AM
0 9 * * * cd /path/to/voter-survey && node voter-survey-workflow.js import daily-voters.csv --send-sms

Security Best Practices

1. Environment Variables

Never commit .env files to git:

# Add to .gitignore
echo ".env" >> .gitignore

2. API Key Rotation

Regularly rotate your API keys:

• Airtable: Regenerate from account settings
• Twilio: Regenerate auth token from console

3. Webhook Authentication

Add webhook verification (optional enhancement):

// In webhook endpoint
const crypto = require("crypto");

function verifyTypeformSignature(req, secret) {
  const signature = req.headers["typeform-signature"];
  const body = JSON.stringify(req.body);
  const hash = crypto
    .createHmac("sha256", secret)
    .update(body)
    .digest("base64");
  return signature === `sha256=${hash}`;
}

4. Rate Limiting

The script includes built-in rate limiting for Twilio. For production:

• Monitor Twilio usage dashboard
• Adjust config.sms.rateLimit based on your account limits
• Consider implementing exponential backoff for retries

Troubleshooting

Common Issues

Issue: "Invalid phone number" errors

• Check phone format in CSV (should include country code or be valid US numbers)
• Review console warnings for specific invalid numbers
• Adjust defaultCountry parameter in normalizePhone() if needed

Issue: Airtable API rate limits

• Airtable allows 5 requests/second
• Script handles batching automatically
• If issues persist, add delays between batch operations

Issue: Twilio SMS failures

• Verify TWILIO_FROM_NUMBER is correct and purchased
• Check Twilio console for error codes
• Ensure recipient numbers are valid and can receive SMS
• Check account balance and geographic permissions

Issue: Webhook not receiving data

• Verify server is publicly accessible (use ngrok for local testing)
• Check Typeform webhook logs for delivery status
• Ensure webhook URL is correct in Typeform settings
• Review server logs for incoming requests

Issue: Duplicate records

• Script prevents duplicates by VANID and phone
• If duplicates occur, check CSV for malformed VANIDs
• Manually clean Airtable before re-importing

Debug Mode

Enable verbose logging:

// Add to top of voter-survey-workflow.js
process.env.DEBUG = "true";

// Then add debug logs throughout:
if (process.env.DEBUG) {
  console.log("Debug:", JSON.stringify(data, null, 2));
}

Testing Locally

Use ngrok to expose webhook server:

# Install ngrok
npm install -g ngrok

# Start webhook server
node voter-survey-workflow.js webhook

# In another terminal, expose port
ngrok http 3000

# Use the ngrok URL in Typeform webhook settings
# Example: https://abc123.ngrok.io/webhook/typeform

Advanced Customization

Custom SMS Templates

Edit buildSMSMessage() function:

function buildSMSMessage(firstName, surveyLink) {
  return `Hey ${firstName}! 🗳️ Your opinion matters! Take our 2-min survey: ${surveyLink} Text STOP to unsubscribe.`;
}

Multiple Campaigns

Pass campaign name via CSV or command line:

// Add campaign column to CSV
const utmCampaign = row["Campaign"] || config.survey.defaultCampaign;

Survey Link Customization

Modify buildSurveyLink() to add more parameters:

function buildSurveyLink(utmId, campaign, vanid) {
  return `${config.survey.baseUrl}?utm_id=${utmId}&utm_campaign=${campaign}&vanid=${vanid}`;
}

Conditional SMS Sending

Add filters before sending:

async function sendSMSToUnsent() {
  const unsentRecords = await airtableBase(config.airtable.tableName)
    .select({
      filterByFormula: `AND(
        {Phone} != "", 
        OR({SMS Status} = "", {SMS Status} = BLANK()),
        {Age} >= 18,
        {City} = "Jersey City"
      )`,
    })
    .firstPage();

  // ... rest of function
}

Retry Failed SMS

Add a retry command:

async function retrySMS() {
  const failedRecords = await airtableBase(config.airtable.tableName)
    .select({
      filterByFormula: `{SMS Status} = "Failed"`
    })
    .firstPage();

  console.log(`Retrying ${failedRecords.length} failed messages...`);
  await sendSMSBatch(failedRecords);
}

// Add to CLI commands
else if (command === 'retry-sms') {
  retrySMS().then(() => process.exit(0));
}

Monitoring & Metrics

Key Metrics to Track

1. Import Success Rate

   • Total records in CSV
   • Successfully cleaned records
   • Skipped/invalid records

2. SMS Delivery Rate

   • Sent count
   • Delivered count (via Twilio webhooks)
   • Failed count

3. Survey Response Rate

   • SMS sent
   • Surveys completed
   • Completion percentage

4. Vote Distribution

   • Breakdown by candidate/choice
   • Geographic distribution
   • Demographic patterns

Logging to File

Add file logging for production:

const fs = require("fs").promises;

async function logToFile(message) {
  const timestamp = new Date().toISOString();
  const logLine = `[${timestamp}] ${message}\n`;
  await fs.appendFile("workflow.log", logLine);
}

Cost Estimation

Twilio SMS Costs

• US/Canada: ~$0.0079 per SMS
• 1,000 voters: ~$7.90
• 10,000 voters: ~$79.00

Airtable Costs

• Free tier: Up to 1,200 records per base
• Plus: $10/user/month (50,000 records)
• Pro: $20/user/month (100,000 records)

Hosting Costs

• Free options: Heroku free tier, Railway, Render
• Paid options: AWS ($5-10/month), DigitalOcean ($5/month)

License

MIT License - Feel free to use and modify for your campaigns.

Support

For issues or questions:

1. Check troubleshooting section above
2. Review Airtable/Twilio/Typeform documentation
3. Check console logs for detailed error messages

Contributing

To extend this workflow:

1. Fork the repository
2. Add your feature in a new function
3. Update CLI commands in the main block
4. Test thoroughly with sample data
5. Document your changes in this README