✋🏾 Google BigQuery을 이용하여 쓰인 포스팅입니다.

BigQuery 조건식 중에 null을 이용하는 조건식은 대표적으로 3가지가 있다.

• COALESCE(expr1,expr2,expr3…….)
  
  • 인수로 들어간 표현식(expr) 중에 null이 아닌 첫번째 표현식 값을 반환한다.
    
  • 값이 반환되면 나머지 표현식은 평가되지 않는다.
    
  • 입력 표현식에는 모든 데이터 타입이 입력 가능하다.
    
    
• NULLIF(expr1, expr2)
  
  • expr1과 expr2가 값이 같으면, null을 반환하고 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2는 같은 상위 데이터 타입에 속해야하며, 비교 가능해야 한다.
    
    
• IFNULL(expr1, expr2)
  
  • expr1이 null이면 expr2를 반환한다. 그렇지 않으면 expr1을 반환한다.
    
  • expr1과 expr2에는 모든 데이터 타입이 입력될 수 있다.
    
  • COALESCE(expr1,expr2)와 같은 표현이다.
    
    

예시 데이터셋은 아래와 같다.

-- 예시 데이터 셋 제작 코드 
WITH ex_table AS 
( 
    SELECT 30 AS col1, 37 AS col2 , 55 AS col3
  UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
  UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
  UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3 
)

SELECT 
  co11
  ,co12
  ,col3
FROM ex_table
;

위 예시 데이터에 null과 관련된 조건식( coalesce,nullif,ifnull)을 넣고 결과를 비교해보자
출력 결과는 테이블 맨 우측에 위치한 result 칼럼에서 확인할 수 있다.

• COALESCE 구문 예시
  

/*
coalesce 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3
)

SELECT
  *,
  coalesce(col1,col2,col3) as result_coalesce
FROM ex_table;

• NULLIF 구문 예시
  
  

/*
nullif 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 2 AS col2 , null AS col3 
)

SELECT
  col1,
  col2,
 nullif(col1,col2) as result_nullif -- nullif는 expr 총 2개만 비교 가능
FROM ex_table

• IFNULL 구문 예시
  
  

/*
ifnull 예시
*/
WITH ex_table AS ( 
SELECT 30 AS col1, 37 AS col2 , 55 AS col3
UNION ALL SELECT null AS col1, 17 AS col2 , 1 AS col3
UNION ALL SELECT null AS col1, null AS col2 , 1 AS col3
UNION ALL SELECT 3 AS col1, 3 AS col2 , null AS col3
 )

SELECT
  col1,
  col2,
 ifnull(col1,col2) as result_ifnull
FROM ex_table

이처럼 null과 관련된 조건식 3가지를 알아보았다.

나는 특히 실무에서 IFNULL 구문을 주로 활용한다.
(거의 IFNULL만 사용한다)

데이터 추출시 LEFT JOIN을 이용하여 테이블끼리 매칭하고 집계하는 경우가 많은데,
이 때 매칭이 안되는 집계 결과(null)를 0으로 바꿔줄때 많이 사용한다.

ex) IFNULL(t2.won,0) AS won

위의 케이스처럼 IFNULL을 LEFT JOIN과 같이 활용하면 매우 유용하니 한번 활용해봐도 좋을 것 같다.

신규 유저들이 어느 단계에서 이탈하는지 살펴보기 위해 퍼널 분석을 진행하고자 한다.
( 샘플 데이터 )

첫번째 차트는 각 단계별 진행 유저수 , 두번째 차트는 단계별 이탈률 , 세번째 차트는 플랫폼별 단계별 전환률이다.
이 차트들을 활용해 어느 단계에서 유저들이 많이 이탈하는지 , 개선 지점은 어디일지 살펴보자.

Q1. Tutorial 개선과 첫 구매 개선 중 먼저 개선이 필요한 것은 무엇인가?

A1.
장기적인 관점에서 고려했을때 튜토리얼 개선이 우선적이다.

• 튜토리얼 시작에서 완료까지 빠지는 유저 : 80,000 -> 60,000 ( -20,000명 , 약 25% 빠짐 )
  
• 상점 오픈부터 첫 구매까지 빠지는 유저 : 20,000 -> 6,000 ( -14,000명, 약 70% 빠짐 )
  

이전 단계 대비 빠지는 비율을 봤을때 첫구매 전환 단계가 튜토리얼 완료 단계보다 더 크다.
또 튜토리얼과 첫 구매 둘다 5%p 개선이 가능하다고 가정하고 첫 구매 증가량이 얼마나 나오는지 보면
(다른 전환율 고정)

• 튜토리얼 (tutorial Start→ tutorial Complete)를 +5%p 개선: +약 467건의 첫 구매
  
• 첫구매 (Store→Purchase)를 +5%p 개선: +1,150건의 첫 구매
  

첫 구매 전환 단계 개선이 튜토리얼 완료 단계 개선보다 더 많은 첫 구매를 발생시킨다.
이렇게 보면 첫 구매 전환 단계 개선이 우선적으로 필요하다고 생각할 수 있다.

다만 매출과 튜토리얼의 특성을 봐야한다. 튜토리얼은 누구나 거쳐야하는 과정이고
다음 단계로 넘어가지않으면 플레이 자체를 안해버리는 순수 이탈이다.

반면 구매는 나중에 다시 기회가 있는 행동이라 첫 구매 실패가 곧바로 이탈로 연결되지않을 수 있다.
즉 튜토리얼은 유저층을 잡을수있는 기회가 한번뿐인 단계이고 첫 구매는 다음에 다시 기회가 있는 단계이다.

미래 결제 가능성과 향후 트래픽까지 같이 고려했을때 LTV 효과는 튜토리얼 개선이 첫구매 개선보다 더 높을 가능성이 크다.
따라서 튜토리얼 개선이 우선적이라고 판단한다.

Q2. Android에서 Store-> Purchase 전환이 특히 낮은 이유를 가설로 써보자.

A2.
Android와 ios의 두 플랫폼의 차이점 때문에 전환율이 달랐다고 예상할 수 있다.
Android가 ios보다 약 10%p 적은 전환율을 보인다. 두 플랫폼의 차이점으로는 다음 2가지를 예상할 수 있다.

( 이용자층 )
android는 일반적으로 ios보다 구매력이 낮은 유저층의 비율이 더 높다.
구매력이 낮다고 판단할 수 있는 저가 단말을 사용하는 유저들의 비율이 높고
작업장 운영을 위한 단말기로 사용하는 경우도 많다. (일반적으로 작업장 계정은 결제를 하지않는다)

만약 패키지 상품의 가격대가 전반적으로 높다면 구매력이 낮은 유저들이 쉽게 지갑을 열지않았을 것이고
이로인해 android에서 더 낮은 결제전환율을 보였음을 예상할 수 있다. 이 케이스라면 플랫폼 전체 유저를 모수로 삼지말고
모수에 보정을 가해서 구매력이 있는 유저들을 한정하고 그 안에서 전환율을 비교하는 것이 정확한 진단을 내릴 수 있을 것이다.

( 결제 모듈 연동 )
android에 탑재된 결제 모듈이 ios에 탑재된 모듈보다 결제 절차가 복잡할 경우 이용자들은 구매에 불편함을 느낄 수 있다.
또 모듈 차이로 지원하는 결제 수단이 다를 수 있다. ex) 카카오페이는 ios에서는 되나 android에서는 지원 안함.

( 결제 실패/지연 )
android 단말기에서 결제창 진입은 했는데 구매 완료 이벤트 (purchase_success)가 안찍히는 케이스가 많을 수 있다.
purchase_fail_reason, error_code, latency, crash_before_success 같이 원인을 확인할 수 있는 이벤트를 심어서 어느 구간에서 꺾이는지 확인하면 될 것이다.

Q3. Store Opened 이벤트 정의가 너무 넓으면 생기는 문제는? (퍼널 해석이 어떻게 왜곡될까)

A3.
퍼널 분석은 일방향의 연속된 단계하에서 다음 단계로 넘어갈때 유저들이 얼마나 남아있는지를 볼 수 있는 분석이다.
유저들이 어느 단계에서 다음 단계로 넘어갈때 어려워하는지 알 수 있다.

퍼널 분석에서 단계의 정의가 너무 넓으면 유저가 정확히 어느 지점에서 어려워하는지 특정하기 어려워진다.
예를 들어 store opened라는 이벤트가 있다고 가정하자. 1일내 첫 접속인지,2일내 첫 접속인지 아니면 로그인하고 첫번째 방문인지
아니면 store를 방문할때마다인지등 store opened가 정의가 너무 넓다면 유저가 어려워하는 구간이 정확히 어떤 지점인지 알기 어렵다.

요약하자면 이벤트 정의가 넓으면 아래 케이스들처럼 어느 지점이 문제인지 확인이 어렵다.

• 이벤트 중복 (재방문)으로 분모가 과집계 되거나 반대로 유니크 처리로 정확한 분석을 할 수 없음
  
  • store opened가 방문할 때마다 찍히면 세션 기반/유저 기반 집계에 따라 숫자가 흔들림
    
  • 유니크로 유저수를 처리해도 첫 오픈인지 N번째 오픈인지 섞이면 인사이트가 흐려짐
    
• 윈도우 구간 불일치 (시간 불일치)
  
  • Store Opened는 D7까지 포함인데 Purchase는 D1만 보면 스토어는 열었는데 구매가 없다가 과장되어 보일 수 있음
    

1. 분석 대상

• 대상: 신규 유저 7일 코호트(샘플 데이터)
  
• 퍼널: First Open → Tutorial Start → Tutorial Complete → Account Created → First Match Played → Store Opened → First Purchase
  

2. 현황

• 핵심 병목(상대 이탈 1위): Store Opened → First Purchase
  
  • 이탈 70.0% (전환 30.0%)
    
  • 플랫폼: iOS 33.9%, Android 25.5%
    
  • 최대 손실(절대 이탈 1위): Tutorial Start → Tutorial Complete
    
    • -21,500명 (이탈 26.1%)
      

3. 액션 플랜

• 튜토리얼(절대 이탈 1위)을 1차 개선 대상으로 두되, Store→Purchase(상대 이탈 1위)는 결제 직전 마찰/오류 가능성이 커서 병행 점검 권장.

• 튜토리얼 구간 검증 체크리스트
  
  • 튜토리얼 스텝별 이탈(세부 이벤트), 완료 소요시간 분포, 로딩/크래시 여부.
    
• 안드로이드 전환률 낮은 원인 파악 및 개선
  
  • 유저 구매력차이
    
  • 결제 UX/결제수단 차이
    
  • 결제 실패율/오류코드/지연
    

2025-09-01부터 2025-12-01까지 DAU와 7일 이동평균 DAU가 있다.
( 샘플 데이터 )

이 차트를 보고 트래픽이 견조한지, 운영상 이슈는 없었는지 , 앞으로의 과제는 무엇이며
어떻게 개선할 수 있을지 종합적으로 살펴보자.

차트 분석을 먼저 진행하고 이를 기반으로 최종 보고서를 작성하는 것으로 포스팅을 구성한다.

Q1. 추세(Trend): 7D MA 기준으로 전체 기간(약 9~11월) DAU는 상승/하락/횡보 중 무엇인가?

A1.
전체 기간 상승중이다. DAU 기준 , 9월 초 11만명 후반대에서 11월 말 12만명 후반대까지 약 만명의
AU가 증가하였다. ( 약 10% 증가 ) 7D MA 차트로 보면 더 명확하게 전체 기간 상승중임을 알 수 있다.

다만 11월 15일의 급격한 수치 하락으로 7D MA가 크게 감소하는데 DAU로 봤을때는 다음날부터 정상 AU 수준을 회복한다.

이동평균은 노이즈를 줄이는 대신 윈도우 때문에 충격이 며칠간 남는 특성이 있다.
이동평균을 볼때 DAU를 보조 지표로 같이 봐야겠다.

Q2. 계절성(Seasonality): 요일 패턴이 보이는가? 어떤 요일이 강하고 약한가?

A2.
2번째 차트 (Average DAU by Day of Week)를 봤을때 주말에 트래픽이 빠지는것을 알수있다.
거의 모든 주간에서 주말에 트래픽이 빠졌다가 주중에 회복되는 경향을 보인다.
주말에 트래픽이 빠지는 것이 유사한 수준으로 주기성을 보이는지 체크해봐야한다.

주기성있게 주말에 유사한 수준의 트래픽이 빠지는 것이라면 정상 패턴으로 고려할 수 있다.
Weekly weekday vs weekend 차트를 통해 주말도 우상향하고있는지 보면 주말이 문제인지
정상 패턴인지 구분할 수 있다.

Q3. 이상치(Anomaly): 그래프에서 비정상적으로 튀는 날이 보이는가?
그 날짜 전후로 어떤 형태(급락/급등/회복)를 보이는가?

A3.
2025-10-10 , 2025-11-15 이 두지점이 눈에 띈다.

먼저 2025-10-10일(금요일)은 9월부터 10월중 유일하게 DAU 14만명을 찍었다.
주말을 제외하고 평일중 전일 대비 가장 많이 증가한 날이다.
직전 금요일 (10/03) 대비해서도 6.75% 증가하였다.
다만 바로 다음날 트래픽이 빠져서 증가를 유지하진 못했다.

2025-11-15은 최저 트래픽을 기록한 날이며 전일 대비 가장 많이 감소한날이다.
평소에도 주말에 하락폭을 보이긴하지만 가장 큰 폭으로 트래픽이 감소하였다.
전일 대비 약 5만명이 빠졌는데 이는 35.2% 감소한 수치이다.
직전 토요일 (11/08) 대비로는 -26.6% 감소, 토요일 평균 (약 115,692명) 대비로 봐도 꽤 이례적으로 감소한 수치이다.

전일 대비와 함께 동요일 대비도 같이봐야 증감 효과를 명확하게 파악할 수 있다.
전일 대비로만 볼 경우 주말 효과가 섞일 수 있다. ( ex. 금->토 )

장시간 점검이 있었을 가능성이 높다.(접속 불가)
로그인 실패/서버 에러율/크래시율/점검 공지 시간대 매칭을 통해 큰 이슈는 아닐지 체크해봐야한다.

추가로 잔차에서 이상치를 잡는 STL decomposition 같은 방법을 쓰면
요일 효과를 깔끔히 걷어내고 이상치를 판단할 수 있다.

STL(Seasonal-Trend decomposition using LOESS) decomposition : 
시계열 데이터를	관측치 = 추세(Trend) + 시즌(Seasonal) + 잔차(Residual) 로 분해하는 방법.

주간(요일)처럼 강한 주기 패턴이 있을 때, 주기성을 걷어내고도 남는 설명되지 않는 충격(Residual)을 
분리해 이상치를 더 공정하게 잡는 데 유용.								

Q4. 운영 관점 가설과 액션 플랜을 제시하라: 예: 이벤트/마케팅, 점검/장애, 콘텐츠 업데이트, 외부 이슈(연휴 등) 와 다음 액션(데이터 추가 확인)

A4. 주말 트래픽 하락을 보완하는 것이 가장 큰 과제이다. 트래픽 하락의 유형을 먼저 나눠서 살펴본 후
다른 지표에 영향은 없었는지 이를 끌어올리려면 어떻게 해야할지 살펴보자.

• DAU를 신규/복귀/기존(연속) 활성으로 분해 (주말에 무엇이 빠지는지 바로 보임)
  
• 리텐션/코호트: 주말 포함 코호트 vs 주말 제외 코호트 비교 (주말이 이탈 트리거인지 확인)
  
• 세션/플레이타임/핵심행동(던전 클리어, 매칭, 결제 등) 동반 하락 여부
  => 주말에 빠지는 유저층이 누군지 규명하고 이 유저들이 주말에도 접속하게 만드는 방법을 찾자.
  

ex)

CASE 1) 평일에 신규 유저가 가입하고 주말에 바로 빠짐. 신규 유저는 지속 증가
=> 신규 유저들이 이탈하지않도록 보상 설계 : ex) 14일 연속 출석 이벤트. 주말마다 큰 보상

CASE 2) 복귀 유저가 바로 이탈하고 새로운 복귀 유저 들어오는 회전문 형태의 유입
=> 복귀 유저 잔존하도록 이벤트 설계 ex) 연속 리텐션 보상 확대

CASE 3) 평일에 복귀했던 유저가 주말에 빠졌다가 평일에 다시 들어오는지 ( 평일 컨텐츠만 즐김 )
=> 평일에만 플레이하는 유저가 주말에도 플레이하도록 만들기 ex) 평일 던전 -> 주말 던전으로 확장

1. 목적 & 지표 정의

• 목적
  • 9~11월 DAU의 추세 확인
  • 요일 효과 정량화 ( seasonality )
  • 이상치(급등/급락)를 요일 효과와 분리해 탐지하고 운영 액션으로 연결
    
• 지표 정의
  • DAU : 특정 날짜에 앱/서비스에 활동한 고유 사용자 수
  • 7D Moving Average(7D MA): 단기 변동을 완화해 추세를 더 잘 보이게 하는 스무딩 기법
    

2. 핵심 KPI 요약

• 전체 추세 : 7D MA 기준 +15.7% 상승 (기간 초반 ≈ 118,930 → 기간 말 ≈ 137,626)
  
• 요일 효과 (주말 효과) :
  
  • 평일 평균(Mon–Fri) ≈ 131,921
    
  • 주말 평균(Sat–Sun) ≈ 116,028
    
  • 주말/평일 비율 ≈ 0.880 → 주말이 평일 대비 약 12% 낮음
    
• 주요 이상치 후보(시각화에서 점으로 표시)
  
  • 2025-10-10 급등
    
  • 2025-11-15 급락(최저치)
    

3. 시각화 기반 인사이트

• 추세(Trend) : 상승
  • 7D MA와 STL Trend 모두 완만한 우상향.
    
  • 운영 해석: 기본 체력(베이스)이 상승 중이므로, 단기 변동(요일/이벤트/장애)을 분리해 관리하면 성장 해석이 깔끔해짐.
    
• 주기성 (Seasonality) : 주말 약세가 구조적으로 반복
  
  • 요일 패턴이 반복되며, 주말에 일관되게 떨어졌다가 주중에 회복.
    
  • 운영 해석: “주말 하락”이 정상 패턴 범위인지, 혹은 최근에 더 약해지는지(악화)가 핵심 포인트
    
• 이상치(Anomaly) : STL decomposition 으로 요일 효과 제거 후 확인
  
  • STL residual 차트에서 11/15는 매우 큰 음의 잔차(급락), 10/10은 양의 잔차(급등)로 관측.
    
  • 주말이라 원래 낮다 같은 요일효과를 걷어낸 뒤에도 설명되지 않는 충격이 남아 이상치 가능성이 높음
    
  • Residual z-score 기반 탐지(ㅣzㅣ ≥ 3)로 요일효과 제거 후에도 남는 충격을 이상치 후보로 표시
    

STL(Seasonal-Trend decomposition using LOESS) decomposition :
시계열 데이터를	관측치 = 추세(Trend) + 시즌(Seasonal) + 잔차(Residual) 로 분해하는 방법.

주간(요일)처럼 강한 주기 패턴이 있을 때, 주기성을 걷어내고도 남는 설명되지 않는 충격(Residual)을 
분리해 이상치를 더 공정하게 잡는 데 유용.				

적용 방식 (보고서 기준):	
	period=7(주간), robust=True로 분해
	Residual z-score(|z|≥3)를 이상치 후보로 표기 (차트 점 표시)

4. 운영 관점 가설

• 2025-11-15 급락: 장애/점검/로그 누락/집계 파이프라인 이슈 가능성
  
  • 근거: 요일 효과를 제거한 STL residual에서도 큰 감소가 보임 (설명 불가 하락)
    
• 2025-10-10 급등: UA/프로모션/대형 업데이트/외부 노출로 인한 단기 유입 급증 가능
  
  • 근거: 다음날 감소 (증가가 지속되지않음) → 일회성 드라이브 패턴
    
• 주말 약세 구조: 유저 구성(신규/복귀/기존) 중 특정 세그먼트가 주말에 더 크게 빠지는 구조 가능
  

5. 권장 액션

• 원인 규명: 주말에 누가 빠지는가?를 먼저 분해
  
  • DAU 구성요소 분해: 신규 / 복귀 / 연속활성(기존)
    
  • 코호트/리텐션: 주말 포함 코호트 vs 주말 미포함 코호트 비교
    
  • 행동 동반지표: 세션수, 플레이타임, 핵심 콘텐츠 참여(던전/매칭), 결제/구매 시도
    
• 주말 트래픽 보완 액션 플랜
  
  • CASE 1(신규 이탈): 주말에 연속 출석 보상 강화(주말 보상 가중)
    
  • CASE 2(복귀 회전문): 복귀 후 3~7일 리텐션 미션(주말 포함 시 보상 업)
    
  • CASE 3(평일형 유저): 평일 콘텐츠 → 주말 변형/확장 콘텐츠로 주말 접속 명분 제공
    주의 사항: 주말 이벤트는 주말 DAU/주말 잔존/주말 ARPPU/세션당 플레이타임을 함께 보며, 평일 지표 악화(피로도) 없는지 체크
    
• 이상치 대응 (ex. 11/15)
  • STL residual 기반 자동 알람 룰 (예: ㅣzㅣ≥3) + 장애 지표(로그인 실패/에러율/결제 실패) 연동
    
  • 다음날 회복했으니 OK가 아니라 매출/환불/이탈 후행 영향이 남는지 반드시 체크
    

6. 후속 분석

• 주말 트래픽 감소의 세그먼트 원인(신규/복귀/기존) 확인
  
• 10/10, 11/15의 운영 로그/캠페인 캘린더 매칭하여 원인 확인
  
• 주말 보완 실험 1개 실행 → 주말/평일 모두 KPI 증가하는지 확인
  

데이터마트는 DB에 쌓여있는 데이터를 분석에 활용할 수 있도록 별도의 테이블을 만들어 구축해놓은 시스템을 말한다.
원본 데이터를 바로 쓰면 되지 뭐하러 테이블을 또 만드느냐라고 생각할 수 있다.

원본 테이블을 직접 바라보면 다음과 같은 이슈가 있다.

• 정제가 필요한 경우가 많음
  • 집계하려면 별도의 구문 처리가 필요함
  • 결측치가 있는 경우, Json 형식으로 되어있는 경우 , 데이터 타입이 분석에 적합하지않게 저장 (ex. 숫자가 string 타입으로 남음 ) 되어있는 경우
• 불필요하게 많은 데이터를 조회해야함
  • 필요한 데이터는 일부인데 매번 전체 데이터에서 where절로 조건을 걸어서 가져오는 것은 불필요한 쿼리 비용을 발생시킴.
  • 데이터를 조회하는 크기 자체를 미리 줄인다면 쿼리 비용을 줄일 수 있음
• 보안 문제 (권한)
  • 개인 정보가 담겨있거나 보안사항에 따라 데이터 접근 권한을 분리해야하는 경우가 있음
  • 데이터 마트를 구축하고 이 단위로 권한을 관리할 수 있음

위와 같은 이유들로 데이터 마트를 구축하는 것이다.

데이터마트를 어떤 구조로 구축할지에 대한 다양한 방법론이 있다. 이를 데이터마트 아키텍처라고 한다.
트랜잭션을 기록하는 Fact 테이블과 그것을 맥락화하는 차원 테이블 (Dimension) 을 설계하는 것을 기본 골자로 한다.

1) Fact 테이블 예시

1-1) fact_sales

• 이 테이블은 “매출”이라는 사건(event) 을 저장
• 각 행은 하나의 구매 이벤트이며, 금액(sales_amount) 같은 측정값이 포함된다.
• user_id, product_id, date_key 는 각각 “누가”, “무엇을”, “언제” 했는지를 나타내는 외래키다. 즉, “어떤 차원에서 본 사건인가”를 연결하는 역할

2) 차원 테이블 예시

2-1) dim_user

• 유저에 대한 속성 정보를 저장
• 팩트 테이블의 user_id 와 연결되어 분석 시 “국가별 매출”, “디바이스별 리텐션” 등을 계산할 수 있게 해줌

2-2) dim_product

• 아이템, 상품 등 “무엇을 구매했는가”에 대한 메타데이터. 팩트 테이블의 product_id와 연결된다.

요약하자면 Fact 테이블은 무엇이 일어났는가를 저장하고 있는 테이블이고 차원 테이블은 그것이 어떤 맥락인가를 볼 수 있는 테이블이라고 보면 된다.

데이터마트 아키텍처는 대표적으로 스타 스키마 , 스노우 플레이크 스키마 , 갤럭시 스키마 , 데이터볼트 모델링 , 메달리온 아키텍처등이 있다. 이후 포스팅에서 각 아키텍처의 특징과 장단점은 무엇인지 살펴보도록하자.

날짜 함수란 날짜 타입 ( DATE, DATETIME ,TIMESTAMP…) 의 데이터를 다룰때 사용하는 함수이다.

날짜의 출력 형식을 지정하거나 날짜를 계산할 때 사용한다.

• Case 1 ) 날짜 출력 형식 :
  
  • YYYY-MM-DD 형식으로 값이 입력되어 있는 날짜 필드를 YYYYMMDD 형식으로 바꾸고 싶다.
    
  • ( ex. 2024-08-02 -> 20240802 )
    
• Case 2 ) 날짜 계산 :
  
  • 현재 날짜 기준, n일전 데이터에서 전일자 데이터까지 조회하고 싶다.
    
  • ( ex. 2024년 8월 28일 기준 , 35일전인 데이터에서 전일자 데이터까지 조회하고 싶다 )
    

날짜 출력 형식을 지정할때는 어떤 함수를 쓰는지, 날짜를 계산할때는 어떤 함수를 쓰는지 아래에서 알아보자.

앞서 소개했듯이, 날짜 함수는 크게 두가지로 분류할 수 있다.
( 내가 만든 분류이다. 공식적인 분류는 아니다. )

• 출력 형식 지정 함수: 데이터 타입이나 출력 형태를 지정하는 함수
  
  • 대표 함수 : FORMAT_DATE , PARSE_DATE , DATE_TRUNC , EXTRACT
    
• 계산 함수 : 날짜를 계산하는 함수
  
  • 대표 함수 : DATE_ADD , DATE_SUB , DATE_DIFF
    

각 대표 함수에 대한 설명은 아래 표에서 살펴 보자.

1) 출력 형식 지정 함수

2) 계산 함수

특히 출력 형식 지정함수에서 FORMAT_DATE과 PARSE_DATE을 헷갈려하는 경우가 많다.
( ex. 20240825 -> 2024-08-25 )

둘은 서로 역의 관계라고 생각하면 된다.

FORMAT_DATE은 date를 string으로, PARSE_DATE은 string을 date로 바꿔서 출력한다.

특히 PARSE_DATE은 날짜를 나타내는, ‘%Y-%m-%d 형식이 아닌 string값을 날짜 타입으로 변경할때 유용하다.
( ex. 20240825 -> 2024-08-25 )

DATE( ) 사용해서 날짜 타입으로 변경 많이 하는데 %Y-%m-%d 형식이 아닌 string 값은 DATE( ) 함수 사용시 에러가 난다.
이 경우 PARSE_DATE( ) 를 사용해야한다. substr과 concat함수를 이용해서 조합하는 방법도 있지만
구문이 길어지기 때문에 PARSE_DATE( ) 을 사용하는 것을 권장한다.

DB를 일별로 수집한다고 했을때 테이블의 _table_suffix가 ‘%Y%m%d’ 형식으로 되어있는 경우가 많다.
불러온 _table_suffix를 그대로 날짜 필드로 넣고 싶을때 PARSE_DATE를 쓰면 된다.
( 예시: PARSE_DATE(‘%Y%m%d’,_table_suffix) AS yyyymmdd )

1) 다루고자 하는 날짜 타입 (Date, Datetime ,Timestamp )에 따라 함수명을 변경할 수 있다.

위에서 소개한 함수들은 DATE 타입을 기준으로 함수명을 써놓았는데 예시처럼 다 변경이 가능하다
ㄴ ex) FORMAT_DATE -> FORMAT_DATETIME , FORMAT_TIMESTAMP
ㄴ ex) DATE_ADD -> DATETIME_ADD , TIMESTAMP_ADD

참고로 DATE와 DATETIME, TIMESTAMP는 표현할 수 있는 날짜 범위에 차이가 있다.

• DATE : 날짜를 년, 월 , 일 까지 저장하고 있는 날짜 타입
  
• DATETIME : 날짜를 년 , 월 , 일 , 시 , 분 , 초 ,초 미만으로 저장하고 있는 날짜 타입
  
• TIMESTAMP : 날짜를 마이크로초단위 ( 초 이하 소숫점 6자리 ) 로 정밀하게 저장하고 있는 날짜 타입
  

TIMESTAMP는 빅쿼리상에서는 타임존이 같이 출력된다. ( ex: 2024-01-01 12:30:12.000000 UTC )
빅쿼리 자체적으로는 마이크로초로 날짜값을 저장하고있는데 ( 고정된 초기 시점 이후 경과된 마이크로초 수 )
표기시에 변환해서 출력해주면서 타임존까지 같이 나타내주는것이다.

DATETIME과의 차이는 타임존을 포함하고 있다 정도로 보면 될것같다.

2) 날짜 출력 형식은 아래 표현을 참고하면 된다.