Required consent documentation per subscriber:
- Phone number
- Timestamp of consent (UTC)
- IP address (for web opt-ins)
- Exact consent language shown to the subscriber
- Source (web form URL, paper form scan, verbal recording)
- Campaign(s) consented to
- Expected message frequency disclosed
Required consent documentation per subscriber:
- Phone number
- Timestamp of consent (UTC)
- IP address (for web opt-ins)
- Exact consent language shown to the subscriber
- Source (web form URL, paper form scan, verbal recording)
- Campaign(s) consented to
- Expected message frequency disclosed
Required consent documentation per subscriber:
- Phone number
- Timestamp of consent (UTC)
- IP address (for web opt-ins)
- Exact consent language shown to the subscriber
- Source (web form URL, paper form scan, verbal recording)
- Campaign(s) consented to
- Expected message frequency disclosed
Reply STOP to unsubscribe. Msg & data rates may apply.
Reply STOP to unsubscribe. Msg & data rates may apply.
Reply STOP to unsubscribe. Msg & data rates may apply.
VICIdial Agent dispositions call → vicidial_log updated ↓
Polling script reads new dispositions every 30-60 seconds ↓
Disposition-to-SMS mapping determines which template to send ↓
API call to Telnyx/Twilio/SignalWire sends the message ↓
SMS delivery status logged to sms_log table ↓
Inbound replies routed back to agent screen or queue
VICIdial Agent dispositions call → vicidial_log updated ↓
Polling script reads new dispositions every 30-60 seconds ↓
Disposition-to-SMS mapping determines which template to send ↓
API call to Telnyx/Twilio/SignalWire sends the message ↓
SMS delivery status logged to sms_log table ↓
Inbound replies routed back to agent screen or queue
VICIdial Agent dispositions call → vicidial_log updated ↓
Polling script reads new dispositions every 30-60 seconds ↓
Disposition-to-SMS mapping determines which template to send ↓
API call to Telnyx/Twilio/SignalWire sends the message ↓
SMS delivery status logged to sms_log table ↓
Inbound replies routed back to agent screen or queue
#!/usr/bin/env python3
"""sms_trigger.py - Send SMS based on VICIdial call dispositions""" import os
import json
import time
import requests
import mysql.connector
from datetime import datetime, timedelta # Configuration
DB_CONFIG = { "host": "localhost", "user": "cron", "password": os.environ.get("VICI_DB_PASS", ""), "database": "vicidial"
} # Telnyx API (swap for Twilio/SignalWire as needed)
TELNYX_API_KEY = os.environ.get("TELNYX_API_KEY", "")
TELNYX_FROM_NUMBER = "+15551234567"
TELNYX_MESSAGING_PROFILE = os.environ.get("TELNYX_MSG_PROFILE", "") # Disposition-to-SMS mapping
DISPOSITION_SMS_MAP = { "NA": { "template": "Hi {first_name}, we tried reaching you about {campaign_topic}. " "Text back a good time to talk. Reply STOP to opt out.", "delay_seconds": 60, "max_sends": 2, "cooldown_hours": 24 }, "AM": { "template": "Hi {first_name}, we left you a voicemail about {campaign_topic}. " "Have a quick question? Text us back. Reply STOP to opt out.", "delay_seconds": 120, "max_sends": 1, "cooldown_hours": 48 }, "CALLBK": { "template": "Hi {first_name}, confirming your callback for {callback_date}. " "Text YES to confirm or suggest a new time. Reply STOP to opt out.", "delay_seconds": 30, "max_sends": 1, "cooldown_hours": 0 }, "SALE": { "template": "Thanks {first_name}! Your enrollment is confirmed. " "Your rep {agent_name} is your point of contact. " "Reply STOP to opt out.", "delay_seconds": 300, "max_sends": 1, "cooldown_hours": 0 }
} def get_new_dispositions(since_minutes=2): """Pull recent call dispositions from VICIdial.""" conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT v.uniqueid, v.lead_id, v.user AS agent_user, v.status AS disposition, v.phone_number, v.call_date, v.campaign_id, l.first_name, l.last_name FROM vicidial_log v JOIN vicidial_list l ON v.lead_id = l.lead_id WHERE v.call_date >= NOW() - INTERVAL %s MINUTE AND v.status IN ('NA', 'AM', 'CALLBK', 'SALE') AND v.phone_number NOT IN ( SELECT phone_number FROM sms_dnc_list ) AND v.phone_number NOT IN ( SELECT phone_number FROM sms_log WHERE sent_at >= NOW() - INTERVAL 24 HOUR AND disposition = v.status ) ORDER BY v.call_date DESC """, (since_minutes,)) rows = cursor.fetchall() cursor.close() conn.close() return rows def send_sms(to_number, message): """Send SMS via Telnyx API.""" resp = requests.post( "https://api.telnyx.com/v2/messages", headers={ "Authorization": f"Bearer {TELNYX_API_KEY}", "Content-Type": "application/json" }, json={ "from": TELNYX_FROM_NUMBER, "to": f"+1{to_number}", "text": message, "messaging_profile_id": TELNYX_MESSAGING_PROFILE } ) return resp.status_code == 200, resp.json() def log_sms(lead_id, phone_number, disposition, message, status): """Log SMS send to database for tracking and deduplication.""" conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor() cursor.execute(""" INSERT INTO sms_log (lead_id, phone_number, disposition, message, status, sent_at) VALUES (%s, %s, %s, %s, %s, NOW()) """, (lead_id, phone_number, disposition, message, status)) conn.commit() cursor.close() conn.close() def process_dispositions(): """Main processing loop.""" dispositions = get_new_dispositions(since_minutes=2) for dispo in dispositions: sms_config = DISPOSITION_SMS_MAP.get(dispo["disposition"]) if not sms_config: continue message = sms_config["template"].format( first_name=dispo.get("first_name", "there"), campaign_topic="your recent inquiry", callback_date="your scheduled time", agent_name=dispo.get("agent_user", "your representative") ) # Check timing restriction (8 AM - 9 PM local) hour = datetime.now().hour if hour < 8 or hour >= 21: log_sms(dispo["lead_id"], dispo["phone_number"], dispo["disposition"], message, "deferred_time") continue success, resp = send_sms(dispo["phone_number"], message) log_sms(dispo["lead_id"], dispo["phone_number"], dispo["disposition"], message, "sent" if success else "failed") if __name__ == "__main__": process_dispositions()
#!/usr/bin/env python3
"""sms_trigger.py - Send SMS based on VICIdial call dispositions""" import os
import json
import time
import requests
import mysql.connector
from datetime import datetime, timedelta # Configuration
DB_CONFIG = { "host": "localhost", "user": "cron", "password": os.environ.get("VICI_DB_PASS", ""), "database": "vicidial"
} # Telnyx API (swap for Twilio/SignalWire as needed)
TELNYX_API_KEY = os.environ.get("TELNYX_API_KEY", "")
TELNYX_FROM_NUMBER = "+15551234567"
TELNYX_MESSAGING_PROFILE = os.environ.get("TELNYX_MSG_PROFILE", "") # Disposition-to-SMS mapping
DISPOSITION_SMS_MAP = { "NA": { "template": "Hi {first_name}, we tried reaching you about {campaign_topic}. " "Text back a good time to talk. Reply STOP to opt out.", "delay_seconds": 60, "max_sends": 2, "cooldown_hours": 24 }, "AM": { "template": "Hi {first_name}, we left you a voicemail about {campaign_topic}. " "Have a quick question? Text us back. Reply STOP to opt out.", "delay_seconds": 120, "max_sends": 1, "cooldown_hours": 48 }, "CALLBK": { "template": "Hi {first_name}, confirming your callback for {callback_date}. " "Text YES to confirm or suggest a new time. Reply STOP to opt out.", "delay_seconds": 30, "max_sends": 1, "cooldown_hours": 0 }, "SALE": { "template": "Thanks {first_name}! Your enrollment is confirmed. " "Your rep {agent_name} is your point of contact. " "Reply STOP to opt out.", "delay_seconds": 300, "max_sends": 1, "cooldown_hours": 0 }
} def get_new_dispositions(since_minutes=2): """Pull recent call dispositions from VICIdial.""" conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT v.uniqueid, v.lead_id, v.user AS agent_user, v.status AS disposition, v.phone_number, v.call_date, v.campaign_id, l.first_name, l.last_name FROM vicidial_log v JOIN vicidial_list l ON v.lead_id = l.lead_id WHERE v.call_date >= NOW() - INTERVAL %s MINUTE AND v.status IN ('NA', 'AM', 'CALLBK', 'SALE') AND v.phone_number NOT IN ( SELECT phone_number FROM sms_dnc_list ) AND v.phone_number NOT IN ( SELECT phone_number FROM sms_log WHERE sent_at >= NOW() - INTERVAL 24 HOUR AND disposition = v.status ) ORDER BY v.call_date DESC """, (since_minutes,)) rows = cursor.fetchall() cursor.close() conn.close() return rows def send_sms(to_number, message): """Send SMS via Telnyx API.""" resp = requests.post( "https://api.telnyx.com/v2/messages", headers={ "Authorization": f"Bearer {TELNYX_API_KEY}", "Content-Type": "application/json" }, json={ "from": TELNYX_FROM_NUMBER, "to": f"+1{to_number}", "text": message, "messaging_profile_id": TELNYX_MESSAGING_PROFILE } ) return resp.status_code == 200, resp.json() def log_sms(lead_id, phone_number, disposition, message, status): """Log SMS send to database for tracking and deduplication.""" conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor() cursor.execute(""" INSERT INTO sms_log (lead_id, phone_number, disposition, message, status, sent_at) VALUES (%s, %s, %s, %s, %s, NOW()) """, (lead_id, phone_number, disposition, message, status)) conn.commit() cursor.close() conn.close() def process_dispositions(): """Main processing loop.""" dispositions = get_new_dispositions(since_minutes=2) for dispo in dispositions: sms_config = DISPOSITION_SMS_MAP.get(dispo["disposition"]) if not sms_config: continue message = sms_config["template"].format( first_name=dispo.get("first_name", "there"), campaign_topic="your recent inquiry", callback_date="your scheduled time", agent_name=dispo.get("agent_user", "your representative") ) # Check timing restriction (8 AM - 9 PM local) hour = datetime.now().hour if hour < 8 or hour >= 21: log_sms(dispo["lead_id"], dispo["phone_number"], dispo["disposition"], message, "deferred_time") continue success, resp = send_sms(dispo["phone_number"], message) log_sms(dispo["lead_id"], dispo["phone_number"], dispo["disposition"], message, "sent" if success else "failed") if __name__ == "__main__": process_dispositions()
#!/usr/bin/env python3
"""sms_trigger.py - Send SMS based on VICIdial call dispositions""" import os
import json
import time
import requests
import mysql.connector
from datetime import datetime, timedelta # Configuration
DB_CONFIG = { "host": "localhost", "user": "cron", "password": os.environ.get("VICI_DB_PASS", ""), "database": "vicidial"
} # Telnyx API (swap for Twilio/SignalWire as needed)
TELNYX_API_KEY = os.environ.get("TELNYX_API_KEY", "")
TELNYX_FROM_NUMBER = "+15551234567"
TELNYX_MESSAGING_PROFILE = os.environ.get("TELNYX_MSG_PROFILE", "") # Disposition-to-SMS mapping
DISPOSITION_SMS_MAP = { "NA": { "template": "Hi {first_name}, we tried reaching you about {campaign_topic}. " "Text back a good time to talk. Reply STOP to opt out.", "delay_seconds": 60, "max_sends": 2, "cooldown_hours": 24 }, "AM": { "template": "Hi {first_name}, we left you a voicemail about {campaign_topic}. " "Have a quick question? Text us back. Reply STOP to opt out.", "delay_seconds": 120, "max_sends": 1, "cooldown_hours": 48 }, "CALLBK": { "template": "Hi {first_name}, confirming your callback for {callback_date}. " "Text YES to confirm or suggest a new time. Reply STOP to opt out.", "delay_seconds": 30, "max_sends": 1, "cooldown_hours": 0 }, "SALE": { "template": "Thanks {first_name}! Your enrollment is confirmed. " "Your rep {agent_name} is your point of contact. " "Reply STOP to opt out.", "delay_seconds": 300, "max_sends": 1, "cooldown_hours": 0 }
} def get_new_dispositions(since_minutes=2): """Pull recent call dispositions from VICIdial.""" conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT v.uniqueid, v.lead_id, v.user AS agent_user, v.status AS disposition, v.phone_number, v.call_date, v.campaign_id, l.first_name, l.last_name FROM vicidial_log v JOIN vicidial_list l ON v.lead_id = l.lead_id WHERE v.call_date >= NOW() - INTERVAL %s MINUTE AND v.status IN ('NA', 'AM', 'CALLBK', 'SALE') AND v.phone_number NOT IN ( SELECT phone_number FROM sms_dnc_list ) AND v.phone_number NOT IN ( SELECT phone_number FROM sms_log WHERE sent_at >= NOW() - INTERVAL 24 HOUR AND disposition = v.status ) ORDER BY v.call_date DESC """, (since_minutes,)) rows = cursor.fetchall() cursor.close() conn.close() return rows def send_sms(to_number, message): """Send SMS via Telnyx API.""" resp = requests.post( "https://api.telnyx.com/v2/messages", headers={ "Authorization": f"Bearer {TELNYX_API_KEY}", "Content-Type": "application/json" }, json={ "from": TELNYX_FROM_NUMBER, "to": f"+1{to_number}", "text": message, "messaging_profile_id": TELNYX_MESSAGING_PROFILE } ) return resp.status_code == 200, resp.json() def log_sms(lead_id, phone_number, disposition, message, status): """Log SMS send to database for tracking and deduplication.""" conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor() cursor.execute(""" INSERT INTO sms_log (lead_id, phone_number, disposition, message, status, sent_at) VALUES (%s, %s, %s, %s, %s, NOW()) """, (lead_id, phone_number, disposition, message, status)) conn.commit() cursor.close() conn.close() def process_dispositions(): """Main processing loop.""" dispositions = get_new_dispositions(since_minutes=2) for dispo in dispositions: sms_config = DISPOSITION_SMS_MAP.get(dispo["disposition"]) if not sms_config: continue message = sms_config["template"].format( first_name=dispo.get("first_name", "there"), campaign_topic="your recent inquiry", callback_date="your scheduled time", agent_name=dispo.get("agent_user", "your representative") ) # Check timing restriction (8 AM - 9 PM local) hour = datetime.now().hour if hour < 8 or hour >= 21: log_sms(dispo["lead_id"], dispo["phone_number"], dispo["disposition"], message, "deferred_time") continue success, resp = send_sms(dispo["phone_number"], message) log_sms(dispo["lead_id"], dispo["phone_number"], dispo["disposition"], message, "sent" if success else "failed") if __name__ == "__main__": process_dispositions()
CREATE TABLE IF NOT EXISTS sms_log ( id INT AUTO_INCREMENT PRIMARY KEY, lead_id INT NOT NULL, phone_number VARCHAR(20) NOT NULL, disposition VARCHAR(10), message TEXT, status ENUM('sent','failed','delivered','deferred_time','opted_out') DEFAULT 'sent', sent_at DATETIME NOT NULL, delivered_at DATETIME, response_text TEXT, response_at DATETIME, INDEX idx_phone_date (phone_number, sent_at), INDEX idx_lead (lead_id), INDEX idx_status (status, sent_at)
) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_dnc_list ( phone_number VARCHAR(20) PRIMARY KEY, opted_out_at DATETIME NOT NULL, opt_out_keyword VARCHAR(20), source VARCHAR(50) DEFAULT 'sms_reply'
) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_templates ( id INT AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(100) NOT NULL, disposition VARCHAR(10), campaign_id VARCHAR(20), message_text TEXT NOT NULL, delay_seconds INT DEFAULT 60, max_sends INT DEFAULT 1, cooldown_hours INT DEFAULT 24, active TINYINT DEFAULT 1, created_at DATETIME DEFAULT NOW(), UNIQUE KEY idx_dispo_campaign (disposition, campaign_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS sms_log ( id INT AUTO_INCREMENT PRIMARY KEY, lead_id INT NOT NULL, phone_number VARCHAR(20) NOT NULL, disposition VARCHAR(10), message TEXT, status ENUM('sent','failed','delivered','deferred_time','opted_out') DEFAULT 'sent', sent_at DATETIME NOT NULL, delivered_at DATETIME, response_text TEXT, response_at DATETIME, INDEX idx_phone_date (phone_number, sent_at), INDEX idx_lead (lead_id), INDEX idx_status (status, sent_at)
) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_dnc_list ( phone_number VARCHAR(20) PRIMARY KEY, opted_out_at DATETIME NOT NULL, opt_out_keyword VARCHAR(20), source VARCHAR(50) DEFAULT 'sms_reply'
) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_templates ( id INT AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(100) NOT NULL, disposition VARCHAR(10), campaign_id VARCHAR(20), message_text TEXT NOT NULL, delay_seconds INT DEFAULT 60, max_sends INT DEFAULT 1, cooldown_hours INT DEFAULT 24, active TINYINT DEFAULT 1, created_at DATETIME DEFAULT NOW(), UNIQUE KEY idx_dispo_campaign (disposition, campaign_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS sms_log ( id INT AUTO_INCREMENT PRIMARY KEY, lead_id INT NOT NULL, phone_number VARCHAR(20) NOT NULL, disposition VARCHAR(10), message TEXT, status ENUM('sent','failed','delivered','deferred_time','opted_out') DEFAULT 'sent', sent_at DATETIME NOT NULL, delivered_at DATETIME, response_text TEXT, response_at DATETIME, INDEX idx_phone_date (phone_number, sent_at), INDEX idx_lead (lead_id), INDEX idx_status (status, sent_at)
) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_dnc_list ( phone_number VARCHAR(20) PRIMARY KEY, opted_out_at DATETIME NOT NULL, opt_out_keyword VARCHAR(20), source VARCHAR(50) DEFAULT 'sms_reply'
) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS sms_templates ( id INT AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(100) NOT NULL, disposition VARCHAR(10), campaign_id VARCHAR(20), message_text TEXT NOT NULL, delay_seconds INT DEFAULT 60, max_sends INT DEFAULT 1, cooldown_hours INT DEFAULT 24, active TINYINT DEFAULT 1, created_at DATETIME DEFAULT NOW(), UNIQUE KEY idx_dispo_campaign (disposition, campaign_id)
) ENGINE=InnoDB;
# Run disposition-triggered SMS every 2 minutes during operating hours
*/2 8-20 * * 1-6 python3 /opt/sms-integration/sms_trigger.py >> /var/log/sms/trigger.log 2>&1 # Process inbound SMS replies every minute
* 8-21 * * * python3 /opt/sms-integration/sms_inbound.py >> /var/log/sms/inbound.log 2>&1 # Daily SMS report
0 7 * * * python3 /opt/sms-integration/sms_report.py >> /var/log/sms/daily_report.log 2>&1
# Run disposition-triggered SMS every 2 minutes during operating hours
*/2 8-20 * * 1-6 python3 /opt/sms-integration/sms_trigger.py >> /var/log/sms/trigger.log 2>&1 # Process inbound SMS replies every minute
* 8-21 * * * python3 /opt/sms-integration/sms_inbound.py >> /var/log/sms/inbound.log 2>&1 # Daily SMS report
0 7 * * * python3 /opt/sms-integration/sms_report.py >> /var/log/sms/daily_report.log 2>&1
# Run disposition-triggered SMS every 2 minutes during operating hours
*/2 8-20 * * 1-6 python3 /opt/sms-integration/sms_trigger.py >> /var/log/sms/trigger.log 2>&1 # Process inbound SMS replies every minute
* 8-21 * * * python3 /opt/sms-integration/sms_inbound.py >> /var/log/sms/inbound.log 2>&1 # Daily SMS report
0 7 * * * python3 /opt/sms-integration/sms_report.py >> /var/log/sms/daily_report.log 2>&1
#!/usr/bin/env python3
"""sms_inbound.py - Process inbound SMS replies""" import os
import mysql.connector
import requests DB_CONFIG = { "host": "localhost", "user": "cron", "password": os.environ.get("VICI_DB_PASS", ""), "database": "vicidial"
} OPT_OUT_KEYWORDS = {"stop", "unsubscribe", "cancel", "end", "quit"} def process_inbound_messages(): """Fetch new inbound messages from provider and process them.""" # Pull unprocessed inbound messages from webhook table conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT id, from_number, message_text, received_at FROM sms_inbound_queue WHERE processed = 0 ORDER BY received_at ASC """) messages = cursor.fetchall() for msg in messages: phone = msg["from_number"].replace("+1", "").strip() text_lower = msg["message_text"].strip().lower() # Check for opt-out if text_lower in OPT_OUT_KEYWORDS: cursor.execute(""" INSERT IGNORE INTO sms_dnc_list (phone_number, opted_out_at, opt_out_keyword) VALUES (%s, NOW(), %s) """, (phone, text_lower)) # Send confirmation send_sms(phone, "You have been unsubscribed. No further messages will be sent.") else: # Find the original agent and create a callback cursor.execute(""" SELECT v.user, v.lead_id, v.campaign_id FROM vicidial_log v JOIN sms_log s ON v.lead_id = s.lead_id WHERE s.phone_number = %s ORDER BY v.call_date DESC LIMIT 1 """, (phone,)) original = cursor.fetchone() if original: # Insert callback for the original agent cursor.execute(""" INSERT INTO vicidial_callbacks (lead_id, list_id, campaign_id, status, user, recipient, callback_time, comments) SELECT lead_id, list_id, %s, 'LIVE', %s, 'USERONLY', NOW(), %s FROM vicidial_list WHERE lead_id = %s """, (original["campaign_id"], original["user"], f"SMS reply: {msg['message_text'][:200]}", original["lead_id"])) cursor.execute("UPDATE sms_inbound_queue SET processed = 1 WHERE id = %s", (msg["id"],)) conn.commit() cursor.close() conn.close()
#!/usr/bin/env python3
"""sms_inbound.py - Process inbound SMS replies""" import os
import mysql.connector
import requests DB_CONFIG = { "host": "localhost", "user": "cron", "password": os.environ.get("VICI_DB_PASS", ""), "database": "vicidial"
} OPT_OUT_KEYWORDS = {"stop", "unsubscribe", "cancel", "end", "quit"} def process_inbound_messages(): """Fetch new inbound messages from provider and process them.""" # Pull unprocessed inbound messages from webhook table conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT id, from_number, message_text, received_at FROM sms_inbound_queue WHERE processed = 0 ORDER BY received_at ASC """) messages = cursor.fetchall() for msg in messages: phone = msg["from_number"].replace("+1", "").strip() text_lower = msg["message_text"].strip().lower() # Check for opt-out if text_lower in OPT_OUT_KEYWORDS: cursor.execute(""" INSERT IGNORE INTO sms_dnc_list (phone_number, opted_out_at, opt_out_keyword) VALUES (%s, NOW(), %s) """, (phone, text_lower)) # Send confirmation send_sms(phone, "You have been unsubscribed. No further messages will be sent.") else: # Find the original agent and create a callback cursor.execute(""" SELECT v.user, v.lead_id, v.campaign_id FROM vicidial_log v JOIN sms_log s ON v.lead_id = s.lead_id WHERE s.phone_number = %s ORDER BY v.call_date DESC LIMIT 1 """, (phone,)) original = cursor.fetchone() if original: # Insert callback for the original agent cursor.execute(""" INSERT INTO vicidial_callbacks (lead_id, list_id, campaign_id, status, user, recipient, callback_time, comments) SELECT lead_id, list_id, %s, 'LIVE', %s, 'USERONLY', NOW(), %s FROM vicidial_list WHERE lead_id = %s """, (original["campaign_id"], original["user"], f"SMS reply: {msg['message_text'][:200]}", original["lead_id"])) cursor.execute("UPDATE sms_inbound_queue SET processed = 1 WHERE id = %s", (msg["id"],)) conn.commit() cursor.close() conn.close()
#!/usr/bin/env python3
"""sms_inbound.py - Process inbound SMS replies""" import os
import mysql.connector
import requests DB_CONFIG = { "host": "localhost", "user": "cron", "password": os.environ.get("VICI_DB_PASS", ""), "database": "vicidial"
} OPT_OUT_KEYWORDS = {"stop", "unsubscribe", "cancel", "end", "quit"} def process_inbound_messages(): """Fetch new inbound messages from provider and process them.""" # Pull unprocessed inbound messages from webhook table conn = mysql.connector.connect(**DB_CONFIG) cursor = conn.cursor(dictionary=True) cursor.execute(""" SELECT id, from_number, message_text, received_at FROM sms_inbound_queue WHERE processed = 0 ORDER BY received_at ASC """) messages = cursor.fetchall() for msg in messages: phone = msg["from_number"].replace("+1", "").strip() text_lower = msg["message_text"].strip().lower() # Check for opt-out if text_lower in OPT_OUT_KEYWORDS: cursor.execute(""" INSERT IGNORE INTO sms_dnc_list (phone_number, opted_out_at, opt_out_keyword) VALUES (%s, NOW(), %s) """, (phone, text_lower)) # Send confirmation send_sms(phone, "You have been unsubscribed. No further messages will be sent.") else: # Find the original agent and create a callback cursor.execute(""" SELECT v.user, v.lead_id, v.campaign_id FROM vicidial_log v JOIN sms_log s ON v.lead_id = s.lead_id WHERE s.phone_number = %s ORDER BY v.call_date DESC LIMIT 1 """, (phone,)) original = cursor.fetchone() if original: # Insert callback for the original agent cursor.execute(""" INSERT INTO vicidial_callbacks (lead_id, list_id, campaign_id, status, user, recipient, callback_time, comments) SELECT lead_id, list_id, %s, 'LIVE', %s, 'USERONLY', NOW(), %s FROM vicidial_list WHERE lead_id = %s """, (original["campaign_id"], original["user"], f"SMS reply: {msg['message_text'][:200]}", original["lead_id"])) cursor.execute("UPDATE sms_inbound_queue SET processed = 1 WHERE id = %s", (msg["id"],)) conn.commit() cursor.close() conn.close()
SELECT t.disposition AS trigger_dispo, COUNT(DISTINCT s.lead_id) AS leads_texted, SUM(CASE WHEN s.status = 'delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN s.response_text IS NOT NULL THEN 1 ELSE 0 END) AS responses, ROUND(SUM(CASE WHEN s.response_text IS NOT NULL THEN 1 ELSE 0 END) / GREATEST(COUNT(*), 1) * 100, 1) AS response_rate_pct, SUM(CASE WHEN v2.status IN ('SALE','XFER') THEN 1 ELSE 0 END) AS conversions_after_sms, ROUND(SUM(CASE WHEN v2.status IN ('SALE','XFER') THEN 1 ELSE 0 END) / GREATEST(COUNT(DISTINCT s.lead_id), 1) * 100, 1) AS sms_assisted_conv_pct
FROM sms_log s
LEFT JOIN vicidial_log v2 ON s.lead_id = v2.lead_id AND v2.call_date > s.sent_at AND v2.call_date < s.sent_at + INTERVAL 7 DAY AND v2.status IN ('SALE','XFER')
JOIN sms_templates t ON s.disposition = t.disposition
WHERE s.sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY t.disposition
ORDER BY sms_assisted_conv_pct DESC;
SELECT t.disposition AS trigger_dispo, COUNT(DISTINCT s.lead_id) AS leads_texted, SUM(CASE WHEN s.status = 'delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN s.response_text IS NOT NULL THEN 1 ELSE 0 END) AS responses, ROUND(SUM(CASE WHEN s.response_text IS NOT NULL THEN 1 ELSE 0 END) / GREATEST(COUNT(*), 1) * 100, 1) AS response_rate_pct, SUM(CASE WHEN v2.status IN ('SALE','XFER') THEN 1 ELSE 0 END) AS conversions_after_sms, ROUND(SUM(CASE WHEN v2.status IN ('SALE','XFER') THEN 1 ELSE 0 END) / GREATEST(COUNT(DISTINCT s.lead_id), 1) * 100, 1) AS sms_assisted_conv_pct
FROM sms_log s
LEFT JOIN vicidial_log v2 ON s.lead_id = v2.lead_id AND v2.call_date > s.sent_at AND v2.call_date < s.sent_at + INTERVAL 7 DAY AND v2.status IN ('SALE','XFER')
JOIN sms_templates t ON s.disposition = t.disposition
WHERE s.sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY t.disposition
ORDER BY sms_assisted_conv_pct DESC;
SELECT t.disposition AS trigger_dispo, COUNT(DISTINCT s.lead_id) AS leads_texted, SUM(CASE WHEN s.status = 'delivered' THEN 1 ELSE 0 END) AS delivered, SUM(CASE WHEN s.response_text IS NOT NULL THEN 1 ELSE 0 END) AS responses, ROUND(SUM(CASE WHEN s.response_text IS NOT NULL THEN 1 ELSE 0 END) / GREATEST(COUNT(*), 1) * 100, 1) AS response_rate_pct, SUM(CASE WHEN v2.status IN ('SALE','XFER') THEN 1 ELSE 0 END) AS conversions_after_sms, ROUND(SUM(CASE WHEN v2.status IN ('SALE','XFER') THEN 1 ELSE 0 END) / GREATEST(COUNT(DISTINCT s.lead_id), 1) * 100, 1) AS sms_assisted_conv_pct
FROM sms_log s
LEFT JOIN vicidial_log v2 ON s.lead_id = v2.lead_id AND v2.call_date > s.sent_at AND v2.call_date < s.sent_at + INTERVAL 7 DAY AND v2.status IN ('SALE','XFER')
JOIN sms_templates t ON s.disposition = t.disposition
WHERE s.sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY t.disposition
ORDER BY sms_assisted_conv_pct DESC; - Respond to STOP, UNSUBSCRIBE, CANCEL, END, and QUIT keywords automatically
- Process opt-outs from email requests, phone calls, and web forms
- Remove the number from all SMS campaigns (not just the one they replied to)
- Send a confirmation message after processing the opt-out - 10DLC registration -- start today, it takes 1-3 weeks. Do not wait.
- Consent audit -- verify you have documented one-to-one consent for every contact you plan to text. If you don't, you can not text them.
- Single disposition trigger -- start with "NA" (no answer) only. Send one text within 60 seconds of a missed call. This is the highest-ROI single addition.
- Opt-out handling -- make sure STOP works before you send a single message.
- Multi-touch cadence -- once the basic trigger works, expand to the 7-day cadence.
- Reporting and optimization -- measure delivery, response, and conversion rates. A/B test templates.
