> ## Documentation Index
> Fetch the complete documentation index at: https://help.treble.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# fact_conversations

> One row per conversation handled by an agent.

# fact\_conversations

Each row represents a conversation on the agent platform. It includes information about the agent who handled it, the team, the final status, and data of the associated conversation flow.

## What questions does it answer?

* How many conversations were handled in a period?
* Which agent handled each conversation?
* How long did it take to resolve a conversation?
* How many conversations were inbound vs outbound?
* How many transfers did each conversation have?
* What was the user's rating?

## Columns

| Column                   | Type       | Description                                                                              |
| ------------------------ | ---------- | ---------------------------------------------------------------------------------------- |
| `conversation_id`        | Int32      | Unique conversation identifier                                                           |
| `company_id`             | Int32      | Company identifier (filtered automatically)                                              |
| `survey_user_id`         | Int64      | Identifier of the associated conversation flow. `0` if no flow is linked                 |
| `contact_id`             | Int32      | Contact identifier on the agent platform                                                 |
| `created_at`             | DateTime64 | Date and time of conversation creation                                                   |
| `finished_at`            | DateTime64 | Date and time of closing. `NULL` if still open                                           |
| `assigned_at`            | DateTime64 | Date and time of assignment to the agent. `NULL` if not assigned                         |
| `status`                 | String     | Current status: `OPEN`, `CLOSED`, `PENDING`, etc.                                        |
| `finish_type`            | String     | How it was closed: `AGENT`, `SYSTEM`, `TIMEOUT`, etc.                                    |
| `rating`                 | Int32      | User rating (1-5). `0` if not rated                                                      |
| `is_redirected`          | Bool       | `true` if the conversation was transferred at least once                                 |
| `agent_id`               | Int32      | Identifier of the agent who handled it. `0` if not assigned                              |
| `agent_name`             | String     | Full name of the agent                                                                   |
| `tag_id`                 | Int32      | Identifier of the assigned tag/queue                                                     |
| `tag_name`               | String     | Tag/queue name                                                                           |
| `team_id`                | Int32      | Team identifier                                                                          |
| `team_name`              | String     | Team name                                                                                |
| `channel_id`             | Int32      | Channel identifier (WhatsApp line)                                                       |
| `inbound_outbound`       | String     | `inbound` if the user started the conversation, `outbound` if started by campaign        |
| `campaign_name`          | String     | Name of the associated campaign                                                          |
| `transfer_count`         | UInt32     | Number of transfers the conversation had                                                 |
| `first_agent_message_at` | DateTime64 | Date of the first agent message. `NULL` if the agent did not respond                     |
| `first_response_sec`     | Int32      | Seconds between conversation creation and the first agent message. `NULL` if no response |
| `helpdesk_contact_id`    | String     | Contact identifier in the external CRM (if integration exists)                           |
| `contact_wa_id`          | String     | Contact's WhatsApp number                                                                |

## Example queries

### Conversations per agent in the last 30 days

```sql theme={null}
SELECT
    agent_name,
    count() AS total_conversations,
    countIf(status = 'CLOSED') AS closed,
    round(avg(if(finished_at IS NOT NULL,
        dateDiff('minute', created_at, finished_at), NULL)), 1) AS avg_resolution_time_min
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY agent_name
ORDER BY total_conversations DESC
```

### Service level per team (customizable SLA)

```sql theme={null}
SELECT
    team_name,
    count() AS conversations,
    -- You can change 120 to whatever threshold you need (in seconds)
    round(countIf(first_response_sec <= 120) * 100.0 / count(), 1) AS sla_120s_pct,
    round(countIf(first_response_sec <= 60) * 100.0 / count(), 1) AS sla_60s_pct,
    round(avg(first_response_sec), 0) AS avg_response_sec
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 7 DAY
  AND first_response_sec IS NOT NULL
GROUP BY team_name
ORDER BY sla_120s_pct DESC
```

### Inbound vs outbound conversations per day

```sql theme={null}
SELECT
    toDate(created_at) AS day,
    inbound_outbound,
    count() AS total
FROM client_analytics.fact_conversations
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY day, inbound_outbound
ORDER BY day DESC
```