Vicidial Database Structure
From ViciWiki
Revision as of 19:11, 16 March 2013 by Bill (talk | contribs) (Created page with " =vicidial_agent_log= {| class="wikitable" |- ! Field Name !! Description !! Location |- | agent_log_id || Primary Key for this table|| |- | user || User Name ID field || vici...")
vicidial_agent_log
Field Name | Description | Location |
---|---|---|
agent_log_id | Primary Key for this table | |
user | User Name ID field | vicidial/AST_agent_time_detail.php |
server_ip | IP address of server user is connected to | vicidial/AST_usergroup_login_report.php |
event_time | Time logged for start of event or call | |
lead_id | link to vicidial_list.lead_id | |
campaign_id | Campaign user is logged into | /vicidial/AST_agent_time_detail.php |
pause_epoch | start of agent pause time | |
pause_sec | length in second of pause time | vicidial/AST_agent_performance_detail.php |
wait_epoch | start of agent wait time | |
wait_sec | total length in seconds of wait time | vicidial/AST_agent_performance_detail.php |
talk_epoch | start of agent talk time | |
talk_sec | total agent talk time per call | vicidial/AST_agent_performance_detail.php |
dispo_epoch | start of disposition time | |
dispo_sec | total amount of time to disposition a call | vicidial/AST_agent_performance_detail.php |
status | Call dispositioned at the end of the call | this can be changed by /vicidial/admin_modify_lead.php?lead_id= |
user_group | call group user is assigned to | can be located at vicidial/admin.php?ADD=100000 |
comments | Call comments inputted | /vicidial/admin_search_lead.php |
sub_status | ||
dead_epoch | start of dead time | dead time is after lead has hung up but agent has not pressed "hangup call" on screen |
dead_sec | length of dead time in seconds | vicidial/AST_agent_performance_detail.php |
processed | ||
uniqueid | Identifier for phone call, generated by Asterisk at the onset of call |
vicidial_log
Field Name | Description | Location |
---|---|---|
uniqueid | unique id for record | |
lead_id | Lead Identifier | /vicidial/admin_search_lead.php |
list_id | List Name | vicidial/admin.php?ADD=100 |
campaign_id | Campaign Name | vicidial/admin.php?ADD=10 |
call_date | Call Date and Time | /vicidial/user_stats.php?user= |
start_epoch | Start of Call | |
end_epoch | End of Call | |
length_in_sec | Length of Call in seconds | /vicidial/user_stats.php?user= |
status | Dispositioned Status given at end of call | Note this may no longer be the status for the lead, it may change. |
phone_code | Phone Dial Code (IE "1") | /vicidial/admin_modify_lead.php?lead_id= |
phone_number | Phone number | /vicidial/admin_modify_lead.php?lead_id= |
user | User Name or ID | vicidial/AST_agent_performance_detail.php |
comments | MANUAL or AUTO (or NULL) | |
processed | unused? | |
user_group | User Group of agent on call | vicidial/admin.php?ADD=100000 |
term_reason | Who terminated the call, Caller or Agent | 'CALLER','AGENT','QUEUETIMEOUT','ABANDON','AFTERHOURS','NONE' |
alt_dial | field name if called using alternate phone number field | ie: alt_phone or address3 |
vicidial_list
Most of these fields are visible in /vicidial/admin_modify_lead.php?lead_id={LEAD_ID} and/or on the agent screen. If so, they are marked "admin_modify_lead" or "agent screen" (or "both").
Field Name | Description | Location | |
---|---|---|---|
lead_id | Unique ID for leads, incrementing | admin_modify_lead | |
entry_date | Date/time lead imported | ||
modify_date | Date/time of last modification | Enforced by mysql code | |
status | Disposition/Status (NEW, DROP) | admin_modify_lead | |
user | vicidial user | admin_modify_lead | |
vendor_lead_code | vendor id | both | |
this field is used to link "multi-lead" alt phones together and is also often used to link to external databases such as CRMs | |||
source_id | used to link to external databases as either a unique record or a category | ||
list_id | List containing the lead_id | Used to link the lead to a campaign. | |
gmt_offset_now | Time Zone | Calculated during import and again nightly with perl script | |
called_since_last_reset | Has the lead been called | Resetting this value to Zero allows the lead to be called again. Cannot be called if set to 1. | |
phone_code | Dial Code (1 in US, or could be 44 or 01144 for UK) | both | |
phone_number | Phone Number | both | |
title | Title | both | |
first_name | First name | both | |
middle_initial | Middle Initial | both | |
last_name | Last name | both | |
address1 | Primary Street Address | both | |
address2 | Address 2 | both | |
address3 | Address 3 | both | |
city | City | both | |
state | State | both | |
province | Province if outside CUSA | both | |
postal_code | Postal(ZIP) Code | both | |
country_code | Country | admin_modify_lead | |
used to assist in GMT calculation. Two character code. ie: US or CA | |||
gender | Gender | both | |
date_of_birth | Birthdate | admin_modify_lead.php | |
alt_phone | Second Phone Number | both | |
both | |||
security_phrase | Labeled as "Show" in agent screen | both | |
comments | Comments | both | |
called_count | Number of times this lead has been called | ||
last_local_call_time | date and time of the last call | ||
rank | admin_modify_lead | ||
owner | Name of user that can dial lead if configured for owner dialing | admin_modify_lead | |
entry_list_id | original list id during import | if using custom fields, this is the "home" list id where those fields are kept. | |
If the lead is moved between lists, this field must be populated to link to the field structure (and custom field record). |
vicidial/admin_modify_lead.php
vicidial_closer_log
Field Name | Description | Location |
---|---|---|
closecallid | closed call table Primary key | |
lead_id | ID of Lead | vicidial/admin_modify_lead.php?lead_id= |
list_id | ID of List | vicidial/admin_modify_lead.php?lead_id= |
campaign_id | Alpha Numeric Campaign ID | from Vicidial Campaigns |
call_date | Date and Time call was made | |
start_epoch | Start of Call Time | |
end_epoch | End of Call Time | |
length_in_sec | Total Length of call in seconds | vicidial/AST_agent_performance_detail.php |
status | what happpened to the call | Samples: AFTHRS = did not match call times for an Ingroup or Call Menu; NANQUE = no agent was available |
phone_code | from lead record | vicidial/admin_modify_lead.php?lead_id= |
phone_number | from lead record | vicidial/admin_modify_lead.php?lead_id= |
user | User ID | vicidial/AST_agent_time_detail.php |
comments | Explanation related to Status in this table | More information related to status: for instance: "After Hours Drop" when status is "AFTHRS". |
processed | ||
queue_seconds | Wait time before arriving at an agent | |
user_group | user group of agent who handled this call record | |
xfercallid | ||
term_reason | Call Termination Reason | Samples: AGENT; ABANDON; CALLER |
uniqueid | Unique ID assigned by asterisk | |
agent_only | ||
queue_position |