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
- the access number the caller called (or was forwarded to).
- for chat, it’s the Puzzel id of the chat queue.
- for email, it’s usually an email address or a generic access point like "STask_<customer_key>" or "ETask_<customer_key>".
- for social, it usually contains SoMe, some digits and e.g. ‘Facebook’ or ‘Twitter’

For conversation events for media type phone, the destination will be the agent’s phone number or the called destination’s number.
When agent uses Puzzel Softphone:
- agent.puzzel.com’s Softphone: 19510yyyyyy (where yyyyyy is the agent id)
- client.puzzel.com’s Softphone: 19500yyyyyy (no longer in use)
Other 195xx-prefixes might be used, and we might start adding +47 before 195xx.

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! 
More details here.  

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) For records with event_type=’c’ and media_type_id=’1’ (calls):
Type of call: Agent, TransferAgent, ConsultAgent, Transfer, Caller, DirectCall*
originating varchar(50)

For records with event_type=’c’ and media_type_id=’1’ (calls):
CLI/display number used for the call*

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):
Number of seconds the caller was on hold due to this agent’s Put on hold and Consult. In special cases there might be a value here even if the agent did not click Put on hold or Consult*

 

* From v1.9, April 26th 2023 

Published

17/08/2020 - 09:40

Last updated

13/02/2024 - 10:40
4
1