# Conversations

## v\_Conversation\_{TenantName}

All customer conversations with routing and status information.

### Columns

| Column                         | Type     | Description                                                     |
| ------------------------------ | -------- | --------------------------------------------------------------- |
| `id`                           | varchar  | Unique conversation identifier                                  |
| `contactId`                    | varchar  | Link to customer contact                                        |
| `started`                      | datetime | When the conversation started                                   |
| `updated`                      | datetime | Last activity timestamp                                         |
| `closedAt`                     | datetime | When closed (null if open)                                      |
| `originalChannel`              | int      | Original channel code                                           |
| `originalChannelName`          | varchar  | Original channel name (see below)                               |
| `currentChannel`               | int      | Current channel code                                            |
| `currentChannelName`           | varchar  | Current channel name                                            |
| `status`                       | int      | Current status code                                             |
| `statusName`                   | varchar  | Current status name (see below)                                 |
| `initialStatus`                | int      | Status when conversation was created (null for historical data) |
| `initialStatusName`            | varchar  | Initial status name (null for historical data)                  |
| `transferredToHumanAt`         | datetime | When Alia transferred to a human agent (null if not applicable) |
| `initialTeamId`                | varchar  | First team assigned                                             |
| `teamId`                       | varchar  | Current team assigned                                           |
| `startedByAgentId`             | varchar  | Agent who started (outbound)                                    |
| `assignedToAgentId`            | varchar  | Currently assigned agent                                        |
| `tagIds`                       | varchar  | JSON array of tag IDs                                           |
| `conversationTagNames`         | varchar  | Comma-separated tag names                                       |
| `conversationCustomFields`     | varchar  | JSON custom field data                                          |
| `minutesToFirstResponse`       | float    | Minutes until first agent response                              |
| `totalResolutionTimeInMinutes` | float    | Total time to resolve (minutes)                                 |
| `lastAgentMessageAt`           | datetime | Last agent message timestamp                                    |
| `currentSlaPolicyStatus`       | int      | SLA status code                                                 |
| `currentSlaPolicyStatusName`   | varchar  | SLA status name (see below)                                     |

### Channel Values

| Code | Name              | Description                       |
| ---- | ----------------- | --------------------------------- |
| 0    | Unknown           | Unknown channel                   |
| 1    | FacebookMessenger | Facebook Messenger                |
| 2    | WhatsApp          | WhatsApp                          |
| 3    | Sms               | SMS text message                  |
| 4    | EmbeddedChat      | Embedded chat widget              |
| 5    | Email             | Email (includes form submissions) |
| 6    | VoiceCall         | Voice call                        |

### Status Values

| Name     | Description                      |
| -------- | -------------------------------- |
| Active   | Open and being handled           |
| Snoozed  | Temporarily paused               |
| Archived | Completed/archived               |
| Enqueued | Waiting in queue                 |
| Calling  | Active voice call                |
| Closed   | Closed                           |
| Alia     | Handled by Alia AI               |
| Merged   | Merged into another conversation |

### SLA Policy Status Values

| Name     | Description              |
| -------- | ------------------------ |
| Unknown  | No SLA or not applicable |
| Met      | SLA requirements met     |
| Low      | Low urgency              |
| Normal   | Normal priority          |
| High     | High priority            |
| Critical | Critical priority        |
| Breached | SLA breached             |

### Dynamic Custom Field Columns

Your custom fields are automatically available as proper columns, prefixed with `custom_`. No JSON parsing needed!

**Example - if you have custom fields `serial_number` and `warranty_type`:**

```sql
SELECT
    id,
    statusName,
    custom_serial_number,
    custom_warranty_type
FROM v_Conversation_YourCompany
WHERE custom_serial_number IS NOT NULL;
```

**Result:**

```
id                                   | statusName | custom_serial_number | custom_warranty_type
-------------------------------------|------------|----------------------|---------------------
8ae2aed0-cc44-4a7e-b272-a5ddafe9b5cf | Active     | ZAP316923            | Extended
3cdc6422-60db-463d-8901-82c495845bd9 | Active     | ZCS034842            | Standard
```

Custom field columns are:

* **Automatically generated** from your `v_CustomFieldDefinition_{TenantName}` table
* **Refreshed daily** at 2:00 AM UTC
* **Named as** `custom_{field_key}` (special characters become underscores)

> **Note:** These columns are dynamically generated by extracting values from the `conversationCustomFields` JSON column. When you add or remove custom field definitions in Superagent, the new columns will appear after the next daily refresh. Contact support if you need an immediate refresh.

### Raw Custom Fields JSON (Advanced)

The `conversationCustomFields` column also contains the raw JSON if you need it:

```json
{
  "priority": { "stringValue": "high" },
  "orderNumber": { "integerValue": 12345 },
  "amount": { "numberValue": 99.95 },
  "isVip": { "boolValue": true },
  "categories": { "arrayValues": ["billing", "refund"] }
}
```

| Field Type   | JSON Property  | Description               |
| ------------ | -------------- | ------------------------- |
| Text         | `stringValue`  | Free text                 |
| Integer      | `integerValue` | Whole numbers             |
| Number       | `numberValue`  | Decimal numbers           |
| Boolean      | `boolValue`    | true/false                |
| SingleSelect | `stringValue`  | Selected option           |
| MultiSelect  | `arrayValues`  | Array of selected options |

**Manual JSON extraction (if needed):**

```sql
-- Extract text/single-select value
SELECT
    id,
    JSON_VALUE(conversationCustomFields, '$.priority.stringValue') AS Priority
FROM v_Conversation_YourCompany

-- Extract numeric value
SELECT
    id,
    JSON_VALUE(conversationCustomFields, '$.orderNumber.integerValue') AS OrderNumber
FROM v_Conversation_YourCompany
```

### Example Queries

```sql
-- Conversations by status
SELECT statusName, COUNT(*) AS Count
FROM v_Conversation_YourCompany
GROUP BY statusName

-- Conversations by channel this month
SELECT originalChannelName, COUNT(*) AS Count
FROM v_Conversation_YourCompany
WHERE started >= DATEADD(month, -1, GETDATE())
GROUP BY originalChannelName

-- Open conversations per agent
SELECT assignedToAgentId, COUNT(*) AS OpenConversations
FROM v_Conversation_YourCompany
WHERE statusName = 'Active'
GROUP BY assignedToAgentId
```

### Alia (AI Bot) Queries

Use `initialStatus` and `transferredToHumanAt` to analyze Alia bot performance.

> **Note:** `initialStatus` and `transferredToHumanAt` are only populated for conversations created after these fields were deployed. Historical conversations will have `NULL` values for these columns.

```sql
-- Total Alia conversations (started as Alia)
SELECT COUNT(*) AS AliaConversations
FROM v_Conversation_YourCompany
WHERE initialStatusName = 'Alia'

-- Alia conversations resolved by AI (never transferred to human)
SELECT COUNT(*) AS ResolvedByAlia
FROM v_Conversation_YourCompany
WHERE initialStatusName = 'Alia'
  AND transferredToHumanAt IS NULL
  AND statusName IN ('Archived', 'Closed')

-- Alia conversations transferred to human
SELECT COUNT(*) AS TransferredToHuman
FROM v_Conversation_YourCompany
WHERE initialStatusName = 'Alia'
  AND transferredToHumanAt IS NOT NULL

-- Alia resolution rate
SELECT
    COUNT(*) AS Total,
    SUM(CASE WHEN transferredToHumanAt IS NULL AND statusName IN ('Archived', 'Closed') THEN 1 ELSE 0 END) AS ResolvedByAlia,
    SUM(CASE WHEN transferredToHumanAt IS NOT NULL THEN 1 ELSE 0 END) AS TransferredToHuman
FROM v_Conversation_YourCompany
WHERE initialStatusName = 'Alia'

-- Average time before Alia transfers to human (minutes)
SELECT AVG(DATEDIFF(MINUTE, started, transferredToHumanAt)) AS AvgMinutesToTransfer
FROM v_Conversation_YourCompany
WHERE initialStatusName = 'Alia'
  AND transferredToHumanAt IS NOT NULL
```

***

## v\_ConversationTimeline\_{TenantName}

All messages and events within conversations.

### Columns

| Column           | Type     | Description                      |
| ---------------- | -------- | -------------------------------- |
| `id`             | varchar  | Unique message identifier        |
| `conversationId` | varchar  | Parent conversation ID           |
| `timestamp`      | datetime | When the message was sent        |
| `channel`        | int      | Channel code                     |
| `channelName`    | varchar  | Channel name                     |
| `direction`      | int      | Direction code                   |
| `directionName`  | varchar  | `Inbound` or `Outbound`          |
| `type`           | int      | Message type code                |
| `typeName`       | varchar  | Type name (see below)            |
| `agentId`        | varchar  | Agent who sent (outbound)        |
| `text`           | varchar  | Message content (max 8000 chars) |

### Direction Values

| Code | Name     | Description                    |
| ---- | -------- | ------------------------------ |
| 0    | Inbound  | From customer                  |
| 1    | Outbound | From agent                     |
| NULL | —        | Summary entries (no direction) |

### Type Values

| Code | Name    | Description                       |
| ---- | ------- | --------------------------------- |
| 0    | Unknown | Events and other timeline entries |
| 1    | Message | Customer or agent message         |
| 2    | Note    | Internal agent note               |
| 3    | Summary | AI-generated conversation summary |

> **Note:** System events (assignments, status changes, etc.) are exported as `Unknown` (type=0). Summary entries have no direction (NULL) and `agentId` is set to the agent who triggered the summary.

### Example Queries

```sql
-- Messages per day
SELECT CAST(timestamp AS date) AS Date, COUNT(*) AS Messages
FROM v_ConversationTimeline_YourCompany
WHERE typeName = 'Message'
GROUP BY CAST(timestamp AS date)
ORDER BY Date DESC

-- Response count by agent
SELECT agentId, COUNT(*) AS Responses
FROM v_ConversationTimeline_YourCompany
WHERE directionName = 'Outbound' AND typeName = 'Message'
GROUP BY agentId

-- Average messages per conversation
SELECT AVG(MsgCount) AS AvgMessages
FROM (
    SELECT conversationId, COUNT(*) AS MsgCount
    FROM v_ConversationTimeline_YourCompany
    WHERE typeName = 'Message'
    GROUP BY conversationId
) sub
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://atender-labs.gitbook.io/atender-labs-docs/analytics/views/conversations.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.