Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

# Requêtes de référence pour le lac de données Connect Customer
<a name="data-lake-reference-queries"></a>

Cette rubrique fournit des requêtes SQL Athena (moteur Trino v3) permettant de calculer les métriques courantes de Connect Customer à partir de tables de lacs de données. Toutes les requêtes utilisent des identifiants entre guillemets doubles et supposent un nom de `connect_datalake` base de données. Ajustez le nom de la base de données pour qu'il corresponde à la configuration de votre catalogue Glue.

Remplacez `<YOUR_INSTANCE_ID>` chaque requête par votre ID d'instance Connect Customer.

**Topics**
+ [Mesures relatives aux contacts et aux files d'attente](#data-lake-rq-contact-queue)
+ [Indicateurs de performance des agents](#data-lake-rq-agent-performance)
+ [Statistiques du chat](#data-lake-rq-chat)
+ [Métriques d’analytique conversationnelle](#data-lake-rq-contact-lens)
+ [Métriques relatives aux agents d'IA](#data-lake-rq-ai-agent)
+ [Métriques de débit](#data-lake-rq-flow)
+ [Métriques d’évaluation](#data-lake-rq-evaluations)
+ [Métriques des campagnes sortantes](#data-lake-rq-campaigns)
+ [Métriques relatives aux cas](#data-lake-rq-cases)
+ [Métriques relatives aux robots](#data-lake-rq-bot)
+ [Modèles de requêtes courants](#data-lake-rq-patterns)
+ [Respect du calendrier des agents (niveau activité)](#data-lake-rq-schedule-adherence)
+ [Bonnes pratiques](#data-lake-rq-best-practices)

## Mesures relatives aux contacts et aux files d'attente
<a name="data-lake-rq-contact-queue"></a>

### Taux d’abandon
<a name="data-lake-rq-abandonment-rate"></a>

**Définition :** Pourcentage de contacts déconnectés par le client pendant la file d'attente. Rappels exclus.

**Tableau des sources :** `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;
```

### Contacts ayant abandonné
<a name="data-lake-rq-contacts-abandoned"></a>

**Définition :** Nombre de contacts déconnectés par le client alors qu'il attendait dans la file d'attente.

**Tableau des sources :** `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";
```

### Contacts abandonnés dans les X secondes
<a name="data-lake-rq-contacts-abandoned-x-seconds"></a>

**Définition :** Nombre de contacts abandonnés dans les X secondes suivant leur mise en file d'attente.

**Tableau des sources :** `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";
```

### Temps d’abandon moyen dans la file d’attente
<a name="data-lake-rq-avg-queue-abandon-time"></a>

**Définition :** Temps moyen d'attente des contacts dans la file d'attente avant d'abandonner.

**Tableau des sources :** `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";
```

### Temps de réponse moyen dans la file d’attente
<a name="data-lake-rq-avg-queue-answer-time"></a>

**Définition :** Temps moyen d'attente des contacts dans la file d'attente avant qu'un agent ne réponde.

**Tableau des sources :** `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";
```

### Niveau de service
<a name="data-lake-rq-service-level"></a>

**Définition :** Nombre et pourcentage de contacts auxquels on a répondu en X secondes.

**Tableau des sources :** `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";
```

### Contacts dans la file d’attente
<a name="data-lake-rq-contacts-queued"></a>

**Définition :** Nombre de contacts placés dans une file d'attente.

**Tableau des sources :** `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";
```

### Contacts traités
<a name="data-lake-rq-contacts-handled"></a>

**Définition :** Nombre de contacts connectés à un agent.

**Tableau des sources :** `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";
```

### Contacts transférés dans
<a name="data-lake-rq-contacts-transferred-in"></a>

**Définition :** Contacts transférés dans une file d'attente.

**Tableau des sources :** `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";
```

### Contacts transférés vers
<a name="data-lake-rq-contacts-transferred-out"></a>

**Définition :** Contacts transférés hors d'une file d'attente.

**Tableau des sources :** `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";
```

### Durée maximum de mise en file d’attente
<a name="data-lake-rq-max-queued-time"></a>

**Définition :** Le temps le plus long qu'un contact a passé à attendre dans la file d'attente.

**Tableau des sources :** `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";
```

### Durée moyenne du contact
<a name="data-lake-rq-avg-contact-duration"></a>

**Définition :** Temps moyen entre le début du contact et la déconnexion.

**Tableau des sources :** `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";
```

## Indicateurs de performance des agents
<a name="data-lake-rq-agent-performance"></a>

### Durée de traitement moyenne
<a name="data-lake-rq-avg-handle-time"></a>

**Définition :** Temps moyen entre la connexion du contact et l'achèvement de l'ACW.

**Tableau des sources :** `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";
```

### Temps de travail après contact
<a name="data-lake-rq-acw-time"></a>

**Définition :** Temps total passé par les agents dans l'état ACW.

**Tableau des sources :** `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";
```

### Durée d’attente client
<a name="data-lake-rq-customer-hold-time"></a>

**Définition :** Temps total passé par les clients en attente après s'être connectés à l'agent.

**Tableau des sources :** `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";
```

### Temps inactif de l’agent
<a name="data-lake-rq-agent-idle-time"></a>

**Définition :** Temps passé par l'agent dans le statut Disponible sans gérer les contacts.

**Tableau des sources :** `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";
```

### Occupation
<a name="data-lake-rq-occupancy"></a>

**Définition :** Pourcentage de temps pendant lequel les agents étaient actifs sur les contacts par rapport au temps pendant lequel les agents étaient disponibles et actifs.

**Tableau des sources :** `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";
```

### Non-réponse de l’agent
<a name="data-lake-rq-agent-non-response"></a>

**Définition :** Nombre de contacts acheminés vers l'agent sans réponse.

**Tableau des sources :** `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";
```

### Taux de réponse de l’agent
<a name="data-lake-rq-agent-answer-rate"></a>

**Définition :** Pourcentage de contacts routés auxquels l'agent a répondu.

**Tableau des sources :** `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";
```

### Durée en ligne
<a name="data-lake-rq-online-time"></a>

**Définition : Durée** totale pendant laquelle le CCP de l'agent a été défini sur un statut autre que Hors ligne.

**Tableau des sources :** `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";
```

## Statistiques du chat
<a name="data-lake-rq-chat"></a>

### Temps moyen de première réponse de l’agent
<a name="data-lake-rq-avg-agent-first-response-time"></a>

**Définition :** Temps moyen nécessaire à l'agent pour envoyer le premier message après avoir obtenu un contact par chat.

**Tableau des sources :** `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";
```

### Temps de réponse moyen des agents
<a name="data-lake-rq-avg-agent-response-time"></a>

**Définition :** Temps moyen nécessaire aux agents pour répondre aux messages des clients.

**Tableau des sources :** `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";
```

### Nombre total moyen de messages
<a name="data-lake-rq-avg-total-messages"></a>

**Définition :** Nombre total moyen de messages par contact dans le chat.

**Tableau des sources :** `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";
```

### Conversations abandonnées
<a name="data-lake-rq-conversations-abandoned"></a>

**Définition :** Contacts où le chat a été abandonné par un agent ou un client.

**Tableau des sources :** `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étriques d’analytique conversationnelle
<a name="data-lake-rq-contact-lens"></a>

### Temps de conversation moyen
<a name="data-lake-rq-avg-talk-time"></a>

**Définition :** Temps de conversation moyen combiné entre un agent et un client par contact vocal.

**Tableau des sources :** `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>';
```

### Temps sans parole moyen
<a name="data-lake-rq-avg-non-talk-time"></a>

**Définition :** durée moyenne de mise en attente et de silence par contact vocal.

**Tableau des sources :** `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>';
```

### Scores de sentiment
<a name="data-lake-rq-sentiment-scores"></a>

**Définition :** scores de sentiment globaux pour l'agent et le client.

**Tableau des sources :** `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>';
```

### Nombre moyen d’interruptions de l’agent
<a name="data-lake-rq-avg-agent-interruptions"></a>

**Définition :** Nombre moyen d'interruptions d'agent par contact.

**Tableau des sources :** `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étriques relatives aux agents d'IA
<a name="data-lake-rq-ai-agent"></a>

### Taux de réussite des invocations d'agents IA
<a name="data-lake-rq-ai-invocation-success-rate"></a>

**Définition :** taux d'invocations réussies d'un agent AI.

**Tableau des sources :** `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";
```

### Taux de transfert de l'IA
<a name="data-lake-rq-ai-handoff-rate"></a>

**Définition :** Taux de sessions d'IA qui se sont transformées en agents humains.

**Tableau des sources :** `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;
```

### Scores de qualité de l'IA
<a name="data-lake-rq-ai-quality-scores"></a>

**Définition :** Scores moyens de réussite, de fidélité et d'exhaustivité des objectifs.

**Tableau des sources :** `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;
```

### Précision des outils d'IA
<a name="data-lake-rq-ai-tool-accuracy"></a>

**Définition :** scores de précision pour l'utilisation, la sélection et l'utilisation des paramètres des outils d'IA.

**Tableau des sources :** `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étriques de débit
<a name="data-lake-rq-flow"></a>

### Flux démarrés
<a name="data-lake-rq-flows-started"></a>

**Définition :** nombre de flux dont l'exécution a commencé.

**Tableau des sources :** `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";
```

### Pourcentage de résultats du flux
<a name="data-lake-rq-flow-outcome-pct"></a>

**Définition :** pourcentage de chaque type de résultat de flux.

**Tableau des sources :** `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;
```

### Durée de flux moyenne
<a name="data-lake-rq-avg-flow-time"></a>

**Définition :** Durée moyenne des exécutions de flux.

**Tableau des sources :** `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étriques d’évaluation
<a name="data-lake-rq-evaluations"></a>

### Évaluations effectuées
<a name="data-lake-rq-evaluations-performed"></a>

**Définition :** Nombre d'évaluations soumises.

**Tableau des sources :** `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>';
```

### Score d’évaluation moyen
<a name="data-lake-rq-avg-evaluation-score"></a>

**Définition :** Note d'évaluation moyenne pour les évaluations soumises.

**Tableau des sources :** `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>';
```

### Pourcentage d’échecs automatiques
<a name="data-lake-rq-automatic-fails"></a>

**Définition :** Pourcentage d'évaluations ayant entraîné un échec automatique.

**Tableau des sources :** `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étriques des campagnes sortantes
<a name="data-lake-rq-campaigns"></a>

### Contacts de la campagne
<a name="data-lake-rq-campaign-contacts"></a>

**Définition :** Nombre de contacts sortants liés à la campagne.

**Tableau des sources :** `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";
```

### Réponse humaine
<a name="data-lake-rq-human-answered"></a>

**Définition :** appels de campagne sortants connectés à un client réel.

**Tableau des sources :** `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étriques relatives aux cas
<a name="data-lake-rq-cases"></a>

### Cas créés
<a name="data-lake-rq-cases-created"></a>

**Définition :** Nombre total de cas créés au cours d'une période donnée.

**Tableau des sources :** `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>';
```

### Temps moyen de résolution de cas
<a name="data-lake-rq-avg-case-resolution"></a>

**Définition :** délai moyen entre la création du dossier et sa clôture.

**Tableau des sources :** `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étriques relatives aux robots
<a name="data-lake-rq-bot"></a>

### Résultats des conversations avec les robots
<a name="data-lake-rq-bot-outcomes"></a>

**Définition :** Répartition en pourcentage des résultats des conversations avec les robots.

**Tableau des sources :** `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;
```

## Modèles de requêtes courants
<a name="data-lake-rq-patterns"></a>

Les modèles suivants montrent comment combiner plusieurs tables de lacs de données pour obtenir des tableaux de bord et des rapports complets.

### Tableau de bord récapitulatif quotidien
<a name="data-lake-rq-daily-summary"></a>

**Définition :** mesures complètes des files d'attente quotidiennes, y compris le niveau de service.

**Tableau des sources :** `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;
```

### Analyse des tendances horaires
<a name="data-lake-rq-hourly-trend"></a>

**Définition :** évolution du volume horaire des contacts et du niveau de service.

**Tableau des sources :** `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";
```

### Contacts enrichis par lentilles de contact
<a name="data-lake-rq-contact-lens-enriched"></a>

**Définition :** Enrichissez les enregistrements de contacts grâce à l'analyse des lentilles de contact.

**Table source :** `contact_record` jointe à `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';
```

## Respect du calendrier des agents (niveau activité)
<a name="data-lake-rq-schedule-adherence"></a>

**Définition :** compare l'état d'activité réel d'un agent (depuis`agent_statistic_record`) avec ses activités de quart de travail planifiées (à partir des tables de planification) pour chaque intervalle de temps d'une journée. Produit une détermination de l'adhérence par intervalle : IN (l'agent faisait ce qu'il était censé faire) ou OUT (ce n'était pas le cas).

**Colonnes de sortie :** agent, date, début, fin, activité planifiée, activité réelle, état de conformité, durée

**Tableaux des sources :**
+ `staff_shifts`— L'agent change pour la journée (dernière version non supprimée)
+ `staff_shift_activities`— Blocs d'activités planifiés pour chaque quart de travail
+ `shift_activities`— Recherche de nom d'activité (fait correspondre l'ARN à un nom lisible par l'homme)
+ `agent_statistic_record`— État réel de l'agent par intervalle
+ `users`— Nom de l'agent et résolution de l'ARN

**Logique d'adhérence (simplifiée) :**
+ « Ouvert » programmé : l'agent est activé si le statut est Disponible, On Contact ou ACW
+ « Pause » planifiée : l'agent est là si le statut est Pause ou Déjeuner
+ « Réunion » planifiée : l'agent est présent si le statut est Formation ou Réunion
+ Sinon — OUT

```
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;
```

## Bonnes pratiques
<a name="data-lake-rq-best-practices"></a>
+ **Élagage des partitions** : incluez toujours des filtres de partition (`disconnect_timestamp``published_date`, ou`creation_timestamp`) pour minimiser les coûts de numérisation.
+ **Déduplication** — Connect Customer fournit des enregistrements au moins une fois. `DISTINCT`À utiliser sur les clés primaires lorsque des dénombrements exacts sont requis.
+ **Fuseaux horaires** — Tous les horodatages sont en UTC. Faites `AT TIME ZONE` une demande de reporting local.
+ **Millisecondes** : la plupart des champs de durée sont stockés en millisecondes. Divisez par 1000,0 pendant quelques secondes.
+ **Filtre d'ID d'instance** : filtrez toujours par identifiant `instance_id` dans les environnements multi-instances.
+ **Real-time métriques** — Pour de véritables métriques en temps réel, utilisez l'`GetCurrentMetricData`API. Le lac de données fournit uniquement des données historiques.