As traduções são geradas por tradução automática. Em caso de conflito entre o conteúdo da tradução e da versão original em inglês, a versão em inglês prevalecerá.

# Consultas de referência para o data lake Connect Customer
<a name="data-lake-reference-queries"></a>

Este tópico fornece consultas SQL do Athena (motor Trino v3) para calcular métricas comuns do Connect Customer a partir de tabelas de data lake. Todas as consultas usam identificadores entre aspas duplas e assumem um nome de banco de dados. `connect_datalake` Ajuste o nome do banco de dados para corresponder à configuração do catálogo do Glue.

Substitua `<YOUR_INSTANCE_ID>` em cada consulta pelo ID da instância do Connect Customer.

**Topics**
+ [Métricas de contato e fila](#data-lake-rq-contact-queue)
+ [Métricas de desempenho do agente](#data-lake-rq-agent-performance)
+ [Métricas do chat](#data-lake-rq-chat)
+ [Métricas de análise de conversação](#data-lake-rq-contact-lens)
+ [Métricas de agentes de IA](#data-lake-rq-ai-agent)
+ [Métricas de fluxo](#data-lake-rq-flow)
+ [Métricas de avaliação](#data-lake-rq-evaluations)
+ [Métricas da campanha externa](#data-lake-rq-campaigns)
+ [Métricas de casos](#data-lake-rq-cases)
+ [Métricas de bots](#data-lake-rq-bot)
+ [Padrões de consulta comuns](#data-lake-rq-patterns)
+ [Adesão ao cronograma do agente (nível de atividade)](#data-lake-rq-schedule-adherence)
+ [Práticas recomendadas](#data-lake-rq-best-practices)

## Métricas de contato e fila
<a name="data-lake-rq-contact-queue"></a>

### Taxa de abandono
<a name="data-lake-rq-abandonment-rate"></a>

**Definição:** Porcentagem de contatos desconectados pelo cliente enquanto estão na fila. Retornos de chamada excluídos.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    CAST(SUM("is_abandoned") AS DOUBLE) 
        / NULLIF(SUM("is_queued"), 0) * 100.0 AS "abandonment_rate_pct"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id"
ORDER BY "abandonment_rate_pct" DESC;
```

### Contatos abandonados
<a name="data-lake-rq-contacts-abandoned"></a>

**Definição:** Contagem de contatos desconectados pelo cliente enquanto aguardam na fila.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM("is_abandoned") AS "contacts_abandoned"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Contatos abandonados em X segundos
<a name="data-lake-rq-contacts-abandoned-x-seconds"></a>

**Definição:** Contagem de contatos abandonados dentro de X segundos após serem enfileirados.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM(
        CASE WHEN "is_abandoned" = 1 
             AND "queue_time_ms" <= 30000 
             THEN 1 ELSE 0 END
    ) AS "contacts_abandoned_in_30s"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Tempo médio de abandono da fila
<a name="data-lake-rq-avg-queue-abandon-time"></a>

**Definição:** Tempo médio de espera dos contatos na fila antes de serem abandonados.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    AVG("abandon_time_ms") / 1000.0 AS "avg_queue_abandon_time_sec"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "is_abandoned" = 1
  AND "abandon_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Tempo médio de resposta na fila
<a name="data-lake-rq-avg-queue-answer-time"></a>

**Definição:** Tempo médio de espera dos contatos na fila antes de serem atendidos por um agente.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    AVG("queue_answer_time_ms") / 1000.0 AS "avg_queue_answer_time_sec"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "is_handled" = 1
  AND "queue_answer_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Nível de serviço
<a name="data-lake-rq-service-level"></a>

**Definição:** Contagem e porcentagem de contatos respondidos em X segundos.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM(CASE WHEN "is_handled" = 1 AND "queue_answer_time_ms" <= 20000 
             THEN 1 ELSE 0 END) AS "contacts_answered_in_20s",
    SUM("is_queued") AS "contacts_queued",
    CAST(SUM(CASE WHEN "is_handled" = 1 AND "queue_answer_time_ms" <= 20000 
                  THEN 1 ELSE 0 END) AS DOUBLE)
        / NULLIF(SUM("is_queued"), 0) * 100.0 AS "service_level_20s_pct"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Contatos na fila
<a name="data-lake-rq-contacts-queued"></a>

**Definição:** Contagem de contatos colocados em uma fila.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM("is_queued") AS "contacts_queued"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Contatos processados
<a name="data-lake-rq-contacts-handled"></a>

**Definição:** Contagem de contatos conectados a um agente.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM("is_handled") AS "contacts_handled"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Contatos transferidos para dentro
<a name="data-lake-rq-contacts-transferred-in"></a>

**Definição:** Contatos transferidos para uma fila.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM("is_transferred_in") AS "contacts_transferred_in"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Contatos transferidos para fora
<a name="data-lake-rq-contacts-transferred-out"></a>

**Definição:** Contatos transferidos de uma fila.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM("is_transferred_out") AS "contacts_transferred_out",
    SUM("is_transferred_out_internal") AS "transferred_out_internal",
    SUM("is_transferred_out_external") AS "transferred_out_external"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Tempo máximo em fila
<a name="data-lake-rq-max-queued-time"></a>

**Definição:** Maior tempo que qualquer contato passou esperando na fila.

**Tabela de origem:** `contact_record`

```
SELECT
    "queue_id",
    MAX("queue_duration_ms") / 1000.0 AS "max_queued_time_sec"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "queue_duration_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Duração média do contato
<a name="data-lake-rq-avg-contact-duration"></a>

**Definição:** Tempo médio desde o início do contato até a desconexão.

**Tabela de origem:** `contact_record`

```
SELECT
    "queue_id",
    AVG(
        date_diff('millisecond', "initiation_timestamp", "disconnect_timestamp")
    ) / 1000.0 AS "avg_contact_duration_sec"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "initiation_timestamp" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

## Métricas de desempenho do agente
<a name="data-lake-rq-agent-performance"></a>

### Tempo médio de processamento
<a name="data-lake-rq-avg-handle-time"></a>

**Definição:** Tempo médio desde a conexão do contato até a conclusão do ACW.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "agent_id",
    AVG("handle_time_ms") / 1000.0 AS "avg_handle_time_sec"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "is_handled" = 1
  AND "handle_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "agent_id";
```

### Tempo de trabalho pós-atendimento
<a name="data-lake-rq-acw-time"></a>

**Definição:** Tempo total que os agentes passaram no estado ACW.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "agent_id",
    SUM("after_contact_work_time_ms") / 1000.0 AS "total_acw_time_sec"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "after_contact_work_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "agent_id";
```

### Tempo de espera do cliente
<a name="data-lake-rq-customer-hold-time"></a>

**Definição:** Tempo total que os clientes passaram em espera após se conectarem ao agente.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "agent_id",
    SUM("customer_hold_time_ms") / 1000.0 AS "total_hold_time_sec"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "customer_hold_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "agent_id";
```

### Tempo de ociosidade do agente
<a name="data-lake-rq-agent-idle-time"></a>

**Definição:** Tempo gasto pelo agente no status Disponível sem lidar com contatos.

**Tabela de origem:** `agent_statistic_record`

```
SELECT
    "user_id" AS "agent_id",
    SUM("agent_idle_time") / 1000.0 AS "total_idle_time_sec"
FROM "connect_datalake"."agent_statistic_record"
WHERE "published_date" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "published_date" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "user_id";
```

### Ocupação
<a name="data-lake-rq-occupancy"></a>

**Definição:** Porcentagem de tempo em que os agentes estavam ativos nos contatos versus disponíveis e ativos.

**Tabela de origem:** `agent_statistic_record`

```
SELECT
    "user_id" AS "agent_id",
    CAST(SUM("agent_on_contact_time") AS DOUBLE)
        / NULLIF(SUM("agent_on_contact_time") + SUM("agent_idle_time"), 0) 
        * 100.0 AS "occupancy_pct"
FROM "connect_datalake"."agent_statistic_record"
WHERE "published_date" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "published_date" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "user_id";
```

### Ausência de resposta do atendente
<a name="data-lake-rq-agent-non-response"></a>

**Definição:** Contagem de contatos roteados para o agente, mas não respondidos.

**Tabela de origem:** `agent_queue_statistic_record`

```
SELECT
    "user_id" AS "agent_id",
    "queue_id",
    SUM("agent_non_response") AS "agent_non_response_count"
FROM "connect_datalake"."agent_queue_statistic_record"
WHERE "published_date" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "published_date" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "user_id", "queue_id";
```

### Taxa de atendimento do agente
<a name="data-lake-rq-agent-answer-rate"></a>

**Definição:** Porcentagem de contatos roteados respondidos pelo agente.

**Tabela de origem:** `agent_queue_statistic_record`

```
SELECT
    "user_id" AS "agent_id",
    CAST(SUM("contacts_handled") AS DOUBLE) 
        / NULLIF(SUM("contacts_offered"), 0) * 100.0 AS "agent_answer_rate_pct"
FROM "connect_datalake"."agent_queue_statistic_record"
WHERE "published_date" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "published_date" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "user_id";
```

### Tempo online
<a name="data-lake-rq-online-time"></a>

**Definição:** Tempo total em que o agente CCP foi definido para um status diferente de Off-line.

**Tabela de origem:** `agent_statistic_record`

```
SELECT
    "user_id" AS "agent_id",
    SUM("online_time") / 1000.0 AS "total_online_time_sec"
FROM "connect_datalake"."agent_statistic_record"
WHERE "published_date" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "published_date" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "user_id";
```

## Métricas do chat
<a name="data-lake-rq-chat"></a>

### Tempo médio da primeira resposta do atendente
<a name="data-lake-rq-avg-agent-first-response-time"></a>

**Definição:** Tempo médio para o agente enviar a primeira mensagem após obter um contato no chat.

**Tabela de origem:** `contact_record`

```
SELECT
    "queue_id",
    AVG("chat_contact_metrics_agent_first_response_time_ms") / 1000.0 
        AS "avg_agent_first_response_sec"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'CHAT'
  AND "chat_contact_metrics_agent_first_response_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Tempo médio de resposta do atendente
<a name="data-lake-rq-avg-agent-response-time"></a>

**Definição:** Tempo médio que os agentes levam para responder às mensagens dos clientes.

**Tabela de origem:** `contact_record`

```
SELECT
    "queue_id",
    CAST(SUM("chat_agent_metrics_total_response_time_ms") AS DOUBLE)
        / NULLIF(SUM("chat_agent_metrics_num_responses"), 0) / 1000.0
        AS "avg_agent_response_time_sec"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'CHAT'
  AND "chat_agent_metrics_total_response_time_ms" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Média total de mensagens
<a name="data-lake-rq-avg-total-messages"></a>

**Definição:** Média total de mensagens por contato no chat.

**Tabela de origem:** `contact_record`

```
SELECT
    "queue_id",
    AVG(CAST("chat_contact_metrics_total_messages" AS DOUBLE)) AS "avg_total_messages"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'CHAT'
  AND "chat_contact_metrics_total_messages" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

### Conversas abandonadas
<a name="data-lake-rq-conversations-abandoned"></a>

**Definição:** Contatos em que o bate-papo foi abandonado pelo agente ou cliente.

**Tabela de origem:** `contact_record`

```
SELECT
    "queue_id",
    COUNT(*) AS "conversations_abandoned"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'CHAT'
  AND ("chat_agent_metrics_conversation_abandon" = true 
       OR "chat_customer_metrics_conversation_abandon" = true)
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id";
```

## Métricas de análise de conversação
<a name="data-lake-rq-contact-lens"></a>

### Tempo médio de conversa
<a name="data-lake-rq-avg-talk-time"></a>

**Definição:** Tempo médio combinado de conversação entre agentes e clientes por contato de voz.

**Tabela de origem:** `contact_lens_conversational_analytics`

```
SELECT
    AVG("talk_time_total_ms") / 1000.0 AS "avg_talk_time_sec"
FROM "connect_datalake"."contact_lens_conversational_analytics"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'VOICE'
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

### Tempo médio sem conversa
<a name="data-lake-rq-avg-non-talk-time"></a>

**Definição:** Tempo médio de espera mais o tempo de silêncio por contato de voz.

**Tabela de origem:** `contact_lens_conversational_analytics`

```
SELECT
    AVG("non_talk_time_total_ms") / 1000.0 AS "avg_non_talk_time_sec"
FROM "connect_datalake"."contact_lens_conversational_analytics"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'VOICE'
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

### Pontuações de sentimento
<a name="data-lake-rq-sentiment-scores"></a>

**Definição:** pontuações gerais de sentimento para agente e cliente.

**Tabela de origem:** `contact_lens_conversational_analytics`

```
SELECT
    AVG("sentiment_overall_score_agent") AS "avg_agent_sentiment",
    AVG("sentiment_overall_score_customer") AS "avg_customer_sentiment",
    AVG("sentiment_end_score_agent") AS "avg_agent_end_sentiment",
    AVG("sentiment_end_score_customer") AS "avg_customer_end_sentiment"
FROM "connect_datalake"."contact_lens_conversational_analytics"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

### Média de interrupções do atendente
<a name="data-lake-rq-avg-agent-interruptions"></a>

**Definição:** Contagem média de interrupções do agente por contato.

**Tabela de origem:** `contact_lens_conversational_analytics`

```
SELECT
    AVG(CAST("interruptions_agent_count" AS DOUBLE)) AS "avg_agent_interruptions"
FROM "connect_datalake"."contact_lens_conversational_analytics"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "channel" = 'VOICE'
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

## Métricas de agentes de IA
<a name="data-lake-rq-ai-agent"></a>

### Taxa de sucesso de invocação de agentes de IA
<a name="data-lake-rq-ai-invocation-success-rate"></a>

**Definição:** Taxa de invocações bem-sucedidas do AI Agent.

**Tabela de origem:** `ai_agent`

```
SELECT
    "ai_agent_name",
    SUM(CASE WHEN "invocation_success" = true THEN 1 ELSE 0 END) AS "success_count",
    COUNT(*) AS "total_invocations",
    CAST(SUM(CASE WHEN "invocation_success" = true THEN 1 ELSE 0 END) AS DOUBLE)
        / NULLIF(COUNT(*), 0) * 100.0 AS "success_rate_pct"
FROM "connect_datalake"."ai_agent"
WHERE "creation_timestamp" >= CAST('2026-06-09' AS TIMESTAMP) * 1000
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
  AND "ai_agent_id" IS NOT NULL
GROUP BY "ai_agent_name";
```

### Taxa de transferência de IA
<a name="data-lake-rq-ai-handoff-rate"></a>

**Definição:** Taxa de sessões de IA que foram escaladas para agentes humanos.

**Tabela de origem:** `ai_session`

```
SELECT
    SUM(CASE WHEN "is_handed_off" = true THEN 1 ELSE 0 END) AS "ai_handoffs",
    COUNT(*) AS "ai_involved_contacts",
    CAST(SUM(CASE WHEN "is_handed_off" = true THEN 1 ELSE 0 END) AS DOUBLE)
        / NULLIF(COUNT(*), 0) * 100.0 AS "handoff_rate_pct"
FROM "connect_datalake"."ai_session"
WHERE "creation_timestamp" >= CAST('2026-06-09' AS TIMESTAMP) * 1000
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
  AND "ai_session_id" IS NOT NULL;
```

### Pontuações de qualidade de IA
<a name="data-lake-rq-ai-quality-scores"></a>

**Definição: Pontuação** média de sucesso, fidelidade e completude de gols.

**Tabela de origem:** `ai_session`

```
SELECT
    AVG("goal_success_rate") AS "avg_goal_success_rate",
    AVG("faithfulness_score") AS "avg_faithfulness_score",
    AVG("completeness_score") AS "avg_completeness_score"
FROM "connect_datalake"."ai_session"
WHERE "creation_timestamp" >= CAST('2026-06-09' AS TIMESTAMP) * 1000
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
  AND "goal_success_rate" IS NOT NULL;
```

### Precisão da ferramenta de IA
<a name="data-lake-rq-ai-tool-accuracy"></a>

**Definição:** pontuações de precisão para uso, seleção e utilização de parâmetros da ferramenta de IA.

**Tabela de origem:** `ai_tool`

```
SELECT
    "ai_tool_name",
    AVG("ai_tool_parameter_accuracy") AS "avg_parameter_accuracy",
    AVG("ai_tool_selection_accuracy") AS "avg_selection_accuracy",
    AVG("ai_tool_utilization_accuracy") AS "avg_use_accuracy"
FROM "connect_datalake"."ai_tool"
WHERE "creation_timestamp" >= CAST('2026-06-09' AS TIMESTAMP) * 1000
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
  AND "ai_tool_id" IS NOT NULL
GROUP BY "ai_tool_name";
```

## Métricas de fluxo
<a name="data-lake-rq-flow"></a>

### Fluxos iniciados
<a name="data-lake-rq-flows-started"></a>

**Definição:** Contagem dos fluxos que iniciaram a execução.

**Tabela de origem:** `contact_flow_events`

```
SELECT
    "flow_resource_id",
    "flow_type",
    COUNT(*) AS "flows_started"
FROM "connect_datalake"."contact_flow_events"
WHERE "start_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "start_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "flow_resource_id", "flow_type";
```

### Porcentagem do resultado do fluxo
<a name="data-lake-rq-flow-outcome-pct"></a>

**Definição:** Porcentagem de cada tipo de resultado de fluxo.

**Tabela de origem:** `contact_flow_events`

```
WITH flow_counts AS (
    SELECT
        "flow_resource_id",
        "flow_outcome",
        COUNT(*) AS "outcome_count",
        SUM(COUNT(*)) OVER (PARTITION BY "flow_resource_id") AS "total_completed"
    FROM "connect_datalake"."contact_flow_events"
    WHERE "start_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
      AND "start_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
      AND "end_timestamp" IS NOT NULL
      AND "instance_id" = '<YOUR_INSTANCE_ID>'
    GROUP BY "flow_resource_id", "flow_outcome"
)
SELECT
    "flow_resource_id",
    "flow_outcome",
    "outcome_count",
    CAST("outcome_count" AS DOUBLE) / "total_completed" * 100.0 AS "outcome_pct"
FROM flow_counts
ORDER BY "flow_resource_id", "outcome_pct" DESC;
```

### Tempo médio de fluxo
<a name="data-lake-rq-avg-flow-time"></a>

**Definição:** Duração média das execuções de fluxo.

**Tabela de origem:** `contact_flow_events`

```
SELECT
    "flow_resource_id",
    AVG(
        date_diff('millisecond', "start_timestamp", "end_timestamp")
    ) / 1000.0 AS "avg_flow_time_sec"
FROM "connect_datalake"."contact_flow_events"
WHERE "start_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "start_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "end_timestamp" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "flow_resource_id";
```

## Métricas de avaliação
<a name="data-lake-rq-evaluations"></a>

### Avaliações realizadas
<a name="data-lake-rq-evaluations-performed"></a>

**Definição:** Número de avaliações enviadas.

**Tabela de origem:** `contact_evaluation_record`

```
SELECT
    COUNT(DISTINCT "evaluation_id") AS "evaluations_performed"
FROM "connect_datalake"."contact_evaluation_record"
WHERE "evaluation_submitted_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "evaluation_submitted_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "item_type" = 'Form'
  AND "to_delete" = false
  AND ("evaluation_type" IS NULL OR "evaluation_type" != 'calibration')
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

### Pontuação de avaliação média
<a name="data-lake-rq-avg-evaluation-score"></a>

**Definição:** Pontuação média da avaliação em todas as avaliações enviadas.

**Tabela de origem:** `contact_evaluation_record`

```
SELECT
    AVG("score") AS "avg_evaluation_score_pct"
FROM "connect_datalake"."contact_evaluation_record"
WHERE "evaluation_submitted_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "evaluation_submitted_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "item_type" = 'Form'
  AND "to_delete" = false
  AND ("evaluation_type" IS NULL OR "evaluation_type" != 'calibration')
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

### Porcentagem de falhas automáticas
<a name="data-lake-rq-automatic-fails"></a>

**Definição:** Porcentagem de avaliações que acionaram a falha automática.

**Tabela de origem:** `contact_evaluation_record`

```
SELECT
    CAST(
        COUNT(DISTINCT CASE WHEN "automatic_fail" = true THEN "evaluation_id" END) AS DOUBLE
    ) / NULLIF(COUNT(DISTINCT "evaluation_id"), 0) * 100.0 
        AS "automatic_fail_pct"
FROM "connect_datalake"."contact_evaluation_record"
WHERE "evaluation_submitted_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "evaluation_submitted_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "item_type" = 'Form'
  AND "to_delete" = false
  AND ("evaluation_type" IS NULL OR "evaluation_type" != 'calibration')
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

## Métricas da campanha externa
<a name="data-lake-rq-campaigns"></a>

### Contatos da campanha
<a name="data-lake-rq-campaign-contacts"></a>

**Definição:** Contagem de contatos externos da campanha.

**Tabela de origem:** `contact_record`

```
SELECT
    "campaign_id",
    COUNT(*) AS "campaign_contacts"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "campaign_id" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "campaign_id";
```

### Resposta humana
<a name="data-lake-rq-human-answered"></a>

**Definição:** Chamadas externas de campanha conectadas a um cliente ativo.

**Tabela de origem:** `contact_record`

```
SELECT
    "campaign_id",
    COUNT(*) AS "human_answered"
FROM "connect_datalake"."contact_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "campaign_id" IS NOT NULL
  AND "answering_machine_detection_status" = 'HUMAN_ANSWERED'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "campaign_id";
```

## Métricas de casos
<a name="data-lake-rq-cases"></a>

### Casos criados
<a name="data-lake-rq-cases-created"></a>

**Definição:** Total de casos criados em um período de tempo.

**Tabela de origem:** `case_events`

```
SELECT
    COUNT(DISTINCT "case_id") AS "cases_created"
FROM "connect_datalake"."case_events"
WHERE "event_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "event_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "event_type" = 'CASE.CREATED'
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

### Tempo médio de resolução de caso
<a name="data-lake-rq-avg-case-resolution"></a>

**Definição:** Tempo médio desde a criação do caso até o encerramento.

**Tabela de origem:** `case_events`

```
SELECT
    AVG(
        date_diff('hour', "created_timestamp", "last_closed_timestamp")
    ) AS "avg_resolution_time_hours"
FROM "connect_datalake"."case_events"
WHERE "last_closed_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "last_closed_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "created_timestamp" IS NOT NULL
  AND "instance_id" = '<YOUR_INSTANCE_ID>';
```

## Métricas de bots
<a name="data-lake-rq-bot"></a>

### Resultados da conversa do bot
<a name="data-lake-rq-bot-outcomes"></a>

**Definição:** Detalhamento percentual dos resultados da conversa do bot.

**Tabela de origem:** `bot_conversations`

```
WITH bot_outcomes AS (
    SELECT
        "bot_id",
        "bot_conversation_outcome",
        COUNT(*) AS "cnt",
        SUM(COUNT(*)) OVER (PARTITION BY "bot_id") AS "total"
    FROM "connect_datalake"."bot_conversations"
    WHERE "bot_conversation_start_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
      AND "bot_conversation_start_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
      AND "instance_id" = '<YOUR_INSTANCE_ID>'
    GROUP BY "bot_id", "bot_conversation_outcome"
)
SELECT
    "bot_id",
    "bot_conversation_outcome",
    "cnt",
    CAST("cnt" AS DOUBLE) / "total" * 100.0 AS "outcome_pct"
FROM bot_outcomes;
```

## Padrões de consulta comuns
<a name="data-lake-rq-patterns"></a>

Os padrões a seguir mostram como combinar várias tabelas de data lake para criar painéis e relatórios abrangentes.

### Painel de resumo diário
<a name="data-lake-rq-daily-summary"></a>

**Definição:** métricas abrangentes de filas diárias, incluindo nível de serviço.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    "queue_id",
    SUM("is_queued") AS "contacts_queued",
    SUM("is_handled") AS "contacts_handled",
    SUM("is_abandoned") AS "contacts_abandoned",
    AVG(CASE WHEN "is_handled" = 1 THEN "queue_answer_time_ms" END) / 1000.0 
        AS "avg_answer_time_sec",
    AVG(CASE WHEN "is_handled" = 1 THEN "handle_time_ms" END) / 1000.0 
        AS "avg_handle_time_sec",
    CAST(SUM(CASE WHEN "is_handled" = 1 AND "queue_answer_time_ms" <= 20000 
                  THEN 1 ELSE 0 END) AS DOUBLE)
        / NULLIF(SUM("is_queued"), 0) * 100.0 AS "sl_20s_pct"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY "queue_id"
ORDER BY "contacts_queued" DESC;
```

### Análise de tendências horárias
<a name="data-lake-rq-hourly-trend"></a>

**Definição:** Volume de contatos por hora e tendências de nível de serviço.

**Tabela de origem:** `contact_statistic_record`

```
SELECT
    date_trunc('hour', "disconnect_timestamp") AS "hour",
    "queue_id",
    SUM("is_queued") AS "contacts_queued",
    SUM("is_handled") AS "contacts_handled",
    SUM("is_abandoned") AS "contacts_abandoned",
    CAST(SUM("is_abandoned") AS DOUBLE) 
        / NULLIF(SUM("is_queued"), 0) * 100.0 AS "abandon_rate_pct",
    AVG(CASE WHEN "is_handled" = 1 THEN "handle_time_ms" END) / 1000.0 AS "aht_sec"
FROM "connect_datalake"."contact_statistic_record"
WHERE "disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND "disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND "instance_id" = '<YOUR_INSTANCE_ID>'
GROUP BY date_trunc('hour', "disconnect_timestamp"), "queue_id"
ORDER BY "hour";
```

### Contatos enriquecidos com lentes de contato
<a name="data-lake-rq-contact-lens-enriched"></a>

**Definição:** Enriqueça os registros de contato com a análise de lentes de contato.

**Tabela de origem:** `contact_record` unida com `contact_lens_conversational_analytics`

```
SELECT
    cr."contact_id",
    cr."queue_id",
    cr."agent_id",
    cr."agent_interaction_duration_ms" / 1000.0 AS "interaction_sec",
    cl."talk_time_agent_ms" / 1000.0 AS "agent_talk_sec",
    cl."talk_time_customer_ms" / 1000.0 AS "customer_talk_sec",
    cl."sentiment_overall_score_agent",
    cl."sentiment_overall_score_customer"
FROM "connect_datalake"."contact_record" cr
JOIN "connect_datalake"."contact_lens_conversational_analytics" cl
    ON cr."contact_id" = cl."contact_id"
    AND cr."instance_id" = cl."instance_id"
WHERE cr."disconnect_timestamp" >= TIMESTAMP '2026-06-09 00:00:00'
  AND cr."disconnect_timestamp" <  TIMESTAMP '2026-06-10 00:00:00'
  AND cr."instance_id" = '<YOUR_INSTANCE_ID>'
  AND cr."channel" = 'VOICE';
```

## Adesão ao cronograma do agente (nível de atividade)
<a name="data-lake-rq-schedule-adherence"></a>

**Definição:** compara o estado real da atividade de um agente (de`agent_statistic_record`) com suas atividades de turno programadas (das tabelas de agendamento) para cada intervalo de tempo em um dia. Produz uma determinação de adesão por intervalo: IN (o agente estava fazendo o que estava programado para fazer) ou OUT (não estava).

**Colunas de saída:** agente, data, início, fim, atividade agendada, atividade real, estado de adesão, duração

**Tabelas de origem:**
+ `staff_shifts`— Turnos de agente do dia (versão mais recente não excluída)
+ `staff_shift_activities`— Blocos de atividades programadas em cada turno
+ `shift_activities`— Pesquisa do nome da atividade (mapeia o ARN para um nome legível por humanos)
+ `agent_statistic_record`— Estado real do agente por intervalo
+ `users`— Nome do agente e resolução de ARN

**Lógica de adesão (simplificada):**
+ “Aberto” agendado — o agente está ATIVADO se o status for Disponível, Em contato ou ACW
+ “Pausa” programada — o agente entra se o status for Pausa ou Almoço
+ “Reunião” agendada — o agente está ATIVO se o status for Treinamento ou Reunião
+ Caso contrário — FORA

```
WITH latest_shift_versions AS (
    -- Get the latest (non-deleted) shift version per shift_id
    SELECT
        shift_id,
        MAX(shift_version) AS max_version
    FROM "connect_datalake"."staff_shifts"
    WHERE is_deleted = false
      AND CAST(shift_start_timestamp AS DATE) = DATE '2026-06-10'  -- SET REPORT DATE
    GROUP BY shift_id
),

latest_shifts AS (
    SELECT
        ss.shift_id,
        ss.agent_arn,
        ss.shift_start_timestamp,
        ss.shift_end_timestamp
    FROM "connect_datalake"."staff_shifts" ss
    INNER JOIN latest_shift_versions lsv
        ON ss.shift_id = lsv.shift_id
        AND ss.shift_version = lsv.max_version
    WHERE ss.is_deleted = false
),

-- Get scheduled activity blocks with human-readable activity names
scheduled_blocks AS (
    SELECT
        ls.agent_arn,
        ssa.activity_start_timestamp,
        ssa.activity_end_timestamp,
        sa.shift_activity_name,
        CASE
            WHEN sa.shift_activity_name IN ('Work', 'Overtime') THEN 'Open'
            WHEN sa.shift_activity_name IN ('Break', 'Lunch') THEN 'Break'
            WHEN sa.shift_activity_name = 'Training' THEN 'Meeting'
            WHEN sa.shift_activity_name = 'PTO' THEN 'PTO'
            ELSE sa.shift_activity_name
        END AS scheduled_activity_label
    FROM "connect_datalake"."staff_shift_activities" ssa
    INNER JOIN latest_shifts ls
        ON ssa.shift_id = ls.shift_id
    INNER JOIN latest_shift_versions lsv
        ON ssa.shift_id = lsv.shift_id
        AND ssa.shift_version = lsv.max_version
    INNER JOIN "connect_datalake"."shift_activities" sa
        ON ssa.shift_activity_arn = sa.shift_activity_arn
    WHERE ssa.is_deleted = false
),

-- Get actual agent state intervals for the day
actual_states AS (
    SELECT
        u.user_arn AS agent_arn,
        u.first_name,
        u.last_name,
        asr.interval_start_time,
        asr.interval_end_time,
        asr.agent_status_name,
        asr.online_time,
        asr.agent_idle_time,
        asr.agent_on_contact_time,
        asr.non_productive_time,
        CASE
            WHEN asr.agent_on_contact_time IS NOT NULL AND asr.agent_on_contact_time > 0
                THEN 'On Inbound Call'
            WHEN asr.agent_idle_time IS NOT NULL AND asr.agent_idle_time > 0
                THEN 'Available'
            WHEN asr.non_productive_time IS NOT NULL AND asr.non_productive_time > 0
                THEN COALESCE(asr.agent_status_name, 'Non-Productive')
            WHEN asr.online_time IS NOT NULL AND asr.online_time > 0
                THEN 'Available'
            ELSE COALESCE(asr.agent_status_name, 'Offline')
        END AS actual_activity_label
    FROM "connect_datalake"."agent_statistic_record" asr
    INNER JOIN "connect_datalake"."users" u
        ON asr.user_id = u.user_id
    WHERE asr.interval_start_time >= TIMESTAMP '2026-06-10 00:00:00' -- SET REPORT DATE (UTC)
      AND asr.interval_start_time < TIMESTAMP '2026-06-11 00:00:00'
),

-- Join actual states with scheduled blocks
activity_timeline AS (
    SELECT
        act.first_name || ' ' || act.last_name AS agent_name,
        act.interval_start_time,
        act.interval_end_time,
        act.actual_activity_label,
        act.agent_status_name,
        COALESCE(sb.scheduled_activity_label, 'Open') AS scheduled_activity
    FROM actual_states act
    LEFT JOIN scheduled_blocks sb
        ON act.agent_arn = sb.agent_arn
        AND act.interval_start_time < sb.activity_end_timestamp
        AND act.interval_end_time > sb.activity_start_timestamp
)

SELECT
    agent_name AS "AGENT",
    CAST(interval_start_time AS DATE) AS "DATE",
    DATE_FORMAT(interval_start_time, '%H:%i:%s') AS "BEGIN",
    DATE_FORMAT(interval_end_time, '%H:%i:%s') AS "END",
    scheduled_activity AS "SCHEDULED ACTIVITY",
    actual_activity_label AS "ACTUAL ACTIVITY",
    CASE
        WHEN scheduled_activity = 'Open'
            AND actual_activity_label IN ('Available', 'On Inbound Call', 'On Outbound Call',
                                          'Call Ringing', 'Aftercall (ACW)')
            THEN 'IN'
        WHEN scheduled_activity = 'Break'
            AND agent_status_name IN ('Break', 'Lunch')
            THEN 'IN'
        WHEN scheduled_activity = 'Meeting'
            AND agent_status_name IN ('Training', 'Meeting')
            THEN 'IN'
        ELSE 'OUT'
    END AS "ADHERENCE STATE",
    CAST(DATE_DIFF('second', interval_start_time, interval_end_time) / 3600 AS VARCHAR)
        || ':' ||
        LPAD(CAST((DATE_DIFF('second', interval_start_time, interval_end_time) % 3600) / 60 AS VARCHAR), 2, '0')
        || ':' ||
        LPAD(CAST(DATE_DIFF('second', interval_start_time, interval_end_time) % 60 AS VARCHAR), 2, '0')
    AS "DURATION"
FROM activity_timeline
ORDER BY interval_start_time ASC;
```

## Práticas recomendadas
<a name="data-lake-rq-best-practices"></a>
+ **Remoção de partições** — sempre inclua filtros de partição (`disconnect_timestamp`,`published_date`, ou`creation_timestamp`) para minimizar os custos de digitalização.
+ **Desduplicação** — o Connect Customer entrega registros pelo menos uma vez. Use `DISTINCT` em chaves primárias quando forem necessárias contagens exatas.
+ **Fusos horários** — Todos os timestamps estão em UTC. Inscreva-se `AT TIME ZONE` para receber relatórios locais.
+ **Milissegundos** — A maioria dos campos de duração é armazenada em milissegundos. Divida por 1000,0 por segundos.
+ **Filtro de ID de instância** — Sempre filtre por `instance_id` em ambientes com várias instâncias.
+ **Real-time métricas** — Para obter métricas reais em tempo real, use a `GetCurrentMetricData` API. O data lake fornece somente dados históricos.