✋🏾 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과 같이 활용하면 매우 유용하니 한번 활용해봐도 좋을 것 같다.

AI Agent 활용은 이제 필수인 시대가 되었다. 앤트로픽의 Claude, 오픈 AI의 Chatgpt, 구글의 Gemini처럼
다양한 AI Agent들이 서비스되고 있다. AI Agent가 일상생활에 깊게 스며들수록 이 Agent들을 어떻게 하면
잘쓸수있을지에 대한 고민도 깊어지고 있다.

1~2년전만에 해도 질문을 어떻게 하면 좋은 답변을 받을 수 있는지에 대해서 많이 나왔다.
프롬프트를 어떻게 입력하면 좋을지, 어떻게 수정하면 좋을지에 대한 많은 연구가 이뤄졌다.
이를 프롬프트 엔지니어링이라고한다. 예를 들어 아래와 같다.

• 맥락을 전달해라
• 구체적으로 질문하라
• 원하는 결과물의 예시를 전달해라

프롬프트 엔지니어링으로 AI Agent의 답변을 개선할수있지만 이내 이 방법만으로는 아쉬움과 한계가 있음을 알 수 있다.

우선 새 대화를 할때 다시 처음부터 정보를 입력해야한다는 점에서 번거롭다.
새 대화를 할때마다 매번 컨벤션(규칙)을 설정해야하고 컨텍스트(배경지식, 맥락)를 전달해야한다.
답변의 일관성과 연속성을 보장하기 위해 많은 토큰 비용을 써야하며 무엇보다 가장 큰 문제는 … 귀찮다는 점이다

“전에 입력해뒀던 컨벤션의 형태로 답변해줬으면 좋겠는데 ? 컨텍스트를 기억해서 답변해줬으면 좋겠는데?” 이런 생각이 드는것이다.

결국 프롬프트 엔지니어링에서 더 나아가 답변의 규칙(컨벤션)과 맥락(컨텍스트)을 고정해둘수있는 장치가 필요하다고 느끼게된다.
다행히도 클로드에서 스킬과 프로젝트라는 기능을 통해 이를 구현할 수 있다.

클로드 스킬과 클로드 프로젝트 모두 답변의 컨벤션(규칙)과 컨텍스트(맥락)을 미리 세팅해둘 수 있는 장치다.
그렇다면 이 두 기능의 차이는 무엇일까? 우선 클로드 스킬과 프로젝트가 무엇인지 각각 살펴보자.

• 클로드 스킬
  • 사전에 작성된 지침을 기반으로 동작함. 특정 작업 유형이 감지되면 자동으로 트리거되는 구조
  • 관련 키워드나 요청이 나오면 해당 스킬의 작성 규칙과 컨벤션을 불러옴
  • 지침은 SKILL.md 파일에 작성된 내용을 참고하며 이 내용은 언제든 수정 가능
• 클로드 프로젝트
  • 클로드 스킬과 마찬가지로 사전에 작성된 지침을 기반으로 동작함.
  • 특정 프로젝트 공간에 컨텍스트를 설정해두고 그 프로젝트 안에서 대화할때마다 자동으로 적용되는 구조
  • 즉, 이 프로젝트안에서는 항상 이 맥락이 적용된다

가장 큰 차이점은 스킬은 어떤 대화든 특정 작업 유형이 감지되면 트리거되는 구조이고
프로젝트는 특정 공간에서 동일한 지침을 적용한다는 점이다.
이로 인해 실질적으로 주제별로 컨텍스트 관리라는 목적은 둘 다 달성할 수 있지만 활용 방법에는 차이가 생기게 된다.

• 스킬
  • 하나의 대화 안에서 여러 컨텍스트를 필요에 따라 전환.
    • ex) 한 대화에서 ML분석을 하다가 경영진 보고 포맷으로 전환하고 싶을때 스킬을 통해 답변을 자연스럽게 전환시킬수있음
  • 서로 다른 프로젝트에 있는 대화이지만 같은 컨텍스트를 적용하고 싶을때 지침을 프로젝트별로 추가할 필요없이 스킬로 작업
    • ex) 게임 분석 프로젝트와 이커머스 분석 프로젝트에 PPT 출력 (원하는 출력형식이 지정되어있는 지침)이라는 스킬을 실행함으로서 출력 형식을 맞춤
• 프로젝트
  • 동일한 지침을 공유하는 대화를 아카이빙할 수 있다. 지난 대화를 찾아보기 쉬움
  • 지침을 세분화해서 관리하기 쉬움 : UI가 직관적이며 파일 업로드가 가능
  • 답변을 트리거하기 위한 별도의 명령어 없이 프로젝트안에서 대화는 지침이 유지됨
    

클로드 스킬과 프로젝트는 서로를 완전히 대체하는 기능이라기보다는
보완적인 역할로 보는게 좋으며 아래와 같이 조합하여 사용할 수 있다.

• 프로젝트로 지침과 컨텍스트를 설정하고 대화를 아카이빙하되 대화별로 지침을 세분화해야할때 스킬을 활용
• 서로 다른 프로젝트들에 동일하게 지침을 적용할때 스킬 활용
  

예를 들어 다음과 같이 활용할 수 있다.

“나는 산업별로 데이터 분석 프로젝트를 하고 싶다. 산업별로 데이터 성격이 다르기때문에 별도 지침을 설정하려고한다.
프로젝트마다 지침에 맞춰 답변을 받되 보고서 형식은 통일된 레이아웃으로 받고 싶다. 이 경우 어떻게 할까?

• 산업별 데이터 분석에 관한 프로젝트를 생성 : 게임,이커머스,스포츠,금융
• 각 프로젝트마다 산업에 맞는 지침과 참고 자료 설정
• 보고서 형식은 스킬로 지정 ( 트리거 : ppt 만들어줘 )
  위와 같이 세팅해놓으면 각 프로젝트에서 대화하고 대화 끝에 ppt만들어줘라는 트리거 입력하면 된다.

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

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

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 테이블은 무엇이 일어났는가를 저장하고 있는 테이블이고 차원 테이블은 그것이 어떤 맥락인가를 볼 수 있는 테이블이라고 보면 된다.

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