기간 별 매출데이터로 연/월순환매출 계산하기
태블로 프렙을 이용한 구독 모델 매출 계산법 /w Tableau Wiki
※ 해당 글은 태블로위키 블로그의 콜라보레이터로서 작성한 글입니다. 원문은 이 곳에서 보실 수 있습니다.
안녕하세요. 태블로 위키에 콜라보레이터로 참여하게 된 포커스미디어 데이터전략팀의 강슬기입니다. 현업에서 실제로 난관에 부딪혔지만, 구글링으로도 찾을 수 없었던, 그리고 너무나도 필요했던 기간 단위 매출 데이터로 연간/월별 매출 구하기라는 주제로 처음 인사드리게 되었습니다.
저의 경우 광고 상품을 판매하고 있는 회사에서 세일즈 데이터를 이용하여 매출 대시보드를 구축하고 있기에, 시작일 / 종료일 / 매출 금액으로 이루어진 데이터로 어떻게 연간/월별 매출을 계산할 수 있을지 고민이 많았는데요. 실제로 요즘 구독 모델을 가진 플랫폼이 많아지고 있는 추세라 같은 고민을 가진 담당자분들이 많을 것이라 생각합니다.
구독 경제에서 순환 매출은 구독 비즈니스의 재무 상태를 확인할 때 매우 중요한 지표라고 하는데요. 만약 한 명의 고객이 3년에 걸친 장기 구독을 했다면, 해당 연도의 연간 계약 금액 (Annual Contract Value)에는 3년 치 금액이 모두 포함되지만, 연순환매출 (Annual Recurring Revenue, ARR)에는 3년의 기간 중 해당 연도에 해당하는 금액만 포함되는 것이지요!
보통의 매출 데이터는 아래와 같이 세일즈 발생 건 별로 날짜가 명확히 지정되어 있어 일별 / 주간 / 월별 / 연간 세일즈 합계를 집계하는 데에 어려움이 없는데요.
출처: https://www.kaggle.com/kirankumarmanda/supermarket-sales-analysis이렇게 첨부된 샘플 데이터와 같이 하나의 행에 시작일 / 종료일 / 매출액으로 데이터가 구성된 경우, 특정 연간 / 월별 데이터를 집계하는 것이 막막해질 수 있습니다. 시작일~종료일 사이의 기간이 여러 달에 걸쳐있거나 여러 해에 걸쳐 있어 특정 년/월에 해당하는 매출, 즉 연순환매출과 월순환매출 계산이 필요하기 때문입니다.
데이터 비주얼라이제이션 구상하기
저는 태블로 프렙과 태블로를 이용하여 문제를 해결하기 위해 제가 원하는 차트의 모습부터 상상해 보았는데요. 보통 아래와 같이 월별 매출 추이를 전년 동월과 가로 방향으로 보며 비교하고, 월별로 연간 데이터가 함께 보는 형태로 연간 / 월별 매출 추이를 비교해 보실 것 같습니다.
연산에 적합한 데이터 구조 마련하기
위와 같은 비주얼라이제이션을 구현하려면, 데이터 구조 상 하나의 계약 건 당 년/월별로 행이 복제되어 각 년/월 별로 분리된 월별 매출 값이 있어야 할 것으로 보이는데요. 이 행 복제를 태블로 프렙의 조인 기능을 이용하여 진행해보려고 합니다. 그럼 매출 데이터와 조인할 ‘년/월’ 데이터가 있어야겠죠? 하기 파일과 같이 각 년/월이 데이터 상에서 ‘날짜’ 형태를 가져갈 수 있도록, 각 월의 첫째 날로 기입해보았습니다.
태블로 프렙의 조인 기능을 이용하여 위와 같은 행 복제 작업을 진행해보려고 하는데요. 매출 파일에 조인절의 역할을 할 열을 임의로 추가하여 첨부된 ‘년월’파일과의 조인을 하는 작업입니다.
위 작업을 태블로 프렙에서 보여드릴텐데요. ① Period Sales Data.csv와 년월.xlsx이 두 개의 파일을 연결한 후 ② Personal Sales Data에 정리 단계를 추가하고 ③ L. Messi 씨의 계약 1건에 오른쪽 버튼 클릭 > ‘이 항목만 유지’를 눌러 하나의 계약건만 필터링 하였습니다.
이 결과, Messi의 기간별 매출 데이터 한 행만 남게 되었습니다.
이 Messi 데이터가 2년 간의 년월 별로 24행 복제되어야 하기 때문에 년월, Period Sales Data 양쪽 데이터에 임의로 ‘조인절’을 추가해 줍니다. (단계 추가 > 정리 > 계산된 필드 만들기) 데이터는 일괄 ‘-‘이므로 수식 칸에 ‘-‘만 간단히 넣어주시면 됩니다.
양쪽 데이터에 ① 조인절이 추가된 것이 확인되면 ② 하나의 테이블을 다른 하나의 테이블로 드래그하여 조인해줍니다.
① 별다른 설정을 하지 않아도 자동으로 양쪽의 조인절을 기준으로 조인이 진행된 모습입니다! ② 기존에 이름 / 계약시작일 / 계약종료일 / 매출만 있던 데이터에 ‘년월’ 열이 추가되었습니다.
③ 기존 매출 파일에 1행이 있었는데 각 행이 2년의 월 수대로 24번 복제된 결과 총 24행되었네요! ④ 역할을 다 한 ‘조인절’ 2개 필드는 제거해주셔도 됩니다.
데이터로 그 결과를 볼까요? 이렇게 하나의 매출 데이터가 년월 별로 24번 복제된 모습입니다.
이제 데이터의 구조가 갖춰졌으니 ‘년월’ 별 매출 금액을 계산하는 연산 과정을 적용할 차례입니다. Messi의 계약 건만 필터링 하는 단계는 이해를 돕기 위한 작업이었으므로 오른쪽 버튼을 눌러 제거 합니다.
전체 세일즈 데이터를 기준으로 보면 283개의 세일즈 데이터가 24번씩 복제되어 총 6,792행이 된 것을 확인하실 수 있습니다. 이 행 복제가 모든 세일즈 데이터에 적용될 경우, 데이터 행 수가 많이 늘어나는 것이 우려가 되실 수 있는데요. 이후 정제 과정에서 대폭 줄일 수 있으니 계속 지켜봐 주세요!
년월 별 매출 계산식 구상하기
계약 건 별로 년월 별 매출은 하기 계산식으로 연동이 가능합니다.
일별 매출 * 매출의 기간 중 해당 년 월에 해당하는 일수
예를 들어 2020년 5월 21일~2021년 7월 13일 총 419일 간 매출이 848,814원인 계약의 2020년 5월 매출액을 구하고 싶다면, 하기와 같은 과정을 거치면 됩니다.
(A) 일별 매출 : 848,814 ÷ 419 = 2,026원
(B) 2020년 5월 21일~2021년 7월 13일 중, 2020년 5월에 해당하는 일수 : 21일
2020년 5월의 매출 : (A) 2,026 * (B) 21 = 42,546
그럼 태블로 프렙에서 (A) 일별 매출 / (B) 각 년월 별 계약에 해당하는 일수 / 년월 별 매출 필드를 차례대로 생성해보겠습니다.
(A) 일별 매출
역시 조인된 테이블에서 계산된 필드 만들기 기능을 이용하여 ‘일별 매출’ 필드를 추가해줍니다. 매출액을 나눠주는 일 수는 시작일, 종료일을 모두 포함해서 세야 하기 때문에 ‘+1’을 꼭 더해야 하는 점 유의해야 합니다.
[매출]/([계약 종료일]-[계약 시작일]+1)
(B) 각 년월 별 계약에 해당하는 일 수
각 년월 별로 계약에 해당하는 일 수의 경우, 경우의 수와 계산이 좀 더 복잡해지는데요. 제가 원하는 결과는 하기 년월 별로 계약 시작일~계약종료일 사이에 해당하는 일 수가 채워진 테이블입니다. 계약 시작일이 20년 5월 21일, 종료일이 21년 7월 13일이므로 그 외 월에는 숫자 0이 채워져야겠지요?
필드 계산을 위한 수식을 다양한 경우의 수에 따라 작성해볼 수 있습니다. 계약시작일~종료일이 각 년월에 어떻게 걸쳐 있냐에 따라 다른 수식이 적용되는데요. 도식화를 해보면 하기와 같습니다.
여기서 DATEDIFF와 DATEADD라는 함수를 사용하였는데요.
두 날짜의 사이의 일 수를 구할 때 DATEDIFF를,
DATEDIFF(날짜_부분,시작_날짜,끝_날짜) : 두 날짜 사이의 차이를 반환합니다.
예: DATEDIFF(‘day’, #2004-03-01#, #2004-03-05#)=4
특정 월의 말일을 구하기 위해 다음 달의 첫날에서 하루를 빼는 과정에서 DATEADD를 사용하였습니다.
DATEADD(날짜_부분,간격,날짜) : 지정한 날짜에 간격을 추가하여 새 날짜를 반환합니다.
예: DATEADD(‘month’,3, #2004-04-15#)=2004-07-15
월 별 첫날은 일괄 01일로 지정이 가능하지만, 말일을 계산하는 것에는 특정 수식이 필요합니다. 매 달 28일~31일 범위로 일 수가 다르기 때문에 일괄로 말일을 계산할 수 없으므로 다음 달 첫날의 바로 전날을 계산하는 방식으로 해당 월의 말일을 지정합니다.
DATEADD('month',1,[년월])-1
결과적으로 전체 경우의 수에 따라 년월 별 해당일 수를 구하는 IF구문은 하기와 같습니다.
IF [계약 시작일] < [년월]
AND [계약 종료일] < [년월]
THEN 0
ELSEIF [계약 시작일] > DATEADD('month',1,[년월])-1
AND [계약 종료일] > DATEADD('month',1,[년월])-1
THEN 0
ELSEIF [계약 시작일] <= [년월]
AND [계약 종료일] >= DATEADD('month',1,[년월])-1
THEN (DATEDIFF('day',[년월],DATEADD('month',1,[년월])-1)+1)
ELSEIF [계약 시작일] > [년월]
AND [계약 종료일] < DATEADD('month',1,[년월])-1
THEN DATEDIFF('day',[계약 시작일],[계약 종료일])+1
ELSEIF [계약 시작일] <= [년월]
AND [계약 종료일] < DATEADD('month',1,[년월])-1
THEN DATEDIFF('day',[년월],[계약 종료일])+1
ELSEIF [계약 시작일] > [년월]
AND [계약 종료일] >= DATEADD('month',1,[년월])-1
THEN DATEDIFF('day',[계약 시작일],DATEADD('month',1,[년월])-1)+1
END
계산된 필드 만들기 기능을 이용하여 위 구문으로 ‘년월 별 해당 일수’ 필드를 만든 화면입니다. ① 20년 5월 21~21년 7월 13일 계약건의 경우, 그 사이에 있는 ‘년월’ 행에만 ‘년월 별 해당 일수’ 열에 숫자가 채워져 있음을 알 수 있습니다. ② 해당 값이 0인 행들은 년월별 매출 집계에 필요 없는 행이므로 과감히 오른쪽 버튼 > 제거 기능을 통해 삭제해줍니다. 이 과정에서 많은 데이터가 삭제되어 전체 데이터가 한결 가벼워짐을 확인하실 수 있습니다.
년월 별 매출
년월 별 매출 필드는 단순히 (A) 일별 매출 필드와 (B) 년월 별 해당 일수 필드를 곱해주면 되는데요! 역시 계산된 필드 만들기 기능을 이용하여 하기 수식을 입력합니다.
[일별 매출]*[년월별 해당 일수]
이렇게 모든 데이터가 준비되었습니다! 이제 태블로에서 시각화를 해주기 위해 출력 기능으로 추출 파일(.hyper)을 생성해줍니다.
매출 그래프 시각화
생성된 추출 파일을 태블로를 이용하여 열어 줍니다. 태블로 프렙에서 마련한 데이터가 잘 불러와졌네요!
시트로 이동하여 비주얼라이제이션을 구성해보았습니다. 월별로 매출을 보되 가로로 작년 매출과 비교할 수 있도록 ‘열’ 선반에 ‘년월’ 필드를 ‘월’ > ‘년’ 순서로 드래그하였습니다. 행에는 계산된 데이터는 ‘년월 별 매출’을 넣어 주었습니다. 그리고 그래프를 연도 별로 색을 달리 보기 위해 마크의 색상 부분에 ‘년월’의 ‘년’을 추가해 주었습니다.
그 결과, 초반에 계획했던 비주얼라이제이션 구성이 완성되었습니다!
실무에 해당 작업을 적용하면서, 복잡할 줄 알았던 기간 별 매출 현황을 손쉽게 연산할 수 있었는데요. 실제로는 저희가 판매하고 있는 제품의 수량, 정상단가, 할인단가 등에도 각각 적용하여 회사의 비즈니스 현황에 대해 다양한 분석과 러닝을 도출할 수 있었습니다.
한 가지 주의할 점!
데이터 구조 상, ‘년월 별 매출’ 행을 복제하는 과정에서 기존의 ‘매출’은 여러 중복 행이 발생하게 되는데요. 이에 ‘매출’ 열을 합계 등 다양한 연산에 활용할 경우, 실제 값보다 매우 부풀려질 수 있습니다. 이 점을 유념하여 ‘년월 별 매출’을 실제 연산에 사용하시기 바랍니다.
길고 복잡할 수도 있는 내용을 끝까지 읽어 주셔서 감사합니다!
