01. CS 티켓 데이터 탐색

1. 개요 및 시나리오

상황: 당신은 이커머스 회사의 주니어 데이터 분석가입니다. CS(고객 서비스) 팀장님이 찾아와 이렇게 말하네요.

“요즘 고객 문의가 부쩍 늘어난 것 같아요. 도대체 어떤 문의가 언제, 얼마나 들어오는지 현황 좀 파악해주시겠어요?”

하루 평균 5,000건이 넘는 티켓이 쏟아지고 있습니다. 엑셀로는 한계가 있죠.

우리는 **BigQuery(SQL)**와 **Python(Pandas)**을 사용해 이 거대한 데이터를 낱낱이 파헤쳐볼 것입니다.

2. 환경 설정 및 데이터 로드

분석을 시작하기 위해 라이브러리를 불러오고 데이터를 로드해봅시다.

import os
from google.cloud import bigquery
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
 
# 1. 인증 파일 설정 (본인의 키 파일 경로로 수정하세요)
os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "/path/to/your-service-account-key.json"
 
# 2. 클라이언트 객체 생성
client = bigquery.Client()
 
print("✅ BigQuery 연결 완료!")

✅ BigQuery 연결 완료!

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
 
# 데이터 로드
# (실습 파일이 있는 경로를 지정해주세요)
tickets = pd.read_csv('cs_tickets_dummy.csv', parse_dates=['opened_at', 'resolved_at'])
 
print(f"✅ 데이터 로드 완료: {len(tickets):,}건")

✅ 데이터 로드 완료: 5,000건

3. 데이터 구조 파악하기

데이터를 처음 받았을 때 가장 먼저 해야 할 일은 “어떻게 생겼나?” 보는 것입니다.

❓ 문제 1: 데이터 샘플 조회

Q. cs_tickets_dummy 테이블에서 가장 최근에 생성된 티켓 5개를 조회하세요.

SELECT *
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
ORDER BY opened_at DESC
LIMIT 5;

# 최근 5개 티켓 조회
tickets.sort_values(by='opened_at', ascending=False).head(5)

주요 컬럼 설명

데이터를 조회해보면 다음과 같은 컬럼들을 볼 수 있습니다.

4. 데이터 품질 점검

“쓰레기가 들어가면 쓰레기가 나온다(Garbage In, Garbage Out)“는 말이 있죠. 분석 전에 데이터가 깨끗한지 확인해야 합니다.

❓ 문제 2: 결측치(NULL) 확인

Q. first_response_at (첫 응답 시간)이 비어있는(NULL) 티켓은 몇 퍼센트나 되나요?

SELECT
    COUNT(*) as total_count,
    COUNTIF(first_response_at IS NULL) as null_count,
    ROUND(COUNTIF(first_response_at IS NULL) * 100.0 / COUNT(*), 1) as null_pct
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`;

# 결측치 비율 계산
null_count = tickets['first_response_at'].isnull().sum()
total_count = len(tickets)
null_pct = (null_count / total_count) * 100
 
print(f"결측치 비율: {null_pct:.1f}%")

결측치 비율: 28.8%

5. 기본 현황 분석 (EDA)

이제 드디어 팀장님이 궁금해하던 현황을 파악해봅시다.

❓ 문제 3: 상태(Status)별 티켓 분포

Q. 각 처리 상태(status)별로 티켓이 몇 건씩 있고, 전체의 몇 %를 차지하는지 구하세요.

SELECT
    status,
    COUNT(*) as ticket_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) as percentage
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
GROUP BY status
ORDER BY ticket_count DESC;

# 상태별 분포 (비율)
status_dist = tickets['status'].value_counts(normalize=True) * 100
print(status_dist)

status
solved       65.24
escalated    14.64
pending      10.60
open          9.52
Name: proportion, dtype: float64

📊 시각화: 파이 차트 그리기

숫자로만 보면 감이 잘 안 오죠. 파이 차트로 그려봅시다. (Python 코드 공통)

# 분포 데이터 준비
status_counts = tickets['status'].value_counts()
 
# 파이 차트 그리기
plt.figure(figsize=(8, 8))
plt.pie(status_counts, labels=status_counts.index, autopct='%1.1f%%', startangle=90)
plt.title('Ticket Distribution by Status')
plt.show()

[Graph Displayed]

6. 심화 분석: 이슈 유형 파악

❓ 문제 4: 가장 많은 불만 유형은?

Q. issue_type별로 티켓 수를 집계하고, 가장 많이 접수된 불만 유형 1위를 찾으세요.

SELECT
    issue_type,
    COUNT(*) as count
FROM `your-project-id.retail_analytics_us.cs_tickets_dummy`
GROUP BY issue_type
ORDER BY count DESC
LIMIT 1;

# 이슈 유형별 카운트
top_issue = tickets['issue_type'].value_counts().idxmax()
print(f"가장 많은 이슈 유형: {top_issue}")

가장 많은 이슈 유형: quality

💡 요약 및 인사이트

데이터를 탐색해보니 다음과 같은 사실들을 알 수 있었습니다:

1. 데이터 규모: 약 90일간 5,000건의 티켓 발생
2. 미응답 상태: 약 30%의 티켓이 아직 첫 응답을 받지 못함 (NULL 분석)
3. 주요 이슈: shipping(배송)과 quality(품질) 문제가 전체의 50%를 차지함

다음 단계: 현황은 파악했습니다. 그렇다면 “고객들은 우리 서비스에 얼마나 만족하고 있을까요?” 다음 챕터에서는 CSAT(고객 만족도)와 NPS 지표를 직접 계산해보겠습니다.