Contact Expert v7.4 for Skype for Business Server
BI Schema
The BI Schema represents the SQL database objects created to accumulate and present raw historical data to the Power BI platform. The structure is fairly complex and requires the clear understanding of various states, events and workflows happening in the system to be able to utilize the data in the reports. The following definitions are used in this article:
Interaction – a voice call, email or chat message that was arriving to or initiated from Contact Expert. It has an original direction (started as inbound or outbound). It might have traversed multiple queues if it overflew e.g. due to some queue limits. It might have landed at zero, one or more agents depending whether it was abandoned in queue (for inbound), transferred by an agent to another one, etc.
Interaction Session or Segment – one portion or 'leg' of an Interaction. For example if an inbound voice call spent time in a queue and then got transferred to an agent who took and eventually closed it off without a callback, then it was a single segmented interaction having a single session. If this agent would have decided to transfer it to another agent, then this transferred call would be a second segment of the same interaction. Similarly, if the first agent would have filed a callback before closing the call, the callback session initiated automatically at a later due time would again be an additional segment or session of the same interaction. Task is also used to refer to more or less the same thing.
Primary session – since an interaction might often accumulate more than one session, it becomes important to designate the one where a customer was involved.
For further reading about the various states in Contact Expert please visit the following articles:
Session ID and Conversation ID
The BI Schema identifies the Interaction using the ConversationID fields, the Session using the SessionID or SDUID. It is worth noting that some Segments might have the same SDUID/SessionID. The Conversationid was introduced to provide a single overarching identifier for the Interactions.
For further insight please visit the Understanding the difference between ConversationID and SDUID article.
Historical Data Source is Not Good for Realtime Reports
Warning
Data in the BI Schema can not be used for realtime reporting purposes!
This is because in order to present accumulated data, some records in the schema are updated several times during the lifetime of the interactions. If a custom application is written to fetch data from the BI Schema, then it should never anticipate a record to be 'ready' just because it exists. Some fields might still be updated later on.
Developers need to understand when a certain BI Schema record is qualified as 'ready' to be providing meaningful information.
ACEReport and ACESystem Databases
While the Contact Expert database is split in two to be able to locate report related data to a separate database server from data that is needed for the actual operation of the system, the BI Schema contains objects in both databases, not just the reporting one.
ACEReport Views
ACEReport.bi.Agents
A record in this view represents a single agent configured in the Contact Expert tenant the user logged into the report is registered for.
The view runs the ACESystem.bi.ufnGetUserTenants() function to filter the records presented to the tenant of the user 'looking' at the BI Schema (e.g. by running a Contact Expert BI report file or being logged in to the database directly). It also filters data based on the parameter values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
AgentID |
integer | The unique identifier of an agent. Taken from the ACEReport.dbo.AGENTNAMES table. |
Agent name |
string | The name of the agent. Taken from the ACEReport.dbo.AGENTNAMES table. |
Login name |
string | The username part of the account the agent uses to log in to the SfB client. Taken from the ACEReport.dbo.AGENTNAMES table. |
Removed |
Yes or No string |
This flag shows whether the agent record was removed or not. The system retains agent information even after agents are removed, otherwise historical data would be inconsistent. |
Team |
string | Name of the team the agent is registered into within the Contact Expert organizational hierarchy. Taken from the ACEReport.dbo.TEAM table. |
Business unit |
string | The name of the business unit the agent is registered into within the Contact Expert organizational hierarchy. Taken from the ACEReport.dbo.BUSINESSUNIT table. |
Organization unit |
string | The value entered into the organizational unit field in the Human Agents for of the Contact Expert portal. Obviously empty for IVR Channels. Taken from the ACESystem.bi.OrganizationUnit view. |
ACEReport.bi.AgentStat
A record in this view designates one particular state an agent went into, with details such as the start date/time, duration, the actual state, any reason codes, associated queues or campaigns if any, etc. The 'agent' here can either designate a Human Agent or an IVR Channel resource as both are 'agents' in Contact Expert.
The view uses the ACESystem.bi.ufnGetTenantTimezoneOSKeys() function to transform datetime values to the TZ specified for the tenant of the user 'looking' at the BI Schema (e.g. by running a Contact Expert BI report file or being logged in to the database directly). It also filters data based on the parameter values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
AGENTTYPE |
integer | 1 for Human Agents, 2 for IVR Channels |
STARTDATE |
datetime in tenant TZ | The start datetime of this agent state in the TZ of the tenant the user logged in to the report is associated to. |
DateID |
varchar(8) | The date part of the STARTDATE field concatenated into a "YYYYMMDD" format specified by the CONVERT() T-SQL instruction's "112" format specifier. |
MinuteID |
integer | A shortened format of the hour and minute part of STARTDATE achieved by the 60 * DATEPART(hh, STARTDATE) + DATEPART(mi, STARTDATE) T-SQL expression. |
AgentID |
integer | The unique identifier of the agent (Human or IVR) in the Contact Expert database. |
CampaignID |
integer | The unique identifier of the queue or campaign in the Contact Expert database this record is associated to. A NULL value means no association. |
MediaID |
integer | Specifies what media channel, if any, is associated to this record. Values here correspond to the MediaID field in the bi.Media view. A NULL value means no association. |
DirectionID |
integer | Specifies the direction of the task associated to this record (inbound or outbound). Values correspond to the DirectionID filed in the bi.Directions view. A NULL value means no association. |
DialingModeID |
integer | Represents the dialing mode for voice activities associated to this record. Values here correspond to the DialingModeID field in the bi.DialingModes view. A NULL value means no association. |
DistributionType |
string | Manual in case the agent was in Lookup break state while working on a task in Ringing or Preview state. ACD when in any state other than Lookup. A NULL value means the state was something for which DistributionType is irrelevant. |
State |
string | ACW1 when the agent is on break (Passive state with an AUX reason). Preview for outbound Preview tasks in Ringing state. Any other value corresponds to the STATEDESCRIPTION field of the dbo.AGENTSTATES table. |
BreakMode |
string | When the agent is in Passive state then it can be [Default break] or [Lookup] for the corresponding reasons, it can be NULL for ACW, or any value corresponding to the Name field of the bi.ReasonCodes view. It is also set to NULL for when the agent state is not Passive. |
Duration |
float | The time spent in the given state in seconds, rounded to the 1st decimal. |
EmailSent |
string | Yes or No reflecting whether an email was sent out associated to this agent state record (e.g. a new outbound email or a reply to a previous inbound email, etc.) |
ConversationID |
string | The conversationID pertaining to this record. A NULL value means no such association is available. |
CustomerID |
integer | The Contact ID pertaining to this record. A NULL value means no such association is available. |
SDUID |
string | The unique identifier of the task associated to this record. A NULL value means no such association is available. |
ACEReport.bi.CallStat
A record in this view corresponds to one segment of a voice, email or chat interaction. Therefore complex interactions with multiple segments might have more than one record in this view connected via the ConversationID value.
Important
A voice call might not have spent any time in queue if it was an outbound call. Similarly, agent related information might also be empty (NULL) in the record, if the call never managed to reach an agent.
The view uses the ACESystem.bi.ufnGetTenantTimezoneOSKeys() function to transform datetime values to the TZ specified for the tenant of the user 'looking' at the BI Schema (e.g. by running a Contact Expert BI report file or being logged in to the database directly). It also filters data based on the parameter values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
ID |
integer | The unique identifier of the record. |
SDUID |
integer | The unique identifier of the task associated to this record. |
STARTTIME |
datetime in tenant TZ | The start datetime of this agent state in the TZ of the tenant the user logged in to the report is associated to. |
STARTDAY |
date part of datetime | A datetime field with the date part containing the information from STARTTIME. |
DateID |
varchar(8) | The date part of the STARTTIME field concatenated into a "YYYYMMDD" format specified by the CONVERT() T-SQL instruction's "112" format specifier. |
MinuteID |
integer | A shortened format of the hour and minute part of STARTTIME achieved by the 60 * DATEPART(hh, STARTTIME) + DATEPART(mi, STARTTIME) T-SQL expression. |
AgentID |
integer | The unique identifier of the agent (Human or IVR) associated to this record. A NULL value means no association. |
CampaignID |
integer | The unique identifier of the queue or campaign in the Contact Expert database this record is associated to. |
Phone number |
string | The contact point of the customer or Contact that was used for this particular interaction segment. Could be a phone number, an email address or a chat handle. |
MediaID |
integer | Specifies what media channel, if any, is associated to this record. Values here correspond to the MediaID field in the bi.Media view. There should be no NULL value present. |
DirectionID |
integer | Specifies the direction of the task associated to this record (inbound or outbound). Values correspond to the DirectionID filed in the bi.Directions view. There should be no NULL value present. |
DialingModeID |
integer | Represents the dialing mode for the outbound task associated to this record. Values here correspond to the DialingModeID field in the bi.DialingModes view. A NULL value means this was not an outbound task. |
CustomerID |
integer | The Contact ID pertaining to this record. There should be no NULL value present. |
ConversationID |
string | The conversationID pertaining to the interaction this segment record is associated to. A NULL value means no such association is available. |
Call Result |
string | The outcome or state of the task or interaction segment this record pertains to. Values correspond to the DESCRIPTION field of the dbo.CALLSTATES table. |
Callback |
bit | True if this segment represents a callback. False for any other task types. Values correspond to the CALLTYPE filed in dbo.CALLSTAT and since this is an INT type field, the bi.CallStat view converts it to BIT, meaning that dbo.CALLSTAT.CALLTYPE value of 0 will generate a False, and any other value a True. |
Business Tags |
string | Represents the full business tag string applied by an agent or Automation API application to this record. A NULL value means there was no tag applied. |
Call duration |
float | The total duration of the interaction segment this record pertains to rounded to the first decimal. Value is taken from the CALLDURATION field of the dbo.CALLSTAT table. |
Queue duration |
float | The amount of time this segment spent in queue(s), that is achieved by subtracting the Ring duration and Talk duration values from the Call duration. |
Ring duration |
float | The amount of time it took for an agent to answer ('pick up') a call ringing at her desk. The value is taken from the ANSWERINGTIME field of the dbo.CALLSTAT table rounded to the first decimal. |
Talk duration |
float | The amount of time the segment accumulated while being connected with the agent(s). The value is taken from the HANDLINGTIME field of the dbo.CALLSTAT table rounded to the first decimal. |
Hold duration |
float | The amount of time the customer was kept on hold during this interaction segment. The value is taken from the HOLDTIME field of the dbo.CALLSTAT table. |
Follow-up reason |
string | Represents the reason why this interaction segment got a followup segment generated. overflow means that the Routing Rules associated to the Dial Rule of the queue this segment was waiting in got triggered. transfer means that the segment was transferred either by the handling agent or by the customer while waiting in queue. The value is parsed from the FOLLOWUP field of the dbo.CALLSTAT table. A NULL value means there was no followup segment after this record. |
Follow-up result |
string | Represents the outcome of the followup segment creation. ok means the followup interaction segment was generated successfully. failed means the followup segment was not generated successfully, which could happen for example because the external PSTN number dialled in an overflow situation did not answer. unknown means the result information was neither clearly "ok" nor "failed". This value is also parsed from the FOLLOWUP field of the dbo.CALLSTAT table. A NULL value means there was no followup segment after this record. |
Terminated by |
string | Reveals whether the voice interaction was finished (dropped, disconnected, hung up) by the 'far end' party (typically the Customer) or the 'near end' party (Agent). The value is based on the DROPPEDBYAGENT bit field of the dbo.CALLSTAT table. The NULL value means this information was not possible to derive (the original field is probably also NULL). |
EmailSent |
string | Yes when this segment included an outbound email being sent out. No means there was no such email sent out. The value is based on the EMAIL_SENT field of the dbo.CALLSTAT table. There should be no NULL value. |
ACEReport.bi.Campaigns
Each record here represents a single queue / campaign in the tenant of the user.
All values are taken from the ACEReport.dbo.CAMPAIGNNAMES table. The view runs the ACESystem.bi.ufnGetUserTenants() function to filter the records presented to the tenant of the user 'looking' at the BI Schema (e.g. by running a Contact Expert BI report file or being logged in to the database directly). It also filters data based on the parameter values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
CAMPAIGNID |
integer | The unique ID of the record. |
Campaign name |
string | The name of the queue / campaign. |
DirectionID |
integer | Specifies the direction of the tasks associated to this queue or campaign (inbound or outbound). Values correspond to the bi.Directions.DirectionID field. There should be no NULL value present. |
MediaID |
integer | Specifies what media channel, is associated to this queue or campaign. Values here correspond to the bi.Media.MediaID field. There should be no NULL value present. |
ACEReport.bi.DialingModes
Represents the different outbound voice call dialing modes associated to a campaign or queue.
All values are taken from the dbo.DIALINGMODES table without employing any filtering of the data.
| Filed Name | Value | Description |
|---|---|---|
DialingModeID |
integer | The unique ID of the record. |
Dialing Mode |
string | Human readable name of the dialing modes. |
ACEReport.bi.Directions
Represents the two possible directions an interaction / task could have (inbound or outbound). Values are taken from the ACEReport.dbo.DIRECTIONS table without any filtering.
| Filed Name | Value | Description |
|---|---|---|
DirectionID |
integer | The unique ID of the record. |
Direction |
string | Human readable name of the directions. |
ACEReport.bi.EmailCases
A record represents a single email received from a customer. Values are taken from the ACEReport.bi.EmailIn view with no additional filtering.
| Filed Name | Value | Description |
|---|---|---|
SDUID |
integer | The unique identifier of the task associated to this record. |
SentDate |
datetime | The date/time the email was sent by the sender (customer) parsed from the email headers. |
ImportDate |
datetime | The date/time the email arrived to Contact Expert and got filed into the database and joined an email queue. |
ProcessDate |
datetime | The date/time the email in the database got routed from the email queue. |
SentDay |
string | The date part of the SentDate field concatenated into a "YYYYMMDD" format specified by the CONVERT() T-SQL instruction's "112" format specifier. |
ImportDay |
string | The date part of the ImportDate field concatenated into a "YYYYMMDD" format specified by the CONVERT() T-SQL instruction's "112" format specifier. |
ProcessDay |
string | The date part of the ProcessDate field concatenated into a "YYYYMMDD" format specified by the CONVERT() T-SQL instruction's "112" format specifier. |
CampaignID |
integer | The unique identifier of the queue or campaign in the Contact Expert database this record is associated to. |
AgentID |
integer | The unique identifier of the agent (Human or IVR) associated to this record. A NULL value means no association. |
CustomerID |
integer | The Contact ID pertaining to this record. There should be no NULL value present. |
QueueTime |
integer | The amount of time this email spent in the email queue waiting to be routed to an agent in seconds. |
ACEReport.bi.EmailCompletion
A record here corresponds to an outcome event happened with an email interaction segment. Values are taken from the ACEReport.bi.History view and filtered to show email items only (where MediaID = 3).
| Filed Name | Value | Description |
|---|---|---|
SDUID |
integer | The unique identifier of the task associated to this record. |
EventDate |
datetime | The date/time the event happened in the TZ of the tenant the user looking is associated to. |
Event |
string | The internal name of the outcome event (Major Reason task closure codes) that triggered the creation of this record. |
Business tag |
string | Represents the full business tag string applied by an agent or Automation API application to this record. A NULL value means there was no tag applied. |
ACEReport.bi.EmailHandling
Records here correspond to the records in the bi.CallStat view, but are further filtered to present emails only.
| Filed Name | Value | Description |
|---|---|---|
SDUID |
integer | The unique identifier of the task associated to this record. |
HandlingTime |
float | The amount of time the segment accumulated while being connected with the agent(s). The value is taken from the Talk duration field from the bi.CallStat view, but is renamed here. |
ACEReport.bi.EmailIn
A record here represents a single email received from a customer. All values are taken from the ACESystem.bi.EmailIn view and are filtered based on the values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
SDUID |
integer | The unique identifier of the task associated to this record. There could be emails not yet having an session created, therefore no SDUID generated. In such cases the view generates a dummy unique value to retain the database integrity. Taken from the SDU_ID field. |
SentDate |
datetime | The date/time the email was sent by the sender (customer) parsed from the email headers. Taken from the EMAIL_SENTDATE field. |
ImportDate |
datetime | The date/time the email arrived to Contact Expert and got filed into the database and joined an email queue. Taken from the EMAIL_INSDATE field. |
ProcessDate |
datetime | The date/time the email in the database got routed from the email queue. Taken from the EMAIL_PROCDATE field. |
CampaignID |
integer | The unique identifier of the queue or campaign in the Contact Expert database this record is associated to. Taken from the CAMP_ID field. |
AgentID |
integer | The unique identifier of the agent (Human or IVR) associated to this record. A NULL value means no association. Taken from the RESOURCE_ID field. |
CustomerID |
integer | The Contact ID pertaining to this record. There should be no NULL value present. Taken from the CONTACT_ID field. |
QueueTime |
integer | The amount of time this email spent in the email queue waiting to be routed to an agent in seconds. Calculated as the difference in seconds between EMAIL_INSDATE and EMAIL_PROCDATE values. |
ACEReport.bi.History
A record here corresponds to an event that happened with a session. All values are taken from the ACESystem.bi.History view and are filtered based on the values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
HistoryID |
integer | The unique ID of the history entry. Taken from the HISTORY_ID field. |
SessionID |
integer | The unique ID of the session entry. Taken from the SESSION field. |
DirectionID |
integer | Specifies the direction of the session (inbound or outbound). Values correspond to the bi.Directions.DirectionID field. There should be no NULL value present. Taken from the DIRECTION field. |
MediaID |
integer | Specifies what media channel, if any, is associated to this record. Values here correspond to the bi.Media.MediaID field. There should be no NULL value present. Taken from the MEDIA field. |
EVENTDATE |
datetime | The date/time this event happened (the time this record generated). |
Event |
string | The internal name of the outcome event (Major Reason task closure codes) that triggered the creation of this record. Taken from the EVENTNAME field. |
HasInteraction |
integer | 1 means this session had a voice call associated. 0 means there was not. The value is calculated based on the contents of the SIGNALING_INFO field. |
Business tag |
string | Represents the full business tag string applied by an agent or Automation API application to this record. A NULL value means there was no tag applied. Taken from the BUSINESS_TAG field. |
ACEReport.bi.Media
Represents the media channels the system supports. All values are taken from the dbo.MEDIATYPES table without filtering.
| Filed Name | Value | Description |
|---|---|---|
MediaID |
integer | The unique ID of the media type. |
Media |
string | Human readable name of the media type. Taken from the DESCRIPTION field. |
ACEReport.bi.Params
Contains parameters customers can use to tweak the filter on the data the BI Schema presents. Data is taken from the ACEReport.bi.ParamsTable table which is where customers can change them.
| Filed Name | Value | Description |
|---|---|---|
Param |
string | The parameter name. |
Value |
string | The parameter value. |
The BI Schema views use the following parameter data in this table.
| Param field | Description |
|---|---|
LoadData |
When the Value field is set to Y the BI Schema views will fetch data from whatever source they use to do so from. For any other value, NULL value or when the LoadData record is missing, no data is loaded. |
FromDate |
Some BI Schema objects (views mainly) will fetch data from their source starting from the date specified in the Value field for this record. |
ACEReport.bi.Reasoncodes
A record here represents a built-in or custom added break reason that was or is selected by an agent. All values are taken from the dbo.WORKCODES table. The view runs the ACESystem.bi.ufnGetUserTenants() function to filter the records presented to the tenant of the user 'looking' at the BI Schema (e.g. by running a Contact Expert BI report file or being logged in to the database directly). It also filters data based on the parameter values specified in the ACEReport.bi.Params view.
| Filed Name | Value | Description |
|---|---|---|
ID |
integer | The unique ID of the break reason. |
Name |
string | The human readable name of the break code. Taken from the REASONCODENAME field. |
Removed |
string | Yes when the break code was removed since it has been used. No when it was not (yet) removed. The system retains removed codes for historical reports. |
ActiveFrom |
datetime | The start datetime this reason code was active from. Taken from the ACTIVE_FROM field of the source table. |
ActiveTo |
datetime | The datetime until this reason code was active. Taken from the ACTIVE_TO field of the source table. When this is NULL but the ActiveFrom is not, it means this reason code is active right now. |
ACEReport Tables
ACEReport.bi.ParamsTable
The source table behind the ACEReport.bi.Params view with the same structure.
ACEReport Stored Procedures
The BI Schema does not contain stored procedures in ACEReport.
ACEReport Functions
The BI Schema does not contain functions in ACEReport.
ACESystem Views
These views are feeding the ACEReport BI Schema views by taking data from the ACESystem default schema tables, applying the first layer of filtering and data transformation.
ACESystem.bi.EmailIn
A record here represents a single email received from a customer. Data is taken from the ACESystem.dbo.EMAILIN and ACESystem.dbo.CAMPAIGNS tables and are filtered by the ACEReport.bi.Campaigns view and also by the ACESystem.bi.ufnGetTenantTimezoneOSKeys() function to present data from tenants having a valid TZ set.
| Filed Name | Value | Description |
|---|---|---|
ID |
integer | The unique ID of the record in the EMAILIN table. |
SDU_ID |
string | The unique ID of the session data unit. |
EMAIL_SENTDATE |
datetime | The date/time the email was sent by the sender (customer) parsed from the email headers transformed into the TZ set for the tenant the user 'looking' is associated to. |
EMAIL_INSDATE |
datetime | The date/time the email arrived to Contact Expert and got filed into the database and joined an email queue transformed into the TZ set for the tenant the user 'looking' is associated to. |
EMAIL_PROCDATE |
datetime | The date/time the email in the database got routed from the email queue transformed into the TZ set for the tenant the user 'looking' is associated to. |
CAMP_ID |
integer | The unique identifier of the queue or campaign this record is associated to. |
RESOURCE_ID |
integer | The unique identifier of the agent (Human or IVR) associated to this record. |
CONTACT_ID |
integer | The Contact ID pertaining to this record. There should be no NULL value present. |
ACESystem.bi.History
A record here corresponds to an event that happened with a session. Data is taken from the ACESystem.dbo.HISTORY, ACESystem.dbo.SESSIONS, ACESystem.dbo.CAMPAIGNS and are filtered by the ACEReport.bi.Campaigns view and also by the ACESystem.bi.ufnGetTenantTimezoneOSKeys() function to present data from tenants having a valid TZ set.
| Filed Name | Value | Description |
|---|---|---|
HISTORY_ID |
integer | The unique ID of the history entry. Taken from the ACESystem.dbo.HISTORY table. |
SESSION |
integer | The unique ID of the session. Taken from the ACESystem.dbo.HISTORY table. |
SDU_ID |
string | The unique ID of the session data unit. Taken from the ACESystem.dbo.HISTORY table. |
DIRECTION |
integer | Specifies the direction of the session (inbound or outbound). Values correspond to the ACEReport.bi.Directions.DirectionID field. Taken from the ACESystem.dbo.CAMPAIGNS table. |
MEDIA |
integer | Specifies what media channel. Values correspond to the ACEReport.bi.Media.MediaID field. Taken from the ACESystem.dbo.CAMPAIGNS table. |
EVENTDATE |
datetime | The date/time this event happened (the time this record generated) transformed to the TZ specified for the tenant the user 'looking' is associated to. Taken from the ACESystem.dbo.HISTORY table. |
EVENTNAME |
string | The internal name of the outcome event (Major Reason task closure codes) that triggered the creation of this record. Taken from the ACESystem.dbo.REASONCODES.EVENTNAME field. |
SIGNALING_INFO |
string | When filled, it contains voice call related information acquired from the telephony backend, meaning there was a voice call associated to this session. Taken from the ACESystem.dbo.HISTORY table. |
BUSINESS_TAG |
string | Represents the full business tag string applied by an agent or Automation API application to this record. A NULL value means there was no tag applied. Taken from the ACESystem.dbo.HISTORY table. |
ACESystem.bi.OrganizationUnit
A record here represent an agent's details. All data is taken from the ACESystem.dbo.AGENTS table without any filtering.
| Filed Name | Value | Description |
|---|---|---|
RESOURCE_ID |
integer | The unique identifier of the agent (Human or IVR). |
ORGANIZATIONAL_UNIT |
string | The value of the similarly named field in the source table. This is a redundant piece of data as the Business Unit introduced with the multi-tenancy supersedes it. However it is retained for particular customer requests. It is nevertheless not part of the 'organizational hierarchy' in CE, it is just a plain text field. |
ACESystem.bi.Sessions
A record here represents a single session or 'segment' of an interaction. Data is collected from the ACESystem.dbo.SESSIONS, ACESystem.dbo.CAMPAIGNS, ACESystem.dbo.SESSIONSTATE, ACESystem.dbo.CONTACTS, ACESystem.dbo.AGENTS tables filtered by the ACEReport.bi.Campaigns view and also by the ACESystem.bi.ufnGetTenantTimezoneOSKeys() function to present data from tenants having a valid TZ set.
| Filed Name | Value | Description |
|---|---|---|
SESSION_ID |
integer | The unique ID of the session entry. Taken from the ACESystem.dbo.SESSIONS table. |
STATE_NAME |
string | The (internal but) human readable name of the state of the session at the time this record was created. Taken from the ACESystem.dbo.SESSIONSTATE.INTERNAL_NAME field. |
CREATION_TIME |
datetime | The date/time the contact (customer record) was created for this session. Taken from the ACESystem.dbo.CONTACTS.CREATION_TIME field. |
LASTSTART |
datetime | The date/time this session started, it is not necessarily the exact time the contact was created for it (CREATION_TIME). Taken from ACESystem.dbo.SESSIONS.LASTSTART field. |
CAMPAIGN |
integer | The unique identifier of the queue or campaign this record is associated to. Taken from the ACESystem.dbo.SESSIONS.CAMPAIN field. |
CONTACT |
integer | The Contact ID pertaining to this record. There should be no NULL value present. Taken from the ACESystem.dbo.SESSIONS.CONTACT field. |
AGENT |
integer | The unique identifier of the agent (Human or IVR). Taken from the ACESystem.dbo.AGENTS.RESOURCE_ID field. |
LASTBUSINESSTAG |
string | Represents the full business tag string applied by an agent or Automation API application to this record. A NULL value means there was no tag applied. Taken from the ACESystem.dbo.SESSIONS.LASTBUSINESSTAG field. |
DIRECTION |
integer | Specifies the direction of the session (inbound or outbound). Values correspond to the DirectionID field of the ACEReport.bi.Directions view. Taken from the ACESystem.dbo.CAMPAIGNS table. |
MEDIA |
integer | Specifies what media channel. Values correspond to the MediaID field of the ACEReport.bi.Media view. Taken from the ACESystem.dbo.CAMPAIGNS table. |