= Purpose= ServicePattern Reporting Database Specification describes the historical data that is collected and stored in the Reporting Database, also known as DB2. This database contains detailed records about interactions and agent activities as well as many pre-aggregated statistical values. ::'''Note:''' The Reporting Database tables that are not documented in this guide are for internal use only. The data collected in the Reporting Database is used for generation of the out-of-the-box ServicePattern reports. The reports have been developed using the Jaspersoft Studio. For information about these reports, see [[#topic_reporting-reference-guide/Purpose|''ServicePattern Reporting Reference Guide'']]. You can use any SQL-based reporting application to create your custom reports. Note however that using Jaspersoft Studio for custom report creation offers the following advantages: * You can generate and view such custom reports directly in the Contact Center Administrator application in the same way that you generate and view the out-of-the-box reports. For more information, see section [[#topic_contact-center-administrator-guide/ReportTemplates|Report Templates]] of the ServicePattern Contact Center Administrator Guide. * You can re-use the available out-of-the-box report templates to create new reports and make modifications only where necessary. The [[#topic_custom-reporting-tutorial/Purpose|''ServicePattern Custom Reporting Tutorial'']] explains how to configure Jaspersoft Studio for creation of custom ServicePattern reports and how to re-use the out-of-the-box report templates. ::'''Note:''' Jaspersoft has two different tools for report development: iReport Designer and Jaspersoft Studio. Note that only the Studio is supported as the tool for creation of custom report templates for your ServicePattern contact center solution. [[#topic_reporting-database-specification/Audience|Next >]] = Audience= This guide is intended primarily for professionals responsible for design, development, and testing of custom applications and reports in your contact center. Readers are expected to be have experience with relational databases and reporting tools as well as solid understanding of contact center operations and resources that are involved in such operations. [[#topic_reporting-database-specification/Purpose|< Previous]] | [[#topic_reporting-database-specification/GeneralInformationAboutStatisticalData|Next >]] = General Information About Statistical Data= The tables specified in this section contain metrics for the main contact center resources, such as agents and services, arranged in 15-minute statistical intervals. The data in these tables is used, in particular, for generation of the out-of-the-box reports described in the [[#topic_reporting-reference-guide/Purpose|''ServicePattern Reports Reference Guide'']]. You can see the actual queries used in these reports by downloading the corresponding .''jrxml'' templates. The data source for these metrics is the raw event data that is initially written to the Collector Database (DB1) in real-time by various ServicePattern components. This raw data is then periodically extracted by the Aggregator component, transformed into the specified metrics for the base 15-minute statistical intervals, and loaded into the tables of the Reporting Database (DB2). An SQL-compliant reporting application can be used for aggregation of these basic metrics into desired higher-level reporting intervals (hour, day, week, month, etc.). The following considerations apply to all statistical data tables: * Unless noted otherwise with respect to a particular metric, all call-related metrics count inbound calls for the aggregation interval in which they entered the system. (E.g., if a call entered the system in interval A and was answered in interval B, metric ''num_calls_answered'' will count it for interval A and not for interval B.) Likewise, all internal and outbound calls are counted for the aggregation interval in which they were initiated. * Metrics are provided for all supported media types. The media type can be indicated either explicitly via the ''media_type'' field and/or indirectly via the ''service_name'' field. * All call-related metrics are also supported for the chat media type. Thus, if either the ''media_type'' field the ''service_name'' field indicates media type chat, the term ''call'' in the description of any metric shall be interpreted for the given row of the given table as a service chat interaction in the same context. Note that internal chats between agents/supervisors are not taken into consideration by any metrics. * Some call-related metrics are also supported for the email media type. For every such metric, a note is provided about how to interpret it for emails. If email is not explicitly mentioned, the metric should be considered applicable to voice and chat only. * It is possible to have more than one row of data for the same 15-minute interval related to the same resource. This happens when there are interactions that span multiple aggregation intervals. The system learns about such interactions later, but still attributes them to the interval when they started, arranging them in a separate row. Practically this means that when you do queries on the statistical data, you should add up all values from all rows that are returned. [[#topic_reporting-database-specification/Audience|< Previous]] | [[#topic_reporting-database-specification/agent_performance|Next >]] = agent_performance= This table contains agent metrics. Note that interaction-related metrics are counted separately for each service that the agent provided within the aggregation interval. Thus, unless noted otherwise with respect to a particular metric, any interaction mentioned in this table shall be interpreted as an interaction associated with service specified in the ''service_name'' field that was handled by the agent identified by the ''login_id'' field. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Reserved |- |'''pkid''' |INT |Primary key. |- |'''login_id''' |VARCHAR |Agent login as defined in configuration. |- |'''first_name''' |VARCHAR |Agent first name as defined in configuration. |- |'''last_name''' |VARCHAR |Agent last name as defined in configuration. |- |'''team_name''' |VARCHAR |Name of the team that the agent is assigned to as defined in configuration. |- |'''rank''' |VARCHAR |Agent rank as defined in configuration. |- |'''no_service''' |BIT |Reserved |- |'''service_name''' |VARCHAR |Name of the service associated with the calls handled by the agent. If the agent handled calls for multiple services, his call-related metrics will be provided for each service separately. For services of media type chat, any ''call'' mentioned in this table shall be interpreted as a chat interaction in the same context. |- |'''is_internal''' |BIT |TRUE for internal calls. If set to TRUE, all inbound calls in this row shall be interpreted as internal calls received by the agent, and all outbound calls as internal calls made by the agent. |- |'''is_campaign''' |BIT |Services of blended type will have two rows of metrics: one where this bit is set to TRUE, counting campaign calls only; the other row with this bit set to FALSE, counting inbound and non-campaign outbound calls. |- |'''media_type''' |ENUM |Media type. Possible values VOICE, CHAT, EMAIL. If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''total_num_calls''' |BIGINT |Reserved. |- |'''num_calls_in''' |BIGINT |Number of inbound calls offered to the agent. For email, number of emails that were pushed to the agent. |- |'''num_calls_answered''' |BIGINT |Number of inbound calls handled by the agent. For email, total number of inbound emails processed by the agent in any manner (replied to, closed without reply, or transferred). |- |'''num_calls_out''' |BIGINT |Number of initiated outbound calls. For email, number of outbound emails that were sent by this agent. Outbound emails include unsolicited emails and possible additional responses to inbound emails that were previously replied to. |- |'''num_calls_answered_outbound''' |BIGINT |Number of outbound calls handled by the agent. |- |'''num_calls_agent_abandoned''' |BIGINT |Reserved. |- |'''num_calls_rejected''' |BIGINT |Number of inbound or predictive outbound calls the agent rejected. |- |'''num_calls_no_answer''' |BIGINT |Number of inbound or predictive outbound calls the agent did not answer within the specified No Answer timeout. For email, number of emails that were pushed to the agent and were explicitly rejected (i.e., returned to the queue or to the transferring agent). Excludes emails that went into agent’s personal queue. |- |'''num_calls_graded''' |BIGINT |Number of calls handled by the agent that were graded. |- |'''num_initiated_transfers''' |BIGINT |Number of transfers made by the agent. For email, number of emails transferred by the agent to any other resource. |- |'''total_login_time''' |BIGINT |Total time the agent was logged on during the aggregation interval (the sum of all times in states indicating that the agent was logged in). |- |'''total_working_time''' |BIGINT |Total time the agent spent handling calls or being ready to handle calls during the aggregation interval (the sum of times in Talk, Hold, After-Call Work and Ready states). |- |'''total_ready_time''' |BIGINT |Total time the agent spent in the Ready state during the aggregation interval. |- |'''total_handling_time''' |BIGINT |Reserved. |- |'''total_handling_call_time''' |BIGINT |Reserved |- |'''total_handling_call_time_in''' |BIGINT |The sum of talk and hold times for inbound calls. Includes ringing time. |- |'''total_handling_call_time_out''' |BIGINT |The sum of talk and hold times for outbound calls. Includes dialing time. |- |'''total_handling_acw_time''' |BIGINT |Reserved |- |'''total_handling_acw_time_in''' |BIGINT |The sum of after-call work times for inbound calls. For email, total time the agent spent doing after call work related to emails. Includes only the emails that agents replied to. |- |'''total_handling_acw_time_out''' |BIGINT |The sum of after-call work times for outbound calls. |- |'''total_busy_time_in''' |BIGINT |The sum of times the agent was busy with inbound calls. Includes hold times. Does not include ringing time or after call work time. |- |'''total_busy_time_out''' |BIGINT |The sum of times the agent was busy with outbound calls. Includes hold times. Does not include dialing time or after call work time. |- |'''total_ringing_time_in''' |BIGINT |The sum of ringing times for inbound calls delivered to the agent (between call initiation and either remote party answer or abandonment). |- |'''total_ringing_time_out''' |BIGINT |The sum of ringing times for outbound calls initiated by the agent (between call initiation and either remote party answer or abandonment). |- |'''total_acw_time_in''' |BIGINT |Reserved |- |'''total_acw_time_out''' |BIGINT |Reserved |- |'''total_hold_time_in''' |BIGINT |The sum of hold times for inbound calls. For email, total time emails spent in inactive state on the agent desktop. Includes only the emails that agents replied to. |- |'''total_hold_time_out''' |BIGINT |The sum of hold times for outbound calls. |- |'''num_surveys''' |BIGINT |Number of surveys available for calls handled by this agent. |- |'''num_surveys_with_cs''' |BIGINT |Number of surveys where the contact satisfaction question was responded to. |- |'''num_surveys_with_nps''' |BIGINT |Number of surveys where the net promoter score question was responded to. |- |'''num_surveys_with_fcr''' |BIGINT |Number of surveys where the first-call resolution question was responded to. |- |'''cs''' |BIGINT |The sum of contact satisfaction marks for all surveys where the contact satisfaction question was responded to. |- |'''nps''' |BIGINT |The sum of net promoter score points for all surveys where the contact satisfaction question was responded to. |- |'''num_fcr''' |BIGINT |Number of surveys that indicated first-call resolution relative to total number of surveys where the first-call resolution question was responded to. |- |'''grade_name''' |VARCHAR |Name of the call grading category. |- |'''grade_count''' |BIGINT |Number of calls handled by the agent that received any grades in the category specified in ''grade_name''. |- |'''grade_total_value''' |BIGINT |The sum of all grades for the calls counted in ''grade_count''. |- |'''grade_order_num''' |INT |The order in which the category specified in ''grade_name'' is supposed to appear in reports relative to the other grading categories (as defined in configuration). |- |'''not_ready_reason''' |VARCHAR |Not Ready reason as defined in configuration. |- |'''not_ready_time''' |BIGINT |Total time the agent spent in the Not Ready state with the reason specified in ''not_ready_reason''. |- |'''num_emails_pulled''' |BIGINT |Number of inbound emails that the agent pulled from the service queue. |- |'''num_emails_received_as_transfers''' |BIGINT |Number of inbound emails that were transferred to the agent. |- |'''num_emails_replied_by_agent''' |BIGINT |Number of emails that the agent replied to. Includes only the first meaningful response. Note that only the first response is counted. Possible follow-up email messages related to previously replied emails are considered outbound emails and counted in the ''num_calls_out'' field. |- |'''num_emails_closed_without_reply''' |BIGINT |Number of inbound emails that this agent closed without reply. |- |'''num_emails_discarded''' |BIGINT |Number of outbound emails that this agent initiated and subsequently discarded without sending. |- |'''email_answer_time''' |BIGINT |Total time this agent spent replying to inbound emails. The time is measured from the moment an email arrives at the agent desktop to the moment when the first meaningful response leaves his personal queue. |- |'''num_emails_in_carried_over''' |BIGINT |Number of inbound emails that were delivered to this agent by any method before the given reporting interval and remained unprocessed at the beginning of the interval. |- |'''num_emails_in_waiting_''' '''in_personal_queues''' |BIGINT |Number of inbound emails that were in the agent’s personal queue at the end of the interval. |- |'''num_emails_out_waiting_in_''' '''personal_queues''' |BIGINT |Number of outbound emails that were in the agent’s personal queue at the end of the interval. |- |'''num_emails_in_service_changed''' |BIGINT |Number of inbound emails to the given service that the agent re-categorized (i.e., assigned another service to them and continued their processing). |- |'''num_emails_in_waiting_in_''' '''personal_queues_breached_sla''' |BIGINT |Number of inbound emails remaining in the agent’s personal queue at the end of the interval that breached SLA (i.e., the emails whose time in the system exceeded the service level threshold configured for the given service). |} [[#topic_reporting-database-specification/GeneralInformationAboutStatisticalData|< Previous]] | [[#topic_reporting-database-specification/team_performance|Next >]] = team_performance= This table is currently not used. If any team-level metrics are desired in custom reports, such metrics can be obtained by combining the corresponding data from the ''agent_performance'' table for all team members selected using the ''team_name'' field. [[#topic_reporting-database-specification/agent_performance|< Previous]] | [[#topic_reporting-database-specification/service_in_time_counters|Next >]] = service_in_time_counters= This table contains general metrics for all services defined in your contact center configuration. Note the following: * For voice services, if a service can be accessed via multiple access numbers, the metrics are provided for each access number separately. * Unless noted otherwise with respect to a particular metric, any ''service call'' mentioned below shall be interpreted as an inbound call that entered the system via access number specified in the ''destination_phone'' field within the given aggregation interval and requested the service specified in the ''service_name'' field. * Unless noted otherwise with respect to a particular metric, any ''campaign call'' mentioned below shall be interpreted as a call initiated as part of the outbound calling campaign specified in the ''service_name'' field within the given aggregation interval. * In all metrics counting transferred calls, each transfer instance is counted separately. * Any ''campaign record'' mentioned below shall be interpreted as a record from one of the calling lists associated with the campaign specified in the ''service_name'' field whose processing within this campaign was completed within the given aggregation interval. * This table also contains metrics for agent teams that handled interactions for the specified service. Team-specific service metrics are reported in separate table rows, one row per team. Any ''team agent'' mentioned below shall be interpreted as an agent of the team specified in the ''team_name'' field who have the skill corresponding to the service or campaign specified in the ''service_name'' field''.'' {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''service_name''' |VARCHAR |Name of the service as defined in service configuration. |- |'''destination_phone''' |VARCHAR |Access number for this service as defined in the Dial-in Scenario Entry associated with service. If a service is associated with multiple access numbers, metrics specified in this table will be provided for each access number separately. |- |'''team_name''' |VARCHAR |Name of the team that handled calls associated with this service. This field is empty for the service metrics that are not team specific. If several teams handled calls associated with the service, the team metrics will be provided for each team separately. |- |'''media_type''' |ENUM |Media type of this service. Possible values VOICE, CHAT, EMAIL. If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context. |- |'''num_calls_received''' |BIGINT |Number of received service calls. For email, number of received service emails, including new emails and emails related to existing thread. For new emails only, see ''num_emails_received_new''. |- |'''num_calls_received_as_transfers''' |BIGINT |Number of service calls received as transfers. For email, number of service emails received as transfers. |- |'''num_calls_received_as_transfers_''' '''from_same_service''' |BIGINT |Number of service calls received as transfers from the same service. |- |'''num_calls_received_as_transfers_''' '''from_other_service''' |BIGINT |Number of service calls received as transfers from different services. |- |'''num_calls_queued''' |BIGINT |Number of service calls that were queued. |- |'''num_calls_answered''' |BIGINT |Number of service calls that were answered. For email, number of inbound service emails processed in any manner (replied to, closed without reply, transferred, or service changed). |- |'''num_calls_transferred_internally''' |BIGINT |Number of service calls that were answered and then transferred to another internal number (queue or extension). For email, number of service emails that were transferred. |- |'''num_calls_transferred_externally''' |BIGINT |Number of service calls that were answered and then transferred to an external number. |- |'''answer_time''' |BIGINT |Total answer time for all calls counted in ''num_calls_answered''. |- |'''num_calls_abandoned''' |BIGINT |Total number of service calls that were abandoned at any time after entering the queue. |- |'''num_calls_abandoned_after_threshold''' |BIGINT |Number of service calls that were abandoned outside of the Service Level threshold (i.e., after the time set by parameter ''Within threshold'' in service configuration). Includes the calls that were abandoned while ringing. |- |'''num_calls_abandoned_in_ivr''' |BIGINT |Number of service calls that were abandoned before entering the queue (i.e., while still at the IVR stage). |- |'''num_calls_self_service''' |BIGINT |Number of service calls that were serviced by an IVR application (as indicated by execution of scenario block ''Self-Service Provided''). |- |'''num_calls_in_service_level''' |BIGINT |Number of service calls that were answered within the Service Level threshold (i.e., within the time set by parameter ''Within threshold'' in service configuration). For email, number of inbound emails that were replied to within the Service Level threshold. Note that for service level purposes, the reply time excludes the hours when the service was closed according to the configured HOP. |- |'''num_overflow_calls''' |BIGINT |Number of service calls that were distributed to overflow destinations. |- |'''num_calls_held''' |BIGINT |Number of service calls that were held at any time during their handling. |- |'''num_calls_recv_as_transfers_''' '''answered''' |BIGINT |Number of service calls received as transfers that were answered. |- |'''num_calls_recv_as_transfers_''' '''in_service_level''' |BIGINT |Number of service calls received as transfers that were answered within the Service Level threshold (i.e., within the time set by parameter ''Within threshold'' in service configuration). |- |'''num_calls_recv_as_transfers_''' '''abandoned''' |BIGINT |Number of service calls received as transfers that were abandoned. |- |'''num_calls_recv_as_transfers_''' '''abandoned_in_ivr''' |BIGINT |Number of service calls received as transfers that were abandoned before entering the queue (i.e., while still at the IVR stage). |- |'''num_calls_recv_as_transfers_''' '''abandoned_after_threshold''' |BIGINT |Number of service calls received as transfers that were abandoned outside of the Service Level threshold (i.e., after the time set by parameter ''Within threshold'' in service configuration). Includes the calls that were abandoned while ringing. |- |'''num_calls_recv_as_transfers_''' '''queued''' |BIGINT |Number of service calls received as transfers that were queued. |- |'''num_calls_recv_as_transfers_held''' |BIGINT |Number of service calls received as transfers that were held at any time during their handling. |- |'''num_calls_queued_answered''' |BIGINT |Number of queued service calls that were answered. Unlike ''num_calls_answered'', this metric counts only calls that passed through the given service queue. |- |'''answer_time_queued''' |BIGINT |Total answer time for all calls counted in ''num_calls_queued_answered''. |- |'''num_calls_queued_abandoned''' |BIGINT |Number of queued service calls that were abandoned. Unlike ''num_calls_abandoned'', this metric counts only calls that passed through the given service queue. |- |'''num_calls_queued_abandoned_''' '''after_threshold''' |BIGINT |Number of queued service calls that were abandoned outside of the Service Level threshold (i.e., after the time set by parameter ''Within threshold'' in service configuration). Includes the calls that were abandoned while ringing. Unlike ''num_calls_abandoned_after_threshold'', this metric counts only calls that passed through the given service queue. |- |'''num_calls_queued_in_service_level''' |BIGINT |Number of queued service calls that were answered within the Service Level threshold (i.e., within the time set by parameter ''Within threshold'' in service configuration). Unlike ''num_calls_in_service_level'', this metric counts only calls that passed through the given service queue. |- |'''num_calls_queued_held''' |BIGINT |Number of queued service calls that were held at any time during their handling. Unlike ''num_calls_held'', this metric counts only calls that passed through the given service queue. |- |'''abandonment_time_queued''' |BIGINT |Total abandonment time for all calls counted in ''num_calls_queued_abandoned.'' |- |'''abandonment_time_after_''' '''threshold_queued''' |BIGINT |Total abandonment time for all calls counted in ''num_calls_queued_abandoned_after_threshold''. |- |'''abandonment_time''' |BIGINT |Total abandonment time for all calls counted in ''num_calls_abandoned.'' |- |'''abandonment_time_after_threshold''' |BIGINT |Total abandonment time for all calls counted in ''num_calls_abandoned_after_threshold''. |- |'''total_duration_in''' |BIGINT |The sum of total durations of inbound service calls. Includes time in IVR, queue, as well as ringing, talk, and hold times. Does not include after-call work time. |- |'''busy_time_in''' |BIGINT |Total talk and hold time for inbound service calls. |- |'''busy_time_out''' |BIGINT |Total talk and hold time for outbound calls excluding campaign calls. |- |'''acw_time''' |BIGINT |Total after-call work time for inbound and outbound service calls excluding campaign calls. |- |'''acw_time_in''' |BIGINT |Total after-call work time for inbound service calls. |- |'''acw_time_out''' |BIGINT |Total after-call work time for outbound service calls excluding campaign calls. |- |'''hold_time_in''' |BIGINT |Total hold time for inbound service calls. |- |'''hold_time_out''' |BIGINT |Total hold time for outbound service calls excluding campaign calls. |- |'''ringing_time_in''' |BIGINT |Total ringing time for inbound service calls. |- |'''ringing_time_out''' |BIGINT |Total dialing time for outbound service calls excluding campaign calls (time from the moment an outbound call is initiated till it is answered by the remote party). |- |'''num_calls_outbound''' |BIGINT |Number of outbound calls that were made with respect to this service. Does not include campaign calls. For email, number of outbound emails that were sent. Outbound emails include unsolicited emails and possible additional responses to inbound emails that were previously replied to. |- |'''num_calls_answered_outbound''' |BIGINT |Number of outbound calls made with respect to this service that were answered. Does not include campaign calls. |- |'''num_calls_held_outbound''' |BIGINT |Number of answered outbound calls made with respect to this service that were held by agents at any time during their handling. Does not include campaign calls. |- |'''ready_time''' |BIGINT |Total time in state Ready for all team agents within the aggregation interval. |- |'''not_ready_time''' |BIGINT |Total time in state Not Ready for all team agents within the aggregation interval. |- |'''login_time''' |BIGINT |Total login time for all team agents within the aggregation interval. |- |'''handling_time''' |BIGINT |Total handling time for calls of the given service/campaign handled by all team agents within the aggregation interval. Includes talk, hold, and after-call work time. |- |'''handling_call_time''' |BIGINT |Total on-call time for calls of the given service/campaign handled by all team agents within the aggregation interval. Includes ringing/dialing, talk, and hold time. |- |'''handling_acw_time''' |BIGINT |Total after-call work time for calls of the given service/campaign handled by all team agents within the aggregation interval. |- |'''assigned_handling_call_time''' |BIGINT |Total handling time for all calls handled by all team agents within the aggregation interval. Includes talk, hold, and after-call work time. |- |'''assigned_handling_acw_time''' |BIGINT |Total after-call work time for all calls handled by all team agents within the aggregation interval. |- |'''min_agents''' |BIGINT |Minimum number of agents possessing this service skill who were simultaneously logged on within the aggregation interval. |- |'''max_agents''' |BIGINT |Maximum number of agents possessing this service skill who were simultaneously logged on within the aggregation interval. |- |'''campaign_calls_attempted''' |BIGINT |Number of campaign calls that were attempted. |- |'''campaign_dialer_calls_queued''' |BIGINT |Reserved. |- |'''campaign_dialer_calls_handled''' |BIGINT |Reserved. |- |'''campaign_calls_ivr''' |BIGINT |Number of campaign calls that entered an IVR application. |- |'''campaign_calls_queued''' |BIGINT |Number of campaign calls that were queued. |- |'''campaign_calls_abandoned''' |BIGINT |Number of campaign calls that were abandoned (terminated by the remote party while waiting in queue to be connected to an agent). |- |'''campaign_calls_handled''' |BIGINT |Number of campaign calls that were handled by agents. |- |'''campaign_calls_held''' |BIGINT |Number of campaign calls that were held any number of times during their handling. |- |'''campaign_calls_rpc''' |BIGINT |Reserved. |- |'''campaign_records_completed''' |BIGINT |Number of campaign records completed within the reporting interval including the records for which no attempts were made because of DNC match or filter exclusion. |- |'''campaign_records_valid''' |BIGINT |Number of campaign records completed within the reporting interval except the ones counted in ''campaign_records_excluded''. |- |'''campaign_records_dialed''' |BIGINT |Number of campaign records completed within the reporting interval for which at least one number was dialed. |- |'''campaign_records_queued''' |BIGINT |Number of campaign records completed within the reporting interval for which at least one call was queued. |- |'''campaign_records_handled''' |BIGINT |Number of campaign records completed within the reporting interval for which at least one call was handled by an agent. |- |'''campaign_records_excluded''' |BIGINT |Number of campaign records completed within the reporting interval for which no attempts were made because of DNC match or filter exclusion. |- |'''campaign_records_rpc''' |BIGINT |Reserved. |- |'''campaign_ivr_time''' |BIGINT |Total IVR time for campaign calls (the sum of times spent in an IVR application). |- |'''campaign_queue_time''' |BIGINT |Total queue time for campaign calls (the sum of times between entering the queue and exiting the queue in any manner for all campaign calls that were queued). |- |'''campaign_abandonment_time''' |BIGINT |Total abandonment time (the sum of times between entering the queue and being abandoned by remote party for all campaign calls that were abandoned or dropped). |- |'''campaign_answer_time''' |BIGINT |Total answer time (the sum of times between entering the queue and being picked up by agents for all campaign calls that were connected to agents). |- |'''campaign_talk_time''' |BIGINT |Total talk time for campaign calls (the sum of times between the moments of being established and being released). |- |'''campaign_hold_time''' |BIGINT |Total hold time for campaign calls (the sum of all hold times for all handled campaign calls). |- |'''campaign_acw_time''' |BIGINT |Total after-call work time for campaign calls (the sum of times between the moment of release and the end of after-call work). |- |'''campaign_handling_call_time''' |BIGINT |Reserved. |- |'''campaign_handling_acw_time''' |BIGINT |Reserved. |- |'''campaign_assigned_handling_''' '''call_time''' |BIGINT |Reserved. |- |'''campaign_assigned_handling_''' '''acw_time''' |BIGINT |Reserved. |- |'''num_surveys''' |BIGINT |Number of surveys available for calls handled for this service/campaign. |- |'''num_surveys_with_cs''' |BIGINT |Number of surveys where the contact satisfaction question was responded to. |- |'''num_surveys_with_nps''' |BIGINT |Number of surveys where the net promoter score question was responded to. |- |'''num_surveys_with_fcr''' |BIGINT |Number of surveys where the first-call resolution question was responded to. |- |'''cs''' |BIGINT |The sum of contact satisfaction marks for all surveys where the contact satisfaction question was responded to. |- |'''nps''' |BIGINT |The sum of net promoter score points for all surveys where the contact satisfaction question was responded to. |- |'''num_fcr''' |BIGINT |Number of surveys that indicated first-call resolution relative to total number of surveys where the first-call resolution question was responded to. |- |'''campaign_calls_unattended''' |BIGINT |Number of campaign calls that were answered at destination and subsequently either diverted to an IVR at any time, or dropped at any time, or connected to an agent with time exceeding two seconds. |- |'''campaign_preview_items''' |BIGINT |Number of preview records associated with the given campaign that were delivered to agents. |- |'''campaign_preview_time''' |BIGINT |Total time spent by agents working on preview records associated with the given campaign. Does not include call time. |- |'''num_emails_replied_by_agent''' |BIGINT |Number of emails that were replied by agents. Note that only the first meaningful response is counted as a reply. Possible follow-up message in response to the same incoming email are counted as outbound emails. |- |'''num_emails_closed_''' '''without_reply''' |BIGINT |Number of emails that were closed by agents without reply. |- |'''email_routing_time''' |BIGINT |Total time emails spent in the service queue before being pushed to agents or pulled by agents. The time is counted only for emails that were both received and routed within the given interval. |- |'''email_reply_time''' |BIGINT |Total reply time for all emails that were replied to. The reply time of a single email is counted between the moment the email is placed in the service queue and the moment when the first meaningful response is sent. |- |'''num_emails_received_new''' |BIGINT |Number of new emails that arrived at this service during the given reporting interval. Excludes emails related to existing threads. Note that the total number of received emails, which includes both new emails and emails related to existing email threads, is reported in column ''num_calls_received''. |- |'''num_emails_carried_over''' |BIGINT |Number of emails that arrived at this service at any time before the given reporting interval and remained unprocessed at the beginning of the interval. Includes both new emails and emails related to existing threads. Note that unlike other metrics, this number is calculated only once in 24 hours. |- |'''num_emails_carried_over_new''' |BIGINT |Number of new emails that arrived at this service at any time before the given reporting interval and remained unprocessed at the beginning of the interval. Excludes emails related to existing threads. Note that unlike other metrics, this number is calculated only once in 24 hours. |- |'''num_emails_remaining_in_''' '''personal_queues''' |BIGINT |Number of emails associated with this service that remained in agents’ personal queues at the end of the interval. Does not include emails that were being actively processed at the end of the interval. |- |'''num_emails_in_progress''' |BIGINT |Number of emails associated with this service that were being actively processed at the end of the interval (i.e., displayed in active communications lists of agents’ desktops). |- |'''num_emails_in_service_changed''' |BIGINT |Number of inbound emails that were re-categorized by agents (i.e., the agent changed this service to another email service and continued processing the interaction). |- |'''num_emails_in_service_changed_received''' |BIGINT |Number of inbound emails that were received via manual re-categorization (i.e., the agent changed a previously assigned service to this service and continued processing the email). |- |'''num_emails_remaining_in_''' '''personal_queues_breached_sla''' |BIGINT |Number of emails remaining in agents’ personal queues at the end of the interval that breached the service level (i.e., the emails whose time in the system exceeded the service level threshold configured for the given service). |} [[#topic_reporting-database-specification/team_performance|< Previous]] | [[#topic_reporting-database-specification/service_performance|Next >]] = service_performance= This table contains a subset of metrics from the ''service_in_time_counters'' table. This table is maintained only in order to support existing service-related custom reports designed prior to release 3.5.2. All metrics found in this table are also available in the ''service_in_time_counters'' table, which is recommended for development of any new service-related reports. [[#topic_reporting-database-specification/service_in_time_counters|< Previous]] | [[#topic_reporting-database-specification/overflow_counters|Next >]] = overflow_counters= This table contains metrics for distribution of inbound interactions among various routing targets including overflow destinations. The metrics are provided for all services defined in your contact center configuration. Note that if a service can be accessed via multiple access numbers, the metrics are provided for each access number separately. Unless noted otherwise with respect to a particular metric, any ''call'' mentioned in this table shall be interpreted as a call that requested the service specified in the ''service_name'' field. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''service_name''' |VARCHAR |Name of the service as defined in service configuration. |- |'''destination_phone''' |VARCHAR |Access number for this service as defined in the Dial-in Scenario Entry associated with service. If a service is associated with multiple access numbers, metrics specified in this table will be provided for each access number separately. |- |'''routed_to''' |VARCHAR |Target destination that the calls were routed to. Name of the team for internally routed calls. External number for externally routed calls. If calls were routed to multiple destinations, metrics specified in this table will be provided for each destination separately. |- |'''is_overflow''' |BIT |TRUE if the given destination is an overflow destination. |- |'''no_team''' |BIT |TRUE if the given destination is an external number. FALSE if the given destination is a team. |- |'''media_type''' |ENUM |Interaction media type. Possible values: VOICE, CHAT, EMAIL. If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context. |- |'''num_calls_received''' |BIGINT |Total number of calls that requested this service and were routed to the given destination. |- |'''num_calls_answered''' |BIGINT |Number of calls that were answered at the given destination. |- |'''num_calls_abandoned_''' '''after_threshold''' |BIGINT |Number of calls that abandoned while ringing after being routed to the given destination. Includes only calls that were abandoned outside of the configured service level threshold. |- |'''handling_time''' |BIGINT |Total time that the answered calls were handled at the given destination (the sum of talk, hold, and after-call work times). |} [[#topic_reporting-database-specification/service_performance|< Previous]] | [[#topic_reporting-database-specification/disposition_counters|Next >]] = disposition_counters= Each row in this table provides metrics for interactions associated with the service specified in the ''service_name'' field whose processing ended with a particular disposition. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''service_name''' |VARCHAR |Name of the service as defined in service configuration. |- |'''disposition_name''' |VARCHAR |Disposition name. |- |'''is_campaign''' |BIT |Services of blended type will have two rows of metrics: one where this bit is set to TRUE, counting campaign calls only; the other row with this bit set to FALSE, counting inbound and non-campaign outbound calls. |- |'''media_type''' |ENUM |Interaction media type. Possible values: VOICE, CHAT, EMAIL. If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context. |- |'''num_records_completed''' |BIGINT |Number of outbound calling records associated with the given service that were completed with the disposition specified in the ''disposition_name'' field. |- |'''num_calls_received''' |BIGINT |Number of calls associated with the given service received by agents whose processing ended with the disposition specified in the ''disposition_name'' field. Include predictive campaign calls. |- |'''num_calls_outbound''' |BIGINT |Number of outbound calls associated with the given service whose processing ended with the disposition specified in the ''disposition_name'' field. Includes regular outbound calls and calls of preview campaign calls that were actually dialed by agents. |- |'''num_preview_items''' |BIGINT |Number of preview records completed by agents without making a call with the disposition specified in the ''disposition_name'' field. |- |'''num_campaign_calls''' |BIGINT |Number of campaign calls associated with the given service whose processing ended with the disposition specified in the ''disposition_name'' field. |- |'''num_non_campaign_''' '''calls_inbound''' |BIGINT |Number of inbound calls associated with the given service whose processing ended with the disposition specified in the ''disposition_name'' field. Exclude predictive campaign calls |- |'''num_non_campaign_''' '''calls_outbound''' |BIGINT |Number of non-campaign outbound calls associated with the given service whose processing ended with the disposition specified in the ''disposition_name'' field. |} [[#topic_reporting-database-specification/overflow_counters|< Previous]] | [[#topic_reporting-database-specification/callback_counters|Next >]] = callback_counters= This table provides metrics about calls that selected the virtual queue option and about the related callback attempts. Unless noted otherwise with respect to a particular metric, any ''callback'' mentioned in this table shall be interpreted as a callback attempt made with respect to the service specified in the ''service_name'' field. Note that callback attempts are counted for the aggregation intervals in which the inbound calls that produced the associated callback requests entered the system. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''service_name''' |VARCHAR |Name of the service as defined in configuration. |- |'''num_calls_queued''' |BIGINT |Total number of calls that requested the given service and were queued. |- |'''num_callbacks_requested''' |BIGINT |Number of queued calls that requested callbacks (i.e., selected the virtual queue option). |- |'''num_callbacks_attempted''' |BIGINT |Number of callbacks that were attempted. |- |'''num_callbacks_busy''' |BIGINT |Number of callbacks that failed because the called party was busy. |- |'''num_callbacks_no_answer''' |BIGINT |Number of callbacks that failed because the called party did not answer. |- |'''num_callbacks_answered''' |BIGINT |Number of callbacks that were answered by the called party. |- |'''num_callbacks_requeued''' |BIGINT |Number of callbacks that were re-queued after the initial unsuccessful attempt. |- |'''num_callbacks_abandoned''' |BIGINT |Number of callbacks that were answered by customers and then abandoned by them while waiting for an agent (in queue or ringing). |- |'''num_callbacks_handled''' |BIGINT |Number of callbacks that were handled by agents. |- |'''callback_wait_time''' |BIGINT |Total callback wait time. The sum of times between the callback requests and the related initial callback attempts. |- |'''callback_customer_answer_time''' |BIGINT |Total callback answer time. The sum of times between the moments when callback attempts were initiated and the customers answered them. |- |'''callback_agent_answer_time''' |BIGINT |Total callback connection time. The sum of times between the moments when customers answered callback attempts and were connected to the agents. |} [[#topic_reporting-database-specification/disposition_counters|< Previous]] | [[#topic_reporting-database-specification/requested_skills|Next >]] = requested_skills= This table contains metrics for interactions that requested a specific skill. Unless noted otherwise with respect to a particular metric, any ''call'' mentioned in this table shall be interpreted as a call that requested the skill specified in the ''skill_name'' field. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''media_type''' |ENUM |Interaction media type. Possible values: VOICE, CHAT, EMAIL. If set to CHAT, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context. |- |'''skill_name''' |VARCHAR |Skill name as defined in configuration. |- |'''skill_group_name''' |VARCHAR |Name of the skill group that the skill belongs to as defined in configuration. |- |'''skill_type''' |ENUM |Skill type. Possible values: * SERVICE – a primary service skill (a skill that is created automatically for each new service). * SKILL – an auxiliary skill (a skill that is not directly associated with any particular service) |- |'''total_answer_time''' |BIGINT |Total answer time (the sum of times between the moments the calls entered queue and were answered by agents). |- |'''num_calls_received''' |BIGINT |Total number of received calls. |- |'''num_calls_queued''' |BIGINT |Number of calls that were queued. |- |'''num_calls_answered''' |BIGINT |Number of calls that were answered. |- |'''num_calls_overflow''' |BIGINT |Number of calls that were distributed to overflow destinations. |} [[#topic_reporting-database-specification/callback_counters|< Previous]] | [[#topic_reporting-database-specification/scenario_step_counters|Next >]] = scenario_step_counters= Each row of this table counts the number of times a specific block of the scenario specified in the ''scenario_name'' field was executed with a specific result during the aggregation interval. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''start_time''' |DATETIME |Start time of the aggregation interval. |- |'''end_time''' |DATETIME |End time of the aggregation interval. |- |'''scenario_name''' |VARCHAR |Name of the service as defined in configuration. |- |'''block_type''' |VARCHAR |Type of the scenario block. |- |'''block_title''' |VARCHAR |Block title as defined in scenario. |- |'''exit_id''' |VARCHAR |Block exit identifier. |- |'''caller_disconnect''' |BIT |TRUE if the interaction was disconnected by the remote party while this block was executed. |- |'''num_steps''' |BIGINT |Number of times the block specified in the block_title field was executed with the given result (either the exit specified in the ''exit_id'' field or abandoned by the caller as indicated by the ''caller_disconnect'' field). |} [[#topic_reporting-database-specification/requested_skills|< Previous]] | [[#topic_reporting-database-specification/concurrent_users|Next >]] = concurrent_users= This table tracks the number of users that were logged on to the system via the Agent Desktop application at any moment in the past. A new row is added every time when a number of logged in users changes from a non-zero value (i.e., when a non-first user logs in or any user logs out), but not more frequently than once a minute. (After a first login/logout within a given minute, all subsequent logins/logouts that happen within that same minute will be aggregated and written as a single record one minute after.) A new record is also created unconditionally at the configured [[#topic_contact-center-administrator-guide/ReportingSettings|Reset time for daily statistics]]. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''num_users''' |INT |Number of users that were logged into the system in the interval specified by ''start_time'' and ''end_time''. |- |'''users''' |JSON array |List of usernames of users that were logged into the system in the interval specified by ''start_time'' and ''end_time''. Specified as JSON array of ''[username1, username2, … usernameN]'' where ''N = num_users'' |- |'''start_time''' |TIMESTAMP |Start time of the interval for which the ''num_users'' and ''users'' data in this record remains valid. The time is rounded to a minute. Note that it is normal for this time to differ by a few seconds from the time of state changes reported in the ''agent_acitivy'' table. |- |'''end_time''' |TIMESTAMP |End time of the interval for which the ''num_users'' and ''users'' data in this record remains valid. The time is rounded to a minute. Note that it is normal for this time to differ by a few seconds from the time of state changes reported in the ''agent_acitivy'' table. |} [[#topic_reporting-database-specification/scenario_step_counters|< Previous]] | [[#topic_reporting-database-specification/GeneralInformationAboutDetailedRecords|Next >]] = General Information About Detailed Records= The tables specified in this section contain detailed information about all agent activities and all interactions that either entered, or were initiated from, the ServicePattern system. The data in these tables can be used, for example, for [[#topic_reporting-reference-guide/GeneralInformationAboutQualityManagement|quality management tasks]], which require searching for specific interactions based on combinations of specific criteria. These data can also be used for development of custom metrics that are not available from the aggregation tables specified in the previous sections. Detailed records are created from the raw event data that is initially written to the collector database (DB1) in real-time by various ServicePattern components. This raw data is periodically extracted by the Aggregator component, transformed into records, and loaded into the tables of the Reporting Database (DB2). The standard aggregation period is 15 minutes; therefore, under normal circumstances, the data in the detailed records for completed interactions and agent activities appear in these tables with a maximum delay of 15 minutes. ::'''Note:''' All durations in detailed records are calculated by subtracting the number of full calendar seconds in the start time form the same number in the end time (i.e., milliseconds are disregarded). Consider these examples: ::* A call that was answered at 11:00:00:005 and released at 11:00:00:998 will have talk time of 0 seconds in the call detail record. ::* A call that was answered at 11:00:00:995 and released at 11:00:01:005 will have talk time of 1 second in the call detail record. [[#topic_reporting-database-specification/concurrent_users|< Previous]] | [[#topic_reporting-database-specification/agent_activity|Next >]] = agent_activity= Each row of this table contains a set of data related to a single agent activity. For interaction-handling activities, this data includes some basic aggregates, such as agent’s talk time and hold time. Note that a single agent activity spanning multiple aggregation intervals will be reported separately for each aggregation interval that it spans, and all time-related metrics will count the time within the given interval only. For reporting purposes, time-related metrics of the activities that span multiple intervals can be combined using the ''activity_id'' field. Unless noted otherwise with respect to a particular metric, any ''call'' mentioned in this table shall be interpreted as a call that was handled by the agent identified by the ''login_id'' field during the given activity. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Reserved |- |'''pkid''' |INT |Primary key. |- |'''activity_id''' |BINARY (16) |Unique identifier assigned to the activity. If a single activity lasts through several adjacent aggregation intervals, it will have the same value of ''activity_id'' in each of those intervals. |- |'''login_id''' |VARCHAR |Agent login as defined in configuration. |- |'''first_name''' |VARCHAR |Agent first name as defined in configuration. |- |'''last_name''' |VARCHAR |Agent last name as defined in configuration. |- |'''team_name''' |VARCHAR |Name of the team that the agent is assigned to as defined in configuration. |- |'''rank''' |VARCHAR |Agent rank as defined in configuration. |- |'''agg_run_id''' |BINARY (16) |Aggregator run that produced this record. |- |'''start_time''' |DATETIME |Activity start time. |- |'''activity''' |ENUM |Activity type. Possible values: LOGIN, INBOUND_CALL, OUTBOUND_CALL, READY, NOT_READY, LOGOUT, PREVIEW. Note the following: * Handling of both preview and predictive campaign calls is reported as activity OUTBOUND_CALL. Only the record preview phase (time before dialing begins) is reported as PREVIEW activity for agents participating in preview campaigns. * Time spent doing after-call work is reported as part of INBOUND_CALL and OUTBOUND_CALL activities. * Internal calls received by the agent are reported as activity INBOUND_CALL. * Internal calls made by the agent are reported as activity OUTBOUND_CALL. * Value of the ''other_party_phone_type'' field can be used to distinguish between service and internal calls. * Handling of a service chat is reported as activity INBOUND_CALL. * Handling of an inbound email is reported as activity INBOUND_CALL. * Handling of a follow-up or unsolicited email is reported as activity OUTBOUND_CALL. * Value of the ''media_type'' and/or ''service_name'' field can be used to distinguish between interactions of different media types. |- |'''duration''' |BIGINT |Activity duration within the aggregation interval. |- |'''detail''' |VARCHAR |For activity type NOT_READY, specifies the Not Ready reason. For activity type OUTBOUND_CALL, specifies login ID of the called party if the call was made internally and was answered. |- |'''pending_time''' |BIGINT |For activity type INBOUND_CALL, duration of call ringing phase from the moment the call was distributed to this agent and until it was either answered or abandoned. For activity type OUTBOUND_CALL, duration of call dialing phase from the moment the number dialed by the agent was received by the system and until the call was either answered or abandoned |- |'''talk_time''' |BIGINT |Total talk time for activity types INBOUND_CALL and OUTBOUND_CALL within the aggregation interval. Does not include hold times. |- |'''hold_time''' |BIGINT |Total hold time for activity types INBOUND_CALL and OUTBOUND_CALL within the aggregation interval. |- |'''acw_time''' |BIGINT |After-call work time for activity types INBOUND_CALL and OUTBOUND_CALL within the aggregation interval. |- |'''service_name''' |VARCHAR |For activity types INBOUND_CALL and OUTBOUND_CALL, the name of the service associated with the call. Not specified for internal calls. For services of media type chat, any ''call'' mentioned in this table shall be interpreted as a service chat interaction in the same context. |- |'''origination_number''' |VARCHAR |For activity types INBOUND_CALL and OUTBOUND_CALL, the phone number from which the call was made. |- |'''destination_number''' |VARCHAR |For activity types INBOUND_CALL and OUTBOUND_CALL, the phone number dialed by the party that initiated the call. |- |'''external_number''' |VARCHAR |For activity type INBOUND_CALL, the access number through which the inbound call entered the system. For activity type OUTBOUND_CALL, the Caller ID assigned to the outbound call. |- |'''other_party_phone_type''' |ENUM |For activity types INBOUND_CALL and OUTBOUND_CALL, location of the remote party on the call. Possible values: INTERNAL, EXTERNAL. |- |'''disposition''' |ENUM |For activity types INBOUND_CALL and OUTBOUND_CALL, specifies how the call-handling activity ended. The term ''call'' in the descriptions below indicates that the given value may be applicable to calls and chats. Possible values: * CALLER_TERMINATED – call terminated by the party that made the call * CALLEE_TERMINATED – call terminated by the party that answered the call * REJECTED – the agent rejected the incoming call * ABANDONED – for inbound calls, abandoned by the remote party while ringing at the agent desktop; for outbound calls, terminated by the agent before it was answered by the remote party * TRANSFERRED – agent transferred the interaction * CONFERENCED – call became a conference (the conference phase is reported a separate activity) * SYSTEM_DISCONNECTED – call was disconnected by the system * CALLER_TRANSFERRED – the remote party transferred the call (i.e., this agent stayed on the call but with another party); the after-transfer part of the call will be reported as the next activity * NO_ANSWER – the agent did not accept an incoming interaction * REPLIED – agent replied to the email; applies to inbound emails only * CLOSED – the agent finished processing of the email without a reply (e.g., the email was a spam or no follow-up was necessary); applies to inbound emails only * SERVICE_CHANGED – the agent changed the service associated with the email and continued processing it (the after-service-change phase is reported as the next activity) * SAVED – the agent saved an email draft * SENT – agent sent the email; applies to outbound emails only * DISCARDED – agent discarded an email draft; applies to outbound emails only |- |'''agent_disposition_name''' |VARCHAR |For activity types INBOUND_CALL and OUTBOUND_CALL, the name of the disposition that the agent assigned to the call. |- |'''agent_disposition_code''' |INT |For activity types INBOUND_CALL and OUTBOUND_CALL, the numerical code (if defined) of the disposition that the agent assigned to the call. |- |'''agent_disposition_notes''' |LONGTEXT |For activity types INBOUND_CALL and OUTBOUND_CALL, the text notes that the agent wrote regarding the call. |- |'''session_id''' |BINARY |Agent login session identifier. A new identifier is assigned to each new LOGIN activity. The same identifier is assigned to the corresponding LOGOUT and all READY and NOT_READY activities that happened in between. |- |'''media_type''' |ENUM |For activity types INBOUND_CALL and OUTBOUND_CALL, media type of the interaction that the agent handled during this activity. Possible values: VOICE, CHAT, EMAIL |- |'''case_number''' |VARCHAR |For activity types INBOUND_CALL and OUTBOUND_CALL, number of the case that this interaction is associated with. Currently applies to emails only. |- |'''email_completion_time''' |BIGINT |Email completion time from the moment the email interaction was accepted by the agent or entered his personal queue and until it was completed (including ACW if any) or transferred. |- |'''workitem_id''' |VARCHAR |Identifier of the outbound work item associated with this activity. |- |'''call_detail_id''' |BINARY |Reference to the record in the [[#topic_reporting-database-specification/call_detail|call_detail]] table created for the interaction handled by the agent during this activity. |- |'''has_screen_recording''' |BIT |Indication whether agent's [[#topic_reporting-reference-guide/ScreenRecordings|screen was recorded]] during this activity. |} [[#topic_reporting-database-specification/GeneralInformationAboutDetailedRecords|< Previous]] | [[#topic_reporting-database-specification/call_detail|Next >]] = call_detail= Each row of this 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. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Units''' |'''Description''' |- |'''id''' |BINARY (16) |None |Reserved |- |'''pkid''' |INT |None |Primary key |- |'''agg_run_id''' |BINARY (16) |None |Aggregator run that produced this record |- |'''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. |- |'''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 |- |'''ivr_time''' |BIGINT |Seconds |Total time the call spent in IVR |- |'''queue_time''' |BIGINT |Seconds |Total time the interaction spent in the service queue |- |'''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. |- |'''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). |- |'''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). |- |'''acw_time''' |BIGINT |Seconds |The amount of time the agents spent doing after-call work related to this interaction |- |'''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. |- |'''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. |- |'''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.) |- |'''trunk_description''' |VARCHAR |None |For inbound and outbound calls, the name of the trunk that was used to establish this call |- |'''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. |- |'''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. |- |'''caller_phone_type''' |ENUM |None |''caller_phone_type'' specifies the location of the party that initiated the interaction. Possible values are INTERNAL or EXTERNAL. |- |'''callee_phone_type''' |ENUM |None |''callee_phone_type'' specifies the location of the party that received 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 |- |'''callee_rank''' |VARCHAR |None |For inbound and internal calls, the rank of the user who received this interaction |- |'''from_phone''' |VARCHAR |None |The phone number from which the call was made, or for emails, the email address in the "From" field |- |'''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. |- |'''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. |- |'''transferred_from_phone''' |VARCHAR |None |For transferred calls and chats, the phone number from which the call/chat was last transferred |- |'''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) |- |'''agent_disposition_name''' |VARCHAR |None |Name of the disposition that was assigned to this interaction |- |'''agent_disposition_code''' |INT |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 |[[#topic_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 |For transferred calls, the start time of the original interaction in the transfer sequence |- |'''initial_service_name''' |VARCHAR |None |For transferred interactions, the name of the service associated with the original interaction in the transfer sequence |- |'''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_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 |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_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 |- |'''flagged''' |BIT |None |TRUE if the interaction was flagged by agent; FALSE otherwise |- |'''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 |- |'''account_number''' |VARCHAR |None |''account_number'' gets the customer’s account number. If the calling list record that initiated this call has a [[#topic_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 [[#topic_reporting-reference-guide/InteractionRecordsSearch|interaction records search]]. |- |'''caller_first_name''' |VARCHAR |None |First name of the party that originated the interaction |- |'''callee_first_name''' |VARCHAR |None |First name of the party that accepted the interaction |- |'''caller_last_name''' |VARCHAR |None |Last name of the party that originated the interaction |- |'''callee_last_name''' |VARCHAR |None |Last name of the party that accepted the interaction |- |'''caller_team_name''' |VARCHAR |None |Name of the team that the agent who originated the interaction is a member of |- |'''callee_team_name''' |VARCHAR |None |Name of the team that the agent who accepted the interaction is a member of |- |'''caller_monitored''' |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 |None |Identifier for the interaction segment of the party that originated this interaction |- |'''callee_interaction_step_id''' |BINARY |None |Identifier for the interaction segment of the party that accepted 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_recording_url''' |VARCHAR |None |URL of the recording for 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_cpa_recording_url''' |VARCHAR |None |URL of the recording for the CPA portion of the interaction segment of the party that originated 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_recording_url''' |VARCHAR |None |URL of the recording for 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_cpa_recording_url''' |VARCHAR |None |URL of the recording for the CPA portion of the interaction segment of the party that accepted this interaction |- |'''caller_encryption_key_id''' |BINARY |None |For internal use only. |- |'''callee_encryption_key_id''' |BINARY |None |For internal use only. |- |'''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) |- |'''email_id''' |VARCHAR |None |Indentifier of the email interaction |- |'''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_subject''' |VARCHAR |None |Content of the email subject field |- |'''email_kb_article_id''' |VARCHAR |None |Identifier of the article used for replying to this email |- |'''response_email_id''' |VARCHAR |None |Identifier of the article that was sent automatically to acknowledge receipt of this email |- |'''case_id''' |VARCHAR |None |Identifier of the [[#topic_agent-guide/UnderstandingEmailCasesandThreads|case]] with which this email is associated |- |'''thread_id''' |VARCHAR |None |''thread_id'' is the identifier of the [[#topic_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''' |VARCHAR |None |''case_number'' specifies the number of the [[#topic_agent-guide/UnderstandingEmailCasesandThreads|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'' |- |'''caller_has_screen_recording''' |BIT |None |Indication that screen of the party that originated this interaction was recorded during the interaction |- |'''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 |} [[#topic_reporting-database-specification/agent_activity|< Previous]] | [[#topic_reporting-database-specification/interaction_step_skills|Next >]] = interaction_step_skills= Each row of this table specifies a skill requested during an interaction referred to in the ''interaction_step_id'' field. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''interaction_step_id''' |BINARY (16) |Identifier of the interaction during which this skill was requested. Typically this is the identifier for the interaction segment of the party that originated this interaction (field ''caller_interaction_step_id'' of the ''call_detail'' table). |- |'''name''' |VARCHAR |Skill name as defined in configuration. |- |'''group_name''' |VARCHAR |Name of the group that this skill is assigned to as defined in configuration. |- |'''type''' |ENUM |Skill type. Possible values: SERVICE – a primary service skill (a skill that is created automatically for each new service). SKILL – an auxiliary skill (a skill that is not directly associated with any particular service) |- |'''service_level''' |INT |Target percentage of calls that shall be answered within the time specified in the ''service_level_threshold'' feild as defined in configuration. |- |'''service_level_threshold''' |INT |Service level threshold as defined in configuration. |- |'''short_abandonment_threshold''' |INT |Reserved. |} [[reporting-database-specification/call_detail< Previous]] | [[#topic_reporting-database-specification/interaction_quality_monitoring|Next >]] = interaction_quality_monitoring= Each row in this table represents an instance of review of an interaction segment referred to in the ''interaction_step_id'' field that was completed in the given aggregation interval. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''interaction_step_id''' |BINARY (16) |Reference to identifier of the reviewed interaction segment in the ''call_details'' table; depending on the type of call (inbound or outbound), it could be either the ''callee_interaction_step_id'' or ''callee_interaction_step_id'' field. |- |'''review_time''' |DATETIME |Start time of the review activity. |- |'''review_agent_login_id''' |VARCHAR |Reviewer’s login as defined in configuration. |- |'''review_agent_first_name''' |VARCHAR |Reviewer’s first name as defined in configuration. |- |'''review_agent_last_name''' |VARCHAR |Reviewer’s last name as defined in configuration. |- |'''review_notes''' |TEXT |Reviewer’s notes for the reviewed interaction segment. |} [[#topic_reporting-database-specification/interaction_step_skills|< Previous]] | [[#topic_reporting-database-specification/interaction_quality_monitoring_grades|Next >]] = interaction_quality_monitoring_grades= Each row in this table represents a grade in a single category given to an interaction segment during an instance of review referred to in the ''iqm_id'' field. {|border="1" style="border-collapse:collapse" cellpadding="5" |'''Column Name''' |'''Data Type''' |'''Description''' |- |'''id''' |BINARY (16) |Primary key. |- |'''iqm_id''' |BINARY (16) |Reference to identifier of the review instance (field ''id'' in the ''interaction_quality_monitoring'' table). |- |'''grade_name''' |VARCHAR |Name of the call grading category. |- |'''grade_value''' |INT |Grade assigned by the reviewer in the given category. |- |'''order_num''' |INT |The order in which the given category is supposed to appear in reports relative to the other grading categories (as defined in configuration). |} [[#topic_reporting-database-specification/interaction_quality_monitoring|< Previous]]