Transformation Rules - ETL Pipelines

This document outlines the transformation rules and source-to-target data mappings for ETL pipelines.

Explanation of Columns

Target Field
- Represents the field name in the target system where the transformed data will be stored.
Source Field (MongoDB)
- Specifies the corresponding field in MongoDB from which the data is extracted.
Source Data Type
- Defines the data type of the source field in MongoDB (e.g., String, Integer, DateTime).
Field Type
- Indicates whether the field is an Existing Field or a Custom Field.
  - Existing Field: An existing field is a field whose data is directly available in the source (MongoDB) and is only flattened by our ETL.
  - Custom Field: A custom field is a field that has some custom logic calculated by our ETL and is added for specific business needs.
Transformation Rules
- Describes the logic applied to convert the source data into the target format.

For Further Transformation Details or Database Schema refer to this document: Reporting Database Schema

ETL Pipelines - Transformation Rules

Configurational ETL Pipelines:

Agent

This manages the records of an agent in the system. The job handles the insertion and updating of agent details, ensuring that each agent's details are correctly maintained in the database. Additionally, it updates the is_deleted column to TRUE if an agent is deleted from the source.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: routing-engine-db.agents
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
agent_id	`_id`	String	Existing	Unique id to identify this agent.
agent_name	`keycloakUser.firstName`	String	Existing	Records the name of the agent.
username	`keycloakUser.username`	String	Existing	Records the username of the agent.
agent_extension	`keycloakUser.attributes.agentExtension`	Array of Strings	Existing	Records the agent extensions set in the system. Stored as an array for each agent extension.
team_id	`keycloakUser.userTeam.teamId`	String	Existing	Identifies the team/teams the agent is part of.
created_at			Custom	Records the date and time of when the agent was created.
updated_at			Custom	Records the date and time of when the agent was last updated.
is_deleted			Custom	Checks if the agent is deleted from the backend or not (soft deletion). If the agent is deleted this sets to ‘TRUE’ else it is ‘FALSE’.

Bot

Bot manages the records of bot/bots configured in the system. This handles the insertion and updating of bot details, ensuring that each bot's details are correctly maintained in the database. One bot's details are added as a single record in the target table.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: bot-framework_db.botConnectors
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
bot_id	`_id`	Object Id	Existing	Unique id for the respective bot
bot_name	`name`	String	Existing	Records the name of the bot.
created_at			Custom	Records the date and time when the bot is created.
updated_at			Custom	Records the date and time when the bot is updated.
is_deleted			Custom	Indicates the bot has been deleted from the system.(Used for soft deletion)

Channel Type

A channel type describes the type/class of the channels, for example, WHATSAPP, SMS, WEB. Multiple channels of a channel type can be created.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: ccm_db.ChannelType
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
channel_type_id	`_id`	Object Id	Existing	Unique id to identify this channel type.
channel_type_name	`name`	String	Existing	Records the name for this channel type, any alphanumeric value.

Channel

A channel instance is created when a new communication channel is registered by the system. It includes all the configuration details of a channel, such as the channel name, service identifier, channel mode, etc. A channel is of a particular channel type. For instance, we can create two channels WhatsApp-external and WhatsApp-internal, both of a channel type WHATSAPP.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: ccm_db.Channel
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
channel_id	`_id`	Object Id	Existing	Unique id to identify the channel.
channel_name	`name`	String	Existing	Records the name of the channel.
channel_type_id	`channelTypeEntity`	Array	Existing	Identifies the channel type for the channel.
channel_mode	`channelConfig.channelMode`	String	Existing	Indicates the channel mode configured for this channel. This can be: HYBRID - Both the bot and the human agents are engaged on communication with the customer. BOT - Only bot is engaged in communication with the customer. AGENT - Only human agents are engaged in communication with the customer.
service_identifier	`serviceIdentifier`	String	Existing	Unique identifier like a phone number or URL associated to this channel.
created_at			Custom	Records the date and time when this record is created.
updated_at			Custom	Records the date and time when this record is updated.
is_deleted			Custom	Indicates that this channel has been deleted from the system. (Used for soft deletion)

Queue

Queue manages the properties of a routing queue. Whenever a human agent is required for a conversation, a request to assign an agent is created by the system and offered to a routing queue. The properties of a single routing queue are inserted as a single record in this table. Learn more about queues here: Precision Routing

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: routing-engine_db.precisionQueues
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
queue_id	`_id`	Object Id	Existing	Unique Id to identify this queue.
queue_name	`name`	String	Existing	Records the queue name, any alphanumeric string.
sl_type	`serviceLevelType`	Int	Existing	Service level type can have the following values: 1 = Ignore Abandoned Chats 2 = Abandoned Chats have a Negative Impact 3 = Abandoned Chats have a Positive Impact
sl_threshold	`serviceLevelThreshold`	Int	Existing	Indicates the number of seconds in which a request must be routed to an agent.
created_at			Custom	Records the date and time when this record is created.
updated_at			Custom	Records the date and time when this record is updated.
is_deleted			Custom	Indicates that the queue has been deleted from the system.(Used for soft deletion)

Media Routing Domain

Media Routing Domains (MRDs) are configurations that define the routing of media types (e.g., voice, chat, email) within the contact center system. Learn more about MRDs here: Channel Categories (Media Routing Domains)

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: routing-engine_db.mediaRoutingDomains
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
mrd_id	`_id`	String	Existing	Unique Id to identify this MRD.
mrd_name	`name`	String	Existing	Records the MRD name, any alphanumeric string.
description	`description`	String	Existing	Short description of the media routing domain.
max_task_request	`maxRequests`	Int	Existing	This is the max tasks that the MRD can receive.
is_interruptible	`interruptible`	Boolean	Existing	Tells whether a conversion of a specific media domain can be interrupted at the agent's end by a higher priority conversation. For instance, a chat conversation on WhatsApp can be interrupted by a voice call. On the contrary an ongoing voice call cannot be interrupted by a chat request.
is_managed_by_re			Existing	When the value is true, it indicates that the Routing Engine is responsible for managing this MRD. Conversely, if the value is false, it means that other sources, such as Cisco, are in charge of managing this specific MRD.
created_at			Custom	Records the date and time when this record is created.
updated_at			Custom	Records the date and time when this record is updated.
is_deleted			Custom	Indicates that the mrd has been deleted from the system.(Used for soft deletion)

List

List records the properties of a routing queue. Whenever a human agent is required for a conversation, a request to assign an agent is created by the system and offered to a precision queue.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: adminPanel.pullmodelists
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
list_id	`_id`	Object Id	Existing	Unique identifier for the list.
list_name	`name`	String	Existing	Name of the list.
created_at			Custom	Records the date and time when this record is created.
updated_at			Custom	Records the date and time when this record is updated.
is_deleted			Custom	Indicates that the list has been deleted from the system.(Used for soft deletion)

MRD Type

MRD Type records the relevant data for an MRD configured in the system.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: routing-engine_db.mrdTypes
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
mrd_type_id	`_id`	Object Id	Existing	Unique Id to identify this MRD type.
mrd_type_name	`name`	String	Existing	Records the MRD type name, any alphanumeric string.
is_managed_by_re	`managedByRe`	Boolean	Existing	When the value is true, it indicates that the Routing Engine is responsible for managing this MRD. Conversely, if the value is false, it means that other sources, such as Cisco, are in charge of managing this specific MRD.
is_auto_join	`autoJoin`	Boolean	Existing
is_interruptible	`interruptible`	Boolean	Existing	Tells whether a conversion of a specific media domain can be interrupted at the agent's end by a higher priority conversation. For instance, a chat conversation on WhatsApp can be interrupted by a voice call. On the contrary an ongoing voice call cannot be interrupted by a chat request.
created_at			Custom	Records the date and time when this record is created.
updated_at			Custom	Records the date and time when this record is updated.
is_deleted			Custom	Indicates that this mrd type has been deleted from the system. (Used for soft deletion)

Transactional ETL Pipelines:

Conversation

A conversation entity records the customer's conversation details. A conversation consists of all channel sessions where a customer is conversing, the conversation participants (bots, agents, customers) and optionally also some conversation data.

See more about Conversations on Conversation Objects Unique id to identify this conversation

This table only records the essential conversation fields. There are separate tables for conversation data and conversation participants.

Extraction Details

Purpose: Uses an API of the historical reports manager component to fetch the required data.
Mongo DB Source:
- conversation-manager_db.conversations
- Query:
  CODE
```
db["conversations"].find()
```
API URL: <FQDN>/historical-reports/stats/conversation?endTime=1900-01-01T00:00:00.000Z&limit=100
Component Description: Makes a REST API call to fetch conversation data from the historical reports manager component.

Target Field	Source Field ( API / MongoDB )	Source Data Type	Field Type	Transformation Rules
conversation_id	`_id`	ObjectId	Existing	Records the conversation id of the system. Directly assigned from the conversation entity.
room_id (reserved for future)	`none`		Existing	Identifies the room this conversation is a part of. Currently, null since a conversation is not a part of any room.
customer_id	`customer._id`	ObjectId	Existing	Unique identifier of the customer associated with the conversation.
customer_name	`customer.firstName`	String	Existing	Records the customer name with whom this conversation is associated.
start_time	`creationTime`	Date	Existing	Records the date and time when this conversation was started.
end_time	`endTime`	Date	Existing	Records the date and time when this conversation was ended.
conversation_duration	`conversationDuration`	[INT]	Custom	Stores the total duration of the conversation, calculated as the difference between `end_time` and `start_time`.
bot_id	`botId`	String	Existing	Identifier of the bot who was a participant of this conversation.
task_state	`taskState`	String	Custom	Extract all `TASK_STATE_CHANGED` events that match the given `conversationId`. Records the state of conversation i.e. ACTIVE, CLOSED Sort events in descending order based on the timestamp to process the latest event first. Retrieve the latest task event `CimEvent.name: TASK_STATE_CHANGED` for that conversation. Extract the `state` field from the event's JSON data which is `CimEvent.data.task.state.name`.
reason_code	`reasonCode`	String	Custom	Extract all `TASK_STATE_CHANGED` events that match the given `conversationId`. Records the reason code of conversation i.e. DONE, AGENT_LOGOUT, CANCELED, NO_AGENT_AVAILABLE, RONA, NULL. see more : Task Reason Codes. Sort events in descending order based on the timestamp to process the latest event first. Retrieve the latest task event `CimEvent.name: TASK_STATE_CHANGED` for that conversation. Extract the `reasonCode`field from the event's JSON data which is `CimEvent.data.task.state.reasonCode`.
disposition	`disposition`	String	Custom	Extract all task-related events that match the given `conversationId`. Sort events in descending order based on the timestamp to process the latest event first. Retrieve the latest task event. Extract the `state` field from the event's JSON data. Set the `taskState` and `reasonCode` in `ConversationSummary`. This is the reason why the conversation was closed. The details can be referenced from the document Task Reason Codes If no matching task events are found, classify as `BOT_HANDLED`. If an error occurs during processing, return `null`.
direction	`conversationDirection`	String	Existing	Records the direction of conversation i.e. INBOUND/OUTBOUND. There can be multiple channel sessions (INBOUND/OUTBOUND) in a conversation. We'll consider the conversation's direction based on the first channel session's direction in a conversation.

Conversation Data

A conversation can have key-value pairs to record any arbitrary additional data required. This is known as the conversation data. A single key-value pair for a conversation is inserted as one record in this table.

See Conversation Data for more details.

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: conversation-manager_db.conversations

MongoDB Query:

CODE

{ "$and": [ { "conversationData": { "$exists": true, "$type": "object", "$ne": {} } } ] }

Field	Source Field ( API / MongoDB )	Source Data Type	Field Type	Transformation Rules
conversation_id	`_id`	ObjectId	Existing	Identifies the conversation for which this conversation data record is inserted.
key	`key`	String	Custom	Any alphanumeric value from conversationData Object.
value	`value`	String	Custom	Any alphanumeric value from conversationData Object.
record_creation_time	`endTime`	Date	Existing	Records the date and time when this conversation was ended.

Conversation Participant

Conversation participants are the participants that take part in a conversation. For example , customers, bots and agents. Each record in the table represents a Conversation Participant.

Extraction Details

Purpose: Uses an API of the historical reports manager component to fetch the required data.
Source:
- conversation-manager_db.CustomerTopicEvents
MongoDB Query:

CODE

{
  "cimEvent.name": { $in: ["CHANNEL_SESSION_STARTED", "AGENT_SUBSCRIBED", "BOT_SUBSCRIBED"]}
}

API URL: <FQDN>/historical-reports/stats/conversation/participant?endTime=<last_sync_time>&limit=100
Component Description: Makes a REST API call to fetch conversation participant data from the historical reports manager component.

Target Field	Source Field ( API / MongoDB )	Source Data Type	Field Type	Transformation Rules
id	`Auto-Generated`	[nvarchar](50)	Custom	A unique identification of a participation by agent/customer/bot etc.
participant_id	`participantId`	String	Existing	Unique id (Customer ID/ Bot ID / Agent ID) to identify a participant. `taskEvents` name: `CHANNEL_SESSION_STARTED` `AGENT_SUBSCRIBED` `BOT_SUBSCRIBED` Extracted from `taskEvents → agentParticipant.id` (for agents), `taskEvents → id` (for bots), and `taskEvents → customerId` (for customers). The participant ID is assigned based on the event type: - If `CimEvent.name = CHANNEL_SESSION_STARTED`, extract `customerId` as `CUSTOMER`. - If `CimEventName = AGENT_SUBSCRIBED`, extract `agentParticipant.id` as `AGENT`. - If `CimEventName = BOT_SUBSCRIBED`, extract `id` as `EXTERNAL`.
conversation_id	`conversationId`	String	Existing	Identifies the conversation this participant is part of. Directly assigned from the conversation entity.
participant_role (AGENT \| CUSTOMER \| EXTERNAL)	`participantRole`	String	Custom	Indicates the role of a participant in the conversation AGENT - If the participant is an agent CUSTOMER - If the participant is one of the channel sessions that a customer is conversing on. EXTERNAL - Other participants like a bot etc. Derived from `taskEvents` based on event type Role is determined as follows: - `"CUSTOMER"` if event type is `CHANNEL_SESSION_STARTED`. - `"AGENT"` if event type is `AGENT_SUBSCRIBED`. - `"EXTERNAL"` if event type is `BOT_SUBSCRIBED`.
record_creation_time	`recordCreationTime`	Timestamp	Existing	Assigned based on `endTime` of the conversation.

Conversation Hold Resume

This records the details and properties of the conversations that were put on hold and later resumed capturing key details such as the time of hold and resume events, and the agent or channel session interaction involved.

Extraction Details

Purpose: Extracts data from MongoDB collection based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.CustomerTopicEvents
MongoDB Query: { 'cimEvent.name': 'CALL_HOLD' }

Target Field	Source Field ( API / MongoDB )	Source Data Type	Field Type	Transformation Rules
conversation_id	`cimEvent.conversationId`	String	Existing	Unique identifier for the conversation, linking it to the main conversation table.
conversation_state	`cimEvent.name`	String	Custom	Indicates the state of the conversation (e.g., HOLD, RESUME). If cimEvent.name = “CALL_HOLD”, then the conversation state is set to “HOLD”. If cimEvent.name = “CALL_RESUME”, then the conversation state is set to “RESUME”.
agent_id	`cimEvent.data.header.sender.id`	String	Existing	Unique identifier to identify the agent part of the conversation.
event_timestamp	`timestamp`	Date	Existing	Timestamp of the hold or resume event in the conversation.
channel_session_id	`cimEvent.channelSession._id`	Object Id	Existing	Identifies the channel session for the conversation.

Agent Task

This records the details of specific agent tasks. Agent task can contain details about task queue time, task end time, task state, direction, routing mode etc.

Purpose: The purpose of this job is to compute agent tasks. During a conversation, when a task is RESERVED for an agent, an agent is assigned to that conversation. The system processes various task states and task media states to compute agent tasks. The task states include ACTIVE, WRAP_UP, and CLOSED, while the task media states include QUEUED, RESERVED, ACTIVE, and CLOSED.
Learn more about Agent Task : Agent Task/Routing Task

This job has multiple sub jobs:

For PUSH mode:

PUSH Task - QUEUED
PUSH Task - RESERVED
PUSH Task - ACTIVE
PUSH Task - CLOSED

For PULL mode:

PULL Task - ACTIVE
PULL Task - CLOSED

Extraction Details

Purpose: Extracts agent task data from MongoDB.
Source: MongoDB source to extract data is as follows: conversation-manager_db.CustomerTopicEvents.
MongoDB Query: Each child ETL has different MongoDB query(s) to extract the data based on their routing mode and task media state.

For PUSH Task - QUEUED

CODE

{
  $and: [{ 'cimEvent.name': 'TASK_STATE_CHANGED' },
    {
      'cimEvent.data.task.activeMedia': {
        $elemMatch: {
          'state': 'QUEUED'
        }
      }
    },{ 'cimEvent.data.task.activeMedia.0.requestSession.channel.channelConfig.routingPolicy.routingMode': 'PUSH'}
  ]
}

For PUSH Task - RESERVED

CODE

{
  $and: [{ 'cimEvent.name': 'TASK_STATE_CHANGED' },
    {
      'cimEvent.data.task.activeMedia': {
        $elemMatch: {
          'state': 'RESERVED'
        }
      }
    },{ 'cimEvent.data.task.activeMedia.0.requestSession.channel.channelConfig.routingPolicy.routingMode': 'PUSH'}
  ]
}

For PUSH Task - ACTIVE

CODE

{
  $and: [{ 'cimEvent.name': 'TASK_STATE_CHANGED' },
    {
      'cimEvent.data.task.activeMedia': {
        $elemMatch: {
          'state': 'ACTIVE'
        }
      }
    },{ 'cimEvent.data.task.activeMedia.0.requestSession.channel.channelConfig.routingPolicy.routingMode': 'PUSH'}
  ]
}

For PUSH Task - CLOSED

CODE

{
  $and: [{ 'cimEvent.name': 'TASK_STATE_CHANGED' },
    {
      'cimEvent.data.task.activeMedia': {
        $elemMatch: {
          'state': 'CLOSED'
        }
      }
    },{ 'cimEvent.data.task.activeMedia.0.requestSession.channel.channelConfig.routingPolicy.routingMode': 'PUSH'}
  ]
}

For PULL Task - ACTIVE

CODE

{
  $and: [{ 'cimEvent.name': 'TASK_STATE_CHANGED' },
    {
      'cimEvent.data.task.activeMedia': {
        $elemMatch: {
          'state': 'ACTIVE'
        }
      }
    },{ 'cimEvent.channelSession.channel.channelConfig.routingPolicy.routingMode': 'PULL'}
  ]
}

For PULL Task - CLOSED

CODE

{
  $and: [{ 'cimEvent.name': 'TASK_STATE_CHANGED' },
    {
      'cimEvent.data.task.activeMedia': {
        $elemMatch: {
          'state': 'CLOSED'
        }
      }
    },{ 'cimEvent.channelSession.channel.channelConfig.routingPolicy.routingMode': 'PULL'}
  ]
}

Target Field	Source Field ( MongoDB )	Source Data Type	Field Type	Transformation Rules
agent_task_id	`_id`	Object Id	Existing	Unique id to identify this task.
conversation_id	`cimEvent.conversationId`	String	Existing	Identifies the conversation this task is created for.
agent_id	`cimEvent.data.task.assignedTo.id`	String	Existing	Identifies the agent which was assigned to this task. If the task was ended while in the QUEUED state then this field is NULL.
task_queue_time	`timestamp`	DATETIME	Existing	Records the date and time when the task is offered to a routing queue. This field is NULL in following cases when queue is not involved: If the task routing is skipped and the task is directly assigned to the agent, like in the case of pull mode routing. If agent has initiated the chat i.e. OUTBOUND chat.
task_reserved_time	`timestamp`	DATETIME		Records the date and time when an agent is reserved for this task. This field is NULL in following cases : If the task is directly assigned to the agent, like in the case of pull mode routing. If agent has initiated the chat i.e. OUTBOUND chat. If the task was ended while in the queued state i.e. for PUSH mode routing.
task_answered_time	`timestamp`	DATETIME	Existing	Records the date and time when the reserved agent accepts the task. This field is NULL in the following cases : If the task was closed while in the queued state. If the task was presented to the agent but the agent did not accept it.
queue_id	`cimEvent.data.task.activeMedia.queue.id`	String	Existing	Identifies the queue in which this task is enqueued for routing. This field is NULL in following cases : In case where queue routing is skipped and the task is directly assigned to an agent like in the case of pull mode routing. If agent has initiated the chat i.e. OUTBOUND chat.
list_id	`cimEvent.channelSession.channel.channelConfig.routingPolicy.routingObjectId`	String	Existing	Identifies the routing list/queue.
task_end_time	`timestamp`	DATETIME	Existing	Records the date and time when the task was ended/closed.
task_disposition	`cimEvent.data.task.state.reasonCode`	String	Existing	This is basically the reason why the task was ended. The reason could be one of the following. DONE RONA RESPONSE_TIMEOUT NO_AGENT_AVAILABLE REROUTE CANCELLED AGENT_LOGOUT TRANSFERRED FORCE_CLOSED.
record_creation_time	`recordCreationTime`	Date	Existing	Records the date and time when the record is created in source DB i.e. MongoDB
mrd_id	`cimEvent.data.task.activeMedia.mrdId`	String	Existing	Unique Id to identify the MRD through which this task is created.
customer_id	`cimEvent.channelSession.customer._id`	Object Id	Existing	Unique identifier of the customer this task is created for.
customer_name	`cimEvent.channelSession.customer._firstName`	String	Existing	Name of the customer this task is created for.
task_state_name	`cimEvent.data.task.state.name`	String	Existing	This records the state of the task. The state can be: ACTIVE if the task is in progress. WRAP_UP if the task is in wrap-up state before ending. CLOSED if the task is closed/completed.
task_media_state	`cimEvent.data.task.activeMedia.type.state`	String	Existing	This records the media state of the task. The state can be: QUEUED : If the task media is in the queue. RESERVED: if the task is in the reserved. ACTIVE: If the task media is in the active. CLOSED: If the task media is in the closed.
task_direction	`cimEvent.channelSession.channelSessionDirection`	String	Existing	This records the direction how the task was created. The directions can be one of the following. INBOUND OUTBOUND CONSULT CONSULT_TRANSFER CONSULT_CONFERENCE DIRECT_TRANSFER DIRECT_CONFERENCE
task_mode	`cimEvent.channelSession.channel.channelConfig.routingPolicy.routingMode`	String	Existing	This records the mode how the task was created. The mode can be one of the following. AGENT: Directly assigned to an agent. QUEUE: Task pushed to queue to route to any agent.
routing_mode	`cimEvent.data.task.activeMedia.0.requestSession.channel.channelConfig.routingPolicy.routingMode`	String	Existing	This records one of the following routing modes. PUSH: The incoming request was pushed to a queue to be routed to agents. PULL: Broadcasted to a Pull-based List to be taken up by any agents subscribed to the List.
channel_session_id	`cimEvent.channelSession.id`	Object Id	Existing	A unique identifier for channel session associated with this task.

Channel Session

A channel session instance is created when a customer's new chat session is started on a particular channel, such as WhatsApp or SMS. Each chat session on different channels results in a new channel session instance. The channel session instance ends when the chat is over on the associated channel. A customer can converse on multiple channels at a time in one conversation.

Extraction Details

Purpose: Uses an API of the historical reports manager component to fetch the required data.
Source: API
API URL: https://<FQDN>//historical-reports/stats/channelSession?endTime=<channel_session_last_sync>&limit=<context.batch_size>
Component Description: This retrieves conversation events (like start, end, messages) using a helper method. It limits the query by endTime and limit, and filters by specific event types:
- CHANNEL_SESSION_STARTED
- CHANNEL_SESSION_ENDED
- CUSTOMER_MESSAGE
- BOT_MESSAGE
- AGENT_MESSAGE
Collection: conversation-manager_db.CustomerTopicEvents
Mongo DB Query: {"cimEvent.name": {"$in": ["CHANNEL_SESSION_STARTED", "CHANNEL_SESSION_ENDED"]}}

Target Field	Source Field (API/MongoDB)	Source Data Type	Field Type	Transformation Rules
session_id	`channelSessionId`	String	Existing	Unique id to identify this channel session.
conversation_id	`conversationId`	String	Existing	Records the conversation id of the channel session. Directly assigned from the conversation entity.
channel_id	`channelId`	String	Existing	Identifies the channel, this channel session is associated to.
channel_customer_identifier	`cimEvent.channelSession.channelData.customerChannelIdentifier`	String	Existing	Channel customer identifier extracted from `CHANNEL_SESSION_STARTED` & `CHANNEL_SESSION_ENDED` CIM events.
start_time	`cimEvent.timestamp`	DateTime	Existing	Filters the matching event in `CHANNEL_SESSION_STARTED`. Records the date and time when this channel session was started.
end_time	`cimEvent.timestamp`	DateTime	Existing	Filters the matching event in `CHANNEL_SESSION_ENDED`. Records the date and time when this channel session was ended.
total_session_duration		Int	Custom	Stores the total duration of the channel session, calculated as the difference between `end_time` and `start_time`.
disposition	`cimEvent.channelSession.state.reasonCode`	String	Existing	Indicates the reason why the channel session was closed. AGENT - the human agent ended the channel session. This is valid in case of Pull-based chats from the list by clicking the "Close" button. CUSTOMER - the customer ended the session. This happens in case when the web chat widget is closed by the customer. Only true for web chat channels. INACTIVITY - the customer was inactive for a configured timeout (timeout is managed by the Bot). NETWORK - the conversation ended due to some network error usually in case when the network is disconnected. FORCE_CLOSED - the system ends the channel session.
bot_acitivity_count	`botMessageCount`	Int	Custom	Only keeps events that belong to the same channel_session_id. Include events of the specified type: BOT_MESSAGE → messages sent by bot. After filtering, use `.count()` to get the total number of matching events.
agent_activity_count	`agentMessageCount`	Int	Custom	Only keeps events that belong to the same channel_session_id. Include events of the specified type: AGENT_MESSAGE → messages sent by a human agent After filtering, use `.count()` to get the total number of matching events.
customer_activity_count	`customerMessageCount`	Int	Custom	Only keeps events that belong to the same channel_session_id. Include events of the specified type: CUSTOMER_MESSAGE → messages sent by the customer After filtering, use `.count()` to get the total number of matching events.
avg_bot_confidence_level		Int	Custom	For each bot response, there is a percentage value that tells how confidently the bot was able to answer the customer’s query. This field gives the average value for all bot-confidence values. For now it is hardcoded to 0.1.
device	`cimEvent.data.channelData.additonalAttributes.0.value.browserDeviceInfo.deviceType`	String	Existing	API sends the `additionalAttributes` array. Records the type of device used for this channel session.
browser_type	`cimEvent.data.channelData.additonalAttributes.0.value.browserDeviceInfo.browserName`	String	Existing	API sends the `additionalAttributes` array. Records the type of browser used for this channel session.
country	`cimEvent.data.channelData.additonalAttributes.0.value.locale.country`	String	Existing	API sends the `additionalAttributes` array. Records the customer’s country.
language	`cimEvent.data.channelData.additonalAttributes.0.value.locale.language`	String	Existing	API sends the `additionalAttributes` array. Records the language used for this channel session.
record_creation_time	`recordCreationTime`	DateTime	Existing	Records the date and time when the record is created in the source DB i.e. MongoDB.

Agent State

This records the details of an agent's state at a particular instance. The agent state data provides information about the current state of the agent including the duration held in each of the states.

Extraction Details

Purpose: Extracts data from MongoDB records based on a specific query.
Source: MongoDB source to extract data is as follows: state-events-logger_cx_db.agentStateChangeEvents
MongoDB Query: {'name': 'AGENT_STATE_CHANGED', 'data.agentStateChanged': true}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
login_date_time	`data.agentPresence.agentLoginTime`	Date	Existing	Records the date and time when the agent logged in.
agent_id	`data.agentPresence.agent.keycloakUser._id`	String	Existing	Unique ID to identify the agent.
agent_state	`data.agentPresence.state.name`	String	Existing	Records the Agent state. Agent state can be: READY NOT_READY LOGOUT
reason_code	`data.agentPresence.state.reasonCode.name`	String	Existing	Records the reason why agent switched states. For example , SHORT_BREAK , TIME_OFF, Out of office.
duration			Custom	Identify each agent’s next state change using the `LEAD()` window function, grouped by `login_date_time` and `agent_id`. Calculate `duration` as the difference in seconds between the current and next `state_change_time`. Update the `duration` field in `agent_state` only where: `duration = -1` (i.e., not yet calculated) A next state (`next_state_time`) exists. A duration of -1 means the agent is currently in the current state, and a duration of 0 means the agent has logged out.
state_change_time	`data.agentPresence.stateChangeTime`	Date	Existing	Records the date and time when the agent state was changed.

Agent MRD State

This records the details of an agent's MRD state at a particular instance. The MRD state data provides information about the states of different media routing domains that the agent is handling. The duration of the states is also recorded, with specific values indicating the current state or logout events.

Extraction Details

Purpose: Extracts data from MongoDB records based on a specific query.
Source: MongoDB source to extract data is as follows: state-events-logger_cx_db.agentStateChangeEvents
MongoDB Query: { $or: [{$and: [{ "name": "AGENT_STATE_CHANGED" }, { "data.agentStateChanged": false }]},{ $and: [{ "name": "AGENT_STATE_CHANGED" }, { "data.agentStateChanged": true }, { "data.agentPresence.state.name": { $ne: "READY" } }]}]}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
agent_id	`data.agentPresence.agent.keycloakUser._id`	String	Existing	Unique ID to identify the agent
mrd_id	`data.mrdStateChanges`	String	Existing	Identifies the MRD this MRD state is associated to
login_date_time	`data.agentPresence.agentLoginTime`	Date	Existing	Records the date and time when the MRD state changed to login.
agent_mrd_state	`data.agentPresence.agentMrdStates.state`	String	Existing	Records the MRD state of an agent.
duration			Custom	Use `LEAD()` window function to find the next `state_change_time` for each agent and MRD, grouped by: `login_date_time` `agent_id` `mrd_id` Calculate `duration` as the time in seconds between the current and next `state_change_time`. Update `duration` in `agent_mrd_state` where: `duration = -1` (i.e., uncalculated) `next_state_time IS NOT NULL` (i.e., a next state exists). When the duration value is "-1" , it indicates that the MRD is presently in its current state and has not transitioned to the succeeding state yet.
state_change_time	`data.agentPresence.stateChangeTime`	Date	Existing	Records the date and time when the agent MRD state was changed.

Wrapup Detail

This is responsible to process wrap-ups associated with conversations handled by agents. Wrap-ups are concluding notes added to conversations to summarize what the conversation was about. Agents can provide wrap-ups during or after the conversation, and they can also add notes independently at any time.

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.CustomerTopicEvents
MongoDB Query: {$and: [{ "cimEvent.data.body.type": "WRAPUP" }, {"cimEvent.data.body.wrapups": {$exists: true, $type: "array", $ne: []}}]}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
wrapup_time	`timestamp`	Date	Existing	Records the time when the wrap-up was applied.
conversation_id	`cimEvent.conversationId`	String	Existing	Identifies the conversation to which the wrap-up was applied.
category_name	`cimEvent.data.body.wrapups.categoryName`	String	Existing	Records the wrap-up category.
wrapup_label	`cimEvent.data.body.wrapups.value`	String	Existing	Records the wrap-up label for the applied wrap-up.
agent_id	`cimEvent.data.header.sender.id`	String	Existing	Identifies the agent who applied the wrap up(s) on the conversation.
notes	`cimEvent.data.body.note`	String	Existing	Notes entered by agents while applying wrap up to a conversation. Entering notes are optional while ending a conversation.
record_creation_time	`recordCreationTime`	Date	Existing	Records the date and time when the record is created in the source DB i.e. MongoDB.

IVR

IVR records the IVR activity of the solution. It loads data from the IVR_AGGREGATED_ACTIVITY event into the database.

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.CustomerTopicEvents
MongoDB Query: { "cimEvent.type": "ACTIVITY", "cimEvent.name": "IVR_AGGREGATED_ACTIVITY"}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
conversation_id	`cimEvent.conversationId`	String	Existing	Identifies the conversation for which the IVR was used.
help_line	`cimEvent.channelSession.channel.serviceIdentifier`	String	Existing	Identifies the Service being used by the customer.
start_date_time	`cimEvent.data.body.startTime`	Date	Existing	Date and time when the IVR conversation was started.
caller_number	`cimEvent.channelSession.channelData.channelCustomerIdentifier`	String	Existing	Shows the phone number of the customer part of this IVR conversation.
main_menu_selection	`cimEvent.data.body.activities.menu`	String	Existing	The option selected by the customer on the main menu of the IVR menu.
sub_menu_option_selected	`cimEvent.data.body.activities`	String	Existing	The option selected by the customer on the sub menu of the IVR main menu.
journey			Custom
selection	`cimEvent.data.body.activities.selection`	String	Existing	Shows if any option was selected by the customer on main menu or not. ‘0' if no selection was made and '1’ if customer selected any option.
duration			Custom	Duration of the call in seconds. This is calculated by the subtracting the end time with the start time.
language	`cimEvent.data.header.language`	String	Existing	Language used for the IVR call.
call_id	`cimEvent.data.body.callId`	String	Existing	Unique identifier to identify the call.
end_date_time	`cimEvent.data.body.endTime`	Date	Existing	Date and time when the IVR conversation was ended.
start_direction	`cimEvent.data.body.startDirection`	String	Existing	Records the start direction of channel session used for the call i.e. INBOUND/OUTBOUND.
call_disposition	`cimEvent.data.body.endDirection`	String	Existing	Records the reason why the IVR call was ended. The reason could be any one of the following: TRANSFER DONE RONA RESPONSE_TIMEOUT NO_AGENT_AVAILABLE REROUTE CANCELLED AGENT_LOGOUT FORCE_CLOSED
record_creation_time	`recordCreationTime`	Date	Existing	Records the date and time when the record is created in the source DB i.e. MongoDB.

Forms

Forms records the data generated by filling forms of surveys. The target table of the ETL records the submitted forms data coming from the conversation_manager_db of mongodb within the CustomerTopicEvents collection. The objects are filtered based on their cimEvent, which corresponds to “FORMS_DATA,” which is further transformed and loaded into a table whose schema details are as follows. Learn more about forms here Forms

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.CustomerTopicEvents
MongoDB Query: {"cimEvent.data.body.type": "FORM_DATA"}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
row_id			Custom	Primary-Key: Hashing Field to implement upserting in table.
conversation_id	`cimEvent.conversationId`	String	Existing	Unique identifier for the conversation, linking it to the main conversation table.
channel_session_id	`cimEvent.data.header.channelSessionId`	String	Existing	Unique identifier for the communication session related to the conversation.
channel_customer_identifier	`cimEvent.data.header.channelData.channelCustomerIdentifier`	String	Existing	identifier for the channel from where customer is coming.
service_indentifier	`cimEvent.data.header.channelData.serviceIdentifier`	String	Existing	Unique identifier like a phone number or URL associated to the customer.
customer_id	`customerId`	Object Id	Existing	Unique identifier for the customer.
customer_is_anonymous	`cimEvent.channelSession.customer.isAnonymous`	Boolean	Existing	Boolean field to check if the form submitted by customer is anonymous or not. This is ‘0' if customer is not anonymous and '1’ if customer is anonymous.
customer_first_name	`cimEvent.channelSession.customer.firstName`	String	Existing	Records the customer’s name from the customer object.
room_id	`cimEvent.roomInfo._id`	Object Id	Existing	Unique identifier for the room used for the conversation, linking it to the main rooms table.
activity_id	`cimEvent.data.id`	String	Existing	Unique identifier for the activity.
form_id	`cimEvent.data.body.formId`	String	Existing	Unique identifier for the submitted forms.
form_type	`cimEvent.data.body.formType`	String	Existing	Type of form submitted by the customer e.g. Survey.
form_title	`cimEvent.data.body.formTitle`	String	Existing	Title of the submitted form.
form_weightage	`cimEvent.data.body.formWeightage`	Null	Existing	Represents the overall weightage assigned to a form in a survey or assessment.
enable_weightage	`cimEvent.data.body.enableWeightage`	Boolean	Existing	Boolean field indicating whether the weightage feature is enabled for the form or survey. This is ‘0' if weightage is not enabled and '1’ if weightage is enabled.
intent	`cimEvent.data.header.intent`	String	Existing	Scope of the submitted form activity.
sentiment_result	`cimEvent.data.body.sentiment.result`	Null	Existing	Stores the sentiment analysis result, typically reflecting the emotional tone of the response.
sender_id	`cimEvent.data.header.sender.id`	String	Existing	Unique identifier for the agent that sent the form.
sender_type	`cimEvent.data.header.sender.type`	String	Existing	Extracts the type of the sender of the form from the sender object. Sender can be Agent, IVR, etc.
sender_name	`cimEvent.data.header.sender.senderName`	String	Existing	Name of the sender.
section_id	`cimEvent.data.body.sections.sectionId`	String	Existing	Unique identifier for the sections of form.
section_name	`cimEvent.data.body.sections.sectionName`	String	Existing	Name of the section.
section_weightage	`cimEvent.data.body.sections.sectionWeightage`	Null	Existing	Weightage assigned to a specific section within a form or survey.
attribute_id	`cimEvent.data.body.sections.attributes.id`	String	Existing	Unique identifier for the attributes.
attribute_type	`cimEvent.data.body.sections.attributes.attributeType`	String	Existing	Type of attribute allocated to the section of form. INPUT = descriptive section of form (shortAnswer) OPTION = MCQs section of form
attribute_label	`cimEvent.data.body.sections.attributes.label`	String	Existing	Label of the section on the form.
attribute_weightage	`cimEvent.data.body.sections.attributes.attributeWeightage`	Null	Existing	Weightage assigned to individual attributes within a form or survey section.
value_type	`cimEvent.data.body.sections.attributes.valueType`	String	Existing	Type of output associated with the form section This can be: shortAnswer, mcq, 5-star-rating.
skip_type	`cimEvent.data.body.sections.attributes.skipType`	Null	Existing	Specifies the condition or rule under which a question or section can be skipped in a form or survey.
answer_label	`cimEvent.data.body.sections.attributes.answer.label`	String	Existing	Output labels associated with each section.
answer_value	`cimEvent.data.body.sections.attributes.answer.value`	String	Existing	Answer submitted for each section.
is_selected	`cimEvent.data.body.sections.attributes.answer.isSelected`	Boolean	Existing	Boolean field showing if any option was selected in each section of the form.
option_weightage	`cimEvent.data.body.sections.attributes.answer.additionalAttributes.optionWeightage`	Int	Existing	Percentage that each option of the form.
timestamp	`timestamp`	Date	Existing	Time when the record is created.
recordCreationTime	`recordCreationTime`	Date	Existing	Time when the record was created in the source DB i.e. MongoDB.
etl_inserted_at			Custom	Records the time and date when the ETL job was run.
tenant_id			Existing	Records the tenant id/name.

Survey Distribution

Survey distributions records the survey created and sent to each conversation.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: surveydb.surveydistributions
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
id	`_id`	Object Id	Existing	MongoDB Object id.
survey_id	`survey`	Object Id	Existing	Unique identifier for the survey.
form_id	`formId`	String	Existing	Unique identifier for the submitted form.
customer_id	`customerId`	String	Existing	Unique identifier for the customer who received the survey.
activity_message	`activities.message`	String	Existing	Message that is sent when the chat has ended.
deleted	`deleted`	Boolean	Existing	Checks if the survey is deleted from the backend or not (soft deletion). If the survey is deleted this sets to ‘TRUE’ else it is ‘FALSE’.
created_at	`createdAt`	Date	Existing	Records the date and time of when the survey was created.
updated_at	`updatedAt`	Date	Existing	Records the date and time of when the survey was updated.
etl_inserted_at			Custom	Records the time and date when the ETL job was run.
tenant_id			Existing	Records the tenant id/name.

Teams

This records the teams and their respective details configured in CX. These details include team name, team supervisor id, created by etc.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: adminPanel.teams
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
team_id	`team_Id`	String	Existing	Unique identifier for teams.
team_name	`team_name`	String	Existing	Name of the team.
supervisor_id	`supervisor_Id`	String	Existing	Unique identifier for supervisor of the respective team.
source	`source`	String	Existing
created_by	`created_by`	String	Existing	By whom is the team created by. This could be admin or any agent with admin level roles.
created_at	`createdAt`	Date	Existing	Records the date and time of when the team was created.
updated_at	`updatedAt`	Date	Existing	Records the date and time of when the team was updated.
_etl_inserted_at			Custom	Records the time and date when the ETL job was run.
tenant_id			Existing	Records the tenant id/name.

Team Members

Records the details of each agent of each team the agent is a part of.

Extraction Details

Purpose: Extracts data from MongoDB collection.
Source: MongoDB source to extract data is as follows: adminPanel.teammembers
MongoDB Query: {}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
id	`_id`	Object Id	Existing	Unique identifier for the mongodb document.
team_id	`team_Id`	String	Existing	Unique identifier for the team.
type	`type`	String	Existing	Type of person e.g. agent, secondary-supervisor.
username	`username`	String	Existing	Username of person.
user_id	`userId`	String	Existing	Unique identifier for person.
created_at	`createdAt`	Date	Existing	Records the date and time of when the team member was assigned to the team.
updated_at	`updatedAt`	Date	Existing	Records the date and time of when the team member details were updated.
etl_inserted_at			Custom	Records the time and date when the ETL job was run.
tenant_id			Existing	Records the tenant id/name.

Voice Activities

A voice activity records the voice conversation details. This can include the start and end time of the conversation, call legs information, customer information, channel session information etc.

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.conversationActivities
MongoDB Query: {"activity.name": "VOICE_ACTIVITY"}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
row_id	`Auto-Generated`		Custom	Primary-Key: Hashing Field to implement upserting operation to prevent loading duplicate data.
id	`_id`	Object ID	Existing	Unique identifier for mongo db record.
customer_id	`customerId`	Object Id	Existing	Identifies the customer who is part of this conversation.
conversation_id	`conversationId`	Object Id	Existing	Identifies the conversation for this voice activity.
channel_session_id	`channelSessionId`	Object Id	Existing	Identifies the channel session for the activity.
channel_session_direction	`activity.channelSession.channelSessionDirection`	String	Existing	Records the direction of channel session i.e. INBOUND/OUTBOUND. There can be multiple channel sessions in a conversation.
channel_id	`activity.channelSession.channel._id`	Object ID	Existing	Identifies the channel for which the conversation was initiated
channel_name	`activity.channelSession.channel.name`	String	Existing	Extracts the channel name, that the voice activity is a part of.
channel_customer_identifier	`activity.channelSession.channelData.channelCustomerIdentifier`	String	Existing	Identifier for the channel from where customer is coming.
service_identifier	`activity.channelSession.channel.serviceIdentifier`	String	Existing	Unique identifier like a phone number or URL associated to the customer.
tenant_id	`activity.channelSession.channel.tenant._id`	Object ID	Existing	Unique identifier for the associated tenant of a conversation.
customer	`activity.channelSession.customer`	Object	Existing	A JSON object containing all the customer associated details from a conversation including customer id, name, phone number, etc.
room_id	`roomId`	Object Id	Existing	Identifies the room this conversation is a part of.
activity_id	`activity._id`	Object Id	Existing	Identifies the activity this conversation is a part of.
activity_name	`activity.name`	String	Existing	Extracts the name of the activity, the conversation is a part of.
activity_timestamp	`activity.timestamp`	Date	Existing	Extracts the date and time, when the activity was performed.
event_emitter_id	`activity.eventEmitter._id`	Object Id	Existing	Identifies the event emitter this conversation is a part of.
event_emitter_type	`activity.eventEmitter.type`	String	Existing	Records the type of the event emitter.
state_name	`activity.channelSession.state.name`	String	Existing	Extracts the state of the conversation in the channel session. This can be ‘STARTED’, 'ENDED'.
state_reason_code	`activity.channelSession.state.reasonCode`	String	Existing	Reason code for the change of state for the channel session.
start_time	`activity.data.startTime`	Date	Existing	Records the date and time when this conversation was started.
end_time	`activity.data.endTime`	Date	Existing	Records the date and time when this conversation was ended.
total_duration	`activity.data.duration`	Int64	Existing	Total duration of the conversation.
conversation_hold_time	`activity.data.holdTime`	Double	Existing	Records the total hold time of the conversation.
sender_id	`activity.data.callLegs.0.sender._id`	String	Existing	Identifies the sender of the activity from the sender object in the call legs array.
sender_type	`activity.data.callLegs.0.sender.type`	String	Existing	Extracts the type of the sender of the conversation from the sender object in the call legs array. Sender can be Agent, IVR, etc.
sender_name	`activity.data.callLegs.sender.senderName`	String	Existing	Extracts the name of the sender based on its type. This can be the configured IVR name or the name of the associated agent for the activity.
call_leg_start_direction	`activity.data.callLegs.startDirection`	String	Existing	Multiple call legs are created when a call is transferred from IVR to Agent or Agent to Agent. Call legs are exploded into rows. This field records the start direction of the call legs. Start direction can be: OUTBOUND, INBOUND.
call_leg_end_direction	`activity.data.callLegs.endDirection`	String	Existing	Multiple call legs are created when a call is transferred from IVR to Agent or Agent to Agent. Call legs are exploded into rows. This field records the end direction of the call legs. DIALOG_ENDED - The conversation has ended DIRECT_TRANSFER - The call is transferred to another agent
call_leg_start_time	`activity.data.callLegs.startTime`	Date	Existing	Multiple call legs are created when a call is transferred from IVR to Agent or Agent to Agent. Call legs are exploded into rows. Records the start date and time of each call leg.
call_leg_end_time	`activity.data.callLegs.endTime`	Date	Existing	Multiple call legs are created when a call is transferred from IVR to Agent or Agent to Agent. Call legs are exploded into rows. Records the end date and time of each call leg.
call_leg_duration	`activity.data.callLegs.duration`	Int64	Existing	Multiple call legs are created when a call is transferred from IVR to Agent or Agent to Agent. Call legs are exploded into rows. Records the total duration of each call leg.
recordCreationTime	`recordCreationTime`	Date	Existing	Assigned based on `endTime` of the conversation when the data is stored in the db.
timestamp	`timestamp`	Date	Existing	Records the date and time of when the conversation was performed.
_etl_inserted_at			Custom	Records the time and date when the ETL job was run.

Voice Connector Activities

A voice connector activity records the voice connector conversation details. This can include the start and end time of the conversation, message details, channel details, scheduling meta data, status etc.

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.conversationActivities
Mongo DB Query: {"activity.eventEmitter.senderName": "CX-Voice-Connector"}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
activity_id	`activity._id`	Object ID	Existing	Unique identifier to show the various activities performed by customers
activity_name	`activity.name`	String	Existing	Extracts the name of the activity, the conversation is a part of.
activity_type	`activity.type`	String	Existing	Extracts the type of the activity, the conversation is a part of.
activity_timestamp	`activity.timestamp`	String	Existing	Extracts the date and time, when the activity was performed.
event_emitter_id	`activity.eventEmitter.id`	String	Existing	Identifies the event emitter this conversation is a part of.
event_emitter_type	`activity.eventEmitter.type`	String	Existing	Records the type of event emitter.
sender_name	`activity.eventEmitter.senderName`	String	Existing	Extracts the name of the sender for the call activity. For this case `CX-Voice-Connector.`
customer_id	`customerId`	String	Existing	Unique identifier of the customer associated with the conversation.
customer	`activity.data.header.customer`	Object	Existing	A JSON object containing all the customer associated details from a conversation including customer id, name, phone number, etc.
channel_session_id	`activity.channelSession._id`	Object ID	Existing	Identifies the channel session for the activity.
participant_type	`activity.channelSession.participantType`	String	Existing	Records the type of the channel session participant from the channelSession object.
channel_id	`activity.channelSession.channel._id`	Object ID	Existing	Identifies the channel for which the conversation was initiated
tenant_id	`activity.channelSession.channel.tenant._id`	Object ID	Existing	Unique identifier for the associated tenant of the conversation.
channel_config_id	`activity.channelSession.channel.channelConfig._id`	Object ID	Existing	Unique identifier for the associated channel config of the conversation.
channel_connector_id	`activity.channelSession.channel.channelConnector._id`	Object ID	Existing	Unique identifier for the associated channel connector of the conversation.
channel_type_id	`activity.channelSession.channel.channelType._id`	Object ID	Existing	Unique identifier for the channel type of the conversation.
scheduling_meta_data	`activity.data.header.schedulingMetaData`	Object	Existing	JSON object containing all the scheduling meta data details from a conversation including the including date and time, campaign id, campaign type, gateway id, etc.
activity_data_id	`activity.data.id`	String	Existing	Identifies the activity this conversation is a part of.
sender_id	`activity.data.header.sender.id`	String	Existing	Identifies the sender of the activity.
sender_type	`activity.data.header.sender.type`	String	Existing	Extracts the type of the sender of the conversation from the sender object in the activity data object.
channel_customer_identifier	`activity.data.header.channelData.channelCustomerIdentifier`	String	Existing	Identifier for the channel from where customer is coming.
service_identifier	`activity.data.header.channelData.serviceIdentifier`	String	Existing	Unique identifier like a phone number or URL associated to the customer
message_type	`activity.data.body.type`	String	Existing	Extracts the type of message sent to the customer for the voice activity. It can be: VOICE DELIVERYNOTIFICATION
message_id	`activity.data.body.id`	String	Existing	Records the identifier for the message sent to the customer.
message_text	`activity.data.body.messageText`		Existing	Records the text of the message sent to the customer.
status	`activity.data.body.status`	String	Existing	Extracts the status of the conversation. The status can be: Connected - If the conversation is successful. Failed - if the conversation is failed.
reason_code	`activity.data.body.reasonCode`	String	Existing	Records the reason code of the conversation. Reason codes can be: NORMAL_CLEARING NO_ANSWER USER_BUSY CALL_REJECTED
room_id	`activity.roomInfo._id`	Object ID	Existing	Identifies the room of which this conversation is a part of.
room_mode	`activity.roomInfo.mode`	String	Existing	Extracts the room mode from the roomInfo object.
timestamp	`timestamp`	Date	Existing	Records the date and time of the conversation.
recordCreationTime	`recordCreationTime`	Date	Existing	Records the date and time when the data is pushed in mongodb.
_etl_inserted_at			Custom	Records the time and date when the ETL job was run

Campaigns

Campaigns records the campaigns added in the system along with their details. For more details on campaigns refer to the following document: https://expertflow-docs.atlassian.net/wiki/x/CQA8Fg?atlOrigin=eyJpIjoiNjRlZWQ1ZWE1NjhmNDVlZmEyNmZjNmJiMjFlMTg2MWUiLCJwIjoiYyJ9

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: campaignsDb.campaigns
MongoDB Query: None

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
id	`_id`	Object ID	Existing	Unique identifier for mongo db record.
flow_id	`flowId`	String	Existing	Unique identifier of the flow builder used to deploy the respective campaign.
title	`title`	String	Existing	Name/title of the campaign.
number_of_contacts	`numberOfContacts`	Int(32)	Existing	The number of contacts uploaded in the campaign.
sources	`sources`	Array	Existing	Stored an JSON object, records the identifiers of sources of the contacts in the campaign.
status	`status`	String	Existing	Shows the status of the campaign. Campaigns can be: Published Unpublished
created_at	`createdAt`	Date	Existing	Extracts the date and time when the campaign was created.
updated_at	`updatedAt`	Date	Existing	Extracts the date and time when the campaign was updated.
version	`__v`	Int(32)	Existing	Version
_etl_inserted_at			Custom	Records the time and date when the ETL job was run.

Campaign Scheduler

Campaigns scheulder records the data for the scheduled campaigns added in the system along with their details. This includes the customer information, channel session information, scheduling metadata etc. For more details on campaigns refer to the following document: https://expertflow-docs.atlassian.net/wiki/x/CQA8Fg?atlOrigin=eyJpIjoiNjRlZWQ1ZWE1NjhmNDVlZmEyNmZjNmJiMjFlMTg2MWUiLCJwIjoiYyJ9 .

Extraction Details

Purpose: Extracts data from MongoDB based on a specific query.
Source: MongoDB source to extract data is as follows: conversation-manager_db.conversationActivities
MongoDB Query: {"activity.eventEmitter.senderName": "CAMPAIGN_SCHEDULER"}

Target Field	Source Field (MongoDB)	Source Data Type	Field Type	Transformation Rules
activity_id	`activity._id`	Object ID	Existing	Unique identifier to show the various activities performed by customers.
name	`activity.name`	String	Existing	Extracts the name of the activity, the conversation is a part of.
type	`activity.type`	String	Existing	Extracts the type of the activity, the conversation is a part of.
activity_timestamp	`activity.timestamp`	Date	Existing	Extracts the date and time, when the activity was performed.
event_emitter_id	`activity.eventEmitter._id`	Object Id	Existing	Identifies the event emitter this conversation is a part of.
event_emitter_type	`activity.eventEmitter.type`	String	Existing	Records the type of the event emitter.
sender_name	`activity.eventEmitter.senderName`	String	Existing	Extracts the name of the sender of the activity. In this case `CAMPAIGN_SCHEDULER`.
customer_id	`customerId`	String	Existing	Unique identifier of the customer associated with the conversation.
customer	`activity.channelSession.customer`	Object	Existing	A JSON object containing all the customer associated details from a conversation including customer id, name, phone number, etc.
channel_session_id	`activity.channelSession._id`	Object ID	Existing	Identifies the channel session for the activity.
participant_type	`activity.channelSession.type`	String	Existing	Records the type of the channel session participant.
channel_id	`activitiy.channelSession.channel._id`	Object ID	Existing	Identifies the channel for which the conversation was initiated.
default_outbound	`activity.channelSession.channel.defaultOutbound`	Boolean	Existing	Records whether the channel is by default set to Outbound or not. It is stored as Boolean value: 0 (False) 1 (True)
tenant_id	`activity.channelSession.channel.tenant._id`	Object ID	Existing	Unique identifier for the associated tenant of the conversation.
channel_config_id	`activity.channelSession.channel.channelConfig._id`	Object ID	Existing	Unique identifier for the associated channel config of the conversation.
channel_connector_id	`activity.channelSession.channel.channelConnector._id`	Object ID	Existing	Unique identifier for the associated channel connector for the conversation.
channel_type_id	`activity.channelSession.channel.channelType._id`	Object ID	Existing	Unique identifier for the channel type used for the conversation.
is_interactive	`activity.channelSession.channel.channelType.interactive`	Boolean	Existing	Records whether the channel type is interactive or not. It is stored as Boolean value: 0 (False) 1 (True)
is_active	`activity.channelSession.isActive`	Boolean	Existing	Records whether the channel session is active or not. It is stored as Boolean value: 0 (False) 1 (True)
scheduling_meta_data	`activity.data.header.schedulingMetadat`	Object	Existing	JSON object containing all the scheduling meta data details from a conversation including the including date and time, campaign id, campaign type, gateway id, etc.
activity_data_id	`activity.data.id`	String	Existing	Identifies the activity data this conversation is a part of.
sender_id	`activity.data.header.sender.id`	String	Existing	Identifies the sender.
sender_type	`activity.data.header.sender.type`	String	Existing	Extracts the type of the sender of the conversation from the sender object in the activity data object.
channel_customer_identifier	`activity.data.header.channelData.channelCustomerIdentifier`	String	Existing	Identifier for the channel from where customer is coming.
service_identifier	`activity.data.header.channelData.serviceIdentifier`	String	Existing	Unique identifier like a phone number or URL associated to the channel.
message_type	`activity.data.body.type`	String	Existing	Extracts the type of message sent to the customer. It can be: VOICE DELIVERYNOTIFICATION
messageId	`activity.data.body.messageId`	String	Existing	Records the identifier for the message sent to the customer.
message_text	`activity.data.body.messageText`	String	Existing
status	`activity.data.body.status`	String	Existing	Records the status of the message. It can be: Delivered Failed
reason_code	`activity.data.body.reasonCode`	String	Existing	Records the reason code of the conversation from the body object in the activity data object.
call_id	`activity.data.body.callId`	String	Existing	Records the identifier for the call to the customer.
room_id	`activity.roomInfo._id`	Object ID	Existing	Identifies the room this conversation is a part of.
room_mode	`activity.roomInfo.mode`	String	Existing	Extracts the room mode from the roomInfo object.
timestamp	`timestamp`	Date	Existing	Records the date and time of the conversation.
recordCreationTime	`recordCreationTime`	Date	Existing	Records the date and time when the data is pushed in mongodb.
_etl_inserted_at			Custom	Records the time and date when the ETL job was run.

This document outlines the transformation rules and source-to-target data mappings for ETL pipelines.Explanation of Columns

Table of Contents:

Configuration ETL Pipelines:

Transactional ETL Pipelines:

ETL Pipelines - Transformation Rules

Configurational ETL Pipelines:

Agent

Extraction Details

Bot

Extraction Details

Channel Type

Extraction Details

Channel

Extraction Details

Queue

Extraction Details

Media Routing Domain

Extraction Details

List

Extraction Details

MRD Type

Extraction Details

Transactional ETL Pipelines:

Conversation

Extraction Details

Conversation Data

Extraction Details

Conversation Participant

Extraction Details

Conversation Hold Resume

Extraction Details

Agent Task

Extraction Details

Channel Session

Extraction Details

Agent State

Extraction Details

Agent MRD State

Extraction Details

Wrapup Detail

Extraction Details

IVR

Extraction Details

Forms

Extraction Details

Survey Distribution

Extraction Details

Teams

Extraction Details

Team Members

Extraction Details

Voice Activities

Extraction Details

Voice Connector Activities

Extraction Details

Campaigns

Extraction Details

Campaign Scheduler

Extraction Details

This document outlines the transformation rules and source-to-target data mappings for ETL pipelines.

Explanation of Columns