Blog

From time to time we get a client that needs to load lists into Vicidial on a daily or even hourly basis. Having the client email the list and then doing a manual upload can be quite cumbersome. To make the process more efficient, I developed an application that will allow the client to use a web interface to upload their list, and have it load to the dialer in real time.

There are 2 tables that need to be accessed in the Vicidal asterisk database in order to load leads;

- vicidial_phone_codes

- vicidial_list

Assuming the list ID has already been created in Vicidial, loading leads directly into the asterisk database is pretty straight forward as there are no complicated dependancies that need to be created. It is pretty much a matter of inserting leads directly into the vicidial_list table. That said there is one column in the vicidial_list table that is a bit tricky, gmt_offset_now.

Gmt_offset_now is the 'Greenwich Mean Time' offset for the area code of the phone number that is being inserted. The gmt_offset_now value lets Vicidial know what the time zone and local time is for the lead. Without this information Vicidial may dial the lead at an inappropriate time. 

The vicidial_phone_codes table contains the GMT Offset data for all of the area codes for the US and other countries. From this table you will need to get the area code, GMT_offset and DST columns for the US. Here is the SQL statement:

                Select areacode, GMT_offset, DST
                FROM vicidial_phone_codes
                WHERE country = 'USA'

The result of this query is a list of US area codes along with the GMT offset (time zone) and whether or not the area code uses Daylight Savings Time.

Daylight Savings Time in the US is active between the second Sunday in March (SSM) and the first Sunday in November (FSN). So if the DST value for the area code is 'Y' and the list is loaded during Daylight SavingsTime then the GMT_offset value needs to be adjusted before the lead is loaded into the vicidial_list table.

For Example:

area code 402 has a GMT_offset value of  '-6' and a DST value of 'Y'

So if this list is loaded between 3/16/2014 and 11/2/2014 then the GMT_offset value would be adjusted to '-5' to account for Daylight Savings Time.

Thankfully, loading leads into the vicidial_list_table is simple once you have the GMT_offset time calculated for the area code of the lead you wish to insert. Mapping your list data to the vicidial_list_table columns is exactly the same as when you use the list loader in Vicidial.  Here is an example SQL statement for loading a lead with just the phone_number, first_name and last_name fields:

                 INSERT INTO vicidial_list
                 Set
                 entry_date = '#entry_date#',
                 modify_date =  '#entry_date#',
                 status = 'NEW',
                 list_id = '#list_id#',
                 gmt_offset_now = '#GMT_offset#',
                 called_since_last_reset = 'N',
                 phone_code = '1',
                 phone_number = '#phone#',
                 first_name = '#first_name#',
                 last_name = '#last_name#',
                 called_count = '0',                            
                 rank = '0'

There are many other columns you can map data to, but these are the required columns that need to be loaded for each lead. (first_name, and last_name are not required, but just shown as an example.) All other columns can be left null or have default values automatically loaded into them.

*Note:
If you want to skip the step of calculating the gmt_offset_now value when loading the leads you can and a default value of '0' will be automatically assigned. The ADMIN_adjust_GMTnow_on_leads.pl script can be run to set the gmt_offset_now value for you. I find it eaiser, and safer to just do the calculation as I enter the leads. 

Example Application:

This example was written using ColdFusion, but it can be implemented in any language.

Those familiar with loading lists in Vicidial know that when a .csv file is uploaded it allows you to map the Vicidial fields to the fields in the list. For our application we decided to eliminate this step from our automated process because it would require that the client provide the correct field mapping. Experience has taught me that it is always best to try build processes that minimize the mistakes a client can make. Additionally, a client's file structure rarely if ever changes. So for the purposes of the application we require an agreed upon file structure.

Depending on the campaign the application can be adjusted to manage the list as needed.  Most of the time we designate a single list ID to be the dynamic list. In this case the list is emptied and reloaded when the client uploads a new file. The application can also be set to append a single dynamic list or create a new list every time a new file is uploaded. Again, this really depends on the needs of the campaign.

For the demonstration in the video the vicidial_hopper table is queried to delete leads from the list that is being cleared and reloaded. Depending on your needs you may or may not have to do anything with this table.

Example Files: vicidial_list_loader.zip