Bigquery 시작하기
with Firebase Analytics
# 글의 개요
최근 Firebase 쪽의 제품들이 굉장한 기세로 퀄리티를 올리고 있는데
트렌드에 맞춰 Firebase Analytics와 Bigquery를 사용해보려는 분들을 위해
간단하게 그동안 사용하며 겪은 고통의 경로를 공유하려고 한다. 고통 속에서 얻은 사소한 노하우도 함께.
# 빅쿼리를 쓰게 된 계기
나는 웹, 웹앱을 주로 기획해왔기 때문에 네이티브 앱의 기록을 볼 일이 없었다.
하여 이전에는 Google Analytics로도 충분했었으나 올해 초, 운영 중이던 서비스의 앱이 웹앱에서 네이티브 앱으로 전환되었다.
마침 파이어베이스의 구글 인수에 이은 공격적인 기능 개발과 공개가 되는 시점이었고 더 뛰어난 다른 대안도 없다고 판단했기 때문에 빠르게 사용해보는 것으로 결정하고 바로 진행되었다.
시작할 때에는 국내에 많은 사용자가 있지 않았는데, 덕분에 여차 저차 하여 운 좋게도 Firebase UX팀과 프러덕 매니저(무려 창업자 중 한 명)가 사용자 조사를 위해 직접 사무실에 방문해 만난 적도 있었다. 밥도 얻어먹고..
현재는 대시보드 > 스트림 뷰를 통해 조회할 순 있지만
당시에는 이벤트에 붙여 보낸 파라미터를 확인할 수 있는 방법이 빅쿼리를 통해 확인하는 방법밖에 없었기 때문에 어쩔 수 없이 빅쿼리와 연동해야 했는데, 일단 보내기 시작하고서는 비용이 부담이 거의 없었기 때문에 수집이 가능하다면 사소하더라도 뒤에 활용 생각하지 않고 전부 보내는 식으로 설계하고 실행했다.
네트워크 상태, 전송 패킷의 크기와 디바이스 환경을 고려해 한번에 묶어서 보내는 등의 처리를 해준다고 하니, 사용자 입장에서도 체감할 수 있을 수준이 아니라 부담 없었다.
잘 넣기는 했으나, 사실 이렇게 넣은 데이터를 언제 어떻게 뽑아볼지는 매우 막막했는데,
SQL을 직접 손으로 짜 본 게 몇 년 전이었고 기껏해야 테이블 네댓 개에 아주 단순한 조회 쿼리밖에 날려보지 않았기 때문이다. 운영 중인 DB에 쿼리를 날릴 생각은 전혀 못했는데, 실제 서비스에 부하를 일으킬 수도 있는 쿼리를 손쉽게 만들 수 있다는 점이 부담스러웠기 때문이다. 대부분 기획자들 다들 그렇겠지.. 싶다.
하여, 어쩔 수 없이 정말 몇 년만에 다시 SQL 강좌를 뒤져보았다.
# 글의 순서
자 그럼, 사용 안내를 그간 겪었던 고난의 길을 따라 정리해보자면.
일단 간단하게 UI 안내
더 간단하게 에디터 안내
Firebase Analytics와 연동하는 방법
Firebase Analytics에서 넘어온 데이터 안내
여러 테이블을 한 번에 보는 방법
Record Type 데이터를 보는 방법
조회 결과받아보기
# 간단하게 UI 안내.
처음 들어가면 보일 화면.최근의 구글 클라우드 콘솔은 다른 곳의 UI 못지않게 이쁘게 잘 뽑아놨는데,
이상하게도 빅쿼리 팀은 디자이너가 없는지 빅쿼리 UI의 모든 곳에서 개발자의 예술혼이 느껴진다.
개발자들 디자인 못한다고 비하하려는 것은 아니지만.. 아, 아니 비하가 맞다.
하지만 첫인상의 투박함과 공돌이 감성을 조금만 참고서 찬찬히 훑어보면 정말 필요한 물건들만 잘 놓인 것을 알 수 있다. 쓸데없이 일 벌이는 기획자 같은 건 없나 보다.
빅쿼리 UI에 대해서는 https://cloud.google.com/bigquery/bigquery-web-ui 이 링크에 설명이 잘 되어 있다. 긴 글이 아니기 때문에 읽어보길 권하지만 사용 설명서를 읽지 않는 사람을 위해 딱 포인트만 집어보자면.
왼쪽 메뉴에서는 [빨간 버튼, Query History, 내 프로젝트] 정도만 쓰게 된다.
오른쪽 화면에는 [쿼리 에디터, 쿼리 실행 결과, 실행 기록 조회] 정도를 보게 된다.
# 더 간단하게 에디터 안내
일단 누르라고 쓰여있는 빨간 버튼을 눌러 에디터를 열면 이런 게 열린다.
옵션을 열어보면,
이런 옵션들이 있는데, 쓸 일이 많지 않으므로 바로 닫아도 무관하다.
맨 밑의 Legacy SQL 사용 여부 옵션은 자주 변경하게 될 옵션일 테지만, 에디터에서 "#standardSQL"이라고 명시하는 것으로도 활성화되므로 굳이 옵션에서 눌러서 설정할 필요가 없다.
그리고 legacySQL과 standardSQL은 문법이 다르고 제공 함수도 다르기 때문에 웬만하면 하나만 쓰기를 권하고, 보통 Stackoverflow나 구글 그룹 류에서는 standardSQL이 더 많이 봤기 때문에 standardSQL이 더 작업하기 수월할 것이다.
에디터에서 쿼리를 작성하고 실행을 하면 에디터 아래에 결과가 출력된다.
이렇게 한번 쿼리를 실행한 이후에는 더 이상 처음 봤던 웰컴 화면을 볼 수 없다.
새 프로젝트를 만들면 볼 수 있으나 보통은 쿼리 먼저 테스트로 날려보기 십상이기 때문에 정말 보기 힘든 화면이다. 기억을 되살려 돌이켜보면, 이런 링크들이 들어간 환영 문구다.
https://cloud.google.com/bigquery/quickstart-web-ui
https://cloud.google.com/bigquery/cost-controls
https://cloud.google.com/bigquery/bigquery-web-ui
여러 가지 볼 것은 있지만 중요한 것은,
에디터에서 실행해 결과를 조회할 수 있고
조회한 결과를 CSV 파일로 다운로드하거나 다른 빅쿼리 테이블로 저장할 수 있다.
# Firebase Analytics로부터 데이터 가져오기
빅쿼리나 파이어베이스에 처음 들어갔다면 프로젝트와 데이터셑 모두 빈 상태로 시작하게 되는데
우선 파이어베이스 쪽에서 빅쿼리와의 연동 설정이 필요하다.
파이어베이스 앱에서 보낸 데이터를 빅쿼리로 보내려면 제일 먼저.
Firebase 요금제.종량제, Blaze 요금제를 써야 한다. 바꾸는 순간부터 돈 나간다.
요금제를 바꾸고 나면 설정 쪽에서 빅쿼리 연동 옵션이 활성화된다.
연결한다고 끝나지 않는다. 프로젝트가 빅쿼리에 연결은 되었으나 앱이 연결되지 않았다.
빅쿼리로 데이터를 보낼 앱을 선택해줘야 한다.
설정한다고 해서 바로 빅쿼리에 뜨지도 않는다.
데이터가 어느 정도 쌓인 다음에야 빅쿼리에 노출되기 시작한다.
개인적인 경험으로는 이벤트를 수백 개를 쏜 이후에 하루 정도가 지나야 데이터셑이 노출되었다.
요즘은 그렇지 않고 더 빨라진 것 같다.
여기서의 포인트는
데이터를 보내지 않은 앱일 경우 설정을 한다 해도 데이터셑이나 테이블이 만들어지지 않는다.
연동 설정 이후 데이터를 넣었다 해도 데이터셑과 테이블이 생성되는 데에 시간이 좀 걸린다.
# Firebase Analytics에서 넘어온 데이터 안내
잘 연동된 이후에는 알아서 테이블을 생성하고 잘 저장이 되는데 스키마에 대한 안내가 없다.
직접 적어 넣을 순 있긴 한데 빅쿼리나 파이어베이스 쪽에 설명이 없다.
이 필드 설명을 왜 내가 써야 하나. 구글 니가 넣었는데 니가 해줘야지..잘 찾아보면 https://support.google.com/firebase/answer/7029846 여기에 스키마 안내가 있다.
크게 두 개 Record로 구분되는데.
user_dim은 사용자 속성으로 넣은 값들이 들어가는 곳이고
event_dim은 이벤트 기록으로 쌓은 기록이 들어가는 곳이다.
# 여러 테이블을 한 번에 보는 방법
며칠 지나고 보면 알게 되는데, 파이어베이스 쪽에서 보내오는 데이터를 일 단위로 테이블 생성하고 저장한다.
음 그럼 내년이면 테이블이 365개 되겠네. 부자되겠어.테이블은 app_event_yyyymmdd 형식으로 쌓이게 되는데, 특이한 것은 오늘 날짜의 테이블은 조금 다르다.
app_event_intraday_yyyymmdd, intraday라는 이름이 더 붙게 되는데
오늘이 지나는 시점에 새 테이블이 만들어진 후 데이터를 옮기고 나서 삭제된다.
또, 고 시점에 새로운 intraday 테이블이 생성되는데, 데이터를 옮기는 작업과 새 테이블의 생성, 삭제 과정이 모두 비동기이므로 intraday 테이블이 두 개가 되는 때도 있다.
이 과정에서 굉장히 많은 테이블에 골고루 데이터가 쌓이게 되는데, 하루치 데이터만 볼 일은 많지 않기 때문에 곧 여러 날에 걸쳐 데이터를 조회할 일이 생긴다.
여러 가지 방법이 있지만 가장 간단한 방법은 와일드카드를 쓰는 방법이다.
#standardSQL
SELECT
column1, column2
FROM
`app_events_*`
이렇게 쿼리를 실행하면 모든 날짜의 column1, column2를 읽어온다.
모든 날짜가 아니라 특정 기간을 지정하고 싶다면
#standardSQL
SELECT
column1, column2
FROM
`app_events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170710' AND '20170712'
요롷게 _TABLE_SUFFIX를 사용하면 된다.
여러 테이블을 조인하거나, Legacy SQL의 TABLE_DATE_RANGE 함수를 사용하는 등의 다른 방법도 있다.
중요한 것은,
Firebase Analytics에서 bigquery로 저장할 때는 하루에 하나씩 테이블이 생성/저장된다는 것과
여러 날짜 걸쳐 조회는 와일드카드 걸면 쉽게 되지만 하는 만큼 돈이 나간다는 것.
되도록이면 조회 범위를 줄이고 조회 컬럼도 줄이고 해야...
물론 한 달 1 테라까지는 무료이므로 웬만큼 쑤셔 넣지 않고서는 넘기 힘들다. 테스트 데이터로는 어림없다.
# Record Type 데이터를 보는 방법
평소에 봐왔던 데이터 타잎은 String, int 같은 형태밖에 보질 못했는데
빅쿼리에는 뜬금없이 Record 타잎 데이터가 있다.
row안에 row가 있다.요즘 DB에 익숙한 개발자들이야 아는 얘기겠지만 나 같은 기획자가 이런 걸 봤을 리가 없고..
쉽게 생각하고 쿼리 날리면 에러 나기 십상이다.
진짜 자주보게 된다..사실 나는 ↓ 이 글을 보기 전까지는 정말 자주 보던 에러였다.
https://firebase.googleblog.com/2017/03/bigquery-tip-unnest-function.html
간단하게 정리하자면 Record Type으로 묶여서 저장된 데이터는 풀어서 가져와야 한다는 것이고.
customEvent1에 파라미터 키:밸류를 score:10000라고 보내 놓았다면 이렇게 확인할 수 있다.
#standardSQL
SELECT
event_param.value.int_value
FROM
`some_table_name_at_20170707`,
UNNEST(event_dim) as event,
UNNEST(event.params) as event_param
WHERE
event.name = "customEvent1"
AND event_param.key = "score"
AND event_param.value.int_value >= 10000
혹은,
#standardSQL
SELECT
(SELECT value.int_value FROM UNNEST(dim.params) WHERE key = "score") as score
FROM
`some_table_name_at_20170707`
WHERE
event.name = "customEvent1"
AND event_param.key = "score"
AND event_param.value.int_value >= 10000
이렇게 변태적으로 서브 쿼리를 짜서 원하는 파라미터 이름의 값만 가져올 수도 있다.
이 쿼리문은 항상 헷갈리는데 기억해두어야 할 것은.
하나의 Row 안에 여러 Row가 들어있을 수 있고.
unnest 함수를 사용해 flatten 시켜주어야 꺼내볼 수 있다.
# 조회 결과받아보기
이제 가져다가 자르고 더하고 해서 정리해야 할 텐데, 데이터를 뽑아내는 방법은 4가지 가 있다.
[CSV 다운로드, JSON 다운로드, 테이블에 저장, 스프레드시트에 저장]한 가지 문제가 있는데, 조회 결과가 조금 큰 경우 다운로드가 안된다.
이럴 때에는 "Save as Table"만 동작하게 된다.
필요한 건 CSV인데 테이블로 저장하라니 답답하긴 하지만
요롷게 일단 테이블로 저장하고서
Export Table을 눌러서
클라우드 스토리지 경로와 파일 이름을 지정해주면 스토리지에서 CSV를 다운로드할 수 있다.
간단하게 정리하자면.
조회 후 CSV나 JSON, SpreadSheet로도 저장이 가능하다.
용량이 크다고 다운로드가 안되면,
테이블로 저장하고 > 저장한 테이블을 export > 클라우드 스토리지에 저장하면 된다.
# 여기까지.
UDF를 사용하거나 외부 클라이언트를 사용해 데이터를 뽑아내는 등의 몇 가지 좀 더 고급 활용이 남아 있지만
이 정도면 대부분 필요한 데이터를 가져와 사용할 수 있다.
# 써보니 좋더라.
최근 업무의 대부분이 빅쿼리에서 뽑아낸 데이터로 돌아간다.
사용자가 뭘 보고 언제 어디에서 이탈하는지, 각 퍼널 사이의 전환율과 얼마나/어디로 이탈하는지, 사용자 세그먼트별 검색어의 차이라던가, 정말 사소하게는 문구 변화에 따른 전환율의 차이를 찾는다거나,
정말 앵간한 질문 모두 쿼리 한 줄로 답할 수 있다.
Remote Config와 User Atrribute를 활용해 A/B를 띄워놓고 데이터 수집해 찾아보는 등, 단순히 질문에 그치지 않는 더 적극적인 활용도 해보았고, 덕분에 정말 처음부터 끝까지 다수의 반복 개선을 진행하면서도 또렷하게 성과를 측정하는 경험도 해봤다.
잘만 활용하면 정말 쉽고 편하게, 강력하게 사용할 수 있다.
물론, 이런 설계를 구현해준 개발자 분들이 고생을 좀 하셨지만..
