기계 번역으로 제공되는 번역입니다. 제공된 번역과 원본 영어의 내용이 상충하는 경우에는 영어 버전이 우선합니다.

# Connect Customer 데이터 레이크에 대한 참조 쿼리
<a name="data-lake-reference-queries"></a>

이 주제에서는 데이터 레이크 테이블에서 일반적인 Connect Customer 지표를 계산하기 위한 Athena SQL 쿼리(Trino 엔진 v3)를 제공합니다. 모든 쿼리는 큰따옴표를 사용하고 `connect_datalake` 데이터베이스 이름을 가정합니다. Glue 카탈로그 구성에 맞게 데이터베이스 이름을 조정합니다.

각 쿼리`<YOUR_INSTANCE_ID>`에서를 Connect Customer 인스턴스 ID로 바꿉니다.

**Topics**
+ [고객 응대 및 대기열 지표](#data-lake-rq-contact-queue)
+ [에이전트 성능 지표](#data-lake-rq-agent-performance)
+ [채팅 지표](#data-lake-rq-chat)
+ [대화 분석 지표](#data-lake-rq-contact-lens)
+ [AI 에이전트 지표](#data-lake-rq-ai-agent)
+ [플로우 지표](#data-lake-rq-flow)
+ [평가 지표](#data-lake-rq-evaluations)
+ [아웃바운드 캠페인 지표](#data-lake-rq-campaigns)
+ [사례 지표](#data-lake-rq-cases)
+ [봇 지표](#data-lake-rq-bot)
+ [일반적인 쿼리 패턴](#data-lake-rq-patterns)
+ [에이전트 일정 준수(활동 수준)](#data-lake-rq-schedule-adherence)
+ [모범 사례](#data-lake-rq-best-practices)

## 고객 응대 및 대기열 지표
<a name="data-lake-rq-contact-queue"></a>

### 중단 발생률
<a name="data-lake-rq-abandonment-rate"></a>

**정의:** 대기열에 있는 동안 고객이 연결 해제한 고객 응대의 비율입니다. 콜백은 제외됩니다.

**소스 테이블:** `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;
```

### 중단된 연락처
<a name="data-lake-rq-contacts-abandoned"></a>

**정의:** 대기열에서 대기하는 동안 고객이 연결 해제한 고객 응대 수입니다.

**소스 테이블:** `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";
```

### X초 이내에 중단된 고객 응대
<a name="data-lake-rq-contacts-abandoned-x-seconds"></a>

**정의:** 대기열에 추가되고 X초 이내에 중단된 고객 응대 수입니다.

**소스 테이블:** `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";
```

### 평균 대기열 중단 시간
<a name="data-lake-rq-avg-queue-abandon-time"></a>

**정의:** 고객 응대가 중단되기 전에 대기열에서 대기한 평균 시간입니다.

**소스 테이블:** `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";
```

### 평균 대기열 응답 시간
<a name="data-lake-rq-avg-queue-answer-time"></a>

**정의:** 에이전트가 응답하기 전에 고객 응대가 대기열에서 대기한 평균 시간입니다.

**소스 테이블:** `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";
```

### 서비스 수준
<a name="data-lake-rq-service-level"></a>

**정의:** X초 이내에 응답한 고객 응대 수 및 백분율입니다.

**소스 테이블:** `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";
```

### 대기 중인 연락처
<a name="data-lake-rq-contacts-queued"></a>

**정의:** 대기열에 배치된 고객 응대 수입니다.

**소스 테이블:** `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";
```

### 처리된 연락처
<a name="data-lake-rq-contacts-handled"></a>

**정의:** 에이전트에 연결된 고객 응대 수입니다.

**소스 테이블:** `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";
```

### 전송된 연락처
<a name="data-lake-rq-contacts-transferred-in"></a>

**정의:** 대기열로 전송된 고객 응대입니다.

**소스 테이블:** `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";
```

### 아웃바운드 전송된 연락처
<a name="data-lake-rq-contacts-transferred-out"></a>

**정의:** 대기열에서 전송된 고객 응대입니다.

**소스 테이블:** `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";
```

### 최대 대기 시간
<a name="data-lake-rq-max-queued-time"></a>

**정의:** 고객 응대가 대기열에서 대기한 가장 긴 시간입니다.

**소스 테이블:** `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";
```

### 평균 고객 응대 시간
<a name="data-lake-rq-avg-contact-duration"></a>

**정의:** 고객 응대 시작부터 연결 해제까지의 평균 시간입니다.

**소스 테이블:** `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";
```

## 에이전트 성능 지표
<a name="data-lake-rq-agent-performance"></a>

### 평균 처리 시간
<a name="data-lake-rq-avg-handle-time"></a>

**정의:** 고객 응대 연결부터 ACW 완료까지의 평균 시간입니다.

**소스 테이블:** `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";
```

### 연락처 작업 시간 후
<a name="data-lake-rq-acw-time"></a>

**정의:** 에이전트가 ACW 상태에서 보낸 총 시간입니다.

**소스 테이블:** `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";
```

### 고객 대기 시간
<a name="data-lake-rq-customer-hold-time"></a>

**정의:** 고객이 에이전트에 연결한 후 대기 상태로 보낸 총 시간입니다.

**소스 테이블:** `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";
```

### 에이전트 유휴 시간
<a name="data-lake-rq-agent-idle-time"></a>

**정의:** 에이전트가 고객 응대를 처리하지 않고 사용 가능 상태로 보낸 시간입니다.

**소스 테이블:** `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";
```

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

**정의:** 에이전트가 고객 응대에서 활성 상태였던 시간 대 사용 가능 \+ 활성 상태였던 시간의 백분율입니다.

**소스 테이블:** `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";
```

### 에이전트 무응답
<a name="data-lake-rq-agent-non-response"></a>

**정의:** 에이전트에게 라우팅되었지만 응답하지 않은 고객 응대 수입니다.

**소스 테이블:** `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";
```

### 에이전트 응답률
<a name="data-lake-rq-agent-answer-rate"></a>

**정의:** 에이전트가 응답한 라우팅된 고객 응대의 비율입니다.

**소스 테이블:** `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";
```

### 온라인 시간
<a name="data-lake-rq-online-time"></a>

**정의:** 에이전트 CCP가 오프라인 이외의 상태로 설정된 총 시간입니다.

**소스 테이블:** `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";
```

## 채팅 지표
<a name="data-lake-rq-chat"></a>

### 평균 에이전트 최초 응답 시간
<a name="data-lake-rq-avg-agent-first-response-time"></a>

**정의:** 채팅 고객 응대를 받은 후 에이전트가 첫 번째 메시지를 보내는 평균 시간입니다.

**소스 테이블:** `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";
```

### 평균 에이전트 응답 시간
<a name="data-lake-rq-avg-agent-response-time"></a>

**정의:** 에이전트가 고객 메시지에 응답하는 데 걸리는 평균 시간입니다.

**소스 테이블:** `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";
```

### 평균 총 메시지
<a name="data-lake-rq-avg-total-messages"></a>

**정의:** 채팅 고객 응대당 평균 총 메시지 수입니다.

**소스 테이블:** `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";
```

### 중단된 대화
<a name="data-lake-rq-conversations-abandoned"></a>

**정의:** 에이전트 또는 고객이 채팅을 중단한 고객 응대입니다.

**소스 테이블:** `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";
```

## 대화 분석 지표
<a name="data-lake-rq-contact-lens"></a>

### 평균 발언 시간
<a name="data-lake-rq-avg-talk-time"></a>

**정의:** 음성 고객 응대당 평균 결합된 에이전트 및 고객 통화 시간입니다.

**소스 테이블:** `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>';
```

### 평균 침묵 시간
<a name="data-lake-rq-avg-non-talk-time"></a>

**정의:** 음성 고객 응대당 평균 대기 시간 \+ 무음 시간.

**소스 테이블:** `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>';
```

### 감정 점수
<a name="data-lake-rq-sentiment-scores"></a>

**정의:** 에이전트 및 고객의 전체 감정 점수입니다.

**소스 테이블:** `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>';
```

### 평균 에이전트 중단
<a name="data-lake-rq-avg-agent-interruptions"></a>

**정의:** 고객 응대당 평균 에이전트 중단 수입니다.

**소스 테이블:** `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>';
```

## AI 에이전트 지표
<a name="data-lake-rq-ai-agent"></a>

### AI 에이전트 호출 성공률
<a name="data-lake-rq-ai-invocation-success-rate"></a>

**정의:** AI 에이전트 호출 성공률입니다.

**소스 테이블:** `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 핸드오프 비율
<a name="data-lake-rq-ai-handoff-rate"></a>

**정의:** 인간 에이전트에게 에스컬레이션된 AI 세션의 비율입니다.

**소스 테이블:** `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;
```

### AI 품질 점수
<a name="data-lake-rq-ai-quality-scores"></a>

**정의:** 평균 목표 성공, 충실도 및 완전성 점수.

**소스 테이블:** `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;
```

### AI 도구 정확도
<a name="data-lake-rq-ai-tool-accuracy"></a>

**정의:** AI 도구 파라미터 사용, 선택 및 사용률의 정확도 점수입니다.

**소스 테이블:** `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";
```

## 플로우 지표
<a name="data-lake-rq-flow"></a>

### 흐름 시작됨
<a name="data-lake-rq-flows-started"></a>

**정의:** 실행을 시작한 흐름 수입니다.

**소스 테이블:** `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";
```

### 흐름 결과 백분율
<a name="data-lake-rq-flow-outcome-pct"></a>

**정의:** 각 흐름 결과 유형의 백분율입니다.

**소스 테이블:** `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;
```

### 평균 흐름 시간
<a name="data-lake-rq-avg-flow-time"></a>

**정의:** 흐름 실행의 평균 기간입니다.

**소스 테이블:** `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";
```

## 평가 지표
<a name="data-lake-rq-evaluations"></a>

### 수행된 평가
<a name="data-lake-rq-evaluations-performed"></a>

**정의:** 제출된 평가 수입니다.

**소스 테이블:** `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>';
```

### 평균 평가 점수
<a name="data-lake-rq-avg-evaluation-score"></a>

**정의:** 제출된 평가의 평균 평가 점수입니다.

**소스 테이블:** `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>';
```

### 자동 실패율
<a name="data-lake-rq-automatic-fails"></a>

**정의:** 자동 실패를 트리거한 평가의 비율입니다.

**소스 테이블:** `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>';
```

## 아웃바운드 캠페인 지표
<a name="data-lake-rq-campaigns"></a>

### 캠페인 고객 응대
<a name="data-lake-rq-campaign-contacts"></a>

**정의:** 아웃바운드 캠페인 고객 응대 수입니다.

**소스 테이블:** `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";
```

### 사람이 답변
<a name="data-lake-rq-human-answered"></a>

**정의:** 라이브 고객과 연결된 아웃바운드 캠페인 통화입니다.

**소스 테이블:** `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";
```

## 사례 지표
<a name="data-lake-rq-cases"></a>

### 생성된 사례
<a name="data-lake-rq-cases-created"></a>

**정의:** 특정 기간에 생성된 총 사례 수입니다.

**소스 테이블:** `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>';
```

### 평균 사례 해결 시간
<a name="data-lake-rq-avg-case-resolution"></a>

**정의:** 사례 생성부터 종료까지의 평균 시간입니다.

**소스 테이블:** `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>';
```

## 봇 지표
<a name="data-lake-rq-bot"></a>

### 봇 대화 결과
<a name="data-lake-rq-bot-outcomes"></a>

**정의:** 봇 대화 결과의 백분율 분석입니다.

**소스 테이블:** `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;
```

## 일반적인 쿼리 패턴
<a name="data-lake-rq-patterns"></a>

다음 패턴은 포괄적인 대시보드 및 보고를 위해 여러 데이터 레이크 테이블을 결합하는 방법을 보여줍니다.

### 일별 요약 대시보드
<a name="data-lake-rq-daily-summary"></a>

**정의:** 서비스 수준을 포함한 포괄적인 일일 대기열 지표입니다.

**소스 테이블:** `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;
```

### 시간당 추세 분석
<a name="data-lake-rq-hourly-trend"></a>

**정의:** 시간당 고객 응대 볼륨 및 서비스 수준 추세입니다.

**소스 테이블:** `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";
```

### Contact Lens 강화 고객 응대
<a name="data-lake-rq-contact-lens-enriched"></a>

**정의:** Contact Lens 분석을 사용하여 고객 응대 레코드를 강화합니다.

**소스 테이블:**와 `contact_record` 조인됨 `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';
```

## 에이전트 일정 준수(활동 수준)
<a name="data-lake-rq-schedule-adherence"></a>

**정의:** 에이전트의 실제 활동 상태(에서`agent_statistic_record`)를 하루의 각 시간 간격에 대해 예약된 교대 근무 활동(예약 테이블에서)과 비교합니다. IN(에이전트가 예약된 작업을 수행 중) 또는 OUT(그렇지 않음)의 간격당 준수 결정을 생성합니다.

**출력 열:** 에이전트, 날짜, 시작, 종료, 예약된 활동, 실제 활동, 준수 상태, 기간

**소스 테이블:**
+ `staff_shifts` - 해당 날짜의 에이전트 교대 근무(삭제되지 않은 최신 버전)
+ `staff_shift_activities` - 각 교대 근무 내에서 예약된 활동 블록
+ `shift_activities` - 활동 이름 조회(ARN을 사람이 읽을 수 있는 이름에 매핑)
+ `agent_statistic_record` - 간격당 실제 에이전트 상태
+ `users` - 에이전트 이름 및 ARN 확인

**규정 준수 로직(단순):**
+ 예약된 "Open" - 상태가 Available, On Contact 또는 ACW인 경우 에이전트가 IN입니다.
+ 예약된 "휴식" - 상태가 휴식 또는 점심 식사인 경우 에이전트가 IN입니다.
+ 예약된 "회의" - 상태가 훈련 또는 회의인 경우 에이전트가 IN입니다.
+ 그렇지 않으면 - 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;
```

## 모범 사례
<a name="data-lake-rq-best-practices"></a>
+ **파티션 정리** `disconnect_timestamp`- 스캔 비용을 최소화하기 위해 항상 파티션 필터(`published_date`, 또는 `creation_timestamp`)를 포함합니다.
+ **중복 제거** - Connect Customer는 레코드를 한 번 이상 전송합니다. 정확한 개수가 필요한 경우 기본 키`DISTINCT`에를 사용합니다.
+ **시간대** - 모든 타임스탬프는 UTC입니다. 로컬 보고를 `AT TIME ZONE` 신청합니다.
+ **밀리초 **- 대부분의 기간 필드는 밀리초 단위로 저장됩니다. 초당 1000.0으로 나눕니다.
+ **인스턴스 ID 필터** - 다중 인스턴스 환경에서 항상 기준으로 필터링`instance_id`합니다.
+ **실시간 지표 **- 실제 실시간 지표의 경우 `GetCurrentMetricData` API를 사용합니다. 데이터 레이크는 기록 데이터만 제공합니다.