fact_agent_conversation_messages

Each row is one message inside an agent conversation: what the customer wrote, what the agent replied, and when it was delivered and read. It replaces the legacy fact_agent_messages.

What questions does it answer?

What was said in an agent conversation, in order?
How many messages does each agent send per day?
How fast are agent messages read by customers?
What share of agent traffic is media vs text?

Columns

Column	Type	Description
`message_id`	Int32	Unique identifier of the message
`company_id`	Int32	Your company (filtered automatically)
`agent_conversation_id`	Int32	The agent conversation it belongs to
`session_id`	Int64	The originating conversation flow; `0` if none
`created_at`	DateTime64	When the message was sent
`sender`	String	`USER` (customer), `AGENT` (human agent), or `AI` (AI assistant replying in the conversation)
`category`	String	`text`, `image`, `audio`, `video`, `document`, `hsm`, `sticker`, `location`, `contacts`, `whatsapp_flow`, `whatsapp_flow_response`, `call_request_permission`
`content`	String	Message content (text, or media URL for files)
`agent_id`	Int32	Agent who sent it; `0` for `USER` and `AI` messages
`agent_name`	String	That agent’s name
`tag_id`	Int32	Tag of the conversation at send time
`team_name`	String	Team name
`read_at`	DateTime64	When the recipient read it; `NULL` if unread
`delivered_at`	DateTime64	When WhatsApp confirmed delivery
`wa_id`	String	WhatsApp message id (`wamid`)
`reply_provider_msg_id`	String	If it quotes/replies to another message, that message’s `wamid`
`reaction`	String	Emoji reaction, if any
`synced_at`	DateTime64	When this row was last written/corrected

Messages belonging to conversations that were deleted in the platform are excluded — every row here has a live parent in fact_agent_conversations.

Example queries

Reconstruct one agent conversation

SELECT created_at, sender, agent_name, category, content
FROM fact_agent_conversation_messages
WHERE agent_conversation_id = {your_conversation_id}
ORDER BY created_at

Messages sent per agent per day

SELECT
    agent_name,
    toDate(created_at) AS day,
    count()            AS messages_sent
FROM fact_agent_conversation_messages
WHERE sender = 'AGENT'
  AND created_at >= today() - 7
GROUP BY agent_name, day
ORDER BY day, messages_sent DESC

⌘I

Get Started

Meta

Build with Treble

Main Platform

Agent Platform

Integrations

Analytics Warehouse

Data Warehouse (Legacy)

fact_agent_conversation_messages

fact_agent_conversation_messages

What questions does it answer?

Columns

Example queries

Reconstruct one agent conversation

Messages sent per agent per day

​fact_agent_conversation_messages

​What questions does it answer?

​Columns

​Example queries

​Reconstruct one agent conversation

​Messages sent per agent per day

fact_agent_conversation_messages

What questions does it answer?

Columns

Example queries

Reconstruct one agent conversation

Messages sent per agent per day