지난 포스트에서 YoY (Year-over-Year)를 루커 스튜디오의 기본 기능으로 구현하는 방법을 살펴보았습니다.
YoY (Year-over-Year)는 한 연도의 특정 기간을 1년 전 같은 기간과 비교하여 성장률이나 성과 변화를 나타내는 분석 지표로, 주로 기업의 매출이나 이익과 같은 주요 실적을 확인할 때 사용할 수 있습니다.
하지만 루커 스튜디오 표 차트의 비교 기능은 비교 기간의 값을 표시할 수 없고 증감액과 증감률로만 표시해야 한다는 단점이 있습니다. 따라서 전년도의 값을 확인하려면 아래와 같이 2개의 차트를 사용해야 합니다.
루커 스튜디오의 기본 비교 기능을 사용하여 YoY (Year-over-Year)를 구현한 대시보드
오늘 다루는 대시보드의 기본 화면은 지난 포스트에서 생성했던 대시보드입니다. 과정과 방법을 살펴보시려면 지난 포스트를 먼저 읽어보시길 바랍니다.
하지만 빅쿼리 SQL을 사용하면 선택 기간과 비교 기간의 매출, 증감액, 증감률을 다음과 같이 하나의 차트에 담아 볼 수 있습니다. 더 깔끔하지요?
오늘은 빅쿼리 SQL을 사용하여 YoY (Year-over-Year)를 구현하고, 선택 연도와 전년도의 매출, 증감액과 증감률까지 하나의 차트에서 보여주는 방법을 소개하겠습니다.
1. 빅쿼리 데이터 소스 생성
예시에서 사용할 빅쿼리 스키마의 구조를 다시 살펴보면 다음과 같습니다. 쿼리에서는 필요한 열만 선택하여 사용할 것입니다.
먼저 진행할 작업은 사전 설정을 위한 쿼리를 작성하여 빅쿼리의 뷰 (View)로 저장하고 루커 스튜디오를 위한 테이블을 세팅하는 것입니다. 쿼리는 다음과 같습니다.
--1. 월별 데이터 집계
WITH sales_data AS (
SELECT
shopName,
item,
color,
location,
channel,
PARSE_DATE('%Y-%m-%d', FORMAT_DATE('%Y-%m-01', salesDate)) AS thisMonth,
SUM(revenue) AS revenueSum
FROM
`autoofficeshare.commerce_sample.LS_salesData`
GROUP BY
shopName, item, color, location, channel, thisMonth
),
--2. 집계된 데이터로 1년전 데이터 매칭
join_data AS (
SELECT
COALESCE(L.shopName, R.shopName) AS shopName,
COALESCE(L.item, R.item) AS item,
COALESCE(L.color, R.color) AS color,
COALESCE(L.location, R.location) AS location,
COALESCE(L.channel, R.channel) AS channel,
COALESCE(L.thisMonth, DATE_ADD(R.thisMonth, INTERVAL 12 MONTH)) AS thisMonth,
COALESCE(DATE_SUB(L.thisMonth, INTERVAL 12 MONTH), R.thisMonth) AS compareMonth,
COALESCE(L.revenueSum, 0) AS thisMonthRevenue, --올해 매출
COALESCE(R.revenueSum, 0) AS compareMonthRevenue, --전년도 매출
COALESCE(L.revenueSum, 0) - COALESCE(R.revenueSum, 0) AS monthDiffRevenue,
FROM
sales_data AS L
FULL OUTER JOIN
sales_data AS R
ON
L.shopName = R.shopName
AND L.item = R.item
AND L.color = R.color
AND L.location = R.location
AND L.channel = R.channel
AND L.thisMonth = DATE_ADD(R.thisMonth, INTERVAL 12 MONTH) --1년전과 데이터와 매칭
)
--3. 반기, 분기, 월별 버튼을 사용하기 위한 열 추가
SELECT
J.*,
EXTRACT(YEAR FROM J.thisMonth) as year,
EXTRACT(MONTH FROM J.thisMonth) as month,
CASE
WHEN EXTRACT(MONTH FROM J.thisMonth) in (1,2,3,4,5,6) THEN '상반기'
WHEN EXTRACT(MONTH FROM J.thisMonth) in (7,8,9,10,11,12) THEN '하반기'
END as half,
CASE
WHEN EXTRACT(MONTH FROM J.thisMonth) in (1,2,3) THEN '1분기'
WHEN EXTRACT(MONTH FROM J.thisMonth) in (4,5,6) THEN '2분기'
WHEN EXTRACT(MONTH FROM J.thisMonth) in (7,8,9) THEN '3분기'
WHEN EXTRACT(MONTH FROM J.thisMonth) in (10,11,12) THEN '4분기'
END as quarter
FROM join_data AS J
SQL
복사
1.
sales_data(CTE): 이 쿼리는 원본 데이터에서 월별 매출을 집계합니다. thisMonth 열은 판매 월의 첫날로 변환하여 월 단위로 그룹화합니다. 그리고 월별 매출을 각각 합산합니다. 최종적으로는 거래처명, 품목명, 색상, 판매지역, 판매채널을 기준으로 그룹화하여 매출을 집계합니다.
2.
join_data(CTE): FULL OUTER JOIN의 조건으로 현재 월과 1년 전 동월의 데이터를 조인합니다. thisMonthRevenue, compareMonthRevenue 열을 통해 현재 월과 비교 월의 매출을 각각 가져옵니다. monthDiffRevenue는 매출의 전년대비 차이를 계산합니다.
3.
최종 쿼리는 필터링 버튼을 사용하기 위해 연도, 반기, 분기, 월을 각각의 단위로 구분한 것입니다. 이렇게 기간 단위의 열을 추가하면 버튼 필터 기능을 사용하여 차트에 나온 기간을 간단히 설정할 수 있습니다.
이렇게 SELECT한 쿼리를 활용하기 위해서 뷰(View)를 만들어 줍니다. 뷰(View)는 물리적인 테이블을 만들지 않고도 쿼리의 결과를 데이터로 사용할 수 있는 가상의 테이블입니다. 실제 데이터를 저장하지 않고 데이터를 호출할 때에 정의된 쿼리를 실행하여 최신 데이터를 조회합니다.
빅쿼리 뷰(View) 기능에 대해서 자세히 알아보려면 다음의 공식 레퍼런스를 참조해주세요.
상단의 ‘저장’ → ‘뷰 저장’ 버튼을 클릭합니다.
다음 화면이 나오면 데이터 세트를 선택한 뒤에 뷰 테이블명을 작성합니다.
뷰 테이블이라는 것을 구분하기 위해서 뷰(View)를 의미하는 ‘V_’를 앞에 붙여주었습니다.
‘저장’ 버튼을 클릭하면 탐색기에 뷰(View) 테이블이 추가됩니다.
뷰(View)를 루커 스튜디오에서 불러올 수도 있지만, BI 엔진을 사용하기 위해서 물리적인 테이블을 만드는 것이 좋습니다. 뷰(View) 결과를 물리적인 테이블에 저장하는 쿼리를 작성해보겠습니다.
CREATE OR REPLACE TABLE `autoofficeshare.commerce_sample.LS_salesCompare` AS
SELECT * FROM `autoofficeshare.commerce_sample.V_salesCompare`
SQL
복사
•
CREATE OR REPLACE TABLE 구문은 지정된 이름의 테이블이 없다면 LS_salesCompare 테이블을 생성하고, 이미 존재하면 기존 테이블을 덮어쓰기 합니다.
•
이 코드는 V_salesCompare를 SELECT하고 있으므로 쿼리가 실행될 때마다 최신 데이터를 불러와서 LS_salesCompare 테이블에 저장합니다.
이 쿼리를 예약하면 주기적으로 루커 스튜디오에 최신 데이터를 가져오게 됩니다. 쿼리를 예약하는 방법은 아래 구글 공식 레퍼런스와 오토오피스에서도 다루고 있으니 참조하시기 바랍니다.
빅쿼리 테이블을 루커 스튜디오에 연결 후 최종적으로 아래와 같은 필드 목록을 불러왔습니다. 루커 스튜디오의 기본 비교 기능과 다른 점은, 빅쿼리에서 전년도 매출과 증감 매출을 집계화하는 가공 과정을 거쳤다는 것이지요. 이렇게 매출과 전년도 매출을 각각의 항목 처리하면 보다 상세한 데이터들을 하나의 차트에서 구현이 가능합니다.
2. 차트 추가
2.1 시계열 차트
이제 시계열 차트를 추가한 뒤, 측정 항목에 매출과 전년도 매출을 넣기만 하면 됩니다. 쿼리를 사용하는 경우 연도를 설정하는 2가지 방법이 있습니다.
1.
연도 필터를 사용
2.
기본 기간을 ‘올해’로 설정
기본 기간을 설정하면 데이터는 올해의 것으로만 고정이 되지만 연도 필터를 사용하면 드롭다운으로 간편하게 다른 연도를 선택할 수 있습니다.
빅쿼리를 사용하면 전년도 데이터가 직접 측정 항목으로 들어가기 때문에 그래프의 스타일을 각각 다르게 지정할 수 있습니다. 반면에 기본 ‘비교 기간’ 기능은 비교하는 기간에 대한 서식 지정이 불가하고, 동일한 컬러에 명도가 낮은 색상으로 자동 지정됩니다.
2번의 기본 기간을 사용한다면 기간을 ‘올해’로 설정, 비교 기간은 별도의 설정이 필요치 않습니다. 빅쿼리에서 이미 연산을 거쳐 필드를 생성해놓았기 때문입니다.
2.2 표 차트
표 차트에 매출, 전년도 매출, 증감매출 필드를 측정 항목에 넣으면 다음과 같은 구성의 차트를 만들 수 있습니다. 여기에 각 월의 매출 비중이 1년 매출의 몇%를 차지하는지, 매출의 증감률은 몇 %인지 측정항목에 추가해보겠습니다.
각 월의 매출 비중은 간단하게 구현 가능합니다. 각각 매출을 화면에 어떻게 보여줄 것인지 지정하기만 하면 됩니다. 먼저, 매출과 전년도 매출 필드를 측정 항목에 1개씩 더 추가합니다. 아직은 매출 값으로 표현되고 있습니다.
이제 비중을 표현하려는 필드의 좌측의 ‘SUM’버튼을 클릭합니다.
SUM은 매출이 집계된 값임을 의미합니다.
데이터 유형을 ‘숫자’ → ‘비율’ 로 선택합니다.
이제 화면이 어떻게 바뀌었는지 확인해보겠습니다. 데이터 유형을 비율로 변경하였지만, 아직도 매출 값을 보여주고 있네요. 이유는 무엇을 기준으로 비율을 계산할 것인지 지정되어 있지 않기 때문입니다.
전체의 값에서 차지하는 비율을 보여주려면 ‘비교 계산’ 기능을 사용해서 표현해야 합니다. 하단에 있는 ‘비교 계산’ 버튼을 눌러보겠습니다.
‘비교 계산’ 에는 특정 기준을 비교 계산하여 백분율로 보여주는 기능이 있습니다. 우리가 보고자 하는 것은 총계에 대한 백분율이므로 ‘총계 백분율’을 선택합니다.
매출이 전체 값에서 차지하는 값을 비율로 잘 표현하고 있지요? 소수점 자리는 ‘표시 형식’에서 선택할 수 있습니다. Percent(0) 처럼 괄호 안에 있는 숫자가 소수점 미만의 자리 수를 의미합니다. 이제 열 이름을 ‘비중’으로 변경하겠습니다. 열 이름을 바꾸려면 우측에 ‘이름’ 이라고 적힌 부분에 원하는 이름을 기입하면 됩니다.
원본 필드의 이름은 우측에 표시한 것처럼 작게 하단에 표기됩니다. 전년도 매출의 비중도 동일하게 설정합니다.
이제 증감률(%)도 차트에 추가해보겠습니다. 증감률은 필드 계산을 한번 거쳐야 합니다.
우측 하단의 ‘필드 추가’ → ‘계산된 필드 추가’ 버튼을 클릭합니다.
필드 이름을 ‘매출증감률’로 설정 후, 수식을 다음과 같이 입력합니다.
SUM(증감매출) / SUM(전년도 매출)
SQL
복사
수식이 ‘증감매출 / 전년도 매출’이 아니라 ‘SUM(증감매출) / SUM(전년도 매출)’인 이유는 무엇일까요?
‘SUM(증감매출) / SUM(전년도 매출)’ 수식은 각 항목의 증감 매출을 모두 더하고, 전년도 매출을 모두 더한 뒤 이 둘을 나누는 방식입니다. 따라서 이 방법이 전체 매출 규모에서의 변화를 나타내는데 적합하지요.
반면에 ‘증감매출/전년도 매출’은 개별 행들의 증감 매출을 전년도 매출로 나눈 뒤, 그 비율을 가공하는 것입니다. 따라서 각 개별 항목들의 변화율만 보여줄 뿐, 전체적인 비율을 나타내지는 않습니다. 또 SUM, AVG 를 사용하는 것에 따라 목적하는 값과는 전혀 다른 값이 계산됩니다.
비율을 표시하는 방법에서 흔히 실수할 수 있는 부분으로, 이 점에 대해 더 자세히 알아보길 원한다면 다음의 포스트를 참조해주세요.
이제 생성된 ‘매출증감률’ 필드를 차트에 추가한 뒤에, 데이터 유형을 ‘비율’로 설정하고 마무리합니다. 증감률을 표시하는 것이므로 ‘비교 계산’은 설정하지 않았습니다.
최종적으로 구성된 화면을 살펴볼까요? 
1.
매출 추이 비교: 연도 필터에서 선택한 연도와 그 이전 연도의 매출을 하나의 차트에서 보여줍니다.
2.
월별 매출 비중: 선택한 연도의 전체 매출에서 각 월이 차지하는 비중을 표현하고 있습니다.
3.
월별 매출 비교: 선택 연도의 매출과 비중, 전년도의 매출과 비중, 증감 매출과 증감률 데이터를 하나의 차트에서 보여줍니다. 루커 스튜디오의 기본 기능만으로 구현하려면 차트를 2개로 나누어야 하기 때문에 가독성이 떨어지는 문제가 있었습니다. 하지만 빅쿼리를 사용하니 하나의 차트에 원하는 데이터를 모두 볼 수 있게 되었습니다.
대시보드가 한 눈에 보기 좋게 만들어졌지요? 오늘은 루커 스튜디오에서 YoY( Year-over-Year)를 빅쿼리 SQL을 사용해서 구현하는 방법을 포스팅 해보았습니다. 이후 포스트는 오늘 함께 만들어보았던 대시보드를 활용하여 다음의 기능들을 담아보도록 하겠습니다.
•
버튼으로 차트의 측정 항목을 변경하는 방법
•
차트 가독성을 높이는 디자인적 요소를 추가하는 방법
•
선택 연도와 비교 연도를 각각 선택하여 비교하는 기능
트렌드와 패턴을 분석하는데에 YoY (Year-over-Year) 차트는 훌륭한 지표 도구 입니다. 루커 스튜디오의 기본 기능만으로도 어느 정도 구현이 가능하지요. 하지만 기업이 성장하며 파악해야하는 데이터의 범위가 방대해지면 그만큼 루커 스튜디오의 기본 기능만으로는 원하는 차트를 구현하기가 까다로울 수 있습니다.
오늘 살펴본 것처럼 빅쿼리는 루커 스튜디오의 한계를 뛰어넘도록 도움을 주는 유용한 도구입니다. 빅쿼리를 활용하여 더 유연하고 강력하게 데이터를 처리해보시길 바랍니다!
