Fact Datasets

In this chapter we list facts available in data warehouse. In the following chapters, we will specify the list of dimensions and measures applicable for these facts.

Task

A Task fact is a process during which a conversation stays in one context of the system.

The Task is finished in two cases:

when context is removed (when all participants are disconnected) or
when the task context is changed (when conversation is transferred to another Service Sip Uri).

A fact table grain is a finished process from start to end when conversation stays in one context.

Service Task fields

Permission: current Service OU

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'ServiceTask', @Version = '3.2.1'

SQL

Effective Organization Unit

A ServiceTask is subset of the Task Fact table. The subset contains only Tasks which go through Service endpoints: TaskType.IsService = 1

Oops, it seems that you need to place a table or a macro generating a table within the Table Filter macro.

Field	Field Type	Description/Related Dimension
TaskId	Integer	Primary key
StartedDateTime	Date time value	Task start timestamp
StartedDateId	Reference	Date
StartedTimeId	Reference	Time
ServiceId	Reference	Service
TaskResultId	Reference	Task Result
TransferTypeId	Reference	Transfer Type
TaskTypeId	Reference	Task Type
WaitBetweenTypeId	Reference	Between Type
OriginTypeId	Reference	Origin Type
FirstAcceptedAgentId	Reference	Agent
FirstAcceptedAgentOuId	Reference	Organization Unit
LastAcceptedAgentId	Reference	Agent
LastAcceptedAgentOuId	Reference	Organization Unit
PrimaryModality	Reference	Modality Type
InitialModality	Reference	Modality Type
CalendarCategoryTypeId	Reference	Calendar Category Type
InitialSipFrom	String value	SIP from incoming caller
InitialSipTo	String value	SIP to what destination the call initially goes
TransferorSipUri	String value
TransferTargetSipUri	String value
IsInOpeningHour	Flag dimension
IsStandByDuty	Flag dimension
AcceptTime	Metric	Duration between the moments when the system detects a new task and the task is accepted by the system or terminated (for the lost tasks): 'StartedDateTime - AcceptedDateTime' or 'StartedDateTime - TerminatedDateTime'
QueueTime	Metric	Duration between moment when Task is placed into queue and the moment when a) the first agent accepted the task or b) task was not accepted by an agent and task has left the queue
IvrTime	Metric	IVR consists of periods 'AcceptedDateTime - QueueStartedDateTime' and incase when no agent accepted plus to that 'QueueLeftDateTime - TerminatedDateTime'. For stand by duty calls without queue, IVR is between service accepts the conversation and agent picks up the call 'AcceptedDateTime' and 'FirstAcceptedAgentDateTime'. When there were several IVR periods during one task, the IVR times are summed up.
ConnectedTime	Metric	Duration when caller is speaking with callee.
HoldTime	Metric	Duration when call between caller and callee was on SfB hold. The Hold time reduces the connected time.
AgentProposalTime	Metric	Duration between the moment when the task is placed into a queue and an agent is proposed by the system. During this time ICH service sends a request to AM service to find a proper agent and after the agent is received, ICH starts calling him. (Proposed time does not mean acceptance of the agent). If no agent was proposed, the AgentProposalTimeMs = 0. For a customer task, it is AgentProposalTimeMs of the first Task for which queue has been started
VoicemailTime	Metric	Duration of voicemail record
PreviewTime	Metric	Summary preview time for service task
DialoutTime	Metric	Summary duration of DialoutTimeMs for all agent tasks in scope of the task.
StandbyDutyDtmfAcceptTime	Metric	Duration between agent accepts the task and correct DTMF tone is recognized
HoldCnt	Metric	Count of service task Hold periods

Field	Field Type	Description/Related Dimension
TaskCompletionCodeId	Reference	Task Completion Code
CrossSellingCodeId	Reference	Cross Selling Code
AgentSelectionTypeId	Reference	Agent Selection Type
IsInSla	Flag dimension
AcwTime	Metric	Summary duration of After call work for all agent tasks in scope of the task.
PendingTime	Metric	Duration between callback request creation and queue start. The pending time can be configured e.g. by supervisor on LUCS FE for each callback task during its creation.
PreviewTime	Metric	Summary preview time for service task
DialoutTime	Metric	Summary duration of DialoutTimeMs for all agent tasks in scope of the task.
StandbyDutyDtmfAcceptTime	Metric	Duration between agent accepts the task and correct DTMF tone is recognized
HoldCnt	Metric	Count of service task Hold periods
RonaCnt	Metric	Count of agent Rona

Service Task metrics per Task Type

Some of the metrics are applicable only to special task types. The applicability definition is in table below.

Metric	Direct	Inbound	Outbound / Callback	Conversation As Service	Email	External
IvrTime	-	+	-	-	+	-
QueueTime	-	+	+	-	+	+
AgentProposalTime	-	+	+	-	+	+
AcceptTime	-	+	-	-	+	-
ConnectedTime	-	+	+	+	+	+
WorkTime	+	+	+	+	+	+
HoldTime	-	+	+	+	+	-
AcwTime	-	+	+	+	+	+
CallbackPendingTime	-	-	+	-	-	-
DialoutTime	-	-	+	+	-	-
StandbyDutyDtmfAcceptTime	-	+	-	-	-	-
StandByDutyFirstDtmfAcceptTime	-	+	-	-	-	-
VoicemailTime	-	+	-	-	-	-

Agent Task fields

Permission: historical User OU

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'AgentTask', @Version = '3.2.1'

SQL

The Agent task is a sub-process of task and is always related to only one Agent.

The Agent task is created for accepted, missed and declined Agent conversations. When several Agents took part in the conversation, an Agent task is created for each Agent. Agent tasks are used to analyse Agent performance.

Fact table grain: finished Agent conversation from start to end.

Oops, it seems that you need to place a table or a macro generating a table within the Table Filter macro.

Field	Field Type	Description/Related data set
AgentTaskId	Integer	Primary key
TaskId	Reference	Task
StartedDateTime	Date time value	Agent task start timestamp
StartedDateId	Reference	Date
StartedTimeId	Reference	Time
PrimaryModality	Reference	Modality Type
InitialModality	Reference	Modality Type
RingTime	Metric	Duration when Agent sees incoming conversation pop-up
WorkTime	Metric	Duration between the moment when Agent accepted the Agent task and Agent task end.
PreviewTime	Metric	Duration between the moment when Agent accepted the outbound \ callback IM and start of the customer dial out (the customer dial out starts when Agent accepts the AO). The preview time can be configured e.g. by supervisor in cmd for each outbound task during its creation.
DialoutTime	Metric	Duration between moment when Agent accepted the outbound \ callback AO and the moment when a) customer accepted the task or b) task was not accepted by a customer and task is terminated after max retries amount is reached.
HoldTime	Metric	Summary duration when Agent task was on Hold
StandbyDutyDtmfAcceptTime	Metric	Duration between needed for Agent to provide DTMF tone
HoldCnt	Metric	Count of Hold period during Agent task
AgentTaskResultId	Reference	Dimension: Agent Task Result
AgentId	Reference	Dimension: Agent
AgentOrganizationUnitId	Reference	Dimension: Organization Unit OU of Agent on time of Agent Task
TaskTypeId	Reference	Task Type
WaitBetweenTypeId	Reference	Between Type
ServiceId	Reference	Service
OriginTypeId	Reference	Origin Type
InitialSipFrom	String value	Caller SipUri
InitialSipTo	String value	Sip to what destination the call initially goes
TransferTargetSipUri	String value	Transfer target in case of Agent/Workflow transfer Note: the TransferTargetSipUri for consultative transfers is saved to the DB in case consultative transfer is made by Agent from Agent SfB window and not saved in case consultative transfer is made by Agent from customer SfB window
IsStandbyDuty	Flag dimension	If Service Task is StandbyDuty
ServiceName	String value	Service Task Service Name
ServiceOrganizationUnit	String value	Service Task Service OU

Field	Field Type	Description/Related data set
AcwTime	Metric	Duration of after conversation work
AcwExtendedTime	Metric	Duration of extended after conversation work
AgentSelectionTypeId	Reference	Dimension: Agent Selection Type
AgentProfileId	Reference	Dimension: Agent Profile Profile of Agent on time of Agent Task

Agent Task metrics per Task Type

Some of the metrics are applicable only to special task types. The applicability definition is in table below.

Metric	Direct	Inbound	Outbound / Callback	Conversation As Service	Email	External
RingTime	+	+	+	-	+	+
PreviewTime	-	-	+	-	-	-
DialoutTime	-	-	+	+	-	-
WorkTime	+	+	+	+	+	+
AcwTime	-	+	+	+	+	-
AcwExtendedTime	-	+	+	+	+	-

Customer Task

Permission: current Service OU to any Service within customer task

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'CustomerTask', @Version = '3.2.1'

SQL

A Customer task is a superior process to "task" and is always related to only one customer. A customer task is created for every accepted or lost task or chain of tasks, which may be result of transfer or forward action. For example when a customer call was transferred to different service number - in which case one customer task will contain two tasks.

Oops, it seems that you need to place a table or a macro generating a table within the Table Filter macro.

Field	Field Type	Description/Related data set
CustomerTaskId	Integer	Primary key
StartedDateTime
StartedDateId	Reference	Date
StartedTimeId	Reference	Time
TaskResultId	Reference	Task Result
TaskTypeId	Reference	Task Type
CustomerPathId	Reference	Customer Path
ServiceIdIncomming	Reference	Service
ServiceIdFirstQueued	Reference	Service
IsInOpeningHour	Flag dimension
IsStandbyDuty	Flag dimension
OriginType	Reference	Origin Type
OriginTypeCustom	Reference	Origin Type
PrimaryModality	Reference	Modality Type
InitialModality	Reference	Modality Type
CalendarCategoryTypeId	Reference	Calendar Category Type
InitialSipFrom	String value	Caller SipUri
InitialSipTo	String value	Sip to what destination the call initially goes
AcceptTime	Integer value	Metric
QueueTime	Integer value	Metric
IvrTime	Integer value	Metric
ConnectedTime	Integer value	Metric
HoldTime	Integer value	Metric
AgentProposalTime	Integer value	Metric
VoicemailTime	Integer value	Metric
StandbyDutyDtmfAcceptTime	Integer value	Metric

Field	Field Type	Description/Related data set
TaskCompletionCodeId	Reference	Task Completion Code
CrossSellingCodeId	Reference	Cross Selling Code
IsInSla	Flag dimension
AcwTime	Integer value	Metric

Transfer

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'Transfer', @Version = '3.2.1'

SQL

A Transfer is a business process which sends the conversation from one Sip URI to another Sip URI.

Fact table grain: completed transfer.

Transfer Types and Customer Tasks

Depending on transfer target (Agent/Service/External) a new Task or AgentTask may or may be not created after a transfer. In case of new Task being created after the transfer, both source and target Tasks are created within one Customer Task.

Transfer to Service – new Task
Transfer to Agent - new Agent Task
Transfer to External – nothing

The table below lists all supported transfer options.

TransferTypeI - TransferType - Description	Transfer activity	Resulting count of Tasks/AgentTasks within customer task
121TRF_SRV_AGT Service - AgentWorkflow Transfer	WFA Virtual TransferWFA Blind TransferWFA Language Transfer	1 Customer Task 1 Task (initial) 1 Agent Task (new after transfer to Agent)
122 TRF_SRV_SRV Service - Service Workflow Transfer	WFA Virtual TransferWFA Blind TransferWFA Language Transfer	1 Customer Task 2 Tasks (initial + new after transfer to service)
123TRF_SRV_EXT Service - ExternalWorkflow Transfer	WFA Virtual TransferWFA Blind TransferWFA Language Transfer	1 Customer Task 1 Task (initial)
221FWD_SRV_AGT Service - AgentWorkflow Transfer	WFA Forward	1 Customer Task 1 Task (initial) 1 Agent Task (new after forward to Agent)
222FWD_SRV_SRV Service - ServiceWorkflow Transfer	WFA Forward	1 Customer Task 2 Tasks (initial + new after forward to service)
223FWD_SRV_EXT Service - ExternalWorkflow Transfer	WFA Forward	1 Customer Task 1 Task (initial)
111 TRF_AGT_AGT Agent - Agent Agent Transfer	SfB Blind Transfer Sfb Consultation Transfer	1 Customer Task 1 Task (initial) 2 Agent Task (initial + new after transfer to Agent)
112TRF_AGT_SRV Agent - ServiceAgent Transfer	SfB Blind Transfer	1 Customer Task 2 Tasks (initial + new after forward to service) 1 Agent Task (initial)
113TRF_AGT_EXT Agent - ExternalAgent Transfer	SfB Blind Transfer	1 Customer Task 1 Task (initial) 1 Agent Task (initial)

Survey Result

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'TransferType', @Version = '3.2.1'

SQL

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'SurveyResult', @Version = '3.2.1'

Survey result is a customer answer to one question.

Fact table grain – submitted answer to one question.

There may be 2 technical result values:

NoAnswer – this value is recorded if caller did not provided value before timeout, or value is of the unexpected type or outside range.
SkipQuestion – this value is recorded if customer forcibly skips the survey question, e.g. hang up.

Field	Field Type	Description/Related data set
SurveyQuestionResultId	Integer	Primary key
SurveyQuestionId	Reference	Survey Question
TaskId	Reference	Task
CustomerTaskId	Reference	CustomerTask
ResultText	String value	Actual value stored in database
ResultNumeric	Integer	Numeric value or NULL in case Result is not numeric/ NoAnswer or SkipQuestion
ResultWeighted	Integer	Weighted result is calculated only for Survey questions with Range. With provided result (NoAnswer and SkipQuestion values are excluded) Percentage value is decimal within 0 and 1) calculated based on the predefined borders. For example if range is 3 till 8, recorder result is 5 the ResultWeighted = 5 -3 / (8-3) = 0.4

Task Completion Information

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'TackCompletionInformation', @Version = '3.2.1'

SQL

Task completion information:

is only contained in service agent tasks.
is an selection information picked by the Agent, consiting of "Task Completion Code" and "Cross Selling Code"

Fact table grain: submitted selection of Task Completion Code and Cross Selling Code.

Agent State Period

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'TransferType', @Version = '3.2.1'

SQL

Fact table contains periods defining when an Agent presence state is persistent. Once a state is changed a new period is started.

Agent State Hourly Snapshot

EXEC [Reporting].[usp_GetDataSet] @DataSet = 'AgentStateHourlySnapshot', @Version = '3.2.1'

SQL

This fact table contains 1-hour-snapshots of the Agents. A row per Agent per hour, one column per Agent State Type sum in milliseconds.

Agent Not Ready Reason

EXEC [Reporting].[usp_AgentNotReadyReasons_DM_Stable]  @Offset = '+01:00', @UseDaylightSavings = 1, @StartDate='20171007', @EndDate='20190127', @LanguageId = 'DEU'

SQL

This fact table contains a list of "Not Ready" periods of the Agents. Every time when an Agent changes state to "Not Ready" (Busy, DND, Be right back, etc.) it is excluded from the task distribution pool, until the state is changed back to "Available". The affected Agent is requested to select a Not-Ready-Reason from the predefined list and add a free-text comment.

Fact table grain: Period when agent is in one state.