지난 포스트에서 우리는 기간 매개변수를 활용하여 기간별 매출 비교, 기간에 따른 거래처별 매출을 비교하는 기능과, 기간 매개변수를 활용하여 동적으로 날짜를 표시해주는 차트의 헤더를 제작하는 방법까지 살펴보았습니다. (⬇️ 아래 이미지 참조)
루커 스튜디오와 빅쿼리를 연동할 때 이렇게 맞춤 쿼리(커스텀 쿼리)를 사용하면 데이터를 보다 동적으로 불러올 수 있다는 것을 알 수 있었지요?
이번 포스트에서는 맞춤 쿼리 기능을 한 층 더 깊이 활용해보겠습니다. 루커 스튜디오에는 매개변수를 사용자가 정의하는 기능이 있습니다. 이러한 매개변수는 드롭다운을 통해서 조작할 수 있으며, 드롭다운에서 선택한 값에 따라 쿼리에 정해진 값을 전달할 수 있습니다.
예를 들어, 루커 스튜디오에서 ‘품목’ 이라는 매개변수를 생성했다고 가정해보겠습니다.
이 ‘품목’ 매개변수에 ‘사과’, ‘배’, ‘바나나’ 를 정의하였습니다. 이제 드롭다운을 클릭하면 아래와 같이 정의된 항목들이 나오고, 여기서 선택한 값이 맞춤 쿼리에 전달이 되는 것입니다.
이 매개변수를 빅쿼리에 전달하는 방법은 맞춤 쿼리에서 매개변수 이름을 기입하기만 하면 됩니다. 아래 예시 쿼리를 보면 WHERE 조건절에서 ‘품목’ 매개변수를 사용하였습니다.
1
2
SELECT*FROM`데이터 테이블`
WHERE productName=@품목
💡‘품목’ 앞에 붙어있는 ‘@’ 표시는 이 문자가 매개변수라는 것을 식별시켜주는 표시입니다.
WHERE 조건절에서 테이블에 있는 품목명과 ‘@품목’ 의 값이 일치하는 것만 SELECT하는 것이지요. 예시에서는 ‘사과’를 선택하였으므로 사과 품목에 대한 데이터만 불러오게 됩니다. 이해를 돕기 위해 단순하게 필터링 하는 수준의 쿼리문이었지만 이 기능은 훨씬 더 많은 것을 가능하게 합니다.
이제 기간 매개변수에 더해서 사용자가 직접 정의한 매개변수를 활용하여 아래의 차트를 완성해보겠습니다.
1️⃣: 차트의 측정 기준을 ‘거래처별’, ‘품목별’, ‘채널별’, ‘판매몰별’로 변경하는 기능
⇒ 드롭 다운에서 선택하는 기준에 따른 ‘매출’ 또는 ‘수량’을 집계하고, 기간별로 비교 값을 보여줍니다.
2️⃣: 차트의 측정 항목을 ‘매출’, ‘수량’ 으로 변경하는 기능
⇒ 드롭 다운에서 선택한 항목에 따라 ‘매출’ 또는 ‘수량’을 보여줍니다.
3️⃣: 반품 건을 집계에 포함할 것인지 설정하기
⇒ 드롭 다운에서 반품 건에 대하여 ‘매출’ 또는 ‘수량’에 포함시킬 것인지를 설정합니다.
💡예시에서 반품 건은 아래와 같이 원본 데이터에서 판매 수량이 -(음수) 인 것을 의미합니다.
💡오늘 사용할 예시는 지난 포스트에서 이어지는 내용으로, 이전 포스트를 먼저 살펴보는 것이 내용을 이해하는데 도움이 됩니다. 아래 링크를 확인해주세요.
먼저 매개변수를 추가해보겠습니다. 수정 화면 상단에서 ‘리소스’ → ‘추가된 데이터 소스 관리’를 클릭합니다.
하단의 ‘데이터 소스 추가’ 버튼을 클릭합니다.
빅쿼리(BigQuery) 선택 → ‘맞춤 검색어’ → 프로젝트 선택 → 쿼리 입력창으로 이동합니다.
하단에 매개변수 설정 메뉴가 나오지요? 오늘 사용할 매개변수는 1️⃣기간 매개변수, 2️⃣측정 기준, 3️⃣측정 항목, 4️⃣반품여부를 포함할 것인지 결정하는 총 4개의 매개변수가 필요합니다. 하나씩 추가해보겠습니다.
💡이 포스트에서는 빅쿼리 테이블에 있는 어떤 컬럼을 select할 것인지 매개변수를 통해 조작할 것이기 때문에 일부 매개변수는 테이블의 실제 컬럼명을 참조합니다. 예시에서 사용할 스키마 구조는 다음과 같습니다.
2-1. 기간 매개변수 추가
‘기간 매개변수 사용 설정’을 체크하여 기간 매개변수 사용을 설정합니다. 이렇게 설정한 기간 매개변수는 SQL식별자에 표기되어있듯이 @DS_START_DATE, @DS_END_DATE 로 정의되어 있어서 쿼리에 직접 입력하여 사용할 수 있습니다.
2-2. 측정 기준 매개변수 추가
하단의 ‘매개변수 추가’ 버튼을 클릭합니다.
‘매개변수 추가’를 클릭하면 매개변수 추가 화면이 나오는데, 여기서 ‘매개변수 이름’은 수정 화면에서 데이터 목록에 표시되는 필드의 이름을 뜻합니다. 따라서 매개변수를 식별할 수 있는 이름으로 지정하였습니다. ‘매개변수 ID’는 맞춤 쿼리에서 실제로 참조하는 매개변수명 입니다. 매개변수 ID 역시 식별할 수 있는 이름으로 지정하였습니다.
💡‘허용된 값’ 에는 ‘모든 값’과 ‘값 목록’이 있습니다. ‘모든 값’으로 설정하면 매개변수에는 사용자가 어떤 값이든 입력하고 그 값을 맞춤 쿼리에 전달할 수 있습니다. 이 포스트에서는 측정 기준의 범위가 정해져있으므로 ‘값 목록’을 선택하였습니다.
‘값 목록’에서 ‘값’은 매개변수에 저장되는 실제 값을 뜻하고, ‘라벨’은 루커 스튜디오의 UI에서 표시되는 이름을 뜻합니다. 드롭다운에 매개변수를 넣을 때 목록에 보여지는 것들이 바로 ‘라벨’ 입니다. 예를 들어, 드롭다운에서 ‘거래처별’을 선택하면 @SELECT_DIMENSION 변수는 그 값인 ‘shopName’을 전달받는 것이지요. 여기서는 빅쿼리의 컬럼명과 일치하게 값을 설정하였습니다.
이렇게 매개변수를 추가한 뒤, 드롭다운에서 선택하면 맞춤 쿼리에 선택한 매개변수의 ‘값’을 전달할 수 있게 됩니다. 측정 기준을 @SELECT_DIMENSION 으로 정의하였다는 것을 기억해주세요.
💡루커 스튜디오의 매개변수에 대해 더 자세히 알아보려면 구글에서 제공하는 다음의 공식 문서를 참조해주세요.
쿼리가 길어보이지만 각각의 CTE가 동작하는 로직은 모두 비슷합니다. 이 중 2가지 CTE만 선택하여 분석해보면 충분히 이해할 수 있습니다. 아래 CTE는 3개월 전 매출 또는 수량을 불러오는 쿼리입니다. 앞서 정의했던 각각의 매개변수가 SQL식별자를 통해 사용되고 있는것이 보이지요?
@SELECT_DIMENSION 매개변수를 CASE문에서 사용하였습니다. 이 매개변수에 shopName, item, channel, store 값들을 정의하였는데 각각의 값에 라벨명을 지정했던것이 기억나지요? 드롭다운에서 라벨들을 선택하면 그에 맞는 값들이 CASE문에서 동작할 것입니다.
예를 들어 드롭다운에서 ‘거래처별’을 선택하면 이 매개변수에는 shopName, ‘품목별’을 선택하면 item이 맞춤 쿼리에 전달되는 것입니다.
💡CASE 구문이란? 조건에 따라 다른 값을 반환하는 SQL의 조건문입니다. 기본 사용 방법은 다음과 같습니다.
1
2
3
4
5
CASE
WHEN조건1THEN결과1
WHEN조건2THEN결과2
ELSE기본결과
END
조건 1이 참이면 결과1을 반환, 조건 1이 거짓이고 조건2가 참이면 결과2를 반환합니다. 모든 조건이 거짓이면 기본 결과를 반환합니다.
측정항목(@SELECT_METRIC)
@SELECT_METRIC 매개변수 역시 CASE문에서 사용하였습니다. 이 매개변수에 revenue, quantity 값을 정의하였는데 각각의 값의 라벨은 ‘매출’과 ‘수량’이었습니다. 역시 드롭다운에서 ‘매출’을 선택하면 매개변수는 revenue를, ‘수량’을 선택하면 quantity를 전달할 것입니다.
기간 매개변수(@DS_START_DATE, @DS_END_DATE)
기간 매개변수는 STRING 형태로 전달되기 때문에 날짜 형태로 파싱해주어야 합니다. 각각의 CTE에서는 이 기간 매개변수를 WHERE 조건 절에 사용하여 전년도, 1개월 전, 2개월 전, 3개월 전 매출 또는 수량을 집계합니다. BETWEEN은 SQL에서 범위 조건을 지정할 때 사용하는 연산자입니다. 보통 날짜나 숫자 범위를 필터링할 때 사용합니다.
반품 포함 여부(@RETURN_CONDITIONS)
이 매개변수는 WHERE절에서 데이터를 필터링 하는 역할을 합니다. @RETURN_CONDITIONS가 ‘include’이면 조건 없이 모든 매출 또는 수량을 포함합니다. 하지만 @RETURN_CONDITIONS가 ‘exclude’이면 매출 또는 수량이 음수인 것은 제외하지요.
💡아래 조건문에서 @RETURN_CONDITIONS 의 선택 값에 따라 CASE문에서 정의된 ‘selectMetric’을 직접 사용하지 않는 이유는 무엇일까요? selectMetric은 CASE 문을 사용해 SELECT절 안에서만 정의되는 별칭(Alias)이기 때문입니다. 이 별칭은 SQL 실행 순서 상 WHERE절에서는 인식이 되지 않습니다.
GROUP BY로 그룹화된 결과에 대해 조건을 걸 수 있는 HAVING절을 사용하면 ‘selectMetric’을 사용할 수 있지만, 반품 건까지 이미 그룹화되어있는 결과에 대해서 필터링을 하는 것이기 때문에 값이 정확하지 않습니다. 따라서 이 경우에는 WHERE절에 조건을 사용하는 것이 적합합니다.
1
2
3
4
5
6
7
8
9
@RETURN_CONDITIONS='include'
OR(
@RETURN_CONDITIONS='exclude'
AND(
(@SELECT_METRIC='revenue'ANDrevenue＞=0)
OR
(@SELECT_METRIC='quantity'ANDquantity＞=0)
)
)
다음으로 검토할 CTE는 다음과 같습니다. 이것은 각 기간별 CTE를 FULL OUTER JOIN 하여 통합하는 것입니다.
지난 포스트에서 설명하였듯이 FULL OUTER JOIN 은 두 테이블 간의 모든 행을 포함하여 조인하고 COALESCE() 는 여러 인수 중 NULL 이 아닌 첫 번째 값을 반환합니다. 두 구문을 사용해서 모든 날짜를 포함하여 데이터 손실 없이 CTE들을 통합하는 것입니다. 같은 이유에서 측정 항목인 매출/수량에도 판매가 없는 경우에는 0으로 설정하였습니다.
💡FULL OUTER JOIN 과 COALESCE() 구문이 본 포스트에 적용된 원리에 대해서 더 자세히 알아보고 싶다면 아래의 포스트를 참조해주세요.
차트가 완성되었으니 한번 테스트해보겠습니다. 측정기준을 ‘품목별’로 변경하면 아래 이미지와 같이 품목별 매출을 보여줍니다.
이번에는 측정항목을 ‘수량’으로 변경하였습니다. 품목별 판매 수량 차트로 잘 바뀌었지요?
마지막으로 ‘반품 포함 여부’를 반품을 포함하는 것으로 설정하였습니다. 반품을 포함하기 때문에 총 합계에서 숫자가 내려간 것이 보입니다.
이것으로 오늘 목표로 했던 대시보드를 완성하였습니다. 만약 이 기능들을 맞춤 쿼리(커스텀 쿼리)를 사용하지 않고 구현한다면 품목별 매출과 수량 차트, 거래처별 매출과 수량 차트 등 차트를 여러개 추가해야만 합니다. 이처럼 맞춤 쿼리에서 사용자 매개변수를 활용하면 차트의 재사용성이 높아진다는 장점이 있습니다.
매개변수와 맞춤 쿼리를 사용하니 데이터베이스와 소통을 하는 것과 같은 기분이 드는 것 같지요? 🤩 루커 스튜디오 사용자라면 이 유용한 기능을 꼭 실무에도 적용해보시길 바랍니다!
다음 포스트에서는 오늘 살펴본 내용에 이어서, 매개변수를 사용해서 정렬 기준과 순서를 변경하는 방법을 소개하겠습니다.
