Using SQL to Build a Voice Broadcast Application with Twilio

Using SQL to Build a Voice Broadcast Application with Twilio

Austin is the developer behind the projects twiSQL and twiCLI, the post below was originally posted on his blog here.

Using SQL Server to Conduct Telephone Campaigns

To make the use of the services that Twilio offers easier I have built two special use projects that are free for download and use. twiCLI is a command line tool for the Windows environments designed to place phone calls and send text messages, and twiSQL is a set of SQL user defined functions to enable telephone calls and text messaging from TSQL code.

In this entry I am speaking to the project twiSQL – and the goal is to communicate how to build a complete call campaign system using nothing by SQL, Twilio and the library functions of twiSQL. For portability the documentation file has been updated with this sample project added to the end.

Sometimes the best way to understand an idea is to see it in action in the form of a working project. In this sample project we will illustrate the idea of a list of prospects that need to be contacted with a telephone campaign.

First we build some tables to hold the details of these transactions.

[sourcecode language=”sql”]
CREATE TABLE [dbo].[myProspects](

[ProspectID] [int] IDENTITY(1,1) NOT NULL,

[ProspectName] [varchar](max) NULL,

[ProspectPhone] [varchar](25) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[myProspectCalls](

[CallID] [int] IDENTITY(1,1) NOT NULL,

[ProspectID] [int] NULL,

[CallDate] [datetime] NULL,

[CallSID] [varchar](255) NULL,

[CallStatus] [int] NULL,

[CallDetails] [varchar](max) NULL

) ON [PRIMARY]
[/sourcecode]

In the example code below we will insert a few records into our myProspects table and then loop through those records placing telephone calls and playing the predefined greeting.

[sourcecode language=”sql”]
— CLEANING OUT THE TABLE FOR PROSPECTS – THIS IS JUST FOR TEST RUN

truncate table myProspects

— BUILDING THE PROSPECT RECORDS – NORMALLY THIS WOULD BE LOADED FROM A FILE

insert into myProspects (ProspectName, ProspectPhone)

select ‘John Doe’,’18005551212′

insert into myProspects (ProspectName, ProspectPhone)

select ‘Bill Smith’, ‘18005551212’

— THIS SECTION HANDLES PLACING THE CALLS

declare @prospectID int

declare @callingPhoneNumber varchar(11)

declare @prospectPhone varchar(25)

declare @prospectName varchar(255)

declare @CallSID varchar(255)

declare @authID varchar(255)

declare @token varchar(255)

declare @whatToSay varchar(max)

declare @whatToSayTemplate varchar(max)

— HERE YOU WOULD USE YOUR CUSTOM MESSAGE TO SPEAK

select @whatToSayTemplate = ‘Hello %NAME%. As a representative of We Make Stuff company I would like to thank you for reaching out to our agent for your needs. If you should need to reach us at any time please feel free to call 888-555-1212 for prompt assistance. We sincerly appreciate your time, and please remember to look us up on Facebook. Have a fantastic day!’

— YOU WILL NEED TO FILL IN YOUR AUTHID AND TOKEN VALUES FROM TWILIO

select @authID = ‘XXXXXXXXXXXXXXXXXXXXXXXX’

select @token = ‘XXXXXXXXXXXXXXXXXXXXXXXXX’

— YOU HAVE TO HAVE A PHONE NUMBER WITH TWILIO – REPLACE THE NUMBER BELOW

select @callingPhoneNumber = ‘16158008999’

— CHECKING TO GET THE FIRST PROSPECT

select @prospectID =

(select top 1 ProspectID from myProspects order by ProspectID asc)

— WE HAVE CALLS TO MAKE HERE PEOPLE – LETS GET TO WORK

while isnull(@prospectID,0) > 0

begin

— GETTING THE VARIABLES TO USE FOR OUR CALL

select @prospectPhone = ProspectPhone, @prospectName = prospectName

from myProspects where ProspectID = @prospectID

— NUMBER MUST BE 11 DIGITS

if isnumeric(@prospectPhone) = 1 and len(@prospectPhone) = 11

begin

select @prospectPhone

— REPLACING THE NAME IN OUR STRING TO SAY – NICE TOUCH

select @whatToSay = replace(@whatToSayTemplate,’%NAME%’,@prospectName)

— HERE WE MAKE THE CALL THROUGH OUR UDF

SELECT @CallSID = dbo.msgTwilioMakeCall(@prospectPhone,@callingPhoneNumber,@whatToSay,’m’,@authID,@token)

if charindex(‘|’,@CallSID) > 0

begin

SELECT @CallSID =

right(@CallSID,len(@CallSID) – charindex(‘|’,@CallSID))

insert myProspectCalls (ProspectID,CallDate,CallSID,CallStatus)

values (@prospectID,getdate(),@CallSID,0)

end

else

begin

insert myProspectCalls (ProspectID,CallDate,CallSID,CallStatus)

values (@prospectID,getdate(),@CallSID,-1)

end

— GETTING THE NEXT PROSPECT

select @prospectID = (select top 1 prospectID from myProspects

where prospectID > @prospectID order by prospectID asc)

end

end
[/sourcecode]

Now that we have placed the telephone calls at some point in the future you may want to know that the call was actually completed. The code below is used to illustrate how you can use the provided functions to check the details of a specific call.

[sourcecode language=”sql”]
— THIS LOGIC WILL CHECK THE STATUS OF THE CALLS

declare @authID varchar(255)

declare @token varchar(255)

— YOU NEED TO REPLACE YOUR AUTHID AND TOKEN

select @authID = ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX’

select @token = ‘XXXXXXXXXXXXXXXXXXXXXXXXXXX’

declare @callSID varchar(255)

declare @callID int

declare @callDetails varchar(max)

declare @callStatus int

— WE WILL BE SETTING UP A LOOP HERE TO GO THROUGH THE RECORDS

select @callID =

(select top 1 CallID from myProspectCalls where CallStatus = 0 order by callID asc)

while isnull(@callID,0) > 0

begin

select @callSID = callSID from myProspectCalls where callID = @callID

select @callDetails = dbo.msgTwilioViewCall(@callSID,@authID,@token)

— THE FULL XML IS RETURNED IN THE @callDetails VARIABLE

— YOU CAN PROCESS THAT XML HOWEVER YOU NEED AS THERE IS GOOD

— INFO CONTAINED IN IT – FOR THIS CASE WE JUST CHECK TO SEE THE STATUS

if charindex(‘|completed|’,@callDetails) > 0

begin

select @callStatus = 1

end

else

begin

select @callStatus = 0

end

— UPDATING THE STATUS OF THE CALL RECORD

update myProspectCalls

set callDetails = @callDetails, callStatus = @callStatus

where callID = @callID

— GETTING THE NEXT CALL ID RECORD

select @callID =

(select top 1 CallID from myProspectCalls

where CallStatus = 0 and callID > @callID

order by callID asc)

end
[/sourcecode]

One other option to consider is that as opposed to using the text to speech engine you can alternatively record an mp3 file, host it on a public URL and pass the URL to the file as the text of the call. This option provides for a very personalized campaign with a small modification to the process.

When we make the call to msgTwilioMakeCall() we just replace the variable @whatToSay with the URL to the file. A simple way to pull this off will be to host a file using the shared link functionality of Dropbox – or any other public URL. In the example below we are using Dropbox.

[sourcecode language=”sql”]
SELECT @CallSID = dbo.msgTwilioMakeCall(@prospectPhone,@callingPhoneNumber, ‘http://dl.dropbox.com/u/1541272/recorded.mp3′,’m’,@authID,@token)
[/sourcecode]

Using the above method you could easily pull of the familiar telephone call campaign component of running for public office or perhaps the reminder of your dentist appointment, and all for almost nothing in expense.

Hopefully this sample project helps give you a vision for what is possible in a very few simple steps with very limited impact on the wallet.