Breadcrumbs

Segregate Stats by Channel and Interaction Type

1. Overview

To enhance reporting accuracy and enable more actionable analytics, it is essential to segregate the statistics based on channel and interaction type.

This refinement proposes an Data Pipeline solution to restructure and classify interaction data across different social and communication platforms. The segregation will support downstream systems (e.g., reporting/grafana dashboards, analytics tools) by enabling filters and breakdowns at the interaction-type level.

Required Segregation Categories

The following categories have been identified as key interaction types that must be segregated:

  • Facebook Inbox

  • Facebook Comments

  • Instagram Timeline

  • Instagram Inbox

  • YouTube Comments

  • LinkedIn Direct Messages (DM)

  • LinkedIn Comments

  • Email

Current Messages in the system

Following are the current messages data required to segregate stats by channel:

  • Agent Messages

  • Customer Messages

2. Source Systems and Data

Primary Source System

  • Database Type: MongoDB

  • Database Name: conversation-manager_db

  • Collections:

    • ConversationActivities

    • CustomerTopicEvents

  • Query:

    1. {$or: [{ "activity.name": "AGENT_MESSAGE" },{ "activity.name": "CUSTOMER_MESSAGE" }]}

    2. {"cimEvent.data.body.type": 'COMMENT'}

Example payloads:

Comment Type Message: 

"body": {
        "type": "COMMENT",
        "markdownText": "aGVsbG8gbmV3IGNvbW1lbnRz",
        "postId": "106943938219327_799974918928392",
        "attachment": null,
        "itemType": "TEXT"
      }

DM Type Message: 

"body": {
        "type": "PLAIN",
        "markdownText": "hy"
      }

3. Dashboards Requirements and KPI Definitions

The following dashboards and key performance indicators (KPIs) are required to support analysis based on the newly segregated interaction data.

  1. Social Media Activity Reasons

Required KPIs

Source

Description

Computation Strategy

Filtration Criteria

Metrics

Major Activity Reasons - Complaints

wrapup_detail

Wrap-up count with respect to categories - Complaints.

This data could be segregated based on category_name field.

category_name = Complaints

Major Activity Reasons

Major Activity Reasons - Information

wrapup_detail

Wrap-up count with respect to categories - Information.

This data could be segregated based on category_name field.

category_name = Information

Major Activity Reasons

  1. Social Media Dashboard - May 2025

Required KPIs

Source

Description

Computation Strategy

Filtration Criteria

Metrics

Total SM Unit Activity


  • This KPI represents the total volume of activities across all Social Media platforms, including messaging (e.g., direct messages), comments, and timeline posts.

Activities are (Messages, Conversations, Wrapups)

  1. For Messages
    Count of agent and customer messages including Comments and DM for all social media channels.

  2. For Conversations
    Count of CX conversations for all social media channels.

  3. For Wrapups
    Count of wrap-up events associated with social media conversations.

  1. Activities considered: Messages, Conversations, and Wrapups

  2. Channels considered: All Social Media platforms (e.g., Facebook, Instagram, LinkedIn, YouTube)

  1. Total SM Unit Activity - Messages

  2. Total SM Unit Activity - Conversations

  3. Total SM Unit Activity - Wrapups

  4. Total SM Unit Activity ( Messages + Conversations + Wrapups)

Facebook Activity

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total interaction volume specifically on the Facebook channel. It includes activities across different interaction types such as inbox messages and comments.

  1. Messages:

    • Count all agent and customer messages from Facebook Inbox (DMs).

    • Count all Facebook Comments (on posts, ads, etc.).

  2. Conversations:

    • Count of distinct conversations on Facebook, whether initiated by the customer or agent (INBOUND/OUTBOUND).

  3. Wrapups:

    • Count of wrapup entries related to Facebook channel conversations.

  1. CimEvent.channelSession.channelType.name= FACEBOOK

  2. Activity Type ∈ {Messages, Conversations, Wrapups}

  3. Further classified into:

    • Facebook Inbox all the messages except comments.

    • Facebook Comments as {"cimEvent.data.body.type": 'COMMENT'}

  1. Facebook Activity - Messages

  2. Facebook Activity - Conversations

  3. Facebook Activity - Wrapups

Twitter Activity

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total interaction volume specifically on the Twitter channel. It includes activities across different interaction types such as inbox messages and comments.

  1. Messages:

    • Count all agent and customer messages from Twitter Inbox (DMs).

    • Count all Twitter Comments (on posts, ads, etc.).

  2. Conversations:

    • Count of distinct conversations on Twitter , whether initiated by the customer or agent (INBOUND/OUTBOUND).

  3. Wrapups:

    • Count of wrapup entries related to Twitter channel conversations.

  1. CimEvent.channelSession.channelType.name= TWITTER

  2. Activity Type ∈ {Messages, Conversations, Wrapups}

  3. Further classified into:

    • Twitter Inbox all the messages except comments.

    • Twitter Comments as {"cimEvent.data.body.type": 'COMMENT'}

  1. Twitter Activity - Messages

  2. Twitter Activity - Conversations

  3. Twitter Activity - Wrapups

Instagram Activity

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total interaction volume specifically on the Instagram channel. It includes activities across different interaction types such as inbox messages and comments.

  1. Messages:

    • Count all agent and customer messages from Instagram Inbox (DMs).

    • Count all Instagram Comments (on posts, ads, etc.).

  2. Conversations:

    • Count of distinct conversations on Instagram , whether initiated by the customer or agent (INBOUND/OUTBOUND).

  3. Wrapups:

    • Count of wrapup entries related to Instagram channel conversations.

  1. CimEvent.channelSession.channelType.name= INSTAGRAM

  2. Activity Type ∈ {Messages, Conversations, Wrapups}

  3. Further classified into:

    • Instagram Inbox all the messages except comments.

    • Instagram Comments as {"cimEvent.data.body.type": 'COMMENT'}

  1. Instagram Activity - Messages

  2. Instagram Activity - Conversations

  3. Instagram Activity - Wrapups

Youtube Activity

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total interaction volume specifically on the Youtube channel. It includes activities across different interaction types such as inbox messages and comments.

  1. Messages:

    • Count all agent and customer messages from Youtube Inbox (DMs).

    • Count all Youtube Comments (on posts, ads, etc.).

  2. Conversations:

    • Count of distinct conversations on Youtube, whether initiated by the customer or agent (INBOUND/OUTBOUND).

  3. Wrapups:

    • Count of wrapup entries related to Youtube channel conversations.

  1. CimEvent.channelSession.channelType.name= YOUTUBE

  2. Activity Type ∈ {Messages, Conversations, Wrapups}

  3. Further classified into:

    • YOUTUBE Inbox (all the messages except comments).

    • YOUTUBE Comments as {"cimEvent.data.body.type": 'COMMENT'}

  1. Youtube Activity - Messages

  2. Youtube Activity - Conversations

  3. Youtube Activity - Wrapups

SM Unit

wrapup_detail

  1. Wrap-up count with respect to categories - Complaints.

  2. Wrap-up count with respect to categories - Information.

  1. This data could be segregated based on category_name field.

  2. This data could be segregated based on category_name field.

  1. category_name = Complaints

  2. category_name = Information

Major Activity Reasons

Facebook

  1. wrapup_detail

  2. channel_session

  3. channel

  1. Wrap-up count with respect to categories for Facebook channel - Complaints.

  2. Wrap-up count with respect to categories for Facebook channel - Information.

  1. This data could be segregated based on category_nameas Complaints and Information field.

  2. wrapup_detail has a field conversation_id from which we can get the channel information from channel session table.

  3. Channel Name must be FACEBOOK.

  1. category_name = Complaints

  2. category_name = Information

  3. From wrapup_detail table get --> conversation_id .

  4. From channel_session table get→ channel_session_id

  5. From channel_session get→ channel_id

  6. From channel table get → channel_name=Facebook

  1. Facebook Activity - Wrapups

Twitter

  1. wrapup_detail

  2. channel_session

  3. channel

  1. Wrap-up count with respect to categories for Twitter channel - Complaints.

  2. Wrap-up count with respect to categories for Twitter channel - Information.

  1. This data could be segregated based on category_nameas Complaints and Information field.

  2. wrapup_detail has a field conversation_id from which we can get the channel information from channel session table.

  3. Channel Name must be Twitter.

  1. category_name = Complaints

  2. category_name = Information

  3. From wrapup_detail table get --> conversation_id .

  4. From channel_session table get→ channel_session_id

  5. From channel_session get→ channel_id

  6. From channel table get → channel_name=Twitter

  1. Twitter Activity - Wrapups

Instagram

  1. wrapup_detail

  2. channel_session

  3. channel

  1. Wrap-up count with respect to categories for Twitter channel - Complaints.

  2. Wrap-up count with respect to categories for Twitter channel - Information.

  1. This data could be segregated based on category_nameas Complaints and Information field.

  2. wrapup_detail has a field conversation_id from which we can get the channel information from channel session table.

  3. Channel Name must be Instagram.

  1. category_name = Complaints

  2. category_name = Information

  3. From wrapup_detail table get --> conversation_id .

  4. From channel_session table get→ channel_session_id

  5. From channel_session get→ channel_id

  6. From channel table get → channel_name=Instagram

  1. Instagram Activity - Wrapups

Youtube/Live Chat

  1. wrapup_detail

  2. channel_session

  3. channel

  1. Wrap-up count with respect to categories for Twitter channel - Complaints.

  2. Wrap-up count with respect to categories for Twitter channel - Information.

  1. This data could be segregated based on category_nameas Complaints and Information field.

  2. wrapup_detail has a field conversation_id from which we can get the channel information from channel session table.

  3. Channel Name must be Youtube.

  1. category_name = Complaints

  2. category_name = Information

  3. From wrapup_detail table get --> conversation_id .

  4. From channel_session table get→ channel_session_id

  5. From channel_session get→ channel_id

  6. From channel table get → channel_name=Youtube

  1. Youtube Activity - Wrapups

Facebook Activity Type






Instagram Activity Type






FB Inbox

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total inbox messages volume specifically on the Facebook channel. It includes activities across interaction type as inbox.

  1. Messages:

    • Count all agent and customer messages from Facebook Inbox (DMs).

  1. Further classified into:

    • Facebook Inbox (all the messages except comments).

  1. Facebook Activity - Messages

FB Comment

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total comment messages volume specifically on the Facebook channel. It includes activities across interaction type as comment.

  1. Messages:

    • Count all Facebook Comments (on posts, ads, etc.).

  1. Further classified into:

    • Facebook Comments as {"cimEvent.data.body.type": 'COMMENT'}

  1. Facebook Activity - Messages

Instagram Timeline

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total posts volume specifically on the Instagram channel.

  1. Messages:

    • Count all Instagram comment_post_id of agents and customers.

  1. Check for conversation messages where message_type=COMMENTand fetch the data from comment_post_id column in agent_messages_silverand customer_messages_silver

  1. Instagram Activity - Timelines

Instagram Inbox

  1. agent_messages_silver

  2. customer_messages_silver

  3. conversations

This KPI measures the total inbox messages volume specifically on the Instagram channel. It includes activities across interaction type as inbox.

  1. Messages:

    • Count all agent and customer messages from Instagram Inbox (DMs).

    • Count all Instagram Comments (on posts, ads, etc.).

  1. Further classified into:

    • Instagram Inbox (all the messages except comments).

  1. Instagram Activity - Messages







  1. Social Media Dashboard

Required KPIs

Source

Description

Computation Strategy

Filtration Criteria

Metrics

T Activity - SM Unit/Facebook/Twitter/Instagram


  • This KPI represents the total volume of activities across all Social Media platforms, including messaging (e.g., direct messages), comments, and timeline posts and wrapups.

Activities are (Messages, Conversations, Wrapups)

  1. For Messages
    Count of agent and customer messages including Comments and DM for all social media channels.

  2. For Conversations
    Count of CX conversations for all social media channels.

  3. For Wrapups
    Count of wrap-up events associated with social media conversations.

  1. Channel based - individual

    1. Channels considered: All Social Media platforms (e.g., Facebook, Instagram, LinkedIn, YouTube)

  2. Total Activities for all channel

    1. Activities considered: Messages, Conversations, and Wrapups

Facebook

  1. Facebook Activity - Messages

  2. Facebook Activity - Conversations

  3. Facebook Activity - Wrapups

Twitter

  1. Twitter Activity - Messages

  2. Twitter Activity - Conversations

  3. Twitter Activity - Wrapups

Instagram

  1. Instagram Activity - Messages

  2. Instagram Activity - Conversations

  3. Instagram Activity - Wrapups

Total Activities

  1. Total SM Unit Activity ( Messages + Conversations + Wrapups)

Complaints

  1. wrapup_detail

Wrap-up count with respect to categories - Complaints.

This data could be segregated based on category_name field.

category_name = Complaints

Major Activity Reasons

Information

  1. wrapup_detail

Wrap-up count with respect to categories - Information.

This data could be segregated based on category_name field.

category_name = Information

Major Activity Reasons

Request

  1. wrapup_detail

Wrap-up count with respect to categories - Request.

This data could be segregated based on category_name field.

category_name = Request

Major Activity Reasons

Share in activities






Activity Type






Weekly Overview - Complaints vs Information






  1. Social Media Sentiment Overview

Required KPIs

Source

Description

Facebook Comment



Facebook Inbox



Instagram Timeline



Punjab


Not available in CX

Sindh


Not available in CX

KPK


Not available in CX

Balochistan


Not available in CX

Federal Capital Territory


Not available in CX