[ZB] SQL분석 - chap2. 이커머스 사업현황파악

결과 대시보드 미리보기

chap2 세일즈 대시보드 Tableau 링크

< 이커머스 주문 데이터 분석과 사업 현황 >

• 진행방식
  • Google Big Query
  • 태블로 / 루커스튜디오
• Data set : kaggle ( olist )

• 측정되어야 하는 비즈니스 ( 지표의 중요성 )
  • 구체적인 목표 설정
  • 성공 여부 판단의 근거
  • 우선순위 설정 및 개선
• 접근할 수 있는 지표로 쪼개자 ! ( 큰 지표 → 작은 지표 )

**데이터셋

Brazilian E Commerce Public Dataset by Olist**

• 제품/주문/고객/주문상품/카테고리명 번역

• 약 10만건의 실제 브라질 이커머스 주문 데이터

• 지리정보, 리뷰 등 다양한 데이터 포함

• 기간 : 2016 ~ 2018

• 샘플링, 익명화 완료

• 여러가지 데이터셋으로 쪼개어져 있음

  • 어떤 데이터셋을 무엇을 기준으로 연결할지 생각

• 객단가

  • ARPU (Average Revenue Per User) = 매출 / 사용자 수
  • ARPPU (Average Revenue Per Paid User) = 매출 / 구매자 수

Google Big Query

• Data Warehouse?

  서비스와 연결되어 있는 데이터로부터 분석을 위해 따로 모아둔 데이터 창고 같은 곳

  • amazon redshift
  • google big query

• 기본적으로 SQL 문법 사용

• 프로젝트 > 데이터 세트 > 테이블

• 탭 구분 기능 활용 ~

• 지표

  매출

  • 들어온 주문 수
    • 고객 수
    • 주문 빈도
  • 건당 주문 금액
    • 판매 품목 수
    • 물품 평균 가격
  • 고객번호 : 주문,품목마다 생성되는 것. 고객에게 부여되는 고유한 번호가 X
  • 품목수 : 동일 주문 내에서 제품을 1개 셀 때마다 증가. 더하면 안됨 !
  • 건당 주문 금액 = 매출 / 주문 건수

• with 절

  • 임시테이블 생성 WITH tb명 AS (table만드는 쿼리);
    
  • comma 기준으로 여러 개 생성 가능 WITH tb명_1 AS (), tb명_2 AS (), ... ;
    

• 날짜 다루기

  유형
  DATE	2024-10-14
  DATETIME	2024-10-14T11:54:50
  TIMESTAMP	2024-10-14 11:54:50 UTC	타임존 정보 포함 - 한국:UTC+9
  TIME	11:54:50

  • 각 숫자 (yyyy, mm, dd 등)들을 파라미터로 적음
  • DATE ( TIMESTAMP “2024-10-14 11:54:50+08", "America/Los_Angeles" ) - 지역 반영해서 DATE 형식으로 변환
  • 추출 EXTRACT ('추출 부분' FROM '날짜 컬럼') - 원하는 정보 추출 EXTRACT 결과 컬럼 datatype - 정수

    추출 부분	의미
    YEAR	연도
    MONTH	월
    DAY	일
    DAYOFWEEK	요일(0:일 ~ 6:토)
    WEEK	주
    WEEK(MONDAY)	주(월요일을 주 시작 기준 )
    QUARTER	분기

    DATE_TRUNC ( '날짜', '남기는 부분' ) - 어디까지 남길지 TRUNC 결과 컬럼 datatype - date

    추출 부분	의미	결과(날짜)
    YEAR	연도	해당 연도 첫 날
    MONTH	월	해당 연도, 월의 첫 날
    DAY	일	해당 날짜 0시 0분
    WEEK	주	해당 주차 첫 날(일)
    WEEK(MONDAY)	주(월요일을 주 시작 기준 )	해당 주차 첫 날(월)
    QUARTER	분기	해당 분기 첫 날
    HOUR, MINUTE, SECOND

• 주석 처리 - 주석처리 하고 싶은 부분 block지정 후 “ctrl + /”

• 저장방식 - 열 기반

• Big Query 함수

  • SELECT * EXCEPT ('제외할 컬럼')
  • SELECT * REPLACE (’대체할 값’ AS ‘컬럼명’)
  • CAST ('값' AS '데이터타입') SAFE_CAST ('값' AS '데이터타입') = error을 발생시키는 상황에서 null 반환 = 오류를 발생시키지 X
    • CAST (’abc’ AS INT64) - error
    • SAFE_CAST (’abc’ AS INT64) - null
  • SAFE_ADD (X, Y) = X+ Y SAFE_DIVIDE (X, Y) = X / Y
  • 컬럼 → A, B, C, D, E WHERE '컬럼' IN (A, B, C) → A, B, C WHERE '컬럼' NOT IN (A, B, C) → D,E
    

• 순위 ROW_NUMBER() OVER ('컬럼' PARTITION BY '기준' ORDER BY '컬럼' ASC|DESC) - 중복없는 순위 RANK() OVER ('컬럼' PARTITION BY '기준' ORDER BY '컬럼' ASC|DESC) - 동점일 때 중복 순위, 이후 그 만큼 건너 뛴 등수 DENSE_RANK() OVER ('컬럼' PARTITION BY '기준' ORDER BY '컬럼' ASC|DESC) - 동점일 때 중복 순위, 이후 순위 연속적으로
  

Tableau

<분석에 쓸 수 있는 기능>

• 필드 더블클릭 or ▽-선반에서 편집 : 필드 함수 보기 및 편집 가능
• 누적합 RUNNING_SUM()
• 차이/비율차이
  • 차이 = 현재행 - 직전행 ZN(SUM([])) - LOOKUP(ZN(SUM([])), -1) -ZN = null인 경우 0 반환 -LOOKUP = 지정된 offset만큼 떨어진 행 값 반환
    
  • 비율차이 = (현재행 - 직전행) / 직전행 절댓값 ZN(SUM([])) - LOOKUP(ZN(SUM([])), -1) / ABS(LOOKUP(ZN(SUM([])), -1))
    
• 이동평균 : 일정 기간 동안의 평균 WINDOW_AVG(SUM([]), -2, 0) - 이전2개+현재 = 총 3개 구간의 평균
  
• YTD= Year To Date
  • 연도마다 0에서 계산 다시
  • YTD 총계 : 누적합 RUNNING_SUM(SUM([]))
    
  • YTD 성장률 : 올해 현재까지의 누적값 / 과거 같은 기간의 누적값
• 전년대비성장률 = 동기간에 대해서 전년과 비하여 얼마나 성장하였는지
  • YoY : Year of Year 전년 비교
  • MoM : Month of Month 전월 비교
  • WoW : Week of Week 전주 비교
• CAGR 통합성장률 = 연평균 성장률 = 연평균 복합성장률 = Compound Annual Growth Rate : 해당 기간동안 평균적으로 얼마나 성장했는지
  • 기하평균 적용 산술평균 - 적용했을 때 값의 왜곡이 있을 수 있음
    
  • $CAGR=(\frac{마지막 연도 금액}{시작 연도 금액})^{\frac{1}{기간 수(연)}} -1$ 중간 연도 금액 X
    

• 숫자 서식
  • +0.00%;-0.00% : 앞에 + or - 붙일 때
    
• 매개변수 : 상수값으로 대체할 수 있는 숫자, 날짜, 문자열 등 = 정해진 값을 동적으로, 상호작용 할 수 있게 해주는 변수
  

• 시각화 팁
  • IF / IIF IIF(조건, 부합할 경우 값, 나머지 경우 값) - 경우 1가지 일 때
    
  • 매개변수
  • First / Last FIRST()=0 : 처음으로부터 0번째 (=가장 처음) LAST()=0
    

• 세부 수준 계산식
  • 예시 { FIXED DATETRUNC('year', [Ord Date] ) : SUM([Ord Amt]) }
    

Looker Studio

• Big Query에서 바로 데이터 가져오기 데이터 추가 - Big Query - () - 맞춤 검색어 - 맞춤 쿼리 입력 - 추가
  
• 매개변수
• 비교기간
  • 이전기간 - 하루 전 날짜와 비교
  • 이전연도 - 1년전 날짜와 비교
• 기간 매개변수