> ## 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_agent_daily

> Pre-aggregated productivity metrics per agent per day.

# fact\_agent\_daily

Pre-aggregated table with the main productivity metrics of each agent per day. Useful for executive reports without having to write complex queries.

## What questions does it answer?

* How many conversations did each agent handle per day?
* What was the average first response time?
* How many messages did each agent send?
* How long was each agent available?
* What was the average rating (CSAT)?

## Columns

| Column                       | Type       | Description                                                                |
| ---------------------------- | ---------- | -------------------------------------------------------------------------- |
| `company_id`                 | Int32      | Company identifier (filtered automatically)                                |
| `agent_id`                   | Int32      | Agent identifier                                                           |
| `day`                        | Date       | Calendar day                                                               |
| `agent_name`                 | String     | Full name of the agent                                                     |
| `chats_handled`              | UInt32     | Conversations handled (agent sent at least 1 message)                      |
| `chats_resolved`             | UInt32     | Closed conversations                                                       |
| `chats_transferred_sent`     | UInt32     | Transfers sent by the agent                                                |
| `chats_transferred_received` | UInt32     | Transfers received by the agent                                            |
| `messages_sent`              | UInt32     | Total messages sent by the agent                                           |
| `hsm_messages_sent`          | UInt32     | Template (HSM) messages sent                                               |
| `first_message_at`           | DateTime64 | First message of the agent in the day. `NULL` if no messages sent          |
| `last_message_at`            | DateTime64 | Last message of the agent in the day                                       |
| `avg_first_response_sec`     | Float64    | Average first response time (in seconds). `0` if not applicable            |
| `avg_response_time_sec`      | Float64    | Average response time between messages (in seconds). `0` if not applicable |
| `avg_resolution_min`         | Float64    | Average resolution time (in minutes). `0` if no conversations closed       |
| `csat_avg`                   | Float64    | Average rating (CSAT). `0` if no ratings                                   |
| `service_level_pct`          | Float64    | Percentage of conversations with first response in less than 120 seconds   |
| `available_minutes`          | Float64    | Minutes the agent was online during the day                                |

<Note>
  To get the agent's team, join this table with `dim_agent_tags` using `agent_id`.
</Note>

## Example queries

### Weekly productivity per agent

```sql theme={null}
SELECT
    agent_name,
    sum(chats_handled) AS total_chats,
    sum(messages_sent) AS total_messages,
    round(avg(avg_first_response_sec), 0) AS first_response_sec,
    round(avg(service_level_pct) * 100, 1) AS service_level_pct,
    round(sum(available_minutes) / 60, 1) AS available_hours
FROM client_analytics.fact_agent_daily
WHERE day >= today() - 7
GROUP BY agent_name
ORDER BY total_chats DESC
```

### Productivity per team

```sql theme={null}
SELECT
    at.team_name,
    sum(ad.chats_handled) AS total_chats,
    round(avg(ad.csat_avg), 2) AS avg_csat
FROM client_analytics.fact_agent_daily ad
INNER JOIN client_analytics.dim_agent_tags at ON at.agent_id = ad.agent_id
WHERE ad.day >= today() - 30
GROUP BY at.team_name
ORDER BY total_chats DESC
```