SQL_Aplied7.sql

# 7주차 
USE classicmodels;

# 1. shop_events 테이블 : 쇼핑몰 서비스의 사용자 행동 로그(event log) 데이터를 저장한 테이블
select * from shop_events; 
/**********************************************************************
 🧱 shop_event 테이블 구조 및 분석 개요
**********************************************************************/
-- 즉, “사용자가 언제, 어디서, 어떤 행동을 했는가”를 추적하는 로그 테이블

-- 주요 분석 목적:
-- ① 사용자 행동 흐름(User Journey)
-- ② 전환율(Funnel Conversion)
-- ③ 디바이스별/국가별 트래픽 분석
-- ④ 세션 단위 행동 패턴 분석 등

/**********************************************************************
 📘 컬럼별 설명
**********************************************************************/

-- event_id         : 이벤트 고유 ID (로그 순서 구분용 기본키)
-- user_id          : 사용자 ID — 어떤 사용자가 이벤트를 발생시켰는지
-- session_id       : 세션 ID — 사용자의 접속 단위(앱 실행~종료 구간)
-- event_name       : 이벤트 이름 — 행동의 종류(app_open, view_item, add_to_cart 등)
-- event_time       : 이벤트 발생 시각 (YYYY-MM-DD HH:MM:SS)
-- event_properties : JSON 형식의 상세 속성 (상품, 위치, 디바이스 등 메타데이터)
-- device           : 사용한 디바이스 (android / ios / web 등)
-- country          : 접속한 국가 코드 (KR, JP, US 등)

/**********************************************************************
 📊 예시 데이터 해석
**********************************************************************/

-- | user_id | event_name   | country | 설명                         |
-- |---------|---------------|----------|------------------------------|
-- | 101     | app_open      | KR       | 한국에서 안드로이드로 앱 실행 |
-- | 101     | view_item     | KR       | 상품 상세페이지 조회          |
-- | 102     | add_to_cart   | US       | 미국에서 장바구니 담기 수행   |
-- | 103     | view_item     | JP       | 일본에서 상품 보기            |
-- | 106     | add_to_cart   | US       | 미국에서 장바구니 담기 수행   |

-- -> 로그를 시간 순서(event_time ASC)로 조회하면 사용자의 여정(User Journey)을 추적할 수 있다

SELECT
  user_id,
  JSON_UNQUOTE(JSON_EXTRACT(event_properties, '$.device')) AS device
FROM shop_events
WHERE JSON_UNQUOTE(JSON_EXTRACT(event_properties, '$.device')) = 'ios';

# 2.  집계를 해보자
### CTE - event, user, session 집계
### 퍼널을 진행해야 한다.
### event_name app_open ... purchase


## 각 세션들의 event_name 별 집계!!!

### (1) 첫/각 단계 시간에 대해서 뽑아보는 것
with session_events as (
select
	session_id,
    any_value(user_id) as user_id,
    min(event_time) as session_start_ts,
    min(case when event_name = 'app_open' then event_time end) as t_open, 		# 앱 실행
    min(case when event_name = 'view_item' then event_time end) as t_view, 	# 상품 상세페이지 조회
    min(case when event_name = 'add_to_cart' then event_time end) as t_cart, 	# 장바구니 담기
    min(case when event_name = 'purchase' then event_time end) as t_purchase	# 구매
from shop_events
group by session_id
),
### 유저가 이탈 후 다시 들어오면, 새로운 세션이 열린다. 즉, 유저 아이디는 같지만 세션 아이디는 다르다
### 최초로 들어간 시간을 찾아서 세션 별 유저의 행동 경로를 알 수 있다.

### (2) 각 세션들의 event 여부
session_flags as (
	select
		session_id,
        user_id,
        date(session_start_ts) as session_date,
        (t_open is not null) as has_open,
        (t_view is not null and t_open is not null and t_view >= t_open) as valid_view,
        (t_cart is not null and t_view is not null and t_view >= t_open and t_cart >= t_view) as valid_cart,
        (t_purchase is not null and t_cart is not null and t_view is not null and t_view >= t_open and t_cart >= t_view and t_purchase >= t_cart) as valid
	from session_events
),

### (3) 위의 1과 2를 이용한 날자별 event 합 집계     
agg as (
	select
		session_date,
        count(*) as session_total,
        sum(has_open) as s_open,
        sum(valid_view) as s_view,
        sum(valid_cart) as s_cart,
        sum(valid) as s_purchase
	from session_flags
    group by session_date
)

### (4) 각 event 별 다음 단계의 event 로 이동하는 비율
select  
	session_date,
    session_total,
    s_open, s_view, s_cart, s_purchase,
    round(s_view/ nullif(s_open,0) * 100, 2) as open_to_view_pct,
    round(s_cart/ nullif(s_view,0) * 100, 2) as view_to_cart_pct,
    round(s_purchase / nullif(s_cart,0) * 100, 2) as cart_to_purchase_pct
from agg;


# 3. 언제나 데이터를 검증해봐야 한다

## 3-1. 무결성 점검
select
	count(*) total_rows,
    count(distinct event_id) as dist_event_id,
    count(distinct user_id) as dist_user_id,
    count(distinct session_id) as dist_session_id
from shop_events;

select * from shop_events
where user_id = '108';
### -> 같은 유저 이지만, 이탈하거나 특정 시간이 지나 거나, 날짜가 바뀐다면, 새로운 세션이 부여된다!

## 3-2. 검증 쿼리
WITH sums AS (
  SELECT
    COUNT(*) AS total_rows,
    COUNT(DISTINCT event_id) AS distinct_event_ids,
    COUNT(DISTINCT user_id) AS distinct_user_ids,
    COUNT(DISTINCT session_id) AS distinct_session_ids
  FROM shop_events
),
dup_event AS (  -- event_id 전역 중복 여부
  SELECT COUNT(*) AS duplicate_event_id_rows
  FROM (
    SELECT event_id
    FROM shop_events
    GROUP BY event_id
    HAVING COUNT(*) > 1
  ) d
),
cross_session_user AS (  -- 한 세션에 서로 다른 user_id가 섞였는지(비정상)
  SELECT COUNT(*) AS sessions_cross_users
  FROM (
    SELECT session_id
    FROM shop_events
    GROUP BY session_id
    HAVING COUNT(DISTINCT user_id) > 1
  ) x
),
identical_rows AS (  -- 완전 동일 행(유저/세션/이벤트명/시간)이 중복으로 들어갔는지
  SELECT COUNT(*) AS fully_identical_row_groups
  FROM (
    SELECT user_id, session_id, event_name, event_time
    FROM shop_events
    GROUP BY user_id, session_id, event_name, event_time
    HAVING COUNT(*) > 1
  ) t
)
SELECT
  s.total_rows,
  s.distinct_event_ids,
  s.distinct_user_ids,
  s.distinct_session_ids,
  de.duplicate_event_id_rows,
  csu.sessions_cross_users,
  ir.fully_identical_row_groups
FROM sums AS s
JOIN dup_event AS de ON 1 = 1
JOIN cross_session_user AS csu ON 1 = 1
JOIN identical_rows AS ir ON 1 = 1;


# 4. 필수 과제
## session_id 즉, "각 세션들의 event_name 별 집계"가 아닌
##  event_id, user_id 도 추가하여 funnel을 만들고, 수치가 왜 다른지를 이해해보자

select * from shop_events;

## 4-1. event_id
with day_events as (
	select
		date(event_time) as event_date,
		sum(case when event_name = 'app_open' then 1 else 0 end) as count_open,
		sum(case when event_name = 'view_item' then 1 else 0 end) as count_view,
		sum(case when event_name = 'add_to_cart' then 1 else 0 end) as count_cart,
		sum(case when event_name = 'purchase' then 1 else 0 end) as count_purchase
	from shop_events
	group by date(event_time)
)
select
	event_date,
    round(count_view / nullif(count_open,0) * 100, 2) as open_to_view_pct,
	round(count_cart / nullif(count_view,0) * 100, 2) as view_to_cart_pct,		
    round(count_purchase / nullif(count_cart,0) * 100, 2) as cart_to_purchase_pct,
    round(count_purchase / nullif(count_open,0) * 100, 2) as open_to_purchase_pct
from day_events;



## 4-2. user_id
with session_events as (
	select
		session_id,
		any_value(user_id) as user_id,
		min(event_time) as session_start_ts,
		min(case when event_name = 'app_open' then event_time end) as t_open,
		min(case when event_name = 'view_item' then event_time end) as t_view,
		min(case when event_name = 'add_to_cart' then event_time end) as t_cart,
		min(case when event_name = 'purchase' then event_time end) as t_pay
	from shop_events
	group by session_id
),
session_flags as (
	select
		session_id,
        user_id,
        date(session_start_ts) as session_date,
        (t_open is not null) as has_open,
        (t_view is not null and t_open is not null and t_view >= t_open) as valid_view,
        (t_cart is not null and t_view is not null and t_view >= t_open and t_cart >= t_view) as valid_cart,
        (t_pay is not null and t_cart is not null and t_view is not null and t_view >= t_open and t_cart >= t_view and t_pay>= t_cart) as valid_pay
	from session_events
),
user_agg as (
	select
		session_date,
        count(*) as session_total,
        sum(has_open) as s_open,
        sum(valid_view) as s_view,
        sum(valid_cart) as s_cart,
        sum(valid_pay) as s_pay
	from session_flags
    group by session_date, user_id
)
select
	session_date,
    session_total,
    s_open, s_view, s_cart, s_pay,
    round(s_view / nullif(s_open,0) * 100, 2) as open_to_view_pct,
	round(s_cart / nullif(s_view,0) * 100, 2) as view_to_cart_pct,		
    round(s_pay / nullif(s_cart,0) * 100, 2) as cart_to_pay_pct,
    round(s_pay / nullif(s_open,0) * 100, 2) as open_to_pay_pct
from user_agg;



# 5. 복습 키워드
---------------------------------------------------------------------

-- 1️⃣ Session (세션)
--  - 사용자가 사이트나 앱을 방문해 머무르는 "하나의 연속된 이용 구간"
--  - 예: 앱 실행 → 상품 조회 → 장바구니 → 종료
--  - session_id 로 동일한 사용자의 여러 이벤트를 묶을 수 있음
--  - 세션 종료 기준은 보통 30분 이상 활동 없음(기본값, GA4 기준)

-- 예시:
-- SELECT session_id, COUNT(*) AS event_count
-- FROM shop_event
-- GROUP BY session_id;

---------------------------------------------------------------------

-- 2️⃣ GA4 Session (Google Analytics 4 세션 개념)
--  - GA4 에서는 session_start 이벤트 발생 시 새로운 세션이 생성됨
--  - session_start 이후 30분간 활동이 없으면 자동 종료
--  - user_pseudo_id 와 session_id 조합으로 고유 세션 구분
--  - 각 세션은 device, country 등 속성으로 분류 가능

---------------------------------------------------------------------

-- 3️⃣ 마케팅 대시보드 쿼리 (Marketing Dashboard Query)
--  - 주요 KPI(핵심 지표)를 SQL 로 추출하여 시각화 도구(GA4, Looker, Metabase 등)에 연결
--  - 자주 쓰는 분석 지표:
--    ① DAU (Daily Active Users)
--    ② 세션당 이벤트 수
--    ③ 유입 채널별 전환율
--    ④ 국가별 트래픽 비중

-- 예시:
-- SELECT DATE(event_time) AS date,
--        COUNT(DISTINCT user_id) AS dau,
--        COUNT(DISTINCT session_id) AS sessions
-- FROM shop_event
-- GROUP BY DATE(event_time);

---------------------------------------------------------------------

-- 4️⃣ Funnel (퍼널)
--  - 사용자의 행동 단계를 순서대로 나열한 흐름 (예: view → cart → purchase)
--  - 각 단계마다 사용자가 이탈하는 비율을 추적할 수 있음
--  - ex) 100명 view → 60명 cart → 30명 purchase → 전환율 30%

---------------------------------------------------------------------

-- 5️⃣ Closed Funnel (폐쇄형 퍼널)
--  - 정해진 순서대로 행동을 **모두 완료한 사용자만** 계산
--  - 예: view → cart → purchase 순서로 반드시 진행한 사용자만 포함
--  - 순서가 틀리거나 중간에 건너뛴 사용자는 제외됨

---------------------------------------------------------------------

-- 6️⃣ 퍼널 분석 (Funnel Analysis)
--  - 각 단계별로 사용자가 얼마나 남고, 얼마나 이탈하는지를 분석하는 기법
--  - 전환율(Conversion Rate) = (다음 단계 사용자 / 이전 단계 사용자) × 100

-- 예시)
-- view_item → add_to_cart → purchase
-- 100명 → 60명 → 30명  → 전환율 30%

---------------------------------------------------------------------

-- 7️⃣ 퍼널 쿼리 (Funnel Query)
--  - 퍼널 단계를 SQL 로 구현한 것 (WITH 절로 단계별 사용자 추출)
--  - 보통 다음 단계의 user_id 를 이전 단계 user_id 와 JOIN 하여 계산