Blog

Load leads directly into the Vicidial Database

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

Comments ( 3 )

  • Shital
    Jan 15, 2016 at 12:43 AM / Reply

    Hi, I am not aware about coldfusion. So not able to unnderstand code. Can you help me in php and how to use it?

    I want to implement auto dialing from vtiger. So please help me about this

  • Brian
    Jan 19, 2016 at 11:26 AM / Reply

    @Shital,

    I have not done any integration with Vtiger, but there is a lot of information on how to do it in the Vicidal forums.

    http://www.eflo.net/VICIDIALforum/viewtopic.php?t=8779

    The method outlined in this post is geared toward loading leads into Vicidial without using the Vicidial interface. This can be useful if your data is stored in a CRM and needs to be pushed to Vicidial on a regular basis.

    It should also be noted that Vicidal has a "Non-Agent API" that can be used to load leads into a list as well.

    See the documentation here:
    http://www.vicidial.org/docs/NON-AGENT_API.txt

    I suggest using the non-agent API if you are in need of loading leads one at a time on demand, or have a small number that need to be loaded. Otherwise for large lists, inserting them directly into that database is the way to go as it is much faster.

  • Oliver Jake
    Mar 27, 2018 at 02:13 AM / Reply

    VICIdial is an open-source software, but setting it up, sometimes become very hectic. On the other hand, VoIP Terminator is a very good VICIdial Hosting provider and can set it up all for you instantly. They've been very helpful to me as this article was but if you don't understand much of the technicalities, Vicidial hosting provider is your best option.

    Check them out here: http://www.voipterminator.com/vicidial/


Add Comment