Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

# Richieste di riferimento per il data lake Connect Customer
<a name="data-lake-reference-queries"></a>

Questo argomento fornisce le query SQL Athena (motore Trino v3) per il calcolo delle metriche comuni di Connect Customer dalle tabelle del data lake. Tutte le query utilizzano identificatori tra virgolette doppie e presuppongono un nome di database. `connect_datalake` Modifica il nome del database in modo che corrisponda alla configurazione del catalogo Glue.

`<YOUR_INSTANCE_ID>`Sostituiscilo in ogni query con l'ID dell'istanza Connect Customer.

**Topics**
+ [Metriche relative ai contatti e alle code](#data-lake-rq-contact-queue)
+ [Metriche delle prestazioni degli agenti](#data-lake-rq-agent-performance)
+ [Metriche della chat](#data-lake-rq-chat)
+ [Metriche di analisi di conversazione](#data-lake-rq-contact-lens)
+ [Metriche degli agenti AI](#data-lake-rq-ai-agent)
+ [Metriche di flusso](#data-lake-rq-flow)
+ [Metriche di valutazione](#data-lake-rq-evaluations)
+ [Metriche delle campagne in uscita](#data-lake-rq-campaigns)
+ [Metriche dei casi](#data-lake-rq-cases)
+ [Metriche dei bot](#data-lake-rq-bot)
+ [Schemi di interrogazione comuni](#data-lake-rq-patterns)
+ [Rispetto della pianificazione dell'agente (a livello di attività)](#data-lake-rq-schedule-adherence)
+ [Best practice](#data-lake-rq-best-practices)

## Metriche relative ai contatti e alle code
<a name="data-lake-rq-contact-queue"></a>

### Tasso di abbandono
<a name="data-lake-rq-abandonment-rate"></a>

**Definizione:** percentuale di contatti disconnessi dal cliente durante la coda. Richiamate escluse.

**Tabella dei sorgenti:** `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;
```

### Contatti abbandonati
<a name="data-lake-rq-contacts-abandoned"></a>

**Definizione:** numero di contatti disconnessi dal cliente durante l'attesa in coda.

**Tabella dei sorgenti:** `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";
```

### Contatti abbandonati in X secondi
<a name="data-lake-rq-contacts-abandoned-x-seconds"></a>

**Definizione:** numero di contatti abbandonati entro X secondi dall'inserimento in coda.

**Tabella dei sorgenti:** `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 medio di abbandono coda
<a name="data-lake-rq-avg-queue-abandon-time"></a>

**Definizione:** tempo medio di attesa dei contatti in coda prima di essere abbandonati.

**Tabella dei sorgenti:** `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 medio di risposta coda
<a name="data-lake-rq-avg-queue-answer-time"></a>

**Definizione:** tempo medio di attesa dei contatti in coda prima di ricevere una risposta da un agente.

**Tabella delle fonti:** `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";
```

### Livello di servizio
<a name="data-lake-rq-service-level"></a>

**Definizione:** numero e percentuale di contatti con risposta entro X secondi.

**Tabella delle fonti:** `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";
```

### Contatti in coda
<a name="data-lake-rq-contacts-queued"></a>

**Definizione:** numero di contatti inseriti in una coda.

**Tabella dei sorgenti:** `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";
```

### Contatti gestiti
<a name="data-lake-rq-contacts-handled"></a>

**Definizione:** numero di contatti collegati a un agente.

**Tabella delle fonti:** `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";
```

### Contatti trasferiti all'interno
<a name="data-lake-rq-contacts-transferred-in"></a>

**Definizione:** contatti trasferiti in una coda.

**Tabella dei sorgenti:** `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";
```

### Contatti trasferiti all'esterno
<a name="data-lake-rq-contacts-transferred-out"></a>

**Definizione:** contatti trasferiti fuori dalla coda.

**Tabella dei sorgenti:** `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 massimo coda
<a name="data-lake-rq-max-queued-time"></a>

**Definizione:** il tempo più lungo trascorso da un contatto in attesa in coda.

**Tabella dei sorgenti:** `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";
```

### Durata media del contatto
<a name="data-lake-rq-avg-contact-duration"></a>

**Definizione:** tempo medio tra l'inizio del contatto e la disconnessione.

**Tabella delle fonti:** `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";
```

## Metriche delle prestazioni degli agenti
<a name="data-lake-rq-agent-performance"></a>

### Tempo medio gestione
<a name="data-lake-rq-avg-handle-time"></a>

**Definizione:** tempo medio tra la connessione dei contatti e il completamento dell'ACW.

**Tabella delle fonti:** `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 di attività successivo al contatto
<a name="data-lake-rq-acw-time"></a>

**Definizione:** tempo totale trascorso dagli agenti nello stato ACW.

**Tabella delle fonti:** `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 attesa clienti
<a name="data-lake-rq-customer-hold-time"></a>

**Definizione:** tempo totale trascorso dai clienti in attesa dopo la connessione all'agente.

**Tabella delle fonti:** `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 di inattività dell'agente
<a name="data-lake-rq-agent-idle-time"></a>

**Definizione:** tempo trascorso dall'agente nello stato Disponibile senza gestire i contatti.

**Tabella delle fonti:** `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";
```

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

**Definizione:** percentuale di tempo in cui gli agenti sono stati attivi sui contatti rispetto a quelli disponibili più attivi.

**Tabella delle fonti:** `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";
```

### Mancata risposta dell'agente
<a name="data-lake-rq-agent-non-response"></a>

**Definizione:** numero di contatti indirizzati all'agente ma a cui non è stata data risposta.

**Tabella dei sorgenti:** `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";
```

### Velocità di risposta dell'agente
<a name="data-lake-rq-agent-answer-rate"></a>

**Definizione:** percentuale di contatti instradati a cui l'agente ha risposto.

**Tabella delle fonti:** `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>

**Definizione:** Total Time Agent CCP è stato impostato su uno stato diverso da Offline.

**Tabella di origine:** `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";
```

## Metriche della chat
<a name="data-lake-rq-chat"></a>

### Tempo medio della prima risposta dell’agente
<a name="data-lake-rq-avg-agent-first-response-time"></a>

**Definizione:** Tempo medio impiegato dall'agente per inviare il primo messaggio dopo aver ottenuto un contatto in chat.

**Tabella delle fonti:** `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 medio di risposta dell’agente
<a name="data-lake-rq-avg-agent-response-time"></a>

**Definizione:** tempo medio impiegato dagli agenti per rispondere ai messaggi dei clienti.

**Tabella delle fonti:** `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";
```

### Messaggi totali medi
<a name="data-lake-rq-avg-total-messages"></a>

**Definizione:** media totale dei messaggi per contatto di chat.

**Tabella delle fonti:** `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";
```

### Conversazioni abbandonate
<a name="data-lake-rq-conversations-abandoned"></a>

**Definizione:** contatti in cui la chat è stata abbandonata dall'agente o dal cliente.

**Tabella dei sorgenti:** `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";
```

## Metriche di analisi di conversazione
<a name="data-lake-rq-contact-lens"></a>

### Tempo medio di conversazione
<a name="data-lake-rq-avg-talk-time"></a>

**Definizione:** tempo medio di conversazione combinato tra operatore e cliente per contatto vocale.

**Tabella delle fonti:** `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 medio di non conversazione
<a name="data-lake-rq-avg-non-talk-time"></a>

**Definizione:** tempo medio di attesa più silenzio per contatto vocale.

**Tabella delle fonti:** `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>';
```

### Punteggi di valutazione
<a name="data-lake-rq-sentiment-scores"></a>

**Definizione:** punteggi complessivi di sentiment per agente e cliente.

**Tabella delle fonti:** `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>';
```

### Interruzioni medie degli agenti
<a name="data-lake-rq-avg-agent-interruptions"></a>

**Definizione:** conteggio medio delle interruzioni degli agenti per contatto.

**Tabella delle fonti:** `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>';
```

## Metriche degli agenti AI
<a name="data-lake-rq-ai-agent"></a>

### Percentuale di successo delle chiamate agli agenti AI
<a name="data-lake-rq-ai-invocation-success-rate"></a>

**Definizione:** Percentuale di chiamate riuscite all'agente AI.

**Tabella dei sorgenti:** `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";
```

### Tasso di trasferimento dell'IA
<a name="data-lake-rq-ai-handoff-rate"></a>

**Definizione:** tasso di sessioni di intelligenza artificiale che sono passate ad agenti umani.

**Tabella delle fonti:** `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;
```

### Punteggi di qualità dell'IA
<a name="data-lake-rq-ai-quality-scores"></a>

**Definizione:** punteggi medi di successo, fedeltà e completezza degli obiettivi.

**Tabella delle fonti:** `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;
```

### Precisione dello strumento AI
<a name="data-lake-rq-ai-tool-accuracy"></a>

**Definizione:** punteggi di precisione per l'utilizzo, la selezione e l'utilizzo dei parametri degli strumenti AI.

**Tabella delle fonti:** `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";
```

## Metriche di flusso
<a name="data-lake-rq-flow"></a>

### Flussi avviati
<a name="data-lake-rq-flows-started"></a>

**Definizione:** conteggio dei flussi che hanno iniziato l'esecuzione.

**Tabella dei sorgenti:** `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";
```

### Percentuale del risultato del flusso
<a name="data-lake-rq-flow-outcome-pct"></a>

**Definizione:** percentuale di ogni tipo di risultato del flusso.

**Tabella delle fonti:** `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 medio del flusso
<a name="data-lake-rq-avg-flow-time"></a>

**Definizione:** durata media delle esecuzioni dei flussi.

**Tabella delle fonti:** `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";
```

## Metriche di valutazione
<a name="data-lake-rq-evaluations"></a>

### Valutazioni eseguite
<a name="data-lake-rq-evaluations-performed"></a>

**Definizione:** numero di valutazioni presentate.

**Tabella delle fonti:** `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>';
```

### Punteggio medio di valutazione
<a name="data-lake-rq-avg-evaluation-score"></a>

**Definizione:** punteggio di valutazione medio tra le valutazioni inviate.

**Tabella delle fonti:** `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>';
```

### Percentuale di errori automatici
<a name="data-lake-rq-automatic-fails"></a>

**Definizione:** percentuale di valutazioni che hanno generato un errore automatico.

**Tabella delle fonti:** `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>';
```

## Metriche delle campagne in uscita
<a name="data-lake-rq-campaigns"></a>

### Contatti della campagna
<a name="data-lake-rq-campaign-contacts"></a>

**Definizione:** numero di contatti della campagna in uscita.

**Tabella delle fonti:** `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";
```

### Risposta umana
<a name="data-lake-rq-human-answered"></a>

**Definizione:** chiamate pubblicitarie in uscita collegate a un cliente reale.

**Tabella dei sorgenti:** `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";
```

## Metriche dei casi
<a name="data-lake-rq-cases"></a>

### Casi creati
<a name="data-lake-rq-cases-created"></a>

**Definizione:** totale dei casi creati in un periodo di tempo.

**Tabella delle fonti:** `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 medio di risoluzione di un caso
<a name="data-lake-rq-avg-case-resolution"></a>

**Definizione:** tempo medio dalla creazione del caso alla chiusura.

**Tabella delle fonti:** `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>';
```

## Metriche dei bot
<a name="data-lake-rq-bot"></a>

### Risultati delle conversazioni con i bot
<a name="data-lake-rq-bot-outcomes"></a>

**Definizione:** Ripartizione percentuale dei risultati delle conversazioni con i bot.

**Tabella delle fonti:** `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;
```

## Schemi di interrogazione comuni
<a name="data-lake-rq-patterns"></a>

I modelli seguenti mostrano come combinare più tabelle di data lake per dashboard e report completi.

### Pannello di riepilogo giornaliero
<a name="data-lake-rq-daily-summary"></a>

**Definizione:** metriche complete sulle code giornaliere, incluso il livello di servizio.

**Tabella delle fonti:** `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;
```

### Analisi oraria delle tendenze
<a name="data-lake-rq-hourly-trend"></a>

**Definizione:** volume di contatti su base oraria e tendenze dei livelli di servizio.

**Tabella delle fonti:** `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";
```

### Contatti arricchiti con lenti a contatto
<a name="data-lake-rq-contact-lens-enriched"></a>

**Definizione:** arricchisci i record di contatti con Contact Lens Analytics.

**Tabella di origine:** `contact_record` unita a `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';
```

## Rispetto della pianificazione dell'agente (a livello di attività)
<a name="data-lake-rq-schedule-adherence"></a>

**Definizione:** confronta lo stato effettivo delle attività di un agente (da`agent_statistic_record`) con le attività a turni programmate (dalle tabelle di pianificazione) per ogni intervallo di tempo in un giorno. Produce una determinazione dell'aderenza per intervallo: IN (l'agente stava facendo ciò per cui era programmato) o OUT (non lo faceva).

**Colonne di output:** agente, data, inizio, fine, attività programmata, attività effettiva, stato di aderenza, durata

**Tabelle di origine:**
+ `staff_shifts`— Turni giornalieri dell'agente (ultima versione non eliminata)
+ `staff_shift_activities`— Blocchi di attività programmati all'interno di ogni turno
+ `shift_activities`— Ricerca del nome dell'attività (mappa l'ARN su un nome leggibile dall'uomo)
+ `agent_statistic_record`— Stato effettivo dell'agente per intervallo
+ `users`— Nome dell'agente e risoluzione ARN

**Logica di aderenza (semplificata):**
+ «Aperto» pianificato: l'agente è ATTIVO se lo stato è Disponibile, In contatto o ACW
+ «Pausa» pianificata: l'agente è ATTIVO se lo stato è Pausa o Pranzo
+ «Riunione» pianificata: l'agente è ATTIVO se lo stato è Formazione o Riunione
+ Altrimenti, ESCI

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

## Best practice
<a name="data-lake-rq-best-practices"></a>
+ **Eliminazione delle partizioni: includi** sempre i filtri di partizione (`disconnect_timestamp`,`published_date`, or`creation_timestamp`) per ridurre al minimo i costi di scansione.
+ **Deduplicazione**: Connect Customer fornisce i record almeno una volta. Utilizzalo `DISTINCT` sulle chiavi primarie quando sono richiesti conteggi esatti.
+ **Fusi orari**: tutti i timestamp sono in UTC. Richiedi la rendicontazione `AT TIME ZONE` locale.
+ **Millisecondi**: la maggior parte dei campi relativi alla durata viene archiviata in millisecondi. Dividi per 1000,0 per secondi.
+ **Filtro ID istanza: filtra** sempre per `instance_id` in ambienti a più istanze.
+ **Real-time metriche**: per metriche reali in tempo reale, utilizza l'API. `GetCurrentMetricData` Il data lake fornisce solo dati storici.