# Metrics

## v\_ActiveConversationsDaily\_{TenantName}

Daily count of active conversations, broken down by channel and team. Updated once per day.

A conversation is counted as **active** if its status is Active, Enqueued, or Calling. Conversations handled by Alia are **not** counted.

### Columns

| Column         | Type    | Description                                                           |
| -------------- | ------- | --------------------------------------------------------------------- |
| `tenantId`     | varchar | Tenant identifier                                                     |
| `snapshotDate` | date    | Date                                                                  |
| `channel`      | int     | Channel code                                                          |
| `channelName`  | varchar | Channel name (see below)                                              |
| `teamId`       | varchar | Team identifier                                                       |
| `activeCount`  | int     | Active conversations for this channel + team                          |
| `total`        | int     | Total active conversations across all channels and teams for that day |

### Channel Values

| Code | Name              |
| ---- | ----------------- |
| 0    | Unknown           |
| 1    | FacebookMessenger |
| 2    | WhatsApp          |
| 3    | Sms               |
| 4    | EmbeddedChat      |
| 5    | Email             |
| 6    | VoiceCall         |

### Example Queries

```sql
-- Total active conversations per day
SELECT DISTINCT snapshotDate, total
FROM v_ActiveConversationsDaily_YourCompany
ORDER BY snapshotDate DESC

-- Active conversations by channel per day
SELECT snapshotDate, channelName, SUM(activeCount) AS active
FROM v_ActiveConversationsDaily_YourCompany
GROUP BY snapshotDate, channelName
ORDER BY snapshotDate DESC, active DESC

-- Active conversations by team
SELECT snapshotDate, t.name AS teamName, SUM(ac.activeCount) AS active
FROM v_ActiveConversationsDaily_YourCompany ac
JOIN v_Team_YourCompany t ON ac.teamId = t.id
GROUP BY snapshotDate, t.name
ORDER BY snapshotDate DESC, active DESC

-- Trend: daily total over last 30 days
SELECT DISTINCT snapshotDate, total
FROM v_ActiveConversationsDaily_YourCompany
WHERE snapshotDate >= DATEADD(day, -30, GETDATE())
ORDER BY snapshotDate
```

{% hint style="info" %}
Data is available from February 22, 2026 onwards.
{% endhint %}

***

## v\_AggDailyConversations\_{TenantName}

Pre-computed daily conversation metrics grouped by date, channel, team, and status. Updated **hourly** (recomputes yesterday + today). Much faster than querying raw conversation data.

### Columns

| Column                    | Type    | Description                                      |
| ------------------------- | ------- | ------------------------------------------------ |
| `metricDate`              | date    | Date (based on conversation `started` timestamp) |
| `originalChannel`         | int     | Channel code                                     |
| `originalChannelName`     | varchar | Channel name                                     |
| `teamId`                  | varchar | Team identifier                                  |
| `status`                  | int     | Conversation status code                         |
| `statusName`              | varchar | Status name                                      |
| `conversationCount`       | int     | Total conversations for this group               |
| `aliaInitiatedCount`      | int     | Conversations initially handled by Alia          |
| `emailRepliedCount`       | int     | Email conversations with a first response        |
| `emailSlaMetCount`        | int     | Email conversations replied within 4 hours       |
| `sumFirstReplyMinutes`    | float   | Sum of first response times (minutes)            |
| `countFirstReply`         | int     | Conversations with a first response time         |
| `sumResolutionMinutes`    | float   | Sum of resolution times (minutes)                |
| `countResolution`         | int     | Conversations with a resolution time             |
| `transferredToHumanCount` | int     | Alia conversations transferred to a human        |
| `firstReplyUnder1h`       | int     | First response under 1 hour                      |
| `firstReply1hTo2h`        | int     | First response 1-2 hours                         |
| `firstReply2hTo4h`        | int     | First response 2-4 hours                         |
| `firstReply4hTo8h`        | int     | First response 4-8 hours                         |
| `firstReplyOver8h`        | int     | First response over 8 hours                      |

### Computing Averages

This view stores **sums and counts** so you can compute averages across any grouping:

```sql
-- Average first response time per day
SELECT
    metricDate,
    SUM(sumFirstReplyMinutes) / NULLIF(SUM(countFirstReply), 0) AS avgFirstReplyMinutes
FROM v_AggDailyConversations_YourCompany
GROUP BY metricDate
ORDER BY metricDate DESC

-- Average resolution time per team
SELECT
    teamId,
    SUM(sumResolutionMinutes) / NULLIF(SUM(countResolution), 0) AS avgResolutionMinutes
FROM v_AggDailyConversations_YourCompany
GROUP BY teamId
```

### Example Queries

```sql
-- Daily conversation count by status
SELECT metricDate, statusName, SUM(conversationCount) AS total
FROM v_AggDailyConversations_YourCompany
GROUP BY metricDate, statusName
ORDER BY metricDate DESC

-- Email SLA compliance rate per day
SELECT
    metricDate,
    SUM(emailSlaMetCount) AS slaMet,
    SUM(emailRepliedCount) AS totalReplied,
    CAST(SUM(emailSlaMetCount) AS float) / NULLIF(SUM(emailRepliedCount), 0) * 100 AS slaPercent
FROM v_AggDailyConversations_YourCompany
GROUP BY metricDate
ORDER BY metricDate DESC

-- Daily conversations by channel (last 30 days)
SELECT metricDate, originalChannelName, SUM(conversationCount) AS total
FROM v_AggDailyConversations_YourCompany
WHERE metricDate >= DATEADD(day, -30, GETDATE())
GROUP BY metricDate, originalChannelName
ORDER BY metricDate DESC, total DESC

-- Alia resolution rate per day
SELECT
    metricDate,
    SUM(aliaInitiatedCount) AS aliaStarted,
    SUM(transferredToHumanCount) AS transferred,
    SUM(aliaInitiatedCount) - SUM(transferredToHumanCount) AS resolvedByAlia
FROM v_AggDailyConversations_YourCompany
GROUP BY metricDate
ORDER BY metricDate DESC

-- First response time distribution
SELECT
    metricDate,
    SUM(firstReplyUnder1h) AS under1h,
    SUM(firstReply1hTo2h) AS [1hTo2h],
    SUM(firstReply2hTo4h) AS [2hTo4h],
    SUM(firstReply4hTo8h) AS [4hTo8h],
    SUM(firstReplyOver8h) AS over8h
FROM v_AggDailyConversations_YourCompany
GROUP BY metricDate
ORDER BY metricDate DESC
```

{% hint style="info" %}
Data is updated hourly. Each run recomputes the current day and previous day. Use `v_Conversation_{TenantName}` for real-time data or historical periods not yet aggregated.
{% endhint %}

***

## v\_AggDailyTags\_{TenantName}

Pre-computed daily conversation counts per tag, grouped by date, channel, team, and status. Updated **hourly**.

Conversations with multiple tags appear once per tag (rows are exploded via `CROSS APPLY`).

### Columns

| Column                | Type    | Description                                              |
| --------------------- | ------- | -------------------------------------------------------- |
| `metricDate`          | date    | Date (based on conversation `started` timestamp)         |
| `tagId`               | varchar | Tag identifier (join with `v_Tag_{TenantName}` for name) |
| `originalChannel`     | int     | Channel code                                             |
| `originalChannelName` | varchar | Channel name                                             |
| `teamId`              | varchar | Team identifier                                          |
| `status`              | int     | Conversation status code                                 |
| `statusName`          | varchar | Status name                                              |
| `conversationCount`   | int     | Conversations with this tag in this group                |

### Example Queries

```sql
-- Top tags this month
SELECT t.name AS tagName, SUM(a.conversationCount) AS total
FROM v_AggDailyTags_YourCompany a
JOIN v_Tag_YourCompany t ON a.tagId = t.id
WHERE a.metricDate >= DATEADD(month, -1, GETDATE())
GROUP BY t.name
ORDER BY total DESC

-- Tag trends per day (top 5 tags)
SELECT a.metricDate, t.name AS tagName, SUM(a.conversationCount) AS total
FROM v_AggDailyTags_YourCompany a
JOIN v_Tag_YourCompany t ON a.tagId = t.id
WHERE t.name IN (
    SELECT TOP 5 t2.name
    FROM v_AggDailyTags_YourCompany a2
    JOIN v_Tag_YourCompany t2 ON a2.tagId = t2.id
    GROUP BY t2.name
    ORDER BY SUM(a2.conversationCount) DESC
)
GROUP BY a.metricDate, t.name
ORDER BY a.metricDate DESC, total DESC

-- Tags by channel
SELECT t.name AS tagName, a.originalChannelName, SUM(a.conversationCount) AS total
FROM v_AggDailyTags_YourCompany a
JOIN v_Tag_YourCompany t ON a.tagId = t.id
GROUP BY t.name, a.originalChannelName
ORDER BY total DESC
```

{% hint style="info" %}
Only conversations with tags are included. Conversations without tags do not appear in this view.
{% endhint %}

***

## v\_AggDailyAgents\_{TenantName}

Pre-computed daily agent performance metrics grouped by date, agent, channel, team, and status. Updated **hourly**.

Only includes conversations that have an assigned agent.

### Columns

| Column                 | Type    | Description                                                  |
| ---------------------- | ------- | ------------------------------------------------------------ |
| `metricDate`           | date    | Date (based on conversation `started` timestamp)             |
| `agentId`              | varchar | Agent identifier (join with `v_Agent_{TenantName}` for name) |
| `originalChannel`      | int     | Channel code                                                 |
| `originalChannelName`  | varchar | Channel name                                                 |
| `teamId`               | varchar | Team identifier                                              |
| `status`               | int     | Conversation status code                                     |
| `statusName`           | varchar | Status name                                                  |
| `assignedCount`        | int     | Conversations assigned to this agent                         |
| `closedCount`          | int     | Conversations closed by this agent (status = Closed)         |
| `sumFirstReplyMinutes` | float   | Sum of first response times (minutes)                        |
| `countFirstReply`      | int     | Conversations with a first response time                     |
| `sumResolutionMinutes` | float   | Sum of resolution times (minutes)                            |
| `countResolution`      | int     | Conversations with a resolution time                         |

### Example Queries

```sql
-- Agent leaderboard: conversations handled this month
SELECT
    p.firstName + ' ' + p.lastName AS agentName,
    SUM(a.assignedCount) AS assigned,
    SUM(a.closedCount) AS closed
FROM v_AggDailyAgents_YourCompany a
JOIN v_Agent_YourCompany ag ON a.agentId = ag.id
JOIN v_AgentProfile_YourCompany p ON ag.profileId = p.id
WHERE a.metricDate >= DATEADD(month, -1, GETDATE())
GROUP BY p.firstName, p.lastName
ORDER BY assigned DESC

-- Average response time per agent
SELECT
    p.firstName + ' ' + p.lastName AS agentName,
    SUM(a.sumFirstReplyMinutes) / NULLIF(SUM(a.countFirstReply), 0) AS avgFirstReplyMin,
    SUM(a.sumResolutionMinutes) / NULLIF(SUM(a.countResolution), 0) AS avgResolutionMin
FROM v_AggDailyAgents_YourCompany a
JOIN v_Agent_YourCompany ag ON a.agentId = ag.id
JOIN v_AgentProfile_YourCompany p ON ag.profileId = p.id
GROUP BY p.firstName, p.lastName
ORDER BY avgFirstReplyMin

-- Agent workload by channel
SELECT
    a.agentId,
    a.originalChannelName,
    SUM(a.assignedCount) AS total
FROM v_AggDailyAgents_YourCompany a
GROUP BY a.agentId, a.originalChannelName
ORDER BY total DESC
```

{% hint style="info" %}
Only conversations with an assigned agent are included. Unassigned conversations (e.g., still in queue) do not appear.
{% endhint %}


---

# 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/active-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.