Transformation Rules - ETL Pipelines

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

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.

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

String

Existing

Identifies the customer who is part of this conversation.

conversation_id

conversationId

String

Existing

Identifies the conversation for which this voice activity data record is inserted.

channel_session_id

channelSessionId

String

Existing

Identifies the channel session for which this voice activity data record is inserted.

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 this voice activity data record is inserted.

channel_name

activity.channelSession.channel.name

String

Existing

Extracts the channel name, that the respective voice activity is a part of.

channel_customer_identifier

activity.channelSession.channelData.channelCustomerIdentifier

String

Existing

Records the identifier for the respective channel customer of a respective voice activity.

service_identifier

activity.channelSession.channel.serviceIdentifier

String

Existing

Records the service identifier that the CX solution is deployed on.

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

Extracts all the customer associated details from a conversation including customer id, name, phone number, etc. It is stored in the form of a JSON object.

room_id

roomId

String

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 type/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

String

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 from the state object in the channel session object

state_reason_code

activity.channelSession.state.reasonCode

String

Existing

Records the reason code of the conversation from the state object in the channel session object

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.date.duration

Int64

Existing

Stores the 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.sender.id

String

Existing

Identifies the sender of the respective conversation.

sender_type

activity.data.callLegs.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.name

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 of the conversation.

call_leg_start_direction

activity.data.callLegs.startDirection

String

Existing

Extracted from the call leg detail object. Records the start direction of the call legs. Start direction can be:

• OUTBOUND

• INBOUND

call_leg_end_direction

activity.data.callLegs.endDirection

String

Existing

Extracted from the call leg detail object. Records the end direction of the call legs. End direction can be:

• 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

Extracted from the call leg detail object. Records the start date and time of the call legs.

call_leg_end_time

activity.data.callLegs.endTime

Date

Existing

Extracted from the call leg detail object. Records the end date and time of the call legs.

call_leg_duration

activity.data.callLegs.duration

Int64

Existing

Extracted from the call leg detail object. Stores the total duration of the 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 activity was performed.

_etl_inserted_at

Custom

Date and time when the ETL data pipeline was run and data was inserted in table.

activity_id

activity._id

Object ID

Existing

Identifies the activity this voice conversation is a part of.

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 based on its type. This can be the configured IVR name, the name of the associated agent of the conversation, or for this case CX-Voice-Connector.

customer_id

docuemnt.customerId

String

Existing

Unique identifier of the customer associated with the conversation.

customer

activity.data.header.customer

Object

Existing

Extracts all the customer associated details from a conversation including customer id, name, phone number, etc. It is stored in the form of a JSON object.

channel_session_id

activity.channelSession._id

Object ID

Existing

Identifies the channel session for which this voice activity data record is inserted.

participant_type

activity.channelSession.participantType

String

Existing

Records the type of the channel session participant.

channel_id

activity.channelSession.channel._id

Object ID

Existing

Identifies the channel for which this voice activity data record is inserted.

tenant_id

activity.channelSession.channel.tenant._id

Object ID

Existing

Unique identifier for the associated tenant of a 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 associated channel type of the conversation.

scheduling_meta_data

activity.data.header.schedulingMetaData

Object

Existing

Extracts the all the scheduling meta data details from a conversation including the including date and time, campaign id, campaign type, gateway id, etc. is is stored as a JSON object.

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 of this conversation.

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

Records the identifier for the respective channel customer of a respective voice activity.

service_identifier

activity.data.header.channelData.serviceIdentifier

String

Existing

Records the service identifier that the CX solution is deployed on.

message_type

activity.data.body.type

String

Existing

Records the type of message sent to the customer for the conversation.

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 - if the call has happened successfully.

• NO_ANSWER - if the customer does not pick up the call.

• USER_BUSY - If the call is declined by the customer.

• 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 when the activity was performed.

recordCreationTime

recordCreationTime

Date

Existing

Assigned based on endTime of the conversation when the data is stored in the db.

_etl_inserted_at

Custom

Date and time when the ETL data pipeline was run and data was inserted in table.

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

Extracts the title of the campaign.

number_of_contacts

numberOfContacts

Int(32)

Existing

Counts and extracts the number of contacts from the csv file uploaded while creating 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

Extracts the status of the campaign. Campaign can either be:

• Published

• Unpublished

created_at

createdAt

Date

Existing

Extracts the date and time when the campaign was created in the system.

updated_at

updatedAt

Date

Existing

Extracts the date and time when the campaign was updated in the system.

version

__v

Int(32)

Existing

Version

_etl_inserted_at

Custom

Date and time when the ETL data pipeline was run and data was inserted in table.

activity_id

activity._id

Object ID

Existing

Identifies the activity this conversation is a part of.

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

String

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 based on its type. This can be the configured IVR name, the name of the associated agent of the conversation, or in this case CAMPAIGN_SCHEDULER.

customer_id

document.customerId

String

Existing

Unique identifier of the customer associated with the conversation.

customer

activity.channelSession.customer

Object

Existing

Extracts all the customer associated details from a conversation including customer id, name, phone number, etc. It is stored in the form of a JSON object.

channel_session_id

activity.channelSession._id

Object ID

Existing

Identifies the channel session for which this voice activity data record is inserted.

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 this voice activity data record is inserted.

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 a 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 associated channel type of the conversation.

is_interactive

activity.channelSession.channel.channelType.isInteractive

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

Extracts the all the scheduling meta data details from a conversation including the including date and time, campaign id, campaign type, gateway id, etc. is is stored as a JSON object.

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 of the respective conversation.

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

Extracts the identifier for the respective channel customer of a respective voice activity.

service_identifier

activity.data.header.channelData.serviceIdentifier

String

Existing

Extracts the service identifier that the CX solution is deployed on.

message_type

activity.data.body.type

String

Existing

Extracts the type of message sent to the customer. It can be:

• VOICE

• DELIVERYNOTIFICATION

messageId

Existing

Records the identifier for the message sent to the customer.

message_text

activity.data.body.messageText

String

Existing

Extracts the message text sent to the customer.

status

Existing

Records the status of the message. It can be:

• Delivered

• Failed

reason_code

activity.data.body.reasonCode

Null

Existing

Records the reason code of the conversation from the body object in the activity data object

call_id

activity.data.body.callId

Null

Existing

Records the identifier for the call sent 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 when the activity was performed.

recordCreationTime

recordCreationTime

Date

Existing

Records the date and time of the conversation when the data is pushed in the db.

_etl_inserted_at

Custom

Date and time when the ETL data pipeline was run and data was inserted in table.

row_id

Auto-Generated

Custom

Primary-Key: Hashing Field to implement upserting operation to prevent loading duplicate data.

customer_id

customerId

String

Existing

Identifies the customer who is part of this conversation.

conversation_id

conversationId

String

varchar(255)

Identifies the conversation for which this data record is inserted.

channel_session_id

channelSession._id

String

Existing

Identifies the channel session for which this data record is inserted.

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_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 event emitter.

event_emitter_sender_name

activity.eventEmitter.senderName

String

Existing

Extracts the name of the event emitter sender based on its type. This can be the configured IVR name, the name of the associated agent of the conversation, or for this case CONVERSATION_MONITOR.

channel_id

activity.channelSession.channel._id

Object Id

Existing

Identifies the channel for which this voice activity data record is inserted.

channel_name

activity.channelSession.channel.name

String

Existing

Extracts the channel name, that the conversation is a part of.

service_identifier

activity.channelSession.channel.serviceIdentifier

String

Existing

Records the service identifier that the CX solution is deployed on.

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 a conversation.

tenant_name

.

.

Custom

Tenant name to identify the tenant of the conversation (by default set to ‘expertflow’ as of now)

channel_mode

activity.channelSession.channel.channelConfig.channelMode

String

Existing

Records the Channel Mode.

channel_connector_id

activity.channelSession.channel.channelConnector._id

Object Id

Existing

Unique identifier for the associated channel connector of the conversation.

channel_type

activity.channelSession.channel.channelType.name

String

Existing

Records the associated channel type of the conversation.

customer

activity.channelSession.customer

Object

Existing

Extracts all the customer associated details from a conversation including customer id, name, phone number, etc. It is stored in the form of a JSON object.

channel_customer_identifier

activity.channelSession.channelData.channelCustomerIdentifier

String

Existing

Records the identifier for the respective channel customer of a conversation.

room_id

activity.channelSession.roomInfo._id

Object Id

Existing

Identifies the room this conversation is a part of.

room_mode

activity.channelSession.roomInfo.mode

String

Existing

Extracts the room mode from the roomInfo object.

state_name

activity.channelSession.state.name

String

Existing

Extracts the state of the conversation from the state object in the channel session object

state_reason_code

activity.channelSession.state.reasonCode

String

Existing

Records the reason code of the conversation from the state object in the channel session object.

channel_session_direction

activity.channelSession.channelSessionDirection

String

Existing

Records the direction of channel session i.e. INBOUND/OUTBOUND.

There can be multiple channel sessions (INBOUND/OUTBOUND) in a conversation.

sender_id

activity.data.header.sender.id

String

Existing

Identifies the sender of the respective conversation.

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.

type

activity.data.body.type

String

Existing

Type (in this case “SLG”)

agent_id

activity.data.body.agentId

String

Existing

Identifies the agent part of this conversation.

agent_name

activity.data.body.agentName

String

Existing

Records the name of the agent part of this conversation.

agent_extension

activity.data.body.agentExtension

String

Existing

Records the extension of the agent part of this conversation.

status

activity.data.body.status

String

Existing

Extracts the status of the conversation. It can be:

• Successful

• Busy

• State_changed

• Break

reason_for_expiration

activity.data.body.agentId.expiredReason

String

Existing

Records the reason for expiration of conversation. It can be:

• READY

• NOT_READY

• successful_call

timestamp

timestamp

Date

Existing

Records the date and time of when the conversation was performed.

recordCreationTime

recordCreationTime

Date

Existing

Records the date and time of the conversation when the data is pushed in the db.

_etl_inserted_at

.

.

Custom

Date and time when the ETL data pipeline was run and data was inserted in table.