Message Level Data Metrics

In Message Level data, in order to enhance the user’s reporting/analytics experience we have decided to provide custom calculated metrics data for Messages. This approach is implemented through Data Build Tool (DBT) in which first we load all the raw/semi-structured data from the source and then use dbt models to get the silver layer data. Then this silver layer data is used to create consolidated reporting metrics.

This document outlines the custom calculated metrics for Messages. The details of currently developed metrics for Messages data are as follows:

1. Agent Total Messages

Description: This metric gives the count of Total Messages Sent by Each Agent in an hour’s interval irrespective of the message type.

Table Name: agent_total_messages

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent who sent the message.
`sender_name`	Name of the agent who sent the message.
`hour_interval` – consolidate it in 15 minutes	The hour interval in which the messages were sent.
`total_messages_sent`	Count of the total messages sent in the hour interval.

2. Number of Agent Messages in Each Session

Description: This metric gives the count of messages sent by each agent in each session in an hour’s interval.

Table Name: agent_messages_per_session

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`channel_session_id`	Identifies the channel session.
`sender_id`	Identifies the agent who sent the message.
`sender_name`	Name of the agent who sent the message.
`hour_interval`	The hour interval in which the messages were sent.
`messages_in_session`	Count of the total messages sent in the respective session.

3. Total number of Text Messages sent by an Agent

Description: This metric gives the count of Total Messages Sent by Each Agent in an hour’s interval. This only includes messages with type ‘PLAIN’ (or text messages).

Table Name: agent_plain_messages

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent who sent the message.
`sender_name`	Name of the agent who sent the message.
`hour_interval`	The hour interval in which the messages were sent.
`text_message_count`	Count of the total text messages sent by the respective agent.

4. Total Sessions Handled by an Agent per Day

Description: This metric gives the count of Sessions Handled Per Agent Per Day

Table Name: sessions_per_agent_per_day

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent in the respective session.
`sender_name`	Name of the agent in the respective session.
`activity_date`	The date on which the session was handled.
`count_of_sessions_handled`	Count of the total sessions handled by the agent on that date.

5. Total Number of Agent Messages in a respective Channel

Description: This metric gives the count of messages sent by each agent per channel.

Table Name: agent_messages_per_channel

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent in the respective conversation.
`sender_name`	Name of the agent in the respective conversation.
`channel_name`	Name of the channel (e.g., web, fb, instagram).
`hour_interval`	The hour interval in which the messages were sent.
`agent_message_count`	Count of the agent messages in the respective channel.

6. Agent First Messages Time

Description: This metric tells when was the first message sent by the agent on a respective day. This shows that at what time the agent became active on a specific date.

Table Name: agent_first_message_time

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent.
`sender_name`	Name of the agent.
`activity_date`	The date on which the agent was active.
`first_message_time`	The time when the agent sent the first message on the respective date.

7. Agent Last Messages Time

Description: This metric tells when was the last message sent by the agent on a respective day. This shows that at what time the agent became in-active on a specific date.

Table Name: agent_last_message_time

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent.
`sender_name`	Name of the agent.
`activity_date`	The date on which the agent was active.
`last_message_time`	The time when the agent sent the last message on the respective date.

8. Hourly Distribution of Agent Messages

Description: This metric shows the count of agent messages segregated by hour of each day

Table Name: agent_hourly_messages

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent who sent the messages.
`sender_name`	Name of the agent who sent the messages.
`hour_interval`	The hour interval in which the messages were sent.
`hour_message_count`	The total number of messages sent in each hour interval.

9. Sessions with Single Agent Message

Description: This metric gives the count of sessions where the agent only sent one message.

Table Name: agent_single_message_sessions

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`sender_id`	Identifies the agent who sent the messages.
`sender_name`	Name of the agent who sent the messages.
`single_message_count`	The count of sessions with only a single message from the agent.

10. Average Agent Response Time

Description: This metric gives the average time taken in seconds by an agent to respond to customer message in a session.

Table Name: agent_single_message_sessions

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`channel_session_id`	Identifies the channel session.
`sender_id`	Identifies the agent in the session.
`sender_name`	Name of the agent in the session.
`hour_interval`	The hour interval in which the session was held.
`avg_response_time_sec`	The average agent response time in seconds to a customer message.

11. Chat Engagement Ratio

Description: This metric gives the ratio of customer messages to agent messages in a session. If the ratio is 1.0 then the conversation is balanced, if ratio is greater than 1 then customer messages are more than agent messages, similarly if ratio is less than 1 then agent messages are more than customer messages. This helps determine if conversations are balanced or agent or customer dominated.

Table Name: chat_engagement_ratio

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`channel_session_id`	Identifies the channel session.
`agent_id`	Identifies the agent in the session.
`agent_name`	Name of the agent in the session.
`customer_message_count`	Count of messages sent by the customer.
`agent_message_count`	Count of messages sent by the agent.
`engagement_ratio`	The ratio of customer messages to agent messages.

12. Longest Customer Wait Time

Description: This metric gives the longest time delay between a customer message and the next agent reply in a session.

Table Name: longest_customer_wait_time

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`channel_session_id`	Identifies the channel session.
`hour_interval`	The hour interval in which the session was held.
`longest_customer_wait_sec`	The longest wait time in seconds between a customer message and the next agent message in the respective session.

13. Number of Agent Follow Up Messages

Description: This metric gives the count of follow up messages sent by the agent after the customer stopped responding (proactive follow-ups).

Table Name: agent_follow_up_messages

Table Schema: Schema for the metric table in the database is as follows:

Field	Description
`channel_session_id`	Identifies the channel session.
`agent_id`	Identifies the agent in the session.
`agent_name`	Name of the agent in the session.
`hour_interval`	The hour interval in which the session was held.
`total_agent_followup_messages`	The total number of follow-up messages sent by the agent after the customer stopped responding.

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.