Google BigQuery와 Data Studio를 활용한 매출 분석 대시보드 개발기
안녕하세요, 더핑크퐁컴퍼니 웹개발팀에서 백엔드 업무를 맡고 있는 메타입니다. 🌱
이번에 기존 AWS RDS와 PHP로 운영중이었던, 앱 광고 매출 분석 시스템을 Google BigQuery와 Data Studio를 이용해 이전 및 확장 작업을 진행하면서 느낀 점을 정리해보았습니다.
먼저 요구사항은 아래와 같았습니다.
사내 앱 광고 매출 분석 시스템에서 트래킹 하고 있던 플랫폼을 기존 Admob 1곳에서 8곳으로 늘려주세요. 그리고 그 8곳에서의 매출 현황을 한눈에 보고 싶어요!
🤔 레거시 시스템을 수정할 것이냐 VS 새로운 시스템을 도입할 것이냐
기존 시스템은 새로운 광고 플랫폼 1곳을 추가 할때마다, 별도 추가 테이블을 생성하고 추가된 데이터를 보여주기 위한 프론트 단 개발도 들어가야 했기 때문에 플랫폼이 추가되고 제거될 때마다 PHP 버전 이슈 등 프론트 쪽에서도 신경써야 하는 부분이 많았고, 플랫폼에 등록되어있는 매출 이외에 부가정보(앱정보, 광고단위 정보 등)를 수동으로 관리 하고 있었기 때문에, 운영하면서 타깃 플랫폼이 늘어날 때마다 수동으로 관리해야 하는 포인트도 따라서 늘어나는 문제가 있었습니다.
그래서 과감하게 새로운 시스템을 도입하기로 했습니다.
먼저 프론트 개발 없이 데이터 분석을 가능하게 하는 도구들을 리서치해보았습니다.
후보군은 아래와 같았습니다.
- elasticsearch + Kibana
- BigQuery + Data Studio
제일 먼저 생각해 보았던 것은 이전에 앱 사용자 로그 처리 및 분석 작업을 진행 하면서 사용해보았던 elasticsearch + Kibana 스택이었습니다.
하지만 elasticsearch는 RDB에서의 relation과 같은 개념이 없기 때문에 매출정보와 앱정보, 광고단위 정보를 별도 index로 관리할 수 없다는 단점이 있었습니다. (elasticsearch에서 관계형 데이터 모델링 방법이 있기는 하지만, 별도의 두 인덱스를 조인하는 개념이 아니라 하나의 인덱스에 계층이나, 부모 구조의 형태로 구성된 데이터를 넣고 관리하는 느낌)
한번 발생하면 수정될 일이 없는 로그 정보와는 달리 각 광고 플랫폼에 등록된 앱정보나, 광고단위 정보는 운영 중 수시로 바뀔 수 있는 정보였기 때문에 elasticsearch + Kibana로 그 정보들까지 묶어서 확인하기에는 쉽지 않을것 같았습니다.
그리고 elastic cloud 서비스를 이용해 240GB 까지의 스토리지를 할당 받아 사용하고 있었기 때문에, 추후 별도의 확장 이슈가 또 생긴다는 점도 단점으로 생각했습니다.
그래서 그 대안으로 고민해보았던 도구는 BigQuery + Data Studio 였습니다.
Google BigQuery 장단점
😃 BigQuery를 알아보면서 장점으로 생각되었던 점은
- 완전관리형 엔터프라이즈 데이터 웨어하우스로, 별도 설치 운영할 필요가 없고 그렇기 때문에 나중에 생길 수 있는 확장 이슈에 대응이 쉽다.
- SQL을 지원하기 때문에 접근성이 좋고, RDB처럼 테이블간 relation을 지원하기 때문에 데이터를 테이블 별로 나누어 관리가 가능하다.
- 쿼리 결과를 클릭 몇 번으로 구글 시트로 내보거나, Data Studio를 통해 시각화 대시보드를 만들어 다른 사람들과 공유할 수 있다.
- on-demand 가격 정책을 지원하기 때문에 사용량이 많지 않을 때 비용 절감이 가능하다.
- 파티션 나누기, 자동 캐싱 처리 등 쿼리 비용 절감을 위한 여러 방법들이 존재한다.
🤔 단점으로 생각되었던 점은
- BigQuery에는 키나 인덱스의 개념이 없다. 무조건 풀스캔이다.
그렇기 때문에 기존 RDB나 elasticsearch로 운영했을 때만큼의 쿼리 속도를 보장하기 힘들다는 점이었습니다.
하지만 앱 광고 매출 분석 시스템의 경우 빈번하게 데이터의 수정, 삭제가 일어나지도 않고, 조회 작업도 대시보드를 이용하는 팀에서 데이터 분석 작업이 있을 때만 몇 번 일어나는 게 전부이기에, OLTP보다는 OLAP 시스템에 특화 된 BigQuery로 이전하는 것이 추후 관련 부서의 여러 요청에 더 유연하게 대응할 수 있을 것 같다는 결론을 내렸습니다.
Google BigQuery 테이블 만들어 보기
먼저 BigQuery의 경우 아래와 같은 구조로 되어있습니다.
GCP 프로젝트 > 데이터세트 >테이블
각 GCP 프로젝트 별로 데이터세트를 구성할 수 있고 그 아래 테이블을 만들어 운영할 수 있게 되어있습니다.
테이블 생성시에는 크게 2가지 항목을 설정합니다.
- 스키마 정보 정하기
필드 데이터 유형의 경우, STRING, INTEGER 이외에 다양한 필드들을 설정 할 수 있었습니다.
2. 파티션 방식 정하기
파티션 방식을 지정하면 해당 테이블은 특정 세그먼트로 분할된 특수한 테이블인 “파티션을 나눈 테이블" 이 됩니다.
기본적으로 빅쿼리에서는 풀스캔이 일어나고, 스캔되는 데이터의 양이 늘어날수록 청구되는 비용도 늘어나기 때문에, 큰 테이블을 작은 파티션으로 나누면 쿼리 성능을 높일 수 있으며 쿼리에서 읽는 바이트 수를 줄여 비용을 제어할 수 있다고 합니다.
파티션을 나눌수 있는 방법에는
데이터가 빅쿼리로 들어온 1. 수집시간대로 알아서 빅쿼리가 테이블을 나누게 하거나,
특정 2. 컬럼 값대로 테이블을 쪼개도록 지정할 수 있습니다. (아래 링크에 자세한 설명이 있습니다.)
파티션을 지정하여 테이블 생성 시, 아래 사진처럼 세부정보란에서 파티션에 대한 정보를 확인할 수 있었습니다.
+추가로, 테이블 생성시 아래 사진처럼 ‘데이터를 쿼리하려면 WHERE 절 필요’ 항목에 체크를 할 경우, 매 쿼리 시마다 꼭 파티션에 기준이 되는 필드를 WHERE 절로 필수적으로 걸어줘야 했습니다.
파티션 결과 쿼리 테스트
테스트 쿼리 (1) : 전체 기간 (> 10M rows)
SELECT * FROM `project.dataset.table` WHERE date > ‘2010–01–01’;
테스트 쿼리 (2) : 올해 기간 (< 1M rows)
SELECT * FROM `project.dataset.table` WHERE date > ‘2022–01–01’;
파티션을 적용 한 경우, SELECT 범위가 좁아질수록 처리 및 청구된 바이트와 시간이 감소되는 것을 확인할 수 있었습니다.
쿼리 비용 관리에 많은 도움이 되기 때문에 추후 데이터 양이 계속해서 많아질수 있는 테이블은 파티션을 필수로 설정하는 것이 좋다는 느낌을 받았습니다.
Google BigQuery에 데이터 넣어 보기
BigQuery의 경우 google에서 공식 node.js 클라이언트도 제공하기 때문에 node.js로 구성된 시스템에 이식하기 쉽게 되어있었습니다.
const { BigQuery } = require('@google-cloud/bigquery');const options = {
keyFilename: 'test-bigquery-user-key.json',
projectId: 'GCP 프로젝트 ID'
};const bigquery = new BigQuery(options);
node.js 시스템 내에서 위와 같이 BigQuery 인스턴스를 생성해서 제공되는 기능을 사용할 수 있습니다.
데이터를 넣는 방식에는 크게 2가지 방식이 존재했습니다.
- 데이터 넣기: 스트리밍 방식
https://github.com/googleapis/nodejs-bigquery/blob/main/samples/insertRowsAsStream.js
await bigquery
.dataset('타깃데이터세트명')
.table('타깃테이블명')
.insert(records);Google BigQuery 스트리밍 삽입은 데이터가 ‘거의 실시간’으로 펌핑되도록 하지만, 주의사항이 있습니다.
- 스트리밍 방식으로 데이터 삽입 시 비용이 청구됩니다.
- 스트리밍 방식으로 넣은 데이터는 30분간 DML문으로 삭제, 변경을 할 수 없습니다. (문서)
- 프로젝트 별 초당 1GB까지 스트리밍할 수 있는 등 제한이 존재합니다.
2. 데이터 넣기: INSERT DML 이용 방식
https://cloud.google.com/bigquery/docs/reference/standard-sql/data-manipulation-language
const query = `INSERT dataset.Inventory (product, quantity)
VALUES('top load washer', 10),
('front load washer', 20),
('dryer', 30),
('refrigerator', 10),
('microwave', 20),
('dishwasher', 30),
('oven', 5)`const options = {
query: query,
location: commonConfig.location
};const [ job ] = await bigquery.createQueryJob(options);
const result = await job.getQueryResults();return result;
DML 방식의 경우 스트리밍처럼 거의 실시간 펌핑이 일어나지는 않지만,
프로젝트에서 하루에 실행할 수 있는 DML문 수의 제한도 없고, 데이터를 삽입한 이후부터 자유롭게 수정, 삭제 가능했습니다.
(DML 한도 관련 자세한 정보는 여기 공식 문서에서 확인 가능합니다. )
앱 광고 매출 데이터의 경우 배치 작업으로 매일 매일 데이터를 가져오기 때문에 스트리밍 방식을 이용할 이유가 없었습니다. 그래서 DML 방식을 사용했습니다.
쿼리 결과 분석하기
BigQuery에서는 쿼리 결과를 다양한 방식으로 분석할 수 있는 방법을 제공합니다.
저희는 위의 여러 방법 중, 시각화 대시보드 페이지가 필요했기 때문에, 필요한 쿼리 결과를 운용하기 쉽게 뷰테이블로 생성했고,
해당 뷰들을 Data Studio 대시보드에 리소스로 연결하여 대시보드 화면을 구성했습니다.
Data Studio에서는 표, 시계열, 막대 등 다양한 그래프를 제공하고 있어서 요구사항에 맞게 대시보드를 구성할 수 있었습니다.
그리고 무엇보다도 Kibana와 달리 색상 테마나, 레이아웃 지정하는 것이 자유로워서 아래 처럼 여러 디자인으로 대시보드를 꾸미는 것이 가능했습니다!! 😃
마지막으로,
이번 작업을 진행하면서 느낀 점은, 기존 시스템이 RDB로 구성된 환경이었다면, 게다가 빠른 쿼리 속도가 그다지 필요 하지 않고, 사용도 빈번하지 않다면 elasticsearch 보다 Google BigQuery를 차기 데이터 웨어하우스로 사용하는 것이 더 작업하기 쉬울 것 같다는 것이었습니다.
elasticsearch + Kibana의 경우, 쿼리 속도가 빠르기 때문에 실시간으로 대응해야 하는 라이브 데이터 분석에,
BigQuery + Data Studio의 경우, 데이터 수가 1M rows 미만이어도 쿼리 속도가 1초 이상 걸리고, 자체 자동 캐싱 시스템이 있지만 그래도 매 새로운 쿼리마다 비용이 청구되기 때문에, 매출 데이터 처럼 어느정도 배치 처리가 가능하고 요청도 그리 빈번하지 않은 프로젝트에 적용하는 것이 알맞을 것 같습니다.
무엇보다도 완전관리형 서비스이기 때문에 설치 및 운영에 큰 공수가 들지 않는다는 점에서 프로젝트에 빠르게 적용해서 원하는 결과를 여러 방법으로 뽑아내고 공유할수 있다는 점이 가장 큰 장점 이라고 느꼈습니다.
추가로, 광고 플랫폼별 API 리서치, Data Studio 대시보드 작업을 함께 진행해주신 웹개발팀 페이지님, BigQuery관련 인사이트를 제공해주신 gTech professional service팀 고락윤님께 감사드립니다.
