[SQL 분석][Ch6] SCM 대시보드 (2)

날짜, 요일별 판매 트렌드 모니터링 (고객 판매 데이터)

[결과]

데이터셋 살펴보기

• 판매 데이터를 일자별로 보면, 1월31일에서 2월1일로 바뀌면서 판매개수, 판매금액 합계값, 브랜드 개수가 확 줄어드는걸 볼 수 있음
• 좀 더 자세히 보기 위해 행렬로 일자별, store별로 판매개수 확인
  → 2월로 바뀌면서 일부 가게 데이터만 존재
  → 데이터 누락인지 실제로 판매가 없었는지는 알 수 없음
  → 데이터가 존재하는 가게만 필터링하여 진행
  필터링 방법 (2가지)
  • 슬라이서 사용 (다만, 다른 사용자가 설정값을 바꿀 수 있음)
  • 필터 적용
    - 이 페이지의 필터에 Store - 기본 필터링 에서 보려는 store만 선택
    - 필터 잠그기 (보는 사용자가 따로 조작 불가능)
    - 필터 숨기기 (작성자만 보이고 보는 사용자에게는 보이지 않음)

    

분해트리

• 계층 구조 데이터를 시각적으로 보려할 때 유용할 수 있겠다
• 날짜별로 판매량이 어떻게 되는지 표현
  
• 분석 : 분석할 값 / 설명 기준 : 날짜 (데이터가 1,2월이라 분기는 의미없으므로 제외)
• 전체 판매량 합계에서 높은 값/낮은 값 어떤 기준으로 트리를 펼쳐나갈 것인지 선택 가능
  • 높은 값으로 선택 > 연도는 2016년 하나뿐이라 나눠질 트리가 없음
  • 이후 높은 값으로 계속해서 드릴다운하면 월, 일까지 드릴다운 됨
    → 1월에는 29일, 2월에는 12일이 판매량이 가장 많음
  • 막대 옵션 중 크기 조정에서
    • 최대 수준 : 일자별 판매량을 기준으로 막대그래프 색이 채워짐
    • 최상위 노드 : 전체 판매량을 기준으로 막대그래프 색이 채워짐

판매가격과 매입가격

▶ 매입가격과 대비하여 팔고남은 이익은 얼마인지

Inventory 테이블의 PurchasePrice과 Sales 테이블의 SalesPrice와 병합

방법1. PurchasePrice가 들어있는 새로운 Dimension Table을 만들어서 Sales Table과 join
방법2. Sales Table에 PurchasePrice라는 새로운 컬럼을 추가
Join key : InventoryId

방법1을 먼저 해보자
Inventory 테이블의 InventoryId와 Price 컬럼 활용

• 데이터 변환 이동 - Inventory 테이블 우클릭 - 복제

  • 복제 : 변환이 일어난 단계가 모두 유지된채로 테이블 복사
  • 참조 : 새 원본 - 빈 쿼리 선택하여 =Inventory 입력한 것과 동일한 결과 (변환없음)

• key값으로 사용할 InventoryId컬럼에는 city값이 비어있었던 store 46번의 city는 들어가있지 않아서 Store, City, Brand를 _로 연결하여 InventoryId 설정

  • 컬럼을 합치기 위해서는 세 컬럼의 타입이 동일해야 하기 때문에 Store와 Brand 컬럼을 텍스트 타입으로 변경
  • 사용할 컬럼(Store, City, Brand, Price)만 선택 후 컬럼 우클릭 - 다른 열 제거

• 열 추가 - 사용자 지정 열 (생성된 InventoryId 컬럼도 텍스트 타입으로 변경)

  

• 테이블명은 D_Inventory_Price

• 모델 보기 이동 - D_Inventory_Price의 InventoryId와 Sales의 InventoryId 연결 (경고 발생)

  

  • InventoryId별 Price가 하나씩 존재해야 하는데 그렇지 않음
  • BegInv와 EndInv를 병합한 것이기 때문에 같은 ID가 2개가 있을 수 있음
  • InventoryId별로 한 개의 price만을 두기 위해 보수적인 기준으로 매입 가격이 더 비싼 것을 남기자
  • 데이터 변환 이동 - D_Inventory_Price 테이블 InventoryId컬럼 선택 후 변환 탭 - 그룹화 선택
    (SQL에서 groupby한다고 생각하면 된다)
    

• 모델 보기 에서 InventoryId를 다시 연결해보면 문제없이 잘 진행된다

  

• ID별로 판매가격과 매입가격을 확인하는 테이블 생성

  • 판매 가격이 매입가격보다 작은 경우도 有 (역마진)
  • 우려했던 사항 有
    → 재고 데이터는 1월 1일과 12월 31일 날짜만 존재하는데, 12월 31일 전에 재고가 없어진 경우 매입단가가 없을 수 있음
  • 위와 같은 경우의 공란은 평균값이나 다른 값을 참조하여 채울 수 있음
    → 우리는 이익이 0이라 가정하고 공란을 판매가격과 동일한 값으로 보간
    

• 데이터 변환 이동 - (방법2 사용) Sales 테이블에 left join 방법 사용하여 만들어둔 D_Inventory_Price의 PurchasePrice 컬럼 추가

  • Sales 테이블 선택 후 홈탭 - 결합 메뉴 - 쿼리 병합 선택
  • 병합하려는 테이블 선택 후 두 테이블에서 기준 컬럼(InventoryId) 선택
    (만약 key값이 여러 개면 Ctrl 누르면서 다중선택 가능)
  • 가장 오른쪽에 D_Inventory_Price 컬럼 생성되는데, 필요한 건 PurchsePrice 컬럼이므로 해당 컬럼만 선택
    ('원래 열 이름을 접두사로 사용' 하면 컬럼명이 D_Inventory_Price.PurchsePrice)
    

• 병합한 Sales 테이블에서 PurchasePrice 공란 값을 채우자

  • 새 열 추가
    

• Sales 테이블에서 새 측정값 추가

  • 판매이익(Profit)과 매입가 대비 판매이익율(Profit%) > 역마진인 경우도 잘 보인다

    

Calendar Table

▶ 요일별 판매 트렌드 시계열 분석을 위해

테이블 보기 이동 - 새 테이블

• 시작 날짜와 끝 날짜 넣으면 그 기간의 날짜값이 생김
  
• 새 열 추가 (year, month, day)
  → Year = D_Calendar[Date].[연도] (= YEAR(D_Calendar[Date]))
  → Weeknum = WEEKDAY(D_Calendar[Date], 1) --일요일부터 1 (요일을 숫자로 출력)
  → 요일 이름으로 나타내기 위해 if 중첩문 사용
  
• 모델 보기 이동 - Sales 테이블의 SalesDate와 D_Calendar의 Date 컬럼 연결
  ((D_Calendar)1 : 다(Sales) 관계 생성)
• 연결 후 분해트리는 줄어들었는데 어차피 연결되어있으므로 설명 기준을 D_Calendar의 Date로 바꾸면 다시 드릴다운 가능
  
  
  

요일별 판매 트렌드

• D_Calendar의 Date를 X축, Sales의 SalesQuantity를 y축으로 하는 꺾은선형 그래프 생성
  
  • 요일별로 패턴이 나타나는듯 함
    • 목요일에 증가하여 금요일에 정점을 찍고 (평일 중에는) 하락하는 추세가 반복하여 보임
      
      
• 일별 평균 판매량, 판매금액 측정값 생성 (Calc 테이블)
  • S1_Daily AVG Sales Qty = DIVIDE(sum(Sales[SalesQuantity]), DISTINCTCOUNT(Sales[SalesDate]))
    (D_Calendar[Date] 사용해도 됨)
  • S2_Daily AVG Sales Amount = DIVIDE(sum(Sales[SalesDollars]), DISTINCTCOUNT(Sales[SalesDate]))
    
    
• 테이블로 확인해보면, SalesQuantity를 날짜별로 합산한 것과 일별 평균 측정값은 합계에서 차이를 보임
  • 일자별로 판매량 모두 합산 vs 모두 합산한 판매량 / 날짜 수
    
• 일 평균 판매 단가도 추가
  • S3_Daily AVG Sales Price = DIVIDE([S2_Daily AVG Sales Amount], [S1_Daily AVG Sales Qty])
    
    
• 요일별 트렌드를 확인하는 (테이블 &) 그래프 생성
  

계기 차트

타겟 달성률을 모니터링할 때 효율적인 시각적 개체

일 평균 판매량을 시각화 해보자

• 임의의 목표값 10000 생성 (Calc 테이블에 새 측정값 추가 > Sales_Aim = 10000)

  

KPI 생성

KPI(핵심 성과 지표)

• 측정 가능한 목표에 대해 만든 진행률의 정도를 알리는 시각적 신호 > 진행률 측정 시 유용
• 이전 트렌드까지 보여주면서 가장 최근 실적은 어떻고 그 실적이 목표 대비하여 얼만큼인지

계기 차트와의 차이점

• 계기 차트 : 누적된 데이터가 목표를 얼마나 달성했는지 보는데 유용
• KPI : 과거 추세가 어떠했고 가장 최근 달성 정도는 이렇다 (가장 최근 현황) / 목표 대비 얼마나 초과/미달 달성했는지 보여주는 시각화

KPI 목표값은

• 앞서 요일별로 추세가 다름을 확인하였으므로, 모두 동일한 일평균을 목표량으로 잡게 되면 특정 요일에 상대적으로 목표 미달이 될 수 있음
  ▶ 기존 판매 데이터(매출 당일 이전, 당일 제외)의 요일별 평균을 목표값으로 설정
  → 해당 요일에 요일 목표치를 초과/미달했는지 확인 가능

Sales 테이블에 새 측정값(요일별 목표치) 추가

• AVERAGEX : AVERAGEX(<table>, <expresion>)
  • VALUES(D_Calendar[Date] : 각 날짜별로 [S1_Daily AVG Sales Qty] 계산 > "일평균 판매량"
• ALL : calendar 내에 어떤 date도 신경쓰지 않겠다 (따로 필터를 걸어줄 예정)
  • SalesDate와 Date는 join되어 있기 때문에 모두 써준 것
• FILTER에서 max(Sales[SalesDate])는 KPI에서 표시될 날짜를 의미

• 당일이 포함되지 않는다는 것을 확인하기 위해 슬라이서로 날짜를 조정 (26일은 금요일)
  • 22일 월요일까지 계산된 평균 판매량이 29일 목표 판매값임을 확인 가능