From Bright Pattern Documentation
Jump to: navigation, search
 
 
(One intermediate revision by the same user not shown)
Line 17: Line 17:
 
|'''Description'''
 
|'''Description'''
 
|-
 
|-
−
|'''id'''
+
|'''account_number'''
−
|BINARY (16)
+
|VARCHAR
 
|None
 
|None
−
|Reserved
+
|''account_number'' gets the customer’s account number. If the calling list record that initiated this call has a [[contact-center-administrator-guide/Lists|list field]] of ''Account'' type, the value of that field will be stored here. Account numbers can be used as selection criteria in [[reporting-reference-guide/InteractionRecordsSearch|interaction records search]].
 +
|-
 +
|'''acw_time'''
 +
|BIGINT
 +
|Seconds
 +
|The amount of time the agents spent doing after-call work related to this interaction
 
|-
 
|-
−
|'''pkid'''
+
|'''agent_disposition_code'''
 
|INT
 
|INT
 
|None
 
|None
−
|Primary key
+
|Numerical code (if defined) of the disposition that was assigned to this interaction
 +
|-
 +
|'''agent_disposition_name'''
 +
|VARCHAR
 +
|None
 +
|Name of the disposition that was assigned to this interaction
 +
|-
 +
|'''agent_disposition_notes'''
 +
|LONGTEXT
 +
|None
 +
|The text note that the agent wrote regarding the interaction
 
|-
 
|-
 
|'''agg_run_id'''
 
|'''agg_run_id'''
Line 32: Line 47:
 
|Aggregator run that produced this record
 
|Aggregator run that produced this record
 
|-
 
|-
−
|'''media_type'''
+
|'''callee_cpa_recording_url'''
−
|ENUM
+
|VARCHAR
 +
|None
 +
|URL of the recording for the CPA portion of the interaction segment of the party that accepted this interaction
 +
|-
 +
|'''callee_cpa_rtp_server_id'''
 +
|BINARY
 +
|None
 +
|Identifier of the RTP server that made the recording for the CPA portion of interaction segment of the party that accepted this interaction
 +
|-
 +
|'''callee_encryption_key_id'''
 +
|BINARY
 +
|None
 +
|For internal use only.
 +
|-
 +
|'''callee_first_name'''
 +
|VARCHAR
 +
|None
 +
|First name of the party that accepted the interaction
 +
|-
 +
|'''callee_has_screen_recording'''
 +
|BIT
 +
|None
 +
|Indication that screen of the party that accepted this interaction was recorded during the interaction
 +
|-
 +
|'''callee_interaction_step_id'''
 +
|BINARY
 
|None
 
|None
−
|''media_type'' specifies the interaction media type with possible values of VOICE, CHAT, or EMAIL.
+
|Identifier for the interaction segment of the party that accepted this interaction
−
 
 
−
If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context.
 
 
|-
 
|-
−
|'''start_time'''
+
|'''callee_last_name'''
−
|DATETIME
+
|VARCHAR
−
|Seconds
+
|None
−
|For inbound interactions, ''start_time'' specifies the date and time when the interaction entered the system.
+
|Last name of the party that accepted the interaction
−
 
 
−
For outbound and internal interactions, ''start_time'' specifies the date and time when the interaction was initiated.
 
−
 
 
−
The time is given in the Universal Coordinated Time (UTC) time zone.
 
 
|-
 
|-
−
|'''ivr_time'''
+
|'''callee_login_id'''
−
|BIGINT
+
|VARCHAR
−
|Seconds
+
|None
−
|Total time the call spent in IVR
+
|For inbound and internal interactions, ''callee_login_id'' specifies the login ID of the user who received this interaction. If the interaction was transferred, it specifies the login ID of the user for which the interaction was last transferred.
 
|-
 
|-
−
|'''queue_time'''
+
|'''callee_monitored'''
−
|BIGINT
+
|BIT
−
|Seconds
+
|None
−
|Total time the interaction spent in the service queue
+
|TRUE if the party that accepted this interaction was monitored at any time during the interaction handling
 
|-
 
|-
−
|'''pending_time'''
+
|'''callee_phone_type'''
−
|BIGINT
+
|ENUM
−
|Seconds
+
|None
−
|For inbound calls, ''pending_time'' specifies the duration of call ringing phase from the moment the call was distributed to an extension and until it was either answered or abandoned. For internal and outbound calls, ''pending_time'' specifies the duration of call dialing phase from the moment the dialed number was received by the system and until the call was either answered or abandoned. It does not apply to email.
+
|''callee_phone_type'' specifies the location of the party that received the interaction. Possible values are INTERNAL or EXTERNAL.
 
|-
 
|-
−
|'''talk_time'''
+
|'''callee_rank'''
−
|BIGINT
+
|VARCHAR
−
|Seconds
+
|None
−
|''talk_time'' specifies the total call talk time. It excludes hold time. For chats and emails, ''talk_time'' is the total in-focus time (the time the interaction was selected in the active communications lists of the agents who processed it).
+
|For inbound and internal calls, the rank of the user who received this interaction
 
|-
 
|-
−
|'''hold_time'''
+
|'''callee_recording_url'''
−
|BIGINT
+
|VARCHAR
−
|Seconds
+
|None
−
|''hold_time'' specifies the total time that the call spent on hold. For chats and emails, it is the total out-of-focus time (the time the interaction spent at the agents’ desktops excluding the ''Talk'' time).
+
|URL of the recording for the interaction segment of the party that accepted this interaction
 
|-
 
|-
−
|'''acw_time'''
+
|'''callee_rtp_server_id'''
−
|BIGINT
+
|BINARY
−
|Seconds
+
|None
−
|The amount of time the agents spent doing after-call work related to this interaction
+
|Identifier of the RTP server that made the recording for the interaction segment of the party that accepted this interaction
 
|-
 
|-
−
|'''duration'''
+
|'''callee_team_name'''
−
|BIGINT
+
|VARCHAR
−
|Seconds
+
|None
−
|For calls and chats, ''duration'' is the total duration of the interaction from the moment it entered the system or was initiated and until it was released.
+
|Name of the team that the agent who accepted the interaction is a member of
−
 
 
−
For inbound emails, ''duration'' is the time between the moment the email entered the system and the moment when the first meaningful response was sent (or the email was closed or transferred externally).
 
−
 
 
−
For outbound emails, ''duration'' is the time between the moment the email was initiated by the agent and the moment the email was sent.
 
−
 
 
−
Note that transferred and recategorized interactions produce multiple CDR records, where the first record shows "Duration" as empty string and the last record shows the total interaction duration.
 
−
 
 
−
Duration always shows calendar time (the total time between the specified moments) regardless of hours of operation of the associated service. For example, if an email was received at 4 pm and replied the next day at 10 am, the duration will show 18 hours, even if the call center was closed during the night.
 
 
|-
 
|-
−
|'''service_name'''
+
|'''caller_cpa_recording_url'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|''service_name'' specifies the name of the service associated with the interaction. If the interaction was recategorized or transferred to a different service, each such event will produce a new record with the new service value.
+
|URL of the recording for the CPA portion of the interaction segment of the party that originated this interaction
 +
|-
 +
|'''caller_cpa_rtp_server_id'''
 +
|BINARY
 +
|None
 +
|Identifier of the RTP server that made the recording for the CPA portion of interaction segment of the party that originated this interaction
 +
|-
 +
|'''caller_encryption_key_id'''
 +
|BINARY
 +
|None
 +
|For internal use only.
 
|-
 
|-
−
|'''scenario_name'''
+
|'''caller_first_name'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|''scenario_name'' specifies the name of the scenario used to process this interaction. If the interaction was processed by multiple scenarios, the first applied scenario will appear in this field. (Other scenarios that may have been invoked from the main scenario do not affect this field.)
+
|First name of the party that originated the interaction
 +
|-
 +
|'''caller_has_screen_recording'''
 +
|BIT
 +
|None
 +
|Indication that screen of the party that originated this interaction was recorded during the interaction
 +
|-
 +
|'''caller_interaction_step_id'''
 +
|BINARY
 +
|None
 +
|Identifier for the interaction segment of the party that originated this interaction
 
|-
 
|-
−
|'''trunk_description'''
+
|'''caller_last_name'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For inbound and outbound calls, the name of the trunk that was used to establish this call
+
|Last name of the party that originated the interaction
 
|-
 
|-
 
|'''caller_login_id'''
 
|'''caller_login_id'''
Line 111: Line 157:
 
|For outbound and internal interactions, ''caller_login_id'' specifies the login ID of the user who initiated this interaction. If the interaction was transferred, login ID of the user who initiated the transfer.
 
|For outbound and internal interactions, ''caller_login_id'' specifies the login ID of the user who initiated this interaction. If the interaction was transferred, login ID of the user who initiated the transfer.
 
|-
 
|-
−
|'''callee_login_id'''
+
|'''caller_monitored'''
−
|VARCHAR
+
|BIT
 
|None
 
|None
−
|For inbound and internal interactions, ''callee_login_id'' specifies the login ID of the user who received this interaction. If the interaction was transferred, it specifies the login ID of the user for which the interaction was last transferred.
+
|TRUE if the party that originated this interaction was monitored at any time during the interaction handling
 
|-
 
|-
 
|'''caller_phone_type'''
 
|'''caller_phone_type'''
Line 121: Line 167:
 
|''caller_phone_type'' specifies the location of the party that initiated the interaction. Possible values are INTERNAL or EXTERNAL.
 
|''caller_phone_type'' specifies the location of the party that initiated the interaction. Possible values are INTERNAL or EXTERNAL.
 
|-
 
|-
−
|'''callee_phone_type'''
+
|'''caller_rank'''
−
|ENUM
+
|VARCHAR
 +
|None
 +
|For outbound and internal interactions, the rank of the user who initiated this interaction
 +
|-
 +
|'''caller_recording_url'''
 +
|VARCHAR
 +
|None
 +
|URL of the recording for the interaction segment of the party that originated this interaction
 +
|-
 +
|'''caller_rtp_server_id'''
 +
|BINARY
 
|None
 
|None
−
|''callee_phone_type'' specifies the location of the party that received the interaction. Possible values are INTERNAL or EXTERNAL.
+
|Identifier of the RTP server that made the recording for the interaction segment of the party that originated this interaction
 
|-
 
|-
−
|'''caller_rank'''
+
|'''caller_team_name'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For outbound and internal interactions, the rank of the user who initiated this interaction
+
|Name of the team that the agent who originated the interaction is a member of
 
|-
 
|-
−
|'''callee_rank'''
+
|'''case_id'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For inbound and internal calls, the rank of the user who received this interaction
+
|Identifier of the [[agent-guide/UnderstandingEmailCasesandThreads|case]] with which this email is associated
 
|-
 
|-
−
|'''from_phone'''
+
|'''case_number'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|The phone number from which the call was made, or for emails, the email address in the "From" field
+
|''case_number'' specifies the number of the [[agent-guide/UnderstandingEmailCasesandThreads|case]] with which this email is associated. Unlike ''case_id'', case number is a simple number suitable for manual processing.
 
|-
 
|-
−
|'''original_destination_phone'''
+
|'''case_search_result'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|''original_destination_phone'' specifies the phone number that was dialed by the calling party. If the call or chat was transferred, it specifies the phone number dialed by the party that made the transfer. For emails, it is the email address used as the destination by the original sender.
+
|For each incoming email, the system will look for possible association with an existing case using the ''thread_id'' added to the original reply.
 +
 
 +
Possible values include the following:
 +
* ''found'' – A unique case associated with this email was found; the case number is copied to the ''case_number'' field
 +
* ''found_mulitple'' – Multiple cases were found; the ''case_number'' field is not populated
 +
* created – No matching cases were found, a new case was created and its number is copied to the ''case_number'' field
 +
* ''error''
 
|-
 
|-
 
|'''connected_to_phone'''
 
|'''connected_to_phone'''
Line 151: Line 213:
 
|''connected_to_phone'' specifies the phone number of the party to which the call or chat was delivered. If the call/chat was transferred, it specifies the phone number of the party to which the call/chat was last transferred.
 
|''connected_to_phone'' specifies the phone number of the party to which the call or chat was delivered. If the call/chat was transferred, it specifies the phone number of the party to which the call/chat was last transferred.
 
|-
 
|-
−
|'''transferred_from_phone'''
+
|'''detail_record_count'''
−
|VARCHAR
+
|INT
 
|None
 
|None
−
|For transferred calls and chats, the phone number from which the call/chat was last transferred
+
|Number of segments in this interaction (i.e., number of records in the ''call_detail'' table related to this interaction)
 
|-
 
|-
 
|'''disposition'''
 
|'''disposition'''
Line 183: Line 245:
 
* SERVICE_CHANGED – Agent changed the service associated with the email and continued processing it (the after-service-change phase is reported in a separate record)
 
* SERVICE_CHANGED – Agent changed the service associated with the email and continued processing it (the after-service-change phase is reported in a separate record)
 
|-
 
|-
−
|'''agent_disposition_name'''
+
|'''duration'''
−
|VARCHAR
+
|BIGINT
−
|None
+
|Seconds
−
|Name of the disposition that was assigned to this interaction
+
|For calls and chats, ''duration'' is the total duration of the interaction from the moment it entered the system or was initiated and until it was released.
 +
 
 +
For inbound emails, ''duration'' is the time between the moment the email entered the system and the moment when the first meaningful response was sent (or the email was closed or transferred externally).
 +
 
 +
For outbound emails, ''duration'' is the time between the moment the email was initiated by the agent and the moment the email was sent.
 +
 
 +
Note that transferred and recategorized interactions produce multiple CDR records, where the first record shows "Duration" as empty string and the last record shows the total interaction duration.
 +
 
 +
Duration always shows calendar time (the total time between the specified moments) regardless of hours of operation of the associated service. For example, if an email was received at 4 pm and replied the next day at 10 am, the duration will show 18 hours, even if the call center was closed during the night.
 
|-
 
|-
−
|'''agent_disposition_code'''
+
|'''email_completion_time'''
−
|INT
+
|BIGINT
−
|None
 
−
|Numerical code (if defined) of the disposition that was assigned to this interaction
 
−
|-
 
−
|'''agent_disposition_notes'''
 
−
|LONGTEXT
 
−
|None
 
−
|The text note that the agent wrote regarding the interaction
 
−
|-
 
−
|'''reported_problem'''
 
−
|ENUM
 
−
|None
 
−
|''reported_problem'' specifies the call quality problem as reported by the agent during this call using the ''report a call problem'' desktop control. Possible values include CALL_WENT_SILENT, CALL_DROPPED, POOR_VOICE_QUALITY, and OTHER
 
−
|-
 
−
|'''global_interaction_id'''
 
−
|BINARY
 
−
|None
 
−
|[[reporting-reference-guide/GlobalInteractionIdentifier|Global interaction identifier]]
 
−
|-
 
−
|'''initial_call_id'''
 
−
|BINARY
 
−
|None
 
−
|For transferred calls, ''initial_call_id'' is the identifier of the original interaction in the transfer sequence. It is maintained for backward compatibility only. Starting from release 3.11, use of the ''global_interaction_id'' is recommended for all interaction identification and linking purposes.
 
−
|-
 
−
|'''initial_start_time'''
 
−
|DATETIME
 
 
|Seconds
 
|Seconds
−
|For transferred calls, the start time of the original interaction in the transfer sequence; time is given in the Universal Coordinated Time (UTC) time zone
+
|Email completion time from the moment the email interaction was accepted or entered agent's personal queue and until it was completed (including ACW if any) or transferred
 
|-
 
|-
−
|'''initial_service_name'''
+
|'''email_detail_id'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For transferred interactions, the name of the service associated with the original interaction in the transfer sequence
+
|''email_detail_id'' is the identifier of the given step in processing of the email. A single email may have several records in the ''call_detail'' table corresponding to email processing steps (e.g., before and after transfer). Such records will have the same ''email_id'', but each will have its own ''email_detail_id''.
 
|-
 
|-
−
|'''initial_caller_phone_type'''
+
|'''email_id'''
−
|ENUM
 
−
|None
 
−
|For transferred interaction, this is the location of the party that initiated the original interaction in the transfer sequence. Possible values include INTERNAL or EXTERNAL.
 
−
|-
 
−
|'''initial_callee_phone_type'''
 
−
|ENUM
 
−
|None
 
−
|For transferred interactions, this is location of the party that received the original interaction in the transfer sequence. Possible values include INTERNAL or EXTERNAL.
 
−
|-
 
−
|'''initial_from_phone'''
 
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For transferred calls, the phone number from which the original call in the transfer sequence was made
+
|Indentifier of the email interaction
 
|-
 
|-
−
|'''initial_original_destination_phone'''
+
|'''email_kb_article_id'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For transferred calls, this specifies the phone number that was dialed by the original calling party in the transfer sequence was made. For emails, it specifies the email address used as the destination by the original sender.
+
|Identifier of the article used for replying to this email
 
|-
 
|-
−
|'''initial_connected_to_phone'''
+
|'''email_subject'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For transferred calls, the phone number of the original party in the transfer sequence to which the call was delivered
+
|Content of the email subject field
 
|-
 
|-
 
|'''flagged'''
 
|'''flagged'''
Line 253: Line 288:
 
|TRUE if the interaction was flagged by agent; FALSE otherwise
 
|TRUE if the interaction was flagged by agent; FALSE otherwise
 
|-
 
|-
−
|'''voice_signature'''
+
|'''from_phone'''
−
|BIT
 
−
|None
 
−
|TRUE if customer’s voice signature was collected during this call (i.e., the corresponding recording contains voice signature); FALSE otherwise
 
−
|-
 
−
|'''account_number'''
 
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|''account_number'' gets the customer’s account number. If the calling list record that initiated this call has a [[contact-center-administrator-guide/Lists|list field]] of ''Account'' type, the value of that field will be stored here. Account numbers can be used as selection criteria in [[reporting-reference-guide/InteractionRecordsSearch|interaction records search]].
+
|The phone number from which the call was made, or for emails, the email address in the "From" field
 
|-
 
|-
−
|'''caller_first_name'''
+
|'''global_interaction_id'''
−
|VARCHAR
+
|BINARY
 
|None
 
|None
−
|First name of the party that originated the interaction
+
|[[reporting-reference-guide/GlobalInteractionIdentifier|Global interaction identifier]]
 
|-
 
|-
−
|'''callee_first_name'''
+
|'''held'''
−
|VARCHAR
+
|BIGINT
 
|None
 
|None
−
|First name of the party that accepted the interaction
+
|''held'' specifies the number of times the call was placed on hold; for chats, the number of times the chat interaction was out of focus
 
|-
 
|-
−
|'''caller_last_name'''
+
|'''hold_time'''
−
|VARCHAR
+
|BIGINT
−
|None
+
|Seconds
−
|Last name of the party that originated the interaction
+
|''hold_time'' specifies the total time that the call spent on hold. For chats and emails, it is the total out-of-focus time (the time the interaction spent at the agents’ desktops excluding the ''Talk'' time).
 
|-
 
|-
−
|'''callee_last_name'''
+
|'''id'''
−
|VARCHAR
+
|BINARY (16)
 
|None
 
|None
−
|Last name of the party that accepted the interaction
+
|Reserved
 
|-
 
|-
−
|'''caller_team_name'''
+
|'''initial_callee_phone_type'''
−
|VARCHAR
+
|ENUM
 
|None
 
|None
−
|Name of the team that the agent who originated the interaction is a member of
+
|For transferred interactions, this is location of the party that received the original interaction in the transfer sequence. Possible values include INTERNAL or EXTERNAL.
 
|-
 
|-
−
|'''callee_team_name'''
+
|'''initial_caller_phone_type'''
−
|VARCHAR
+
|ENUM
 
|None
 
|None
−
|Name of the team that the agent who accepted the interaction is a member of
+
|For transferred interaction, this is the location of the party that initiated the original interaction in the transfer sequence. Possible values include INTERNAL or EXTERNAL.
 
|-
 
|-
−
|'''caller_monitored'''
+
|'''initial_call_id'''
−
|BIT
 
−
|None
 
−
|TRUE if the party that originated this interaction was monitored at any time during the interaction handling
 
−
|-
 
−
|'''callee_monitored'''
 
−
|BIT
 
−
|None
 
−
|TRUE if the party that accepted this interaction was monitored at any time during the interaction handling
 
−
|-
 
−
|'''caller_interaction_step_id'''
 
 
|BINARY
 
|BINARY
 
|None
 
|None
−
|Identifier for the interaction segment of the party that originated this interaction
+
|For transferred calls, ''initial_call_id'' is the identifier of the original interaction in the transfer sequence. It is maintained for backward compatibility only. Starting from release 3.11, use of the ''global_interaction_id'' is recommended for all interaction identification and linking purposes.
 
|-
 
|-
−
|'''callee_interaction_step_id'''
+
|'''initial_connected_to_phone'''
−
|BINARY
+
|VARCHAR
 
|None
 
|None
−
|Identifier for the interaction segment of the party that accepted this interaction
+
|For transferred calls, the phone number of the original party in the transfer sequence to which the call was delivered
 
|-
 
|-
−
|'''caller_rtp_server_id'''
+
|'''initial_from_phone'''
−
|BINARY
+
|VARCHAR
 
|None
 
|None
−
|Identifier of the RTP server that made the recording for the interaction segment of the party that originated this interaction
+
|For transferred calls, the phone number from which the original call in the transfer sequence was made
 
|-
 
|-
−
|'''caller_recording_url'''
+
|'''initial_original_destination_phone'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|URL of the recording for the interaction segment of the party that originated this interaction
+
|For transferred calls, this specifies the phone number that was dialed by the original calling party in the transfer sequence was made. For emails, it specifies the email address used as the destination by the original sender.
 
|-
 
|-
−
|'''caller_cpa_rtp_server_id'''
+
|'''initial_service_name'''
−
|BINARY
+
|VARCHAR
 
|None
 
|None
−
|Identifier of the RTP server that made the recording for the CPA portion of interaction segment of the party that originated this interaction
+
|For transferred interactions, the name of the service associated with the original interaction in the transfer sequence
 
|-
 
|-
−
|'''caller_cpa_recording_url'''
+
|'''initial_start_time'''
−
|VARCHAR
+
|DATETIME
−
|None
+
|Seconds
−
|URL of the recording for the CPA portion of the interaction segment of the party that originated this interaction
+
|For transferred calls, the start time of the original interaction in the transfer sequence; time is given in the Universal Coordinated Time (UTC) time zone
 
|-
 
|-
−
|'''callee_rtp_server_id'''
+
|'''ivr_time'''
−
|BINARY
+
|BIGINT
−
|None
+
|Seconds
−
|Identifier of the RTP server that made the recording for the interaction segment of the party that accepted this interaction
+
|Total time the call spent in IVR
 
|-
 
|-
−
|'''callee_recording_url'''
+
|'''max_hold'''
−
|VARCHAR
+
|BIGINT
−
|None
+
|Seconds
−
|URL of the recording for the interaction segment of the party that accepted this interaction
+
|Duration of the longest of the number of times the call was placed on hold; for chats, the duration of the longest of the number of times the chat interaction was out of focus
 
|-
 
|-
−
|'''callee_cpa_rtp_server_id'''
+
|'''media_type'''
−
|BINARY
+
|ENUM
 
|None
 
|None
−
|Identifier of the RTP server that made the recording for the CPA portion of interaction segment of the party that accepted this interaction
+
|''media_type'' specifies the interaction media type with possible values of VOICE, CHAT, or EMAIL.
 +
 
 +
If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context.
 
|-
 
|-
−
|'''callee_cpa_recording_url'''
+
|'''original_destination_phone'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|URL of the recording for the CPA portion of the interaction segment of the party that accepted this interaction
+
|''original_destination_phone'' specifies the phone number that was dialed by the calling party. If the call or chat was transferred, it specifies the phone number dialed by the party that made the transfer. For emails, it is the email address used as the destination by the original sender.
 
|-
 
|-
−
|'''caller_encryption_key_id'''
+
|'''pending_time'''
−
|BINARY
+
|BIGINT
−
|None
+
|Seconds
−
|For internal use only.
+
|For inbound calls, ''pending_time'' specifies the duration of call ringing phase from the moment the call was distributed to an extension and until it was either answered or abandoned. For internal and outbound calls, ''pending_time'' specifies the duration of call dialing phase from the moment the dialed number was received by the system and until the call was either answered or abandoned. It does not apply to email.
 
|-
 
|-
−
|'''callee_encryption_key_id'''
+
|'''pkid'''
−
|BINARY
+
|INT
 
|None
 
|None
−
|For internal use only.
+
|Primary key
 
|-
 
|-
−
|'''detail_record_count'''
+
|'''queue_time'''
−
|INT
+
|BIGINT
−
|None
+
|Seconds
−
|Number of segments in this interaction (i.e., number of records in the ''call_detail'' table related to this interaction)
+
|Total time the interaction spent in the service queue
 
|-
 
|-
−
|'''email_id'''
+
|'''reported_problem'''
−
|VARCHAR
+
|ENUM
 
|None
 
|None
−
|Indentifier of the email interaction
+
|''reported_problem'' specifies the call quality problem as reported by the agent during this call using the ''report a call problem'' desktop control. Possible values include CALL_WENT_SILENT, CALL_DROPPED, POOR_VOICE_QUALITY, and OTHER
 
|-
 
|-
−
|'''email_detail_id'''
+
|'''response_email_id'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|''email_detail_id'' is the identifier of the given step in processing of the email. A single email may have several records in the ''call_detail'' table corresponding to email processing steps (e.g., before and after transfer). Such records will have the same ''email_id'', but each will have its own ''email_detail_id''.
+
|Identifier of the article that was sent automatically to acknowledge receipt of this email
 
|-
 
|-
−
|'''email_subject'''
+
|'''scenario_name'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|Content of the email subject field
+
|''scenario_name'' specifies the name of the scenario used to process this interaction. If the interaction was processed by multiple scenarios, the first applied scenario will appear in this field. (Other scenarios that may have been invoked from the main scenario do not affect this field.)
 
|-
 
|-
−
|'''email_kb_article_id'''
+
|'''service_name'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|Identifier of the article used for replying to this email
+
|''service_name'' specifies the name of the service associated with the interaction. If the interaction was recategorized or transferred to a different service, each such event will produce a new record with the new service value.
 
|-
 
|-
−
|'''response_email_id'''
+
|'''start_time'''
−
|VARCHAR
+
|DATETIME
−
|None
+
|Seconds
−
|Identifier of the article that was sent automatically to acknowledge receipt of this email
+
|For inbound interactions, ''start_time'' specifies the date and time when the interaction entered the system.
 +
 
 +
For outbound and internal interactions, ''start_time'' specifies the date and time when the interaction was initiated.
 +
 
 +
The time is given in the Universal Coordinated Time (UTC) time zone.
 
|-
 
|-
−
|'''case_id'''
+
|'''talk_time'''
−
|VARCHAR
+
|BIGINT
−
|None
+
|Seconds
−
|Identifier of the [[agent-guide/UnderstandingEmailCasesandThreads|case]] with which this email is associated
+
|''talk_time'' specifies the total call talk time. It excludes hold time. For chats and emails, ''talk_time'' is the total in-focus time (the time the interaction was selected in the active communications lists of the agents who processed it).
 
|-
 
|-
 
|'''thread_id'''
 
|'''thread_id'''
Line 403: Line 429:
 
|''thread_id'' is the identifier of the [[agent-guide/UnderstandingEmailCasesandThreads|email thread]] that this email is part of. This identifier is added to the subject of the email when the email is replied to and is used for case search during possible follow-up emails (see ''case_search_result'').
 
|''thread_id'' is the identifier of the [[agent-guide/UnderstandingEmailCasesandThreads|email thread]] that this email is part of. This identifier is added to the subject of the email when the email is replied to and is used for case search during possible follow-up emails (see ''case_search_result'').
 
|-
 
|-
−
|'''case_number'''
+
|'''transferred_from_phone'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|''case_number'' specifies the number of the [[agent-guide/UnderstandingEmailCasesandThreads|case]] with which this email is associated. Unlike ''case_id'', case number is a simple number suitable for manual processing.
+
|For transferred calls and chats, the phone number from which the call/chat was last transferred
 
|-
 
|-
−
|'''case_search_result'''
+
|'''trunk_description'''
 
|VARCHAR
 
|VARCHAR
 
|None
 
|None
−
|For each incoming email, the system will look for possible association with an existing case using the ''thread_id'' added to the original reply.
+
|For inbound and outbound calls, the name of the trunk that was used to establish this call
−
 
 
−
Possible values include the following:
 
−
* ''found'' – A unique case associated with this email was found; the case number is copied to the ''case_number'' field
 
−
* ''found_mulitple'' – Multiple cases were found; the ''case_number'' field is not populated
 
−
* created – No matching cases were found, a new case was created and its number is copied to the ''case_number'' field
 
−
* ''error''
 
 
|-
 
|-
−
|'''caller_has_screen_recording'''
+
|'''voice_signature'''
 
|BIT
 
|BIT
 
|None
 
|None
−
|Indication that screen of the party that originated this interaction was recorded during the interaction
+
|TRUE if customer’s voice signature was collected during this call (i.e., the corresponding recording contains voice signature); FALSE otherwise
−
|-
 
−
|'''callee_has_screen_recording'''
 
−
|BIT
 
−
|None
 
−
|Indication that screen of the party that accepted this interaction was recorded during the interaction
 
 
|-
 
|-
−
|'''email_completion_time'''
 
−
|BIGINT
 
−
|Seconds
 
−
|Email completion time from the moment the email interaction was accepted or entered agent's personal queue and until it was completed (including ACW if any) or transferred
 
 
|}
 
|}
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
 +
  
  

Latest revision as of 23:30, 17 August 2018

• 3.10 • 3.11 • 3.12 • 3.13 • 3.14 • 3.15 • 3.16 • 3.17 • 3.18

call_detail

Each row of the call_detail table contains a set of data related to the processing of a single interaction, including some interaction-level aggregates, such as total interaction duration and total talk time. Note that the entire interaction record in this table is reported for the interval in which the corresponding interaction was entered the system (for inbound interactions) or was initiated (for internal and outbound interactions), regardless of the number of intervals the interaction may have spanned.

For emails, a record is created in this table as soon as an email arrives in the system (for inbound emails) or initiated by an agent (for outbound emails). The record is then updated every time it is saved as a draft. The record is updated and closed when the processing of the email is finished.

Except for the name of the table itself, the term call in the descriptions below indicates that the parameter applies to calls and chats. Where a parameter has the same meaning for all media types, the term interaction is used. Where a parameter applies to multiple media types with a different meaning, each media type is discussed separately.


Description of Data

The following table offers the name (i.e., ID), data type (e.g., BIGINT, BINARY, BIT, DATETIME, ENUM, INT, VARCHAR, etc.), and description for each metric (i.e., column) of the call_detail table.


Column Name Data Type Units Description
account_number VARCHAR None account_number gets the customer’s account number. If the calling list record that initiated this call has a list field of Account type, the value of that field will be stored here. Account numbers can be used as selection criteria in interaction records search.
acw_time BIGINT Seconds The amount of time the agents spent doing after-call work related to this interaction
agent_disposition_code INT None Numerical code (if defined) of the disposition that was assigned to this interaction
agent_disposition_name VARCHAR None Name of the disposition that was assigned to this interaction
agent_disposition_notes LONGTEXT None The text note that the agent wrote regarding the interaction
agg_run_id BINARY (16) None Aggregator run that produced this record
callee_cpa_recording_url VARCHAR None URL of the recording for the CPA portion of the interaction segment of the party that accepted this interaction
callee_cpa_rtp_server_id BINARY None Identifier of the RTP server that made the recording for the CPA portion of interaction segment of the party that accepted this interaction
callee_encryption_key_id BINARY None For internal use only.
callee_first_name VARCHAR None First name of the party that accepted the interaction
callee_has_screen_recording BIT None Indication that screen of the party that accepted this interaction was recorded during the interaction
callee_interaction_step_id BINARY None Identifier for the interaction segment of the party that accepted this interaction
callee_last_name VARCHAR None Last name of the party that accepted the interaction
callee_login_id VARCHAR None For inbound and internal interactions, callee_login_id specifies the login ID of the user who received this interaction. If the interaction was transferred, it specifies the login ID of the user for which the interaction was last transferred.
callee_monitored BIT None TRUE if the party that accepted this interaction was monitored at any time during the interaction handling
callee_phone_type ENUM None callee_phone_type specifies the location of the party that received the interaction. Possible values are INTERNAL or EXTERNAL.
callee_rank VARCHAR None For inbound and internal calls, the rank of the user who received this interaction
callee_recording_url VARCHAR None URL of the recording for the interaction segment of the party that accepted this interaction
callee_rtp_server_id BINARY None Identifier of the RTP server that made the recording for the interaction segment of the party that accepted this interaction
callee_team_name VARCHAR None Name of the team that the agent who accepted the interaction is a member of
caller_cpa_recording_url VARCHAR None URL of the recording for the CPA portion of the interaction segment of the party that originated this interaction
caller_cpa_rtp_server_id BINARY None Identifier of the RTP server that made the recording for the CPA portion of interaction segment of the party that originated this interaction
caller_encryption_key_id BINARY None For internal use only.
caller_first_name VARCHAR None First name of the party that originated the interaction
caller_has_screen_recording BIT None Indication that screen of the party that originated this interaction was recorded during the interaction
caller_interaction_step_id BINARY None Identifier for the interaction segment of the party that originated this interaction
caller_last_name VARCHAR None Last name of the party that originated the interaction
caller_login_id VARCHAR None For outbound and internal interactions, caller_login_id specifies the login ID of the user who initiated this interaction. If the interaction was transferred, login ID of the user who initiated the transfer.
caller_monitored BIT None TRUE if the party that originated this interaction was monitored at any time during the interaction handling
caller_phone_type ENUM None caller_phone_type specifies the location of the party that initiated the interaction. Possible values are INTERNAL or EXTERNAL.
caller_rank VARCHAR None For outbound and internal interactions, the rank of the user who initiated this interaction
caller_recording_url VARCHAR None URL of the recording for the interaction segment of the party that originated this interaction
caller_rtp_server_id BINARY None Identifier of the RTP server that made the recording for the interaction segment of the party that originated this interaction
caller_team_name VARCHAR None Name of the team that the agent who originated the interaction is a member of
case_id VARCHAR None Identifier of the case with which this email is associated
case_number VARCHAR None case_number specifies the number of the case with which this email is associated. Unlike case_id, case number is a simple number suitable for manual processing.
case_search_result VARCHAR None For each incoming email, the system will look for possible association with an existing case using the thread_id added to the original reply.

Possible values include the following:

  • found – A unique case associated with this email was found; the case number is copied to the case_number field
  • found_mulitple – Multiple cases were found; the case_number field is not populated
  • created – No matching cases were found, a new case was created and its number is copied to the case_number field
  • error
connected_to_phone VARCHAR None connected_to_phone specifies the phone number of the party to which the call or chat was delivered. If the call/chat was transferred, it specifies the phone number of the party to which the call/chat was last transferred.
detail_record_count INT None Number of segments in this interaction (i.e., number of records in the call_detail table related to this interaction)
disposition ENUM None disposition specifies how the interaction ended. The term call in the descriptions below indicates that the given value may be applicable to calls and chats.

Possible values include the following:

  • CALLER_TERMINATED – Call terminated by the party that made the call (after the call was answered)
  • CALLEE_TERMINATED – Call terminated by the party that answered the call
  • TRANSFERRED – Interaction was transferred by the party who accepted it (the after-transfer phase is reported in a separate record)
  • CONFERENCED – Call became a conference (the conference phase is reported a separate call)
  • SYSTEM_DISCONNECTED – Call was terminated by the system
  • SELF_SERVICE – Requested service was provided by the IVR application (as indicated by execution of scenario block Self-Service Provided)
  • ABANDONED – Inbound call was terminated by the by the caller while processed in the IVR application (except the SELF_SERVICE case above)
  • ABANDONED_QUEUE – Inbound call was terminated by the caller while waiting in the service queue
  • ABANDONED_RINGING – Inbound or internal call was terminated by the caller after it was delivered to the called party and before it was answered (or before the No Answer timeout expired)
  • NO_ANSWER – Inbound, outbound or internal call attempt was terminated after it was delivered to the called party desktop and was not answered within the No Answer timeout
  • CALLED_PARTY_BUSY – Outbound call attempt was terminated because the called party was busy
  • NETWORK_BUSY – Outbound call attempt was terminated because of the network congestion
  • CALLER_TRANSFERRED – Call was transferred by the caller (the after-transfer phase is reported as a separate call)
  • CALLBACK_REQUESTED – Call was terminated because a callback was requested (the corresponding callback attempt is reported as a separate call)
  • REPLIED – Email was replied to; applies to inbound emails only
  • CLOSED_WITHOUT_REPLY – Processing of the email was finished without a reply (e.g., the email was a spam or no follow-up was necessary); applies to inbound emails only
  • SENT – Email was sent; applies to outbound emails only
  • DISCARDED – Email initiated and subsequently discarded without being sent; applies to outbound emails only
  • SERVICE_CHANGED – Agent changed the service associated with the email and continued processing it (the after-service-change phase is reported in a separate record)
duration BIGINT Seconds For calls and chats, duration is the total duration of the interaction from the moment it entered the system or was initiated and until it was released.

For inbound emails, duration is the time between the moment the email entered the system and the moment when the first meaningful response was sent (or the email was closed or transferred externally).

For outbound emails, duration is the time between the moment the email was initiated by the agent and the moment the email was sent.

Note that transferred and recategorized interactions produce multiple CDR records, where the first record shows "Duration" as empty string and the last record shows the total interaction duration.

Duration always shows calendar time (the total time between the specified moments) regardless of hours of operation of the associated service. For example, if an email was received at 4 pm and replied the next day at 10 am, the duration will show 18 hours, even if the call center was closed during the night.

email_completion_time BIGINT Seconds Email completion time from the moment the email interaction was accepted or entered agent's personal queue and until it was completed (including ACW if any) or transferred
email_detail_id VARCHAR None email_detail_id is the identifier of the given step in processing of the email. A single email may have several records in the call_detail table corresponding to email processing steps (e.g., before and after transfer). Such records will have the same email_id, but each will have its own email_detail_id.
email_id VARCHAR None Indentifier of the email interaction
email_kb_article_id VARCHAR None Identifier of the article used for replying to this email
email_subject VARCHAR None Content of the email subject field
flagged BIT None TRUE if the interaction was flagged by agent; FALSE otherwise
from_phone VARCHAR None The phone number from which the call was made, or for emails, the email address in the "From" field
global_interaction_id BINARY None Global interaction identifier
held BIGINT None held specifies the number of times the call was placed on hold; for chats, the number of times the chat interaction was out of focus
hold_time BIGINT Seconds hold_time specifies the total time that the call spent on hold. For chats and emails, it is the total out-of-focus time (the time the interaction spent at the agents’ desktops excluding the Talk time).
id BINARY (16) None Reserved
initial_callee_phone_type ENUM None For transferred interactions, this is location of the party that received the original interaction in the transfer sequence. Possible values include INTERNAL or EXTERNAL.
initial_caller_phone_type ENUM None For transferred interaction, this is the location of the party that initiated the original interaction in the transfer sequence. Possible values include INTERNAL or EXTERNAL.
initial_call_id BINARY None For transferred calls, initial_call_id is the identifier of the original interaction in the transfer sequence. It is maintained for backward compatibility only. Starting from release 3.11, use of the global_interaction_id is recommended for all interaction identification and linking purposes.
initial_connected_to_phone VARCHAR None For transferred calls, the phone number of the original party in the transfer sequence to which the call was delivered
initial_from_phone VARCHAR None For transferred calls, the phone number from which the original call in the transfer sequence was made
initial_original_destination_phone VARCHAR None For transferred calls, this specifies the phone number that was dialed by the original calling party in the transfer sequence was made. For emails, it specifies the email address used as the destination by the original sender.
initial_service_name VARCHAR None For transferred interactions, the name of the service associated with the original interaction in the transfer sequence
initial_start_time DATETIME Seconds For transferred calls, the start time of the original interaction in the transfer sequence; time is given in the Universal Coordinated Time (UTC) time zone
ivr_time BIGINT Seconds Total time the call spent in IVR
max_hold BIGINT Seconds Duration of the longest of the number of times the call was placed on hold; for chats, the duration of the longest of the number of times the chat interaction was out of focus
media_type ENUM None media_type specifies the interaction media type with possible values of VOICE, CHAT, or EMAIL.

If set to CHAT, any call mentioned in this table shall be interpreted as a service chat interaction in the same context.

original_destination_phone VARCHAR None original_destination_phone specifies the phone number that was dialed by the calling party. If the call or chat was transferred, it specifies the phone number dialed by the party that made the transfer. For emails, it is the email address used as the destination by the original sender.
pending_time BIGINT Seconds For inbound calls, pending_time specifies the duration of call ringing phase from the moment the call was distributed to an extension and until it was either answered or abandoned. For internal and outbound calls, pending_time specifies the duration of call dialing phase from the moment the dialed number was received by the system and until the call was either answered or abandoned. It does not apply to email.
pkid INT None Primary key
queue_time BIGINT Seconds Total time the interaction spent in the service queue
reported_problem ENUM None reported_problem specifies the call quality problem as reported by the agent during this call using the report a call problem desktop control. Possible values include CALL_WENT_SILENT, CALL_DROPPED, POOR_VOICE_QUALITY, and OTHER
response_email_id VARCHAR None Identifier of the article that was sent automatically to acknowledge receipt of this email
scenario_name VARCHAR None scenario_name specifies the name of the scenario used to process this interaction. If the interaction was processed by multiple scenarios, the first applied scenario will appear in this field. (Other scenarios that may have been invoked from the main scenario do not affect this field.)
service_name VARCHAR None service_name specifies the name of the service associated with the interaction. If the interaction was recategorized or transferred to a different service, each such event will produce a new record with the new service value.
start_time DATETIME Seconds For inbound interactions, start_time specifies the date and time when the interaction entered the system.

For outbound and internal interactions, start_time specifies the date and time when the interaction was initiated.

The time is given in the Universal Coordinated Time (UTC) time zone.

talk_time BIGINT Seconds talk_time specifies the total call talk time. It excludes hold time. For chats and emails, talk_time is the total in-focus time (the time the interaction was selected in the active communications lists of the agents who processed it).
thread_id VARCHAR None thread_id is the identifier of the email thread that this email is part of. This identifier is added to the subject of the email when the email is replied to and is used for case search during possible follow-up emails (see case_search_result).
transferred_from_phone VARCHAR None For transferred calls and chats, the phone number from which the call/chat was last transferred
trunk_description VARCHAR None For inbound and outbound calls, the name of the trunk that was used to establish this call
voice_signature BIT None TRUE if customer’s voice signature was collected during this call (i.e., the corresponding recording contains voice signature); FALSE otherwise











< Previous | Next >
Retrieved from "https://help3x.brightpattern.com/index.php?title=3.17:Reporting-database-specification/call_detail&oldid=16430"