결국 빅쿼리까지 가게 된 이유

전 편에서 AI와 앱스크립트로 RAW 가공을 자동화하는 데 성공 했지만, 데이터스튜디오(루커스튜디오)는 결국 용량, 속도, UI 3가지 문제를 다 못풀었어요. 이 문제들을 근본적으로 풀려면 결국 구글 빅쿼리가 필요하다는 걸 알고는 있었는데, 결제카드 등록이라는 진입장벽 때문에 미뤄왔었죠. 이번엔 그 장벽을 넘어서 AI의 도움을 받아 빅쿼리로 이전하기로 했어요

데이터셋 구조 – 갈아끼우는 데이터 vs 쌓이는 데이터

RAW 데이터를 구글 스프레드시트에 업로드해서 빅쿼리로 보내는 방식과 빅쿼리에서 데이터셋을 만들어서 적재하는 방식 2가지가 있는데, 저는 전자를 선택했어요 그리고 저희는 업 특성상 빅쿼리로 데이터를 넣을 때, 광고매체 데이터랑 어드민 데이터는 업데이트되는 방식 자체가 다르게 설정해야 했어요.

광고매체 데이터는 구글 스프레드 시트에 업로드하면 그걸 빅쿼리로 반영하고, 기존 스프레드 시트는 삭제하는 방식이에요. 매체에 다운 받은 데이터는 앞선 데이터가 변경될 일이 없었기 때문이에요. 반면 어드민 RAW는 성격이 달라요. 업 특성상 시간이 지나서 실적 데이터가 업데이트 될 수 있었거든요 그래서 앞선 날짜에서 실적이 변경되면 업데이트가 필요해서 삭제가 아니라 누적하는 방식으로 만들었어요. 이렇게 “갈아끼우는 데이터” 와 “쌓이는 데이터” 를 구분해서 설계해두니까, 나중에 RAW_data 테이블을 관리하기가 편했어요

전체 구조 한눈에 보기

1. 구글시트 RAW 입력
2. 앱스크립트 code.gs에서 uploadAllRawToBigQuery로 실행
3. BigQuery raw_data 데이터셋(원본테이블)에 데이터 적재
4. BigQuery 뷰 체인 생성 (report_data, 데이터셋, 매핑, 집계, 파생지표)
5. 앱스크립트 웹앱 (code.gs, google.scritp.run)
6. 웹대시보드 배포관리 (HTML/JS)

*뷰 = 원본 테이블을 직접 건드리지 않고, 그 위에 필요한 가공 로직(매핑, 집계 등)만 얹어서 보여주는 일종의 “가상 테이블”

위의 내용이 최종적으로 잡힌 구조에요. 여기서 뷰는 한 방에 다 처리하는게 아니라, 매핑 > 집계 > 최종 형태로 단계를 나눠서 쌓아뒀어요. 이렇게 나눠둔 이유는 뒤에서 다룰 집계 버그 얘기할 때 좀 더 와닿을거예요. 말로만 보면 복잡해보이는데, 결국 하는 일은 이전 편들이랑 똑같아요. 매체별로 다른 원본들 표준화하고, 광고랑 전환 데이터를 연결하고, 원하는 형태로 집계해서 보여주는 것. 그걸 이제 엑셀/앱스크립트가 아니라 빅쿼리 위에서 하는거죠.

매체별로 다른 원본 구조를 하나로 합치기

네이버SA, GFA, 카카오, 구글, 메타 등 광고매체마다 원본 데이터 구조가 다 다른건 엑셀때부터 겪던 문제였잖아요. 빅쿼리에서는 이걸 UNION ALL로 합치고, 캠페인/광고그룹을 채널, 매체, 상품으로 매핑해주는 인덱스 테이블을 따로 만들어서 표준화했어요. 사실 이 인덱스 테이블이 하는 역할이 1편에서 만들었던 엑셀의 INDEX 시트랑 개념적으로 똑같아요. 다만 엑셀에서는 함수로 값을 채웠다면, 여기서는 매핑 테이블을 조인해서 값을 붙이는 방식으로 바뀐거고요.

광고 데이터와 전환 데이터 어떻게 붙였나

이것도 계속 풀어온 숙제였는데, 근본적인 어려움은 항상 같았어요. 광고쪽 데이터는 “누가 전환됐는지” 가 없고 어드민쪽 데이터는 “어떤 광고를 봤는지”가 UTM 값으로만 남아있거든요 그래서 날짜+SOURCE+MEDIUM+CAMPAIGN 조합으로 두 데이터를 조인했어요. 여기서 한걸음 더 들어가서, 키워드 단위 성과까지 보고 싶었기 때문에 TERM값 ㄲ가지 매칭해서 키워드별 전환 귀속까지 구현했어요.

표준 주차대신 비교용 주차 계산하기

주차별 성과를 볼때 중요한건 기간을 맞춰주는거에요. 월마다 보면 첫주, 마지막주는 대게 7일을 다 채우지 않아요. 2일일수도 5일일수도 그때그때 다르죠 저는 주차별 성과는 7일을 온전히 다 그대로 비교해야한다고 생각해요. 그래서 월~일 기준으로 주차가 계산되도록 하고 더 일수가 많은 쪽에 월을 부여하는 방식으로 구현이 필요했어요. 그래서 AI한테 얘기하니 표준 ISO를 사용하는게 아닌 이 로직을 BigQuery의 JavaScript UDF(사용자 정의 함수)로 작성해줬어요. 표준 함수로 해결이 안되는 기준이 있다면 이렇게 직접 함수를 만들어서 풀수도 있다는걸 AI가 알려줬어요.

집계 단계에서 생긴 중복 버그

여기서 한 번 제대로 걸렸는데요, 특정 필터(기기구분)를 집계 단계(GROUP BY)에 넣었다가 조인 키가 그 필터를 반영하지 못하면서 전환 수치가 중복 집계된 적이 있었어요. 원인을 찾아보니 “필터링” 과 “집계” 를 같은 단계에서 처리하려고 한 게 문제였더라구요. 그래서 필터링은 집계와 완전히 분리해서, 순수하게 필터용 역할만 하는 뷰를 따로 만들고 그걸 안전하게 조인하는 방식으로 바꿨어요. 단순히 버그 하나를 고쳤다기 보다는, “필터와 집계는 같은 단계에 섞지 않는다”는 원칙 자체를 AI가 짚어준 내용이었어요.

대시보드를 직접 만들면서 만난 벽들

데이터쪽 작업이 끝나고 나서는 이걸 보여줄 웹 대시보드를 만드는 과정에서도 몇 번 걸렸어요. 처음엔 대시보드 JS가 자기 자신의 API 주소를 fetch()로 호출하는 구조였는데, 구글의 인증/리다이렉트 체계때문에 간헐적으로 실패하더라구요. 이건 앱스크립트 전용 클라이언트-서버 통신 방식인 google.script.run으로 바꾸면서 근본적으로 해결되었어요.

또 하나는 테이블 UI 쪽이이었는데, 정렬 가능한 테이블에 헤더랑 합계를 행을 스크롤해도 고정됙게(position:sticky) 만들려고 했는데 이게 계속 안먹혔어요. 알고보니 border-collaapse: collapse랑 position: sticky가 충돌하는 꽤 알려진 브라우저 이슈였더라구요. 이걸 찾아내고 해결하는 데도 시간이 좀 걸렸어요.

이 모든 과정을 거치고 나니, 리포트 갱신이 “수기로 집계하기” 에서 “시트에 데이터 입력 후 버튼 한 번” 으로 바뀌었어요. 처리하는 데이터량 자체는 크지 않아서 빅쿼리 무료 샤용량 범위 안에서 사용 가능하다는 것도 확인했고 모바일에서도 확인할 수 있게 반응형으로 만들었어요.

그래도 남은 삽질들

큰 흐름은 이렇게 완성 했지만 그 과정에서 자잘한 삽질도 꽤 있었어요.

1. 빅쿼리 지역(리전) 불일치로 job을 못 찾는 에러
2. 재배포할 때 권한 재승인을 빠뜨려서 반복된 인증 실패
3. 캠페인명에서 특수 표기가 안맞아서 광고비가 잘못 매핑된 문제
4. 구글 시트 업로드 중복 실행으로 특정 날짜에 데이터가 2배로 잡힌 문제

이런 내용들을 만들고나서 다 실제로 검증을 해봐야지만 찾을 수 있는 내용들이라 구축하는데 시간과 노력이 꽤나 소모 되었어요. 그래도 예전 같으면 이런 부분들이 개발적인 지식들이 필요하다보니 막히면 앞으로 나아갈 수 없었는데 AI chat이 도래하고나서부턴 과정이 험난해도 결국은 해결 가능한 방향으로 진행되더라구요 위에서 작업한 내용들도 사실 저는 방향과 내용 검증만 했지 AI가 다 한거거든요. 이렇게 구축한 웹 대비소브에서 에러가 발생한 내용을 상세하게 한 번 더 풀어볼 예정이에요