Google BigQuery와 Data Studio를 활용한 앱 광고 매출 대시보드 개발기

Google BigQuery와 Data Studio를 활용한 매출 분석 대시보드개발기 안녕하세요, 더핑크퐁컴퍼니 웹개발팀에서 백엔드 업무를 맡고 있는 메타입니다. 이번에 기존 AWS RDS와 PHP로 운영중이었던, 앱 광고 매출 분석 시스템을 Google BigQuery와 Data Studio를 이용해 이전 및 확장 작업을 진행하면서 느낀 점을 정리해보았습니다. 먼저 요구사항은 아래와같았습니다. 사내 앱 광고 매출 분석 시스템에서 트래킹 하고 있던 플랫폼을 기존 Admob 1곳에서 8곳으로 늘려주세요. 그리고 그 8곳에서의 매출 현황을 한눈에 보고싶어요! 레거시 시스템을 수정할 것이냐 VS 새로운 시스템을 도입할것이냐 기존 시스템은 새로운 광고 플랫폼 1곳을 추가 할때마다, 별도 추가 테이블을 생성하고 추가된 데이터를 보여주기 위한 프론트 단 개발도 들어가야 했기 때문에 플랫폼이 추가되고 제거될 때마다 PHP 버전 이슈 등 프론트 쪽에서도 신경써야 하는 부분이 많았고, 플랫폼에 등록되어있는 매출 이외에 부가정보(앱정보, 광고단위 정보 등)를 수동으로 관리 하고 있었기 때문에, 운영하면서 타깃 플랫폼이 늘어날 때마다 수동으로 관리해야 하는 포인트도 따라서 늘어나는 문제가있었습니다. 그래서 과감하게 새로운 시스템을 도입하기로했습니다. 먼저 프론트 개발 없이 데이터 분석을 가능하게 하는 도구들을 리서치해보았습니다. 후보군은 아래와같았습니다. elasticsearch +Kibana BigQuery + DataStudio 제일 먼저 생각해 보았던 것은 이전에 앱 사용자 로그 처리 및 분석 작업을 진행 하면서 사용해보았던 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 이외에 다양한 필드들을 설정 할 수있었습니다. 데이터 유형 | BigQuery | Google Cloud 2. 파티션 방식정하기 파티션 방식을 지정하면 해당 테이블은 특정 세그먼트로 분할된 특수한 테이블인 “파티션을 나눈 테이블" 이됩니다. 기본적으로 빅쿼리에서는 풀스캔이 일어나고, 스캔되는 데이터의 양이 늘어날수록 청구되는 비용도 늘어나기 때문에, 큰 테이블을 작은 파티션으로 나누면 쿼리 성능을 높일 수 있으며 쿼리에서 읽는 바이트 수를 줄여 비용을 제어할 수 있다고합니다. 파티션을 나눌수 있는방법에는 데이터가 빅쿼리로 들어온 1. 수집시간대로 알아서 빅쿼리가 테이블을 나누게하거나, 특정 2. 컬럼 값대로 테이블을 쪼개도록 지정할 수 있습니다. (아래 링크에 자세한 설명이있습니다.) 파티션을 나눈 테이블 소개 | BigQuery | Google Cloud 파티션을 지정하여 테이블 생성 시, 아래 사진처럼 세부정보란에서 파티션에 대한 정보를 확인할 수있었습니다. date라는 날짜 컬럼을 기준으로 파티션을 나눈모습 +추가로, 테이블 생성시 아래 사진처럼 데이터를 쿼리하려면 WHERE 절 필요 항목에 체크를 할 경우, 매 쿼리 시마다 꼭 파티션에 기준이 되는 필드를 WHERE 절로 필수적으로 걸어줘야했습니다. 테이블 생성 시 데이터를 쿼리하려면 WHERE 절 필요 항목에 체크를 할 경우, 매 쿼리 시마다 꼭 파티션에 기준이 되는 필드를 WHERE 절로 걸어주어야 한다. 그렇지 않을 경우 에러가발생한다. 파티션 기준 필드인 date로 WHERE 절을 걸어 주었을 경우, 에러 없이 성공한모습 파티션 결과 쿼리테스트 테스트 쿼리 (1): 전체 기간 (> 10M rows) SELECT * FROM project.dataset.table WHERE date > 20100101 테스트 쿼리 (1) 실행결과 테스트 쿼리 (2): 올해 기간 (< 1M rows) SELECT * FROM project.dataset.table WHERE date > 20220101 테스트 쿼리(2) 실행결과 파티션을 적용 한 경우, SELECT 범위가 좁아질수록 처리 및 청구된 바이트와 시간이 감소되는 것을 확인할 수있었습니다. 쿼리 비용 관리에 많은 도움이 되기 때문에 추후 데이터 양이 계속해서 많아질수 있는 테이블은 파티션을 필수로 설정하는 것이 좋다는 느낌을받았습니다. Google BigQuery에 데이터 넣어보기 BigQuery의 경우 google에서 공식 node.js 클라이언트도 제공하기 때문에 node.js로 구성된 시스템에 이식하기 쉽게되어있었습니다. GitHub - googleapis/nodejs-bigquery: Node.js client for Google Cloud BigQuery: A fast, economical and fully-managed enterprise data warehouse for large-scale data analytics. 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에서는 쿼리 결과를 다양한 방식으로 분석할 수 있는 방법을제공합니다. 쿼리 결과 창 왼쪽에 결과 저장 및 분석을 위한 다양한 옵션을 선택 할 수있습니다. BigQuery에서 제공하는 다양한 내보내기 및 탐색방법들 저희는 위의 여러 방법 중, 시각화 대시보드 페이지가 필요했기 때문에, 필요한 쿼리 결과를 운용하기 쉽게 뷰테이블로생성했고, 해당 뷰들을 Data Studio 대시보드에 리소스로 연결하여 대시보드 화면을구성했습니다. 뷰 만들기 | BigQuery | Google Cloud Data Studio 내 리소스 메뉴 > 추가된 데이터 소스 관리 에서 BigQuery 테이블 을 불러올 수있습니다. 데이터에 연결 항목에서 BigQuery 를 지정하면 BigQuery 내 테이블들을 Data Studio로 불러올 수있습니다. Data Studio에서는 표, 시계열, 막대 등 다양한 그래프를 제공하고 있어서 요구사항에 맞게 대시보드를 구성할 수있었습니다. 그리고 무엇보다도 Kibana와 달리 색상 테마나, 레이아웃 지정하는 것이 자유로워서 아래 처럼 여러 디자인으로 대시보드를 꾸미는 것이 가능했습니다!! Data Studio에 존재하는 다양한 테마 옵션들, 항목별로 원하는 색깔을 지정하는 커스터마이징도가능하다. Data Studio를 통해 광고 매출 대시보드를 구성한모습 마지막으로, 이번 작업을 진행하면서 느낀 점은, 기존 시스템이 RDB로 구성된 환경이었다면, 게다가 빠른 쿼리 속도가 그다지 필요 하지 않고, 사용도 빈번하지 않다면 elasticsearch 보다 Google BigQuery를 차기 데이터 웨어하우스로 사용하는 것이 더 작업하기 쉬울 것 같다는것이었습니다. elasticsearch + Kibana의 경우, 쿼리 속도가 빠르기 때문에 실시간으로 대응해야 하는 라이브 데이터분석에, BigQuery + Data Studio의 경우, 데이터 수가 1M rows 미만이어도 쿼리 속도가 1초 이상 걸리고, 자체 자동 캐싱 시스템이 있지만 그래도 매 새로운 쿼리마다 비용이 청구되기 때문에, 매출 데이터 처럼 어느정도 배치 처리가 가능하고 요청도 그리 빈번하지 않은 프로젝트에 적용하는 것이 알맞을 것같습니다. 무엇보다도 완전관리형 서비스이기 때문에 설치 및 운영에 큰 공수가 들지 않는다는 점에서 프로젝트에 빠르게 적용해서 원하는 결과를 여러 방법으로 뽑아내고 공유할수 있다는 점이 가장 큰 장점 이라고느꼈습니다. 추가로, 광고 플랫폼별 API 리서치, Data Studio 대시보드 작업을 함께 진행해주신 웹개발팀 페이지님, BigQuery관련 인사이트를 제공해주신 gTech professional service팀 고락윤님께감사드립니다. Google BigQuery와 Data Studio를 활용한 앱 광고 매출 대시보드 개발기 was originally published in 더핑크퐁컴퍼니 기술 블로그 on Medium, where people are continuing the conversation by highlighting and responding to this story.