Difference between revisions of "Vicidial Database Structure"
From ViciWiki
(→vicidial_agent_log) |
|||
Line 1: | Line 1: | ||
+ | =recording_log= |
||
+ | |||
+ | {| class="wikitable" |
||
+ | |- |
||
+ | ! Field Name !! Description !! Location |
||
+ | |- |
||
+ | | recording_id || Primary Key for this table|| |
||
+ | |- |
||
+ | | channel || asterisk channel when call was live || vicidial/admin_modify_lead.php?lead_id=#### |
||
+ | |- |
||
+ | | server_ip || IP address of server user is connected to || |
||
+ | |- |
||
+ | | extension || could be callerid of the client or extension call was ultimately sent to || |
||
+ | |- |
||
+ | | start_time || start time of call || |
||
+ | |- |
||
+ | | start_epoch || start time of call in unix epoch format || |
||
+ | |- |
||
+ | | end_time || start time of call || |
||
+ | |- |
||
+ | | end_epoch || end time of call in unix epoch format || |
||
+ | |- |
||
+ | | length_in_sec || length of call in seconds || |
||
+ | |- |
||
+ | | length_in_min || length converted to minutes decimal format || |
||
+ | |- |
||
+ | | filename || without extension to allow use as audio playback in asterisk || |
||
+ | |- |
||
+ | | location || url to listen to recording || note that this is modified via regex in some interfaces IF still located on the original server, it is modified to the URL provided during ftp transfer if already moved to the archive server |
||
+ | |- |
||
+ | | lead_id || if linked to a lead || |
||
+ | |- |
||
+ | | user || agent's user id || |
||
+ | |- |
||
+ | | vicidial_id || could be vicidial_closer_log_id or uniqueid from asterisk || |
||
+ | |} |
||
+ | |||
+ | |||
+ | |||
+ | |||
=vicidial_agent_log= |
=vicidial_agent_log= |
||
Latest revision as of 03:44, 17 August 2013
recording_log
Field Name | Description | Location |
---|---|---|
recording_id | Primary Key for this table | |
channel | asterisk channel when call was live | vicidial/admin_modify_lead.php?lead_id=#### |
server_ip | IP address of server user is connected to | |
extension | could be callerid of the client or extension call was ultimately sent to | |
start_time | start time of call | |
start_epoch | start time of call in unix epoch format | |
end_time | start time of call | |
end_epoch | end time of call in unix epoch format | |
length_in_sec | length of call in seconds | |
length_in_min | length converted to minutes decimal format | |
filename | without extension to allow use as audio playback in asterisk | |
location | url to listen to recording | note that this is modified via regex in some interfaces IF still located on the original server, it is modified to the URL provided during ftp transfer if already moved to the archive server |
lead_id | if linked to a lead | |
user | agent's user id | |
vicidial_id | could be vicidial_closer_log_id or uniqueid from asterisk |
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_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 |
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_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 |