지난 포스트에서는 YoY(Year-over-Year) 차트를 루커 스튜디오에서 구현하는 방법에 대해 살펴보았습니다. 아래의 이미지와 같이 선택 연도와 전년도의 매출 추이, 월별 매출과 증감액, 증감률을 보여주는 차트를 생성하였지요. 이렇게 특정 기간의 성과를 이전의 기간과 비교하면, 계절적 요인이나 마케팅 전략의 성과, 시장 변화로 인한 영향을 이해하는데 도움이 됩니다.
💡YoY(Year-over-Year)차트와 구현하는 방법에 대해 궁금하시다면 아래의 이전 포스트를 먼저 읽어보시길 바랍니다.
루커 스튜디오에는 기간 별로 다양한 수치들을 비교할 수 있는 기능이 내재되어 있습니다. 이번에는 다양한 기능 중에 ‘맞춤 쿼리’ 또는 ‘커스텀 쿼리’라고 하는 기능을 살펴보고자 합니다. 맞춤 쿼리라고 부르는 이유는 SQL문을 루커 스튜디오에서 작성한 뒤에, 빅쿼리로 보내어 실행한 결과를 받아오기 때문입니다.
지난 포스트에서는 선택 연도의 매출과 1년 전 매출을 하나의 행에 매칭 후 쿼리의 결과를 테이블에 저장하여 사용했습니다. 즉, 이것은 정적인 데이터를 불러오는 것이지요. 그런데 맞춤 쿼리 기능은 매개변수를 넣어서 데이터를 동적으로 불러올 수 있습니다. 오늘은 맞춤 쿼리 기능에 대해 소개하고, 예시를 통해 실제 활용 사례를 소개하겠습니다.
1. 맞춤 쿼리(커스텀 쿼리)란?
맞춤 쿼리는 루커 스튜디오에서 제공하는 기능으로, 사용자가 직접 SQL문을 작성해 데이터베이스에서 데이터를 추출할 수 있게 해줍니다. 만약 데이터를 단지 일방적으로 불러오기만 하는 것이라면 맞춤 쿼리를 사용할 필요는 없습니다. 하지만 데이터베이스에 매개변수를 전달하고 연산하는 과정을 거쳐 데이터를 불러와야 한다면 맞춤 쿼리를 사용하는 것이 적절한 방식이 될 수 있습니다.
💡구글에서 제공하는 루커 스튜디오의 맞춤 쿼리에 대한 레퍼런스는 아래 링크를 참조해주세요.
빅쿼리를 클릭하면 다음과 같은 화면이 나옵니다. 맞춤 쿼리를 세팅하는 곳은 ‘맞춤 검색어’ 탭입니다. 이 기능을 통해 직접 SQL 쿼리를 입력하는 것이지요.
‘맞춤 검색어’를 클릭하면 우측에 사용자의 구글 클라우드 내 빅쿼리 프로젝트의 목록이 나옵니다. 프로젝트를 선택하면 아래 이미지와 같이 우측에 맞춤 쿼리 입력창이 나오는데, 이 입력창에 쿼리를 입력하여 사용하는 것입니다.
💡루커 스튜디오에서 빅쿼리 데이터를 연동할 때에 나오는 프로젝트 명(예시에서는 ‘share’)은 구글 클라우드에서 정의했던 프로젝트 명을 뜻합니다.
2-4. 기간 매개변수 설정
쿼리 입력 창에서 매개변수를 사용하려면 별도 설정이 필요합니다. 앞서 대시보드에 추가했던 기간 컨트롤러에서 선택한 시작 일자와 종료 일자를 매개변수로 사용하려면 맞춤 쿼리 입력 창 하단의 ‘기간 매개변수 사용 설정’ 을 체크해야 합니다. 그렇게 하면 아래 이미지의 2번처럼 시작 일자와 종료 일자를 쿼리에서 사용할 수 있도록 SQL 식별자를 알려주며, 이 식별자를 활용하여 쿼리문을 작성할 수 있습니다.
시작 일자는 @DS_START_DATE, 종료 일자는 @DS_END_DATE 로 정의되어 있습니다.
기본 설정을 마쳤으니 쿼리문을 작성해볼까요?
2-5. 쿼리문 작성
쿼리의 주요 목표는 다음과 같습니다.
선택 기간의 일자별 매출 데이터 불러오기
선택 기간의 1년 전, 1개월 전, 2개월 전, 3개월 전 동 기간의 매출을 일자별로 불러오기
비교 분석할 수 있도록 불러온 데이터들을 일자별 하나의 행으로 통합하기
아래는 완성된 전체 코드입니다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
--선택기간의매출일자별로불러오기
WITH currentData AS(
SELECT salesDate,SUM(revenue)AScurrentRevenue
FROM autoofficeshare.commerce_sample.LS_salesData
WHERE salesDate BETWEEN PARSE_DATE('%Y%m%d',@DS_START_DATE)ANDPARSE_DATE('%Y%m%d',@DS_END_DATE)
ON COALESCE(c.salesDate,l.salesDate,m1.salesDate)=m2.salesDate
FULL OUTER JOIN threeMonthAgoData m3
ON COALESCE(c.salesDate,l.salesDate,m1.salesDate,m2.salesDate)=m3.salesDate
ORDER BY salesDate;
쿼리를 CTE별로 분류하여 살펴보겠습니다.
💡CTE란? CTE(Common Table Expression)는 SQL에서 임시로 생성하는 테이블입니다. 이것은 쿼리의 가독성을 높이고 재사용성을 향상시키는 역할을 합니다. WITH문을 사용하여 정의합니다. 예시에서는 각 기간 별로 SELECT하는 구문이 괄호로 묶여있는데, 이 단위들을 CTE라고 합니다.
전체 코드에서 salesData, lastYearData 등이 모두 CTE입니다.
(1) 선택 기간의 매출 불러오기
1
2
3
4
5
6
salesData AS(
SELECT salesDate,SUM(revenue)AScurrentRevenue
FROM autoofficeshare.commerce_sample.LS_salesData
WHERE salesDate BETWEEN PARSE_DATE('%Y%m%d',@DS_START_DATE)ANDPARSE_DATE('%Y%m%d',@DS_END_DATE)
GROUP BY salesDate
)
⚠️ 여기서 시작 일자(@DS_START_DATE)와 종료 일자(@DS_END_DATE) 매개변수를 조건문 WHERE절에서 사용하지요? 해당 기간에 속한 데이터만 불러오기 위한 조건문을 사용한 것인데, 유의할 점은 날짜를 DATE타입으로 파싱하였다는 것입니다.
그 이유는 무엇일까요? 쿼리 입력창 하단의 매개변수 정보를 다시 보면, 시작일과 종료일 식별자의 SQL유형은 STRING 형태로, 날짜를 ‘YYYYMMDD’ 형식으로 취급하고 있습니다.
하지만 참조하는 테이블에서의 날짜는 DATE타입으로 저장되어 있으므로 제공하는 파라미터의 데이터 타입도 DATE타입으로 동일하게 맞춰주어야 합니다. PARSE_DATE() 함수가 바로 그 역할을 수행하는 것이지요.
💡PARSE_DATE() 구문의 사용 방식은 다음과 같습니다. 자세한 사용 방법은 구글 클라우드 문서를 참조하세요.
1
PARSE_DATE(날짜형식,문자열형식의날짜)
‘날짜 형식’은 ‘%Y-%m-%d’, ‘%m/%d/%Y’ 처럼, 제공하는 STRING의 파싱할 날짜 형식을 입력합니다. %Y는 4자리의 연도, %m은 2자리의 월, %d는 2자리의 일을 뜻합니다.
💡현재 날짜를 불러오면서 매출은 과거의 것을 불러오는 것이기 때문에 각 값을 구분하기 위해 컬럼명은 AS 를 사용하여 각각 ‘lastYearRevenue’, ‘lastMonthRevenue’, ‘twoMonthAgoRevenue’, ‘threeMonthAgoRevenue’ 로 구분하였습니다.
왜 SELECT에서 DATE_ADD(salesDate, INTERVAL 1 YEAR) 처럼 날짜에 기간을 더해주었을까요? salesDate를 불러오면 1년 전의 날짜를 불러옵니다. 여기에 1년을 더해 주면 현재 시점의 날짜로 변환이 되는데, 이어지는 (3)에서 조인을 할 때에 조건으로 사용하기 위해 맞추어 준 것입니다.
ON COALESCE(c.salesDate,l.salesDate,m1.salesDate)=m2.salesDate
FULL OUTER JOIN threeMonthAgoData m3
ON COALESCE(c.salesDate,l.salesDate,m1.salesDate,m2.salesDate)=m3.salesDate
ORDER BY salesDate;
이 쿼리는 생성하였던 선택 기간, 1년 전, 1개월 전, 2개월 전, 3개월 전 매출을 한 번에 비교할 수 있도록 FULL OUTER JOIN 구문을 이용해 데이터를 정리하는 과정으로, 쿼리의 결과는 다음과 같은 형식을 이룹니다. 하나의 일자 행마다 현재 기간의 매출, 1년 전 매출, 1개월 전 매출, 2개월 전 매출, 3개월 전 매출이 함께 담겨있는 것이 보이지요?
이 쿼리에서 핵심이 되는 구문은 FULL OUTER JOIN 구문과 COALESCE() 구문입니다. FULL OUTER JOIN 은 두 테이블 간의 모든 행을 포함하여 조인하고 COALESCE() 는 여러 인수 중 NULL이 아닌 첫 번째 값을 반환합니다. 두 구문을 사용해서 모든 날짜를 포함하여 데이터 손실 없이 CTE들을 통합하는 것입니다.
💡FULL OUTER JOIN 구문 COALESCE() 구문과 본 포스트에 적용된 원리에 대해 더 자세히 알아보고 싶다면 아래의 포스트를 참조해주세요.
쿼리를 모두 작성하였으니 이제 데이터를 추가해보겠습니다. 우측 하단의 ‘추가’ 버튼을 클릭합니다.
정상적으로 추가가 되면 ‘리소스’ → ‘추가된 데이터 소스 관리’ 메뉴에서 데이터 소스가 추가된 것을 확인할 수 있습니다. 데이터 소스 이름이 임의로 지정되어있으니 ‘수정’ 버튼을 클릭하여 데이터 소스의 이름을 수정해보겠습니다.
아래 화면에서 빨간 박스 부분을 클릭하면 소스 이름이나 각 필드명을 수정할 수 있습니다.
우측 상단의 ‘완료’ 버튼을 클릭하면 데이터 소스 세팅이 완료됩니다.
💡데이터 소스의 필드 이름을 한번 변경하면 원본 필드 이름을 확인할 수 있는 방법이 없으므로 우측 ‘설명’ 란에 원본 필드 이름을 기입하면 관리하기가 더 편리합니다.
이제 차트를 추가해보겠습니다. 수정 화면에서 메인 화면 상단의 ‘차트 추가’ → ‘피봇 테이블’을 선택합니다.
피봇 테이블을 추가하고, 측정 기준과 측정 항목을 다음과 같이 세팅합니다. (표로도 가능합니다.)
차트가 완성되었습니다! 이제 기간 컨트롤러의 기간을 2025년 2월 1일부터 2월 10일로 변경하면, 맞춤 쿼리를 통해서 매개변수로 전달하고, 조건문에서 해당 기간을 연산하여 각 기간에 대한 매출을 불러오게 됩니다.
3. 다음 포스트 소개
오늘 완성한 차트는 일자별로 과거 시점과 비교할 수 있는 기능이었습니다. 다음 포스트에서는 몇 가지 기능을 추가해서 아래와 같은 차트를 만들어보겠습니다.
1️⃣ 선택 기간에 따른 거래처들의 기간별 매출
2️⃣ 차트 헤더에 선택 기간과 과거의 비교 기간들의 범위를 표시
오늘 살펴본 쿼리를 약간 수정하고, 디자인 요소를 가미하면 아래와 같이 과거의 각 기간들이 어느 기간에 해당되는지 머릿속으로 계산하지 않고도 직관적으로 보여줄 수 있습니다.
이 포스트에서는 맞춤 쿼리 기능에 대한 기본 개념과 사용 방법을 소개하였습니다. 비교 기능을 정적인 데이터만으로 구현하는데에는 한계가 있습니다. 하지만 맞춤 쿼리 기능을 활용하면 비교 기능에서 더 넓은 분석이 가능해집니다. 맞춤 쿼리 기능을 더 깊이 있게 활용하고 싶으시다면 다음 포스트도 꼭 읽어보시길 바랍니다.
