Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.

# Referenzabfragen für den Connect Customer Data Lake
<a name="data-lake-reference-queries"></a>

Dieses Thema enthält Athena-SQL-Abfragen (Trino Engine v3) zur Berechnung gängiger Connect Customer-Metriken aus Data-Lake-Tabellen. Alle Abfragen verwenden Bezeichner in doppelten Anführungszeichen und gehen von einem Datenbanknamen aus. `connect_datalake` Passen Sie den Datenbanknamen an Ihre Glue-Katalogkonfiguration an.

Ersetzen Sie `<YOUR_INSTANCE_ID>` in jeder Abfrage durch Ihre Connect Customer-Instanz-ID.

**Topics**
+ [Kontakt- und Warteschlangenmetriken](#data-lake-rq-contact-queue)
+ [Leistungskennzahlen für Agenten](#data-lake-rq-agent-performance)
+ [Chat-Metriken](#data-lake-rq-chat)
+ [Metriken zur Konversationsanalyse](#data-lake-rq-contact-lens)
+ [Kennzahlen für KI-Agenten](#data-lake-rq-ai-agent)
+ [Flow-Metriken](#data-lake-rq-flow)
+ [Bewertungsmetriken](#data-lake-rq-evaluations)
+ [Metriken für ausgehende Kampagnen](#data-lake-rq-campaigns)
+ [Fallmetriken](#data-lake-rq-cases)
+ [Bot-Metriken](#data-lake-rq-bot)
+ [Allgemeine Abfragemuster](#data-lake-rq-patterns)
+ [Einhaltung des Zeitplans für Agenten (Aktivitätsebene)](#data-lake-rq-schedule-adherence)
+ [Bewährte Methoden](#data-lake-rq-best-practices)

## Kontakt- und Warteschlangenmetriken
<a name="data-lake-rq-contact-queue"></a>

### Abbruchrate
<a name="data-lake-rq-abandonment-rate"></a>

**Definition:** Prozentsatz der Kontakte, die vom Kunden während der Warteschleife unterbrochen wurden. Rückrufe ausgeschlossen.

**Quelltabelle:** `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 abandoned (Abgebrochene Kontakte)
<a name="data-lake-rq-contacts-abandoned"></a>

**Definition:** Anzahl der Kontakte, die vom Kunden unterbrochen wurden, während er in der Warteschlange wartete.

**Quelltabelle:** `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 abandoned in X seconds (Kontakte, die innerhalb von X Sekunden abgebrochen wurden)
<a name="data-lake-rq-contacts-abandoned-x-seconds"></a>

**Definition:** Anzahl der Kontakte, die innerhalb von X Sekunden, nachdem sie in die Warteschlange gestellt wurden, verlassen wurden.

**Quelltabelle:** `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";
```

### Average queue abandon time (Durchschnittliche Warteschlangenabbruchzeit)
<a name="data-lake-rq-avg-queue-abandon-time"></a>

**Definition:** Durchschnittliche Wartezeit von Kontakten in der Warteschlange, bevor sie den Vorgang abbrechen.

**Quelltabelle:** `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";
```

### Durchschnittliche Warteschlangenantwortzeit
<a name="data-lake-rq-avg-queue-answer-time"></a>

**Definition:** Durchschnittliche Zeit, in der Kontakte in der Warteschlange warteten, bis sie von einem Agenten beantwortet wurden.

**Quelltabelle:** `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";
```

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

**Definition:** Anzahl und Prozentsatz der Kontakte, die innerhalb von X Sekunden beantwortet wurden.

**Quelltabelle:** `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 queued (Kontakte in Warteschlange)
<a name="data-lake-rq-contacts-queued"></a>

**Definition:** Anzahl der Kontakte, die in eine Warteschlange aufgenommen wurden.

**Quelltabelle:** `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 handled (Bearbeitete Kontakte)
<a name="data-lake-rq-contacts-handled"></a>

**Definition:** Anzahl der Kontakte, die mit einem Agenten verbunden sind.

**Quelltabelle:** `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 transferred in (Weitergeleitete Kontakte ein)
<a name="data-lake-rq-contacts-transferred-in"></a>

**Definition:** Kontakte, die in eine Warteschlange übertragen wurden.

**Quelltabelle:** `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 transferred out (Weitergeleitete Kontakte aus)
<a name="data-lake-rq-contacts-transferred-out"></a>

**Definition:** Kontakte, die aus einer Warteschlange übertragen wurden.

**Quelltabelle:** `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";
```

### Maximum queued time (Maximale Zeit in der Warteschlange)
<a name="data-lake-rq-max-queued-time"></a>

**Definition:** Längste Zeit, die ein Kontakt damit verbracht hat, in der Warteschlange zu warten.

**Quelltabelle:** `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";
```

### Durchschnittliche Kontaktdauer
<a name="data-lake-rq-avg-contact-duration"></a>

**Definition:** Durchschnittliche Zeit von der Kontaktaufnahme bis zur Unterbrechung der Verbindung.

**Quelltabelle:** `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";
```

## Leistungskennzahlen für Agenten
<a name="data-lake-rq-agent-performance"></a>

### Average handle time (Durchschnittliche Bearbeitungszeit)
<a name="data-lake-rq-avg-handle-time"></a>

**Definition:** Durchschnittliche Zeit von der Kontaktverbindung bis zum Abschluss von ACW.

**Quelltabelle:** `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";
```

### After contact work time (Kontaktnachbearbeitungszeit)
<a name="data-lake-rq-acw-time"></a>

**Definition:** Gesamtzeit, die Agenten im Status ACW verbracht haben.

**Quelltabelle:** `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";
```

### Customer hold time (Kundenhaltezeit)
<a name="data-lake-rq-customer-hold-time"></a>

**Definition:** Gesamtzeit, die Kunden in der Warteschleife verbracht haben, nachdem sie eine Verbindung zum Agenten hergestellt haben.

**Quelltabelle:** `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";
```

### Agent idle time (Leerlaufzeit von Kundendienstmitarbeitern)
<a name="data-lake-rq-agent-idle-time"></a>

**Definition:** Zeit, die der Agent im Status „Verfügbar“ verbracht hat, ohne Kontakte zu bearbeiten.

**Quelltabelle:** `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";
```

### Occupancy (Auslastung)
<a name="data-lake-rq-occupancy"></a>

**Definition:** Prozentualer Anteil der Zeit, in der Agenten bei Kontakten aktiv waren, im Vergleich zu „verfügbar“ und „aktiv“.

**Quelltabelle:** `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";
```

### Kundendienstmitarbeiter non-response (Nichtbeantwortung des Kundendienstmitarbeiters)
<a name="data-lake-rq-agent-non-response"></a>

**Definition:** Anzahl der Kontakte, die an den Agenten weitergeleitet, aber nicht beantwortet wurden.

**Quelltabelle:** `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";
```

### Agent answer rate (Antwortrate des Kundendienstmitarbeiters)
<a name="data-lake-rq-agent-answer-rate"></a>

**Definition:** Prozentsatz der weitergeleiteten Kontakte, die vom Agenten beantwortet wurden.

**Quelltabelle:** `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";
```

### Online time (Online-Zeit)
<a name="data-lake-rq-online-time"></a>

**Definition:** Gesamtzeit, in der Agent CCP auf einen anderen Status als Offline gesetzt wurde.

**Quelltabelle:** `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";
```

## Chat-Metriken
<a name="data-lake-rq-chat"></a>

### Durchschnittl. Zeit erste Reaktion Kundendienstmitarbeiter
<a name="data-lake-rq-avg-agent-first-response-time"></a>

**Definition:** Durchschnittliche Zeit, die ein Agent benötigt, um die erste Nachricht zu senden, nachdem er einen Chat-Kontakt erhalten hat.

**Quelltabelle:** `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";
```

### Durchschnittl. Zeit Reaktion Kundendienstmitarbeiter
<a name="data-lake-rq-avg-agent-response-time"></a>

**Definition:** Durchschnittliche Zeit, die Agenten benötigen, um auf Kundennachrichten zu antworten.

**Quelltabelle:** `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";
```

### Durchschnittliche Gesamtzahl der Nachrichten
<a name="data-lake-rq-avg-total-messages"></a>

**Definition:** Durchschnittliche Gesamtzahl der Nachrichten pro Chat-Kontakt.

**Quelltabelle:** `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";
```

### Abgebrochene Konversationen
<a name="data-lake-rq-conversations-abandoned"></a>

**Definition:** Kontakte, bei denen der Chat von einem Agenten oder Kunden abgebrochen wurde.

**Quelltabelle:** `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";
```

## Metriken zur Konversationsanalyse
<a name="data-lake-rq-contact-lens"></a>

### Durchschnittliche Gesprächszeit
<a name="data-lake-rq-avg-talk-time"></a>

**Definition:** Durchschnittliche kombinierte Gesprächszeit zwischen Agenten und Kunde pro Sprachkontakt.

**Quelltabelle:** `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>';
```

### Durchschnittliche Nicht-Gesprächszeit
<a name="data-lake-rq-avg-non-talk-time"></a>

**Definition:** Durchschnittliche Haltezeit plus Ruhezeit pro Sprachkontakt.

**Quelltabelle:** `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>';
```

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

**Definition:** Allgemeine Stimmungswerte für Agenten und Kunden.

**Quelltabelle:** `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>';
```

### Durchschnittliche Kundendienstmitarbeiterunterbrechungen
<a name="data-lake-rq-avg-agent-interruptions"></a>

**Definition:** Durchschnittliche Anzahl von Agentenunterbrechungen pro Kontakt.

**Quelltabelle:** `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>';
```

## Kennzahlen für KI-Agenten
<a name="data-lake-rq-ai-agent"></a>

### Erfolgsquote beim Aufrufen von KI-Agenten
<a name="data-lake-rq-ai-invocation-success-rate"></a>

**Definition:** Rate erfolgreicher Aufrufe von KI-Agenten.

**Quelltabelle:** `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";
```

### AI-Übergaberate
<a name="data-lake-rq-ai-handoff-rate"></a>

**Definition:** Rate der KI-Sitzungen, die an menschliche Agenten eskalierten.

**Quelltabelle:** `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;
```

### KI-Qualitätswerte
<a name="data-lake-rq-ai-quality-scores"></a>

**Definition:** Durchschnittliche Werte für Torerfolg, Treue und Vollständigkeit.

**Quelltabelle:** `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;
```

### Genauigkeit des KI-Tools
<a name="data-lake-rq-ai-tool-accuracy"></a>

**Definition:** Genauigkeitswerte für die Verwendung, Auswahl und Nutzung von KI-Tool-Parametern.

**Quelltabelle:** `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";
```

## Flow-Metriken
<a name="data-lake-rq-flow"></a>

### Gestartete Flows
<a name="data-lake-rq-flows-started"></a>

**Definition:** Anzahl der Flows, deren Ausführung begonnen hat.

**Quelltabelle:** `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";
```

### Prozentsatz des Ablaufergebnisses
<a name="data-lake-rq-flow-outcome-pct"></a>

**Definition:** Prozentsatz jedes Flow-Ergebnistyps.

**Quelltabelle:** `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;
```

### Durchschnittliche Flow-Zeit
<a name="data-lake-rq-avg-flow-time"></a>

**Definition:** Durchschnittliche Dauer der Flow-Ausführungen.

**Quelltabelle:** `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";
```

## Bewertungsmetriken
<a name="data-lake-rq-evaluations"></a>

### Durchgeführte Evaluationen
<a name="data-lake-rq-evaluations-performed"></a>

**Definition:** Anzahl der eingereichten Bewertungen.

**Quelltabelle:** `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>';
```

### Durchschnittliches Bewertungsergebnis
<a name="data-lake-rq-avg-evaluation-score"></a>

**Definition:** Durchschnittliche Bewertungspunktzahl aller eingereichten Bewertungen.

**Quelltabelle:** `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>';
```

### Automatische Fehlschläge in Prozent
<a name="data-lake-rq-automatic-fails"></a>

**Definition:** Prozentsatz der Bewertungen, die zu einem automatischen Fehlschlagen geführt haben.

**Quelltabelle:** `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>';
```

## Metriken für ausgehende Kampagnen
<a name="data-lake-rq-campaigns"></a>

### Kontakte zur Kampagne
<a name="data-lake-rq-campaign-contacts"></a>

**Definition:** Anzahl der ausgehenden Kampagnenkontakte.

**Quelltabelle:** `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";
```

### Mensch hat geantwortet
<a name="data-lake-rq-human-answered"></a>

**Definition:** Ausgehende Kampagnenanrufe, die mit einem Live-Kunden verbunden sind.

**Quelltabelle:** `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";
```

## Fallmetriken
<a name="data-lake-rq-cases"></a>

### Erstellte Fälle
<a name="data-lake-rq-cases-created"></a>

**Definition:** Gesamtzahl der in einem Zeitraum erstellten Fälle.

**Quelltabelle:** `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>';
```

### Durchschnittliche Falllösungszeit
<a name="data-lake-rq-avg-case-resolution"></a>

**Definition:** Durchschnittliche Zeit von der Erstellung des Falls bis zum Abschluss.

**Quelltabelle:** `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>';
```

## Bot-Metriken
<a name="data-lake-rq-bot"></a>

### Ergebnisse der Bot-Konversation
<a name="data-lake-rq-bot-outcomes"></a>

**Definition:** Prozentuale Aufschlüsselung der Ergebnisse von Bot-Konversationen.

**Quelltabelle:** `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;
```

## Allgemeine Abfragemuster
<a name="data-lake-rq-patterns"></a>

Die folgenden Muster zeigen, wie Sie mehrere Data-Lake-Tabellen kombinieren können, um umfassende Dashboards und Berichte zu erhalten.

### Tägliches Übersichts-Dashboard
<a name="data-lake-rq-daily-summary"></a>

**Definition:** Umfassende tägliche Kennzahlen zur Warteschlange, einschließlich des Servicelevels.

**Quelltabelle:** `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;
```

### Stündliche Trendanalyse
<a name="data-lake-rq-hourly-trend"></a>

**Definition:** Stündliches Kontaktvolumen und Trends beim Servicelevel.

**Quelltabelle:** `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";
```

### Mit Kontaktlinsen angereicherte Kontaktlinsen
<a name="data-lake-rq-contact-lens-enriched"></a>

**Definition:** Bereichern Sie Kontaktdatensätze mit Kontaktlinsenanalysen.

**Quelltabelle:** `contact_record` verknüpft mit `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';
```

## Einhaltung des Zeitplans für Agenten (Aktivitätsebene)
<a name="data-lake-rq-schedule-adherence"></a>

**Definition:** Vergleicht den tatsächlichen Aktivitätsstatus eines Agenten (von`agent_statistic_record`) mit seinen geplanten Schichtaktivitäten (aus Planungstabellen) für jedes Zeitintervall eines Tages. Ermittelt pro Intervall die Einhaltung: IN (der Agent hat getan, was für ihn geplant war) oder OUT (er hat nicht getan).

**Ausgabespalten:** Agent, Datum, Beginn, Ende, geplante Aktivität, Aktuelle Aktivität, Status der Einhaltung, Dauer

**Quelltabellen:**
+ `staff_shifts`— Der Agent wechselt für den Tag (letzte, nicht gelöschte Version)
+ `staff_shift_activities`— Geplante Aktivitätsblöcke innerhalb jeder Schicht
+ `shift_activities`— Suche nach Aktivitätsnamen (ordnet ARN einem menschenlesbaren Namen zu)
+ `agent_statistic_record`— Aktueller Agentenstatus pro Intervall
+ `users`— Agentenname und ARN-Auflösung

**Adhärenzlogik (vereinfacht):**
+ Geplantes „Öffnen“ — der Agent ist AKTIV, wenn der Status „Verfügbar“, „In Kontakt“ oder „ACW“ lautet
+ Geplante „Pause“ — Der Agent ist ZUGESCHALTET, wenn der Status Pause oder Mittagessen lautet
+ Geplantes „Meeting“ — Der Agent ist ANGEMELDET, wenn der Status „Schulung“ oder „Besprechung“ lautet
+ Andernfalls — AUS

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

## Bewährte Methoden
<a name="data-lake-rq-best-practices"></a>
+ Löschen von **Partitionen** — Verwenden Sie immer Partitionsfilter (`disconnect_timestamp``published_date`, oder`creation_timestamp`), um die Scankosten zu minimieren.
+ **Deduplizierung** — Connect Customer liefert Datensätze mindestens einmal. Wird für Primärschlüssel verwendet`DISTINCT`, wenn genaue Zählungen erforderlich sind.
+ **Zeitzonen** — Alle Zeitstempel sind in UTC. `AT TIME ZONE`Beantragen Sie lokale Berichterstattung.
+ **Millisekunden** — Die meisten Felder für die Dauer werden in Millisekunden gespeichert. Teilen Sie für Sekunden durch 1000,0.
+ **Instanz-ID-Filter** — In Umgebungen mit mehreren Instanzen wird immer nach `instance_id` gefiltert.
+ **Real-time Metriken** — Verwenden Sie die `GetCurrentMetricData` API, um echte Echtzeit-Metriken zu erhalten. Der Data Lake stellt nur historische Daten zur Verfügung.