Skip to main content
Skip table of contents

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 (predefined in the system) or a Custom Field (added for specific business needs).

  • Transformation Rules

    • Describes the logic applied to convert the source data into the target format.

Conversations

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.

  • 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

  • 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-related 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-related 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 reasonCodefield 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.

  • 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.

  • 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.

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.

JavaScript errors detected

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

If this problem persists, please contact our support.

Contact Support Close