Jump to: navigation, search

In the Column List:

  • P = Primary key
  • M = Mandatory field
  • F = Foreign key (where the term is used loosely to indicate a surrogate key reference to a field in another table, not a formal constraint)
  • DV = Default value
  • In the Data Type column, varchar/nvarchar means that the data type is varchar except in multi-language databases that use Unicode, in which case the data type is nvarchar. (The Info Mart database in Cloud deployments is not multi-language.)

Table CHAT_SESSION_FACT

Description

Introduced: 8.5.011

In partitioned databases, this table is partitioned.


Each row in this table describes a chat session managed by Chat Server.

Each fact is based on user data sent in an Interaction Server reporting event when the chat session ends. Genesys Info Mart extracts the KVP data from the G_USERDATA_HISTORY table in IDB, and the transformation job combines the statistics in each event into a single CHAT_SESSION_FACT record. Rows are inserted on receipt of the reporting event and are not updated. The chat statistics reported in each record are summarized by session and are not connected to specific agents or, in deployments that include Bot Gateway Server (BGS), bots.

The MEDIA_SERVER_IXN_GUID links the CHAT_SESSION_FACT record with the related INTERACTION_FACT (IF). In deployments that include BGS, the MEDIA_SERVER_IXN_GUID also links the CHAT_SESSION_FACT record with the related BGS_SESSION_FACT records. In this way, Genesys Info Mart enables you to generate reports that provide details about Genesys Chat activity at the interaction level, session level, and chat bot level.

Tip
To assist you in preparing supplementary documentation, click the following link to download a comma-separated text file containing information such as the data types and descriptions for all columns in this table: Download a CSV file.

Hint: For easiest viewing, open the downloaded CSV file in Excel and adjust settings for column widths, text wrapping, and so on as desired. Depending on your browser and other system settings, you might need to save the file to your desktop first.

Column List

Legend

Column Data Type P M F DV

MEDIA_SERVER_IXN_GUID

varchar(64) X X

ADDED_TS

int X

START_DATE_TIME_KEY

int X X X

END_DATE_TIME_KEY

int X X

TENANT_KEY

int X X -2

SESSION_DURATION

int X 0

MSG_FROM_AGENTS_COUNT

int X 0

MSG_FROM_AGENTS_SIZE

int X 0

MSG_FROM_CUSTOMERS_COUNT

int X 0

MSG_FROM_CUSTOMERS_SIZE

int X 0

AGENT_REPLY_COUNT

int X 0

AGENT_REPLY_MAX_DURATION

int X 0

AGENT_REPLY_DURATION

int X 0

AGENT_WAIT_COUNT

int X 0

AGENT_WAIT_MAX_DURATION

int X 0

AGENT_WAIT_DURATION

int X 0

CUSTOMER_REPLY_COUNT

int X 0

CUSTOMER_REPLY_MAX_DURATION

int X 0

CUSTOMER_REPLY_DURATION

int X 0

CUSTOMER_WAIT_COUNT

int X 0

CUSTOMER_WAIT_MAX_DURATION

int X 0

CUSTOMER_WAIT_DURATION

int X 0

UNTIL_FIRST_AGENT_DURATION

int X 0

UNTIL_FIRST_REPLY_DURATION

int X 0

AGENTS_COUNT

int X 0

MSG_FROM_BOTS_COUNT

int X 0

MSG_FROM_BOTS_SIZE

int X 0

UNTIL_FIRST_BOT_DURATION

int X 0

BOTS_COUNT

int X 0

CHAT_SESSION_DIM_KEY

int X X -2

MEDIA_TYPE_KEY

int X X -2

CREATE_AUDIT_KEY

numeric(19) X X

UPDATE_AUDIT_KEY

numeric(19) X

MEDIA_SERVER_IXN_GUID

The interaction GUID, as reported by Interaction Server. This value is the ID of the chat session. This GUID might not be unique. The value allows you to associate interaction details with the chat session details by using the following references:

INTERACTION_FACT.MEDIA_SERVER_IXN_GUID = CHAT_SESSION_FACT.MEDIA_SERVER_IXN_GUID
AND INTERACTION_FACT.START_DATE_TIME_KEY = CHAT_SESSION_FACT.START_DATE_TIME_KEY

In combination with START_DATE_TIME_KEY, MEDIA_SERVER_IXN_GUID forms the value of the composite primary key for this table in nonpartitioned as well as partitioned databases.

ADDED_TS

The UTC-equivalent value of the date and time at which the event with chat data is received.

START_DATE_TIME_KEY

Based on KVP: ChatServerSessionStartedAt

Identifies the start of a 15-minute interval in which the chat session began. Use this value as a key to join the fact tables to any configured DATE_TIME dimension, in order to group the facts that are related to the same interval and/or convert the timestamp from the KVP to an appropriate time zone. In combination with MEDIA_SERVER_IXN_GUID, START_DATE_TIME_KEY forms the value of the composite primary key for this table in nonpartitioned as well as partitioned databases.

END_DATE_TIME_KEY

Based on KVP: ChatServerSessionClosedAt

Identifies the start of a 15-minute interval in which the chat session ended. Use this value as a key to join the fact tables to any configured DATE_TIME dimension, in order to group the facts that are related to the same interval and/or convert the timestamp from the KVP to an appropriate time zone.

TENANT_KEY

Based on KVP: csg_TenantId

The surrogate key that is used to join the TENANT dimension to the fact tables.

SESSION_DURATION

Based on KVP: csg_SessionTotalTime

The duration, in seconds, of the Chat Server session.

MSG_FROM_AGENTS_COUNT

Based on KVP: csg_MessagesFromAgentsCount

The total number of all messages visible to the customer that were sent by all agents involved in the chat. A chat session might involve several agents (for example, in the case of a conference or transfer).

MSG_FROM_AGENTS_SIZE

Based on KVP: csg_MessagesFromAgentsSize

The total size of all messages visible to the customer that were sent by all agents involved in the chat. The size is expressed as number of characters, including spaces.

MSG_FROM_CUSTOMERS_COUNT

Based on KVP: csg_MessagesFromCustomersCount

The total number of messages sent by the customer.

MSG_FROM_CUSTOMERS_SIZE

Based on KVP: csg_MessagesFromCustomersSize

The total size of the messages sent by the customer. The size is expressed as number of characters, including spaces.

AGENT_REPLY_COUNT

Based on KVP: cse_AgentReplyTotalCount

The total number of agent replies to the customer.

AGENT_REPLY_MAX_DURATION

Based on KVP: cse_AgentReplyMaxTime

The maximum time interval, in seconds, the agent(s) spent replying to the customer (in other words, the maximum interval between the customer's response and the time any agent actually sent a reply). If the customer's response was a set of messages, the reply interval is calculated from the time the first message in the set was received.

AGENT_REPLY_DURATION

Based on KVP: cse_AgentReplyTotalTime

The total amount of time, in seconds, the agent(s) spent replying to the customer.

AGENT_WAIT_COUNT

Based on KVP: cse_AgentWaitTotalCount

The number of times the agent(s) waited for a reply from the customer.

AGENT_WAIT_MAX_DURATION

Based on KVP: cse_AgentWaitMaxTime

The maximum time interval, in seconds, the agent(s) spent waiting for a reply from the customer (in other words, the maximum interval between the last response from any agent and the customer's reply). If the agent was waiting for a reply to a set of messages, the wait interval is calculated from the time the last message in the set was sent.

AGENT_WAIT_DURATION

Based on KVP: cse_AgentWaitTotalTime

The total time, in seconds, the agent(s) spent waiting for a reply from the customer. If there were multiple agents on the chat, a time interval is counted only once.

CUSTOMER_REPLY_COUNT

Based on KVP: cse_CustomerReplyTotalCount

The number of times the customer replied to the agent(s).

CUSTOMER_REPLY_MAX_DURATION

Based on KVP: cse_CustomerReplyMaxTime

The maximum time interval, in seconds, the customer spent replying to the agent(s). If the customer was replying to a set of messages, the reply interval is calculated from the time the first message in the set was received.

CUSTOMER_REPLY_DURATION

Based on KVP: cse_CustomerReplyTotalTime

The total time, in seconds, the customer spent replying to the agent(s).

CUSTOMER_WAIT_COUNT

Based on KVP: cse_CustomerWaitTotalCount

The number of times the customer waited for a reply from an agent.

CUSTOMER_WAIT_MAX_DURATION

Based on KVP: cse_CustomerWaitMaxTime

The maximum time interval, in seconds, the customer spent waiting for a reply from an agent. If the customer was waiting for a reply to a set of messages, the wait interval is calculated from the time the last message in the set was sent.

CUSTOMER_WAIT_DURATION

Based on KVP: cse_CustomerWaitTotalTime

The total time, in seconds, the customer spent waiting for a reply from an agent.

UNTIL_FIRST_AGENT_DURATION

Based on KVP: csg_SessionUntilFirstAgentTime

The amount of time, in seconds, the customer waited until the first agent visible to the customer joined the session. An agent is not visible to the customer until the interaction has been successfully routed to and accepted by the agent.

The meaning of a value of 0 (zero) depends on the value of AGENTS_COUNT:

  • If AGENTS_COUNT = 0, no agent ever joined the session.
  • If AGENTS_COUNT > 0, an agent joined very quickly or existed on the session from the start.

UNTIL_FIRST_REPLY_DURATION

Based on KVP: csg_SessionUntilFirstReplyTime

The amount of time since the start of the session, in seconds, until the first agent submits into the chat session the first greeting/message that is visible to the customer.

AGENTS_COUNT

Based on KVP: csg_PartiesAsAgentCount

The number of unique parties that participated in the chat session as agents.

MSG_FROM_BOTS_COUNT

Based on KVP: csg_MessagesFromBotsCount

The total number of messages visible to the customer that were sent by all bots that participated in the chat session.

MSG_FROM_BOTS_SIZE

Based on KVP: csg_MessagesFromBotsSize

The total size of all messages visible to the customer that were sent by all bots that participated in the chat session. The size is expressed as number of characters, including spaces.

UNTIL_FIRST_BOT_DURATION

Based on KVP: csg_SessionUntilFirstBotTime

The amount of time, in seconds, the customer waited until the first bot visible to the customer joined the session.

BOTS_COUNT

Based on KVP: csg_PartiesAsBotCount

The number of unique parties that participated in the chat session as bots.

CHAT_SESSION_DIM_KEY

Based on KVP: csg_SessionEndedBy and csg_SessionEndedReason and csg_LanguageName and csg_MediaOrigin

The surrogate key that is used to join the CHAT_SESSION_DIM dimension to the fact table, to identify typical characteristics of the chat session.

MEDIA_TYPE_KEY

Based on KVP: csg_MediaType

The surrogate key that is used to join the MEDIA_TYPE dimension to the fact tables. The MEDIA_TYPE_KEY references the MEDIA_TYPE dimension record where the value of the KVP matches MEDIA_TYPE.MEDIA_NAME_CODE.

CREATE_AUDIT_KEY

The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools—that is, applications that need to identify newly added data.

UPDATE_AUDIT_KEY

The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools—that is, applications that need to identify recently modified data.


Index List

CODE U C Description

I_CHAT_SESSION_FACT_SDT

Improves access time, based on the Start Date Time key.

Index I_CHAT_SESSION_FACT_SDT

Field Sort Comment
START_DATE_TIME_KEY Ascending

No subject area information available.

 

Feedback

Comment on this article:

blog comments powered by Disqus
This page was last modified on 11 June 2018, at 10:19.