이커머스 데이터 레이크·웨어하우스 설계: 주문·행동 데이터 분석 인프라

이커머스

데이터 레이크데이터 웨어하우스dbtBigQuery데이터 파이프라인

이 글은 누구를 위한 것인가

  • 엑셀로 리포팅하다가 데이터 인프라가 필요해진 팀
  • 마케팅·상품팀이 데이터를 직접 분석하기를 원하는 CTO
  • BigQuery나 Snowflake를 도입하려는 데이터 엔지니어

들어가며

이커머스 데이터는 세 가지로 나뉜다: 트랜잭션 데이터(주문, 결제), 행동 데이터(클릭, 조회, 장바구니), 외부 데이터(광고비, 날씨). 이를 하나의 웨어하우스에서 JOIN해야 진짜 분석이 된다. 행동 데이터의 수집 스키마는 GA4 이커머스 이벤트를, 스트림 수집 계층은 Kafka 문서를 기준으로 설계한다.

이 글은 bluefoxdev.kr의 이커머스 데이터 인프라 가이드 를 참고하여 작성했습니다.

1. 이커머스 데이터 스택 설계

[Modern Data Stack for E-commerce]

수집 (Ingestion):
  트랜잭션 DB → Airbyte/Fivetran → S3 Raw
  앱/웹 이벤트 → Segment/Rudderstack → S3 Raw
  광고 플랫폼 → Airbyte → S3 Raw

저장 (Storage):
  S3 (Raw Zone) → Parquet 변환 → S3 (Processed Zone)

변환 (Transform):
  dbt → BigQuery/Snowflake Mart Layer

서빙 (Serving):
  BI: Metabase, Looker, Tableau
  실시간: Superset, Grafana
  ML: Python, dbt ML
  
[레이어 구조]
Raw → Staging → Intermediate → Mart

Mart 테이블:
  mart_orders: 일별 주문 집계
  mart_products: 상품 성과
  mart_customers: 고객 LTV/RFM
  mart_marketing: 채널별 ROAS

2. 이벤트 수집 파이프라인

from dataclasses import dataclass, asdict
from datetime import datetime
import json

@dataclass
class EcommerceEvent:
    event_id: str
    user_id: str | None
    session_id: str
    event_type: str  # 'page_view', 'product_view', 'add_to_cart', 'purchase'
    timestamp: str
    properties: dict

class EventCollector:
    
    def track_product_view(self, session_id: str, user_id: str | None, product_id: str):
        return EcommerceEvent(
            event_id=generate_uuid(),
            user_id=user_id,
            session_id=session_id,
            event_type="product_view",
            timestamp=datetime.utcnow().isoformat(),
            properties={
                "product_id": product_id,
                "referrer": get_current_referrer(),
                "page_url": get_current_url(),
            },
        )
    
    def track_add_to_cart(
        self, session_id: str, user_id: str,
        product_id: str, quantity: int, price: int,
    ):
        return EcommerceEvent(
            event_id=generate_uuid(),
            user_id=user_id,
            session_id=session_id,
            event_type="add_to_cart",
            timestamp=datetime.utcnow().isoformat(),
            properties={
                "product_id": product_id,
                "quantity": quantity,
                "price": price,
                "total": quantity * price,
            },
        )
    
    async def flush_to_s3(self, events: list[EcommerceEvent]):
        """이벤트를 S3에 Parquet으로 저장"""
        import pandas as pd
        import pyarrow as pa
        import pyarrow.parquet as pq
        import boto3
        
        df = pd.DataFrame([asdict(e) for e in events])
        
        # 날짜별 파티셔닝
        date_str = datetime.utcnow().strftime("%Y/%m/%d")
        hour_str = datetime.utcnow().strftime("%H")
        
        buffer = pa.BufferOutputStream()
        pq.write_table(
            pa.Table.from_pandas(df),
            buffer,
            compression="snappy",
        )
        
        s3 = boto3.client("s3")
        s3.put_object(
            Bucket="ecommerce-datalake",
            Key=f"events/year={date_str[:4]}/month={date_str[5:7]}/day={date_str[8:10]}/hour={hour_str}/{generate_uuid()}.parquet",
            Body=buffer.getvalue().to_pybytes(),
        )

3. dbt 변환 모델

-- models/mart/mart_orders.sql
-- 일별 주문 집계 mart

WITH orders_base AS (
    SELECT
        o.id AS order_id,
        o.user_id,
        o.created_at,
        DATE(o.created_at) AS order_date,
        o.total_amount,
        o.status,
        o.payment_method,
        c.channel AS acquisition_channel
    FROM {{ ref('stg_orders') }} o
    LEFT JOIN {{ ref('stg_order_attributions') }} c 
        ON o.id = c.order_id AND c.model = 'last_click'
    WHERE o.status NOT IN ('cancelled', 'failed')
),

daily_metrics AS (
    SELECT
        order_date,
        COUNT(DISTINCT order_id) AS order_count,
        COUNT(DISTINCT user_id) AS unique_buyers,
        SUM(total_amount) AS gross_revenue,
        AVG(total_amount) AS average_order_value,
        COUNT(DISTINCT CASE WHEN acquisition_channel IS NOT NULL 
            THEN user_id END) AS paid_buyer_count
    FROM orders_base
    GROUP BY 1
)

SELECT
    order_date,
    order_count,
    unique_buyers,
    gross_revenue,
    ROUND(average_order_value) AS aov,
    paid_buyer_count,
    ROUND(gross_revenue / NULLIF(order_count, 0)) AS revenue_per_order
FROM daily_metrics
ORDER BY order_date DESC

마무리

이커머스 데이터 스택은 S3(레이크) → BigQuery(웨어하우스) → dbt(변환) → Metabase(BI) 조합으로 월 100만원 이하로 구축 가능하다. 처음에는 주문 데이터와 행동 이벤트 두 개만 연결하고, 분석 요구가 늘면 광고비·반품 데이터를 추가하는 순서로 진행하라.