call_events
Table name: call_events
Contains/role: All the events happening within all calls/written requests.
| Column Name | Datatype | Description |
|---|---|---|
| rec_id | Int Primary key. | Internal, auto-numbered. A unique id for each record. |
| customer_key | Varchar (100) | The customer key (number) for the “owner” of the call. |
| call_id | Numeric (19,0) | A number which is unique for each call (request). This is a calculated value (max 19 digits) based on a set of internal values/variables, and the value will go up and down for new calls. |
| call_sequence | int | A sequence number (starting with 1) for each events within the same call. The order is determined by the time for start of the event (dte_start). |
| media_type_id | int | Identifying which type of media, refers (foreign key) to table media_types. |
| dte_start | datetime | The date/time for the start of the event. |
| duration_tot_sec | int | The total duration of the event, in seconds. |
| duration_speak_sec | int | The total speak time (if a Conversation event), in seconds. |
| dte_speak_start | datetime | The date/time of when the speaktime started within the event (if a Conversation event). Expressed in seconds. The time gap between duration_speak_sec and dte_speak_start will be the time spent in allocate/setup/alerting. |
| source | Varchar(500) | Identifying the caller. For an event of media type phone, this will be the caller’s phone number (the number the Puzzel platform received as Calling Party Number) For email, it’s the received ‘From’ (email) address For chat, it’s the received Chat_Id which usually is the email address the chatter has entered in the chat form. For social, it usually contains the conversation id. This might be changed to be the ‘author’ in a future release. |
| destination | Varchar(500) | For initiation events, the destination will be For conversation events for media type phone, the destination will be the agent’s phone number (for calls to agents) or the called destination’s number. |
| additional_source | Varchar(500) | Contains the so called “additional calling party number” for phone calls. For other media types we might add information here in a future release. |
| redirect_source | Varchar(500) | Contains the so called “redirecting number” for phone calls. For other media types we might add information here in a future release. |
| service_num | varchar(100) | An access number “belongs” to a service number (which again belongs to a customer). This is the service number. A service number could have more than one access number. |
| queue_key | nvarchar(100) | For events of type queue (‘q’) and conversation (‘c’). The key (short code) for the queue involved in the event. See table queues for a more descriptive name (NOTE! Use both the queue_key and the service_num as join criteria columns, the key itself is not necessarily unique if the database holds data for more than one customer!) Outer join is recommended, since there might exist keys without extended descriptions. |
| menue_key | nvarchar(100) | For events of type menu (‘m’). The key (short code) for the menu involved in the event. See table queues for a more descriptive name (NOTE! Use both the queue_key and the service_num as join criteria columns, the key itself is not necessarily unique if the database holds data for more than one customer!). Outer join is recommended, since there might exist keys without extended descriptions. |
| menue_choice | varchar(255) | For events of type menue (‘m’). The DTMF codes (optionally) keyed in by the caller. Might be screened (set to empty) for “secret” information, like for example credit card numbers. |
| agent_id | int | For events of type conversation (‘c’). The unique id for the agent involved. If events of type ‘c’ does not have an agent_id (i.e. NULL), it indicates an outgoing call to “just” a number, or call to a caller in a call back in queue sequence. Events of type 'r' (recording) will contain a value for agent_id from v1.9 |
| event_type | varchar | Refers (foreign key) to table call_event_types. |
| result_code | varchar | Refers (foreign key) to table call_result_codes. |
| answered | tinyint | Only set for the event of type ‘i’. This is a kind of “summary” for the whole call/email/chat, telling if the request was connected to an agent. The main purpose is to make it easier to search for incoming calls/chats/emails that where answered or not. The value is 0 or 1, where 1 is set if at least one conversation event (with ciq=NULL) for the call_id is answered, or if one conversation event with ciq=a and one with ciq=c is answered. For all other events than ‘i’ the value is NULL. |
| ciq | char | Only set when “call back in queue” happens. On the Conversation event, it might me either ‘a’ (agent) or ‘c’ (caller) to reflect to whom the call (leg) was made. On a ‘q’ (queue) event, it is set to ‘q’. For all others, the value NULL is set. |
| call_transfer | Bit | Set to 0 or 1 for events of type ‘c’ (conversation), for all other events the value is NULL. If 1, the call or email has been transferred (to another agent or to just another number) by the agent just before he/she terminated his/her conversation. |
| wrap_up_sec | int | Only set for events of type ‘c’, and only if a wrap up time is defined/used for the agent. Wrap up time is the time after a terminated call until the agent will be available for new incoming calls. Value expressed in seconds. |
| alert_ms | int | Only set for events of type ‘c’, and (so far) only for calls of type ‘phone’. Express the alert time, also called ringing time. Value expressed in milliseconds. |
| setup_ms | int | Only set for events of type ‘c’, and (so far) only for calls of type ‘phone’. Express the so called setup time in the Public Switched Telephone Network (PSTN), i.e. the time from the number is dialed and up to the time when the alert (ringing) starts. Value expressed in milliseconds. |
| block_duration_sec | int | Will only be used for Conversation events, and only if Busy (b) or No-answer (t) is the result of the call to the agent. After such a result, the agent is set in status Busy/No answer for a short predefined time, and this predefined time is stored here. Value in seconds. Please note that an agent in status Busy/No answer can click to change status to Ready or Pause or Logged off, and if this is done, the value in 'block_duration_sec' is NOT updated! |
| internal_iq_session_ id | Unique-identifier | The session id for the call/request. One call_id might consist of 1 or more sessions. (Primarily for internal use, for “connection” back to Puzzel’s internal system.) |
| internal_odr_id | bigint | Only set for Conversation events. For internal use. Also used for linking the event to it’s “twin” in the agent_events table. |
| dte_updated | datetime | A timestamp indicating when this call_event record is stored/last updated in this database. |
| sla | int | The queue’s predefined SLA, if any |
| alt_sla | int | The queue’s predefined Alternative SLA, if any |
| dte_scheduled_callback | datetimeoffset | The queue event’s scheduled time, if any |
| result_response | int | Shows the signalling response code Puzzel received from the network for a phone call (conversation event with media_type id=1). Please note that the value might be an ISUP cause code (usually 1 or 2 digits) or a SIP response code (3 digits). Please see Extra information for calls (resulting in error) (result_response) |
| request_id | bigint | From db v 1.7. Only records with event_type = ‘i’ and 'p'will have a value for request_id |
| hold | int | For records with event_type=’c’ and media_type_id=’1’ (calls): Number of times the agent clicked ‘Put on hold’ in this conversation* |
| consult | int | For records with event_type=’c’ and media_type_id=’1’ (calls): Number of times the agent clicked ‘Consult’ in this conversation* |
| leg_type | varchar(50) | * Used for records with event_type=’c’ and media_type_id=’1’ (calls): Leg_type values might be: Agent, TransferAgent, ConsultAgent, Transfer, Consult, Caller, DirectCall, UnblockableAgent, ConsultUnblockableAgent, TransferUnblockableAgent, Other |
| originating | varchar(50) | For records with event_type=’c’ and media_type_id=’1’ (calls): |
| add_originating | varchar(50) | For records with event_type=’c’ and media_type_id=’1’ (calls): Additional calling party number if sent (usually empty)* |
| caller_on_hold_sec | int | For records with event_type=’c’ and media_type_id=’1’ (calls): |
* From v1.9, April 26th 2023