[데이터 전처리] GA4 유실 데이터 맵핑 자동화: 스프레드시트 수동 관리에서 Bigquery로 자동화

1. 배경: 데이터 분석 환경의 확장과 한계

초기 데이터 분석 환경 구축 시, 구글 스프레드시트(Google Sheets)와 루커 스튜디오(Looker Studio)의 조합은 쉽고 유효한 선택지였습니다. 별도의 데이터 웨어하우징 없이도 데이터 소스를 연결하고 시각화할 수 있었기 때문입니다.

외부 플랫폼을 통해 유입되는 캠페인 트래픽을 분석할 때 일부 플랫폼의 특성상 웹사이트 주소인page_location 값은 존재하지만, GA4 수집 시점의 이슈로 인해 Page Title이 NULL로 수집되는 경우가 종종 발생합니다. Page Title이 아니더라도 이러한 현상은 전반적 필드에 종종 발생하는데 이를 보완하기 위해 지금까지는 다음과 같은 수동 프로세스를 유지해 왔습니다.

1. 기록된 URL, 또는 id와 맵핑할 실제 값을 스프레드시트에 수기 기록
2. 루커 스튜디오의 '데이터 혼합(Data Blending)' 기능을 이용해 GA4 데이터(또는 Bigquery)와 조인
3. 대시보드에서 맵핑된 이름 확인

이 방식은 초기 단계에서는 유효했으나, 데이터 규모가 커지고 id 등 데이터 수가 증가함에 따라 명확한 한계가 느껴졌습니다. 루커 스튜디오 내에서의 혼합테이블(스프레드 시트 연동) 연산 부하로 인해 대시보드 조회 속도가 저하되었고, 무엇보다 신규 데이터가 생성될 때마다 담당자가 스프레드시트를 업데이트하지 않으면 데이터가 누락되는 '휴먼 에러'가 발생했습니다. 이는 데이터의 신뢰성과 업무 효율성을 저해하는 주된 요인이 되었습니다. 작은 이슈인 것처럼 보여도 실무에서는 이런 문제가 결국 대시보드 전체를 신뢰하지 못하는 큰 이슈로 발화되고는 합니다.

따라서 2026년의 시작과 함께, 이러한 수동 의존적인 프로세스를 제거하고 BigQuery 기반의 데이터 파이프라인으로 로직을 이관하여 완전 자동화를 구축하는 것을 목표로 삼았습니다.

2. 문제 정의 및 설계 방향

핵심 문제는 누락된 데이터의 결측치(NULL)를 어떻게 신뢰성 있게 복구할 것인가였습니다.

이번 케이스의 경우 URL은 https://sample-service.com/{Unique_ID} 형태의 구조를 가집니다. Page Title이 누락되더라도 URL 내의 고유 ID({Unique_ID})는 보존됩니다. 이를 바탕으로 다음과 같은 가설을 세우고 기획을 진행했습니다.

• Dictionary(사전) 구축:과거에 단 한 번이라도 해당 ID로 정상 수집된 로그가 있다면, 이를 'Reference Data'로 활용할 수 있다.
• ETL 시점의 보정: 시각화 단계(View Level)가 아닌, 데이터 적재 단계(Mart Level)에서 결측치를 보정해야 조회 성능과 데이터 정합성을 확보할 수 있다.

이를 구현하기 위해 BigQuery 내에서 [과거 로그 분석] -> [맵핑 테이블 생성] -> [일일 적재 시 자동 조인]으로 이어지는 파이프라인을 구상했습니다.

3. 실행 과정과 기술적 난관

설계된 로직을 실제 쿼리로 구현하는 과정에서, 예상치 못한 몇 가지 기술적 난관에 직면했습니다. 단순한 테이블 조인(Join)으로는 해결되지 않는 데이터의 복잡성 때문이었습니다.

3.1. URL 파라미터로 인한 매칭 실패

첫 번째 시도에서는 URL(page_location)을 기준으로 맵핑 테이블과 조인을 시도했으나, 매칭률이 현저히 낮았습니다. 원인을 분석한 결과, 마케팅 성과 측정을 위한 쿼리 파라미터(Query String)가 원인이었습니다.

• Reference URL: .../campaign_A
• Actual Log URL: .../campaign_A?utm_source=google&_gl=1*xyz...

문자열이 일치하지 않아 조인이 실패하는 문제를 해결하기 위해, URL 정규화(Normalization) 전처리를 도입했습니다. BigQuery의 SPLIT 함수를 사용하여 ? 이후의 파라미터를 제거하고, TRIM 및 RTRIM 함수를 통해 URL 끝의 슬래시(/) 유무로 인한 불일치 케이스를 방어했습니다. 이를 통해 조인 키(Key)의 일관성을 확보할 수 있었습니다.

3.2. BigQuery STRUCT 구조 수정의 제약

GA4 데이터는 event_params가 중첩된 STRUCT(구조체) 배열 형태를 띱니다. 단순한 컬럼 업데이트가 아니라, 구조체 내부의 data_id, data_name 필드를 수정하면서 동시에 데이터를 집계해야 했습니다.

1. Unique Events 집계: 먼저 원본 데이터를 기준으로 GROUP BY를 수행하여 중복을 제거합니다.
2. Mapping 및 Replace: 집계된 결과셋에 대해 REPLACE 구문을 사용하여 NULL 값을 맵핑 테이블의 값으로 치환(COALESCE)합니다.

이러한 구조 변경을 통해 쿼리의 실행 계획(Execution Plan)을 최적화하고 문법적 오류를 해결했습니다.

3.3. 비정형 URL 패턴과 정규표현식(Regex) 적용

가장 까다로웠던 문제는 URL 경로가 고정적이지 않다는 점이었습니다. 대부분은 domain.com/{ID} 형태였으나, 인증 페이지나 완료 페이지의 경우 domain.com/view/{ID}/certification과 같이 ID가 경로 중간에 위치하는 패턴 또한 발견되었습니다.

단순히 URL 뒷부분을 추출하는 로직으로는 이러한 가변적인 경로를 커버할 수 없었습니다. 이에 따라 정규표현식(Regular Expression)을 도입하여 추출 로직을 고도화했습니다.

REGEXP_EXTRACT(page_location, r'domain\.com/(?:view/)?([a-zA-Z0-9]+)')

위 정규식을 통해 URL의 구조가 변경되더라도 핵심이 되는 {ID} 값만을 정확하게 추출할 수 있게 되었습니다.

4. 로직 고도화: 이중 조인(Dual Join) 전략

정규표현식을 통해 ID를 추출했음에도 불구하고, URL 매칭만으로는 해결되지 않는 엣지 케이스들이 존재했습니다. 이를 해결하기 위해 이중 조인(Dual Join) 전략을 수립하여 맵핑 성공률을 극대화했습니다.

• 1차 매칭 (URL 기반): 정제된 URL이 맵핑 테이블의 URL과 정확히 일치하는 경우, 해당 제목을 사용합니다. 가장 정확도가 높은 방식입니다.
• 2차 매칭 (ID 기반): 1차 매칭에 실패하더라도, 추출된 ID가 맵핑 테이블에 존재하는 ID와 일치한다면 해당 정보를 사용합니다. 이는 URL 경로가 달라지더라도 캠페인 ID가 같다면 동일한 캠페인으로 간주하는 로직입니다.

SQL 내에서 LEFT JOIN을 두 번 수행하고, COALESCE(Map_URL.title, Map_ID.title, Original.title) 함수를 통해 우선순위에 따라 값을 적용하도록 구현했습니다. 이 전략을 통해 파생 페이지나 예외적인 URL 패턴에서도 정확한 캠페인명을 복구할 수 있게 되었습니다.

5. 운영 효율화: 수동 개입의 최소화 (MERGE)

자동화된 로직으로 대부분의 데이터를 처리할 수 있게 되었지만, GA4 수집 누락으로 인해 맵핑 테이블 자체에 데이터가 없는 '콜드 스타트(Cold Start)' 문제는 여전히 남아있었습니다.

이를 위해 엔지니어가 필요시 수동으로 데이터를 주입할 수 있는 보조 파이프라인을 구축했습니다. 기존의 CREATE OR REPLACE 방식은 수동 입력 데이터를 초기화시키는 문제가 있었기에, MERGE 문을 활용하여 로직을 변경했습니다.

• 자동 수집된 데이터는 주기적으로 업데이트(Update/Insert) 됩니다.
• 관리자가 수동으로 입력한 데이터 또한 MERGE 로직에 의해 보존되거나 갱신됩니다.

이제 담당자는 쿼리 실행 한 번으로 누락된 메타 데이터를 영구적으로 시스템에 반영할 수 있으며, 이는 다음 스케줄링부터 자동으로 전체 데이터에 적용됩니다.

6. 결론 및 성과

이번 데이터 엔지니어링 작업의 결과로 다음과 같은 성과를 얻을 수 있었습니다.

1. 운영 리소스 절감: 스프레드시트를 관리하고 루커 스튜디오를 수정하는 반복 업무가 제거되었습니다. 시스템은 매일 아침 자동으로 최신 맵핑 정보를 학습하고 적용합니다.
2. 데이터 정합성 향상: 사람의 실수(Human Error)로 인한 누락이 방지되었으며, 과거 데이터에 대해서도 동일한 로직을 소급 적용하여 데이터의 일관성을 확보했습니다.
3. 조회 성능 개선: 무거운 조인 연산을 데이터 마트 적재 시점으로 이관함으로써, 시각화 도구의 조회 속도가 대폭 개선되었습니다.

이번 작업은 단순히 도구를 변경한 것이 아니라, 데이터 처리의 구조적 비효율을 기술적으로 해결했다는 데에 의의가 있습니다. 실무에서 발생하는 '불편함'을 '엔지니어링 과제'로 정의하고, BigQuery의 기능을 적극 활용하여 해결해 나가는 과정은 데이터 파이프라인의 고도화가 조직의 생산성에 어떻게 기여할 수 있는지를 확인하는 계기가 되었습니다.