Y.Siyeon

3 분 소요

1. BigQuery 데이터셋 구조 — 8개 테이블

steam_data 데이터셋 안에 8개 테이블을 둡니다. 원본 데이터(Raw)와 분석 결과(Analysis)를 명확히 분리한 구조입니다.

Raw — 원본 적재

| 테이블 | 핵심 컬럼 | 적재 방식 | |——–|———–|———–| | games | app_id, name, genres, categories, developers, release_date | 초기 + 신규만 append | | reviews | recommendation_id, app_id, voted_up, playtime_at_review, language, review_text, review_text_ko, timestamp_created | 매일 KST 전날분 append | | news | gid, app_id, title, contents, date | 매일 append | | price_history | app_id, snapshot_date, initial_price, final_price, discount_percent | 매일 append | | player_counts | app_id, collected_at, player_count | 3시간마다 append | | review_summary | app_id, total_reviews, total_positive, total_negative, positive_ratio, collected_at | 매일 append (누적) |

Analysis — 분석 결과

| 테이블 | 핵심 컬럼 | 적재 방식 | |——–|———–|———–| | category_analysis | app_id, polarity, category, subcategory, count, collected_date | 일별 누적 | | keyword_analysis | app_id, polarity, keyword, pos_tag, count, ratio, collected_date | 일별 누적 |

설계 결정: 모든 시계열 분석 가능한 테이블은 append 모드로 통일. 처음엔 review_summary를 매일 TRUNCATE 방식이었는데, 4주 긍정률 추세 분석을 위해 누적으로 변경했습니다.

2. 리뷰 텍스트 분석 파이프라인

분석 핵심은 리뷰 텍스트입니다. 4단계로 구성됩니다.

1. 수집 (collect_daily)         → reviews.review_text (원문)
        ↓
2. 번역 (translate_daily)        → reviews.review_text_ko (한국어)
        ↓
3. 형태소 분석 (analyze_daily)    → Kiwi로 명사·형용사 추출
        ↓
4. 분류·집계                      → category_analysis + keyword_analysis

2.1 번역 — Deep Translator

리뷰는 영어/러시아어/중국어 등 다국어. 한국어 분석 일관성을 위해 모두 한국어로 번역합니다.

  • deep-translator (Google Translate 무료 엔드포인트 래퍼) 사용
  • 일 8000건 capacity로 운영 (translate_daily 함수 4회 분산 × 2000건)
  • review_text_ko IS NULL 조건으로 미번역분만 처리, 자연스러운 백로그 해소
  • 한국어 리뷰는 자기 자신을 그대로 사용 (재번역 방지)

2.2 Kiwi 형태소 분석

한국어 형태소 분석기 Kiwi를 사용합니다. 이유:

  • KoNLPy 대비 빠르고 사전 커스터마이징 쉬움
  • 게임 도메인 신조어(“렉”, “핑”, “그래픽카드” 등) 사전 추가 가능
  • pure Python 패키지로 Cloud Functions 환경 호환성 좋음
from kiwipiepy import Kiwi
kiwi = Kiwi()
tokens = kiwi.tokenize("이 게임 그래픽 진짜 좋다")
# → [('이', 'MM'), ('게임', 'NNG'), ('그래픽', 'NNG'), ('진짜', 'MAG'), ('좋', 'VA'), ...]

명사(NNG/NNP)와 일부 형용사 어간을 추출해 빈도 계산.

3. 24개 서브카테고리 — 감성 분류 체계

리뷰 텍스트에서 키워드만 보면 “좋다/나쁘다” 정도만 알 수 있습니다. 더 의미있는 분석을 위해 게임 도메인 카테고리 24개를 정의했습니다.

카테고리 트리

긍정 (positive) - 12 서브카테고리
├── 재미/몰입
├── 그래픽/사운드
├── 스토리/세계관
├── 멀티플레이/협동
├── 자유도/리플레이성
├── 난이도 적절
├── 가성비
├── 운영/지원
├── 캐릭터/디자인
├── 컨트롤/조작감
├── 학습 곡선
└── 커뮤니티

부정 (negative) - 12 서브카테고리
├── 성능/서버
├── 버그/크래시
├── 밸런스/페이투윈
├── 반복성/단조로움
├── 스토리 약함
├── 그래픽 부족
├── 가격 비쌈
├── 운영 불만
├── UI/UX 불편
├── 학습 어려움
├── 콘텐츠 부족
└── 토론/매칭

키워드 → 카테고리 매핑

각 카테고리는 트리거 키워드 사전을 갖고, 리뷰에서 추출된 명사·형용사가 어떤 카테고리에 매칭되는지 확인합니다. 예:

CATEGORY_KEYWORDS = {
    "성능/서버": ["렉", "핑", "서버", "끊김", "프레임", "최적화"],
    "재미/몰입": ["재밌", "몰입", "흥미", "중독", "빠져들"],
    "그래픽/사운드": ["그래픽", "사운드", "비주얼", "음악", "효과음"],
    ...
}

리뷰의 voted_up 값에 따라 같은 키워드가 긍정 측면(“그래픽 좋다”)인지 부정 측면(“그래픽 별로”)인지 구분됩니다.

4. 분석 결과 테이블

category_analysis — 게임별 카테고리 빈도

SELECT app_id, polarity, subcategory, count
FROM category_analysis
WHERE app_id = 892970
  AND collected_date = (SELECT MAX(collected_date) FROM category_analysis WHERE app_id = 892970)
ORDER BY count DESC
polarity subcategory count
positive 멀티플레이/협동 14
positive 재미/몰입 10
positive 자유도/리플레이성 3
negative 반복성/단조로움 4
negative 밸런스/페이투윈 1

→ 이 게임은 멀티플레이로 호평받지만 반복성에 약점.

keyword_analysis — 키워드 빈도

SELECT polarity, keyword, count, ratio
FROM keyword_analysis
WHERE app_id = 892970 AND collected_date = '2026-05-06'
ORDER BY count DESC
LIMIT 10
polarity keyword count ratio
positive 건설 14 0.107
positive 건축 10 0.080
positive 생존 12 0.120
negative 지역 6 0.188
negative 필요 8 0.250

count는 절대 빈도, ratio는 그 game·polarity 안 점유율(TF-IDF 계산용 정규화).

5. KPI 설계

대시보드와 AI 코멘트가 보여주는 지표는 위 테이블 위에 얹은 SQL 집계입니다.

KPI 계산
긍정률 total_positive / total_reviews
카테고리 점유율 count / SUM(count) OVER (PARTITION BY polarity)
TF-IDF 차별화 키워드 ratio × ln(N / df) (포스트 5에서 자세히)
플레이타임 코호트 추천률 playtime_at_review 버킷 분류 후 voted_up 평균
언어별 추천률 갭 language 그룹별 voted_up 평균 — 최대/최저 차
동접 baseline z-score 비-이벤트 일별 평균에 대한 표준편차 단위 거리
패치 임팩트 news 날짜 ±7일 윈도 추천률·동접 비교

6. 부분 데이터 한계

리뷰 데이터엔 한계가 있습니다.

  • Steam API는 게임당 리뷰를 무제한 제공하지 않음 — 게임당 평균 ~948개, 최대 ~2400개
  • 이 표본이 전체 리뷰의 어느 부분인지 정확히 알 수 없음 (helpful·recent 기준 정렬)
  • → 시계열 추세는 reviews 직접 집계 대신 review_summary 일별 누적 스냅샷에서 delta로 계산

이 결정 덕분에 4주 긍정률 추세가 데이터 편향 없이 잡힙니다.

다음 포스트에서는 이 데이터 위에 올린 Streamlit 대시보드를 다룹니다.

참고