1. 들어가며

1.1. 상황

공헌이익은 매출에서 변동비를 뺀 값으로, 스타트업에서 주요하게 보는 재무 지표입니다.

기존 사업부서에서 관리하던 대시보드는 매출을 모두 엑셀에 수기로 입력하고, 제품원가등의 값들이 임의의 상수로 대체하여 운영되고 있었기 때문에 휴먼에러가 빈번하게 발생하고, 값의 정확성 또한 신뢰하기 어렵다는 문제가 있었습니다.

본 [공헌이익] 시리즈는 공헌이익을 데일리로 정확하게 트래킹 하기 위해 기획부터 파이프라인 구축, 쿼리작성, 대시보딩까지 혼자 1 cycle을 구현한 경험을 기록합니다.

1.2. 오늘의 주제

보통의 경우
1) 제품원가는 SKU단위로 관리
2) 광고비는 캠페인 코드 단위로 집계 되기 때문에
해당 데이터들을 어디서, 어떻게 수집하고 어떻게 품목 단위로 매칭할 것인지
고민이 많았는데요,

오늘은 두가지 비용 중
일별, 품목단위로 로 1) 제품원가를 집계하는 방법에 대해 정리하도록 하겠습니다.

1.3. 예상독자

• 이지어드민(링크) 데이터를 활용하여 품목 단위의 매출을 SKU단위로 분해하는 것에 관심 있는 분
• 공헌이익을 데일리로 트래킹하는 방법에 관심있는 분

1.3. 용어 Align

▶️ 품목 = 일반적으로 소비자가 인식하게 되는 상품명

• (실버925) 온리 펄 박스 체인 목걸이 NZ2165과 같이 소비자가 인식하는 상품 단위이자, 카페24/네이버스마트스토어 등에서 매출로 인식하는 단위

▶️ SKU = 품목을 구성하는 가장 작은 단위

(실버925) 온리 펄 박스 체인 목걸이 NZ2165 목걸이를 제작하기 위해

• 진주비즈
• 실버체인
• 지퍼백포장지(소)

등과 같이 구성품이 필요하고,이와 같은 최소 단위를 SKU라고 칭함

2. 프로세스

프로세스 요약
(1) (매출테이블) 각 판매채널별 품목단위(품목코드)로 수집되는 매출 수집
(2) (이지어드민테이블) 매출테이블 품목과 동일 품목임을 인식할 수 있도록 전처리 후 SKU단위 분해 > (제품원가테이블) SKU 코드로 원가 매칭
(3) (최종) 일별/품목단위로 제품원가 sum

2.1 매출

품목, 품목코드별 매출을 다운 받아 그대로 구글스프레드시트에 붙여넣기 > GCP와 연동되어 테이블에 적재

2.2 이지어드민 데이터를 활용하여 SKU매칭

이미지와 같이

• 품목 : 봄맞이 악세사리 기획세트에는 실반지10개와 함께 구매할 수 있는 옵션이 있고
• SKU : 따라서 각각 귀걸이, 실반지로 나뉘며,
• 수량 : 귀걸이 1개, 실반지10개로 구성됨을

알 수 있습니다.

1️⃣ 이지어드민 전처리

기획세트(귀걸이+실반지)에 포함되었던 실반지는 판매처~판매처옵션 Column이 NULL로 수집되기 때문에 실반지도 봄맞이 악세사리 기획세트로 인식할 수 있도록 전처리가 필요 합니다.

a. 각 행에 순차적인 번호(rn)를 부여하고
b. NULL이 아닌 행을 기준으로 group_id를 생성하고
c. FIRST_VALUE() 윈도우 함수를 사용하여 각 group_id 내에서 첫 번째 값을 가져오는 방식

으로 해결하였습니다.

WITH row_num AS 
( SELECT row_number() OVER () AS rn
         , * 
  FROM your_table )
  , temp 
  AS 
( 
    SELECT SUM(CASE WHEN chnl_nm IS NOT NULL THEN 1 ELSE 0 END) OVER (order by rn ) AS group_id 
    , * FROM row_num ) 
    SELECT rn
         , group_id
         , FIRST_VALUE(chnl_nm) OVER (PARTITION BY group_id ORDER BY rn) as chnl_nm 
         , FIRST_VALUE(chnl_prod_cd) OVER (PARTITION BY group_id ORDER BY rn) as chnl_prod_cd 
         , FIRST_VALUE(chnl_prod_nm) OVER (PARTITION BY group_id ORDER BY rn) as chnl_prod_nm 
         , FIRST_VALUE(chnl_prod_opt) OVER (PARTITION BY group_id ORDER BY rn) as chnl_prod_opt 
         , easy_code
         , sku_nm
         , sku_cnt 
         FROM temp 

2️⃣ 매출 테이블과 조인하기

1️⃣ 단계에서 전처리한 결과 판매처~판매처옵션까지 동일 품목임을 인식할 수 있게 되어, 이지어드민 매칭 전처리 테이블과 매출 테이블과 조인

  FROM 매출테이블
  LEFT JOIN 이지어드민 전처리 AS easy
           ON (prod_cd = easy.chnl_prod_cd) 
           AND chnl_nm = 'your_sales_source'
 LEFT JOIN 원가관리테이블 AS sku
           ON sku.sku_cd = easy.sku_cd 
           AND sku.base_dt = FORMAT_DATE('%Y%m', 매출테이블.base_dt) 
           AND sku.sku_category = '제품'

참고) 판매채널마다 이지어드민에서 상품코드로 인식하는 방식이 상이하며, 특히 카페24의 경우 단순히 상품코드만으로 매칭하면 안되고 상품명+옵션코드를 모두 활용 하여 매칭 필요

3️⃣ 집계

품목의 제품원가 =

• 매출테이블의 품목 판매수량 *
• 이지어드민 테이블 sku의 갯수 *
• 원가 테이블 sku의 원가

, sku_cost_sales_cnt AS (
  SELECT 
      *,
      sku_cost * sku_cnt * sales_cnt AS total_cost
  FROM sku_join
),

즉, 봄맞이 악세사리 기획세트가 10개 팔렸고, 2만원 매출 발생했다면

• 매출 테이블의 품목 판매수량 : 10개
• 이지어드민 : 귀걸이 1개, 실반지10개
• 원가 : 귀걸이 100원, 실반지 10원
• 봄맞이 악세사리 기획세트의 원가 = 1,000 + 1,000 = 2,000원

2.3 품목단위 제품원가

봄맞이 악세사리 기획세트 매출 (2만원) - 봄맞이 악세사리 기획세트 원가 (2천원) = 18,000원

base_sales AS (
  SELECT 
      sales_dt,
      prod_cd,
      prod_nm,
      SUM(sales_cnt) AS sales_cnt,
      SUM(sales_amt) AS sales_amt,
      SUM(sum_cost) AS sum_cost
  FROM (
      SELECT 
          cs.cf_rn,
          PARSE_DATE('%Y%m%d', cs.sales_base_dt) AS sales_dt,
          cs.prod_cd,
          cs.prod_nm,
          prod_opt_nm,
          sales_cnt,
          sales_amt,
          sum_cost
      FROM calculate_sales AS cs
      INNER JOIN sum_cost AS sc 
          ON cs.cf_rn = sc.cf_rn 
          AND cs.sales_base_dt = sc.sales_base_dt
  )
  GROUP BY 1,2,3
),

3. 마무리

이렇게 품목단위로 제품원가를 집계할 수 있었는데요, 다음글은 품목단위로 광고비를 집계하여 일별, 품목별 공헌이익을 집계하는 프로세스로 이어집니다🙌🏻

SELECT sales_dt,
       revenue,
       revenue - sum_cost - ad_spend AS crtb_margin -- 공헌이익
FROM cf_final