GA4 기본 화면(UI)의 탐색 분석은 데이터 보존 기간이 최대 14개월로 제한되고, 대량 데이터 조회 시 샘플링과 (기타) 그룹핑 현상이 발생해 진짜 충성 고객의 실체를 파악하기 어렵습니다. 이 글에서는 GA4와 빅쿼리(BigQuery)를 연동해 반복 구매 코호트(Cohort)와 고객 생애 가치(LTV)를 SQL로 직접 분석하는 방법을 단계별로 설명합니다. 실무에서 바로 쓸 수 있는 SQL 쿼리 템플릿과 비용 절감 팁도 함께 제공합니다.
이커머스 담당자라면 한 번쯤 이런 질문을 해봤을 겁니다.
"지난달 광고비를 써서 데려온 신규 고객이, 이번 달에도 다시 구매하고 있을까?"
GA4 기본 화면에서 이 질문에 명확히 답하려고 하면 금방 벽에 부딪힙니다. 이유는 세 가지입니다.
결론적으로 GA4 UI는 '대략적인 흐름'을 보는 도구이지, 유저 한 명 한 명의 반복 구매 이력을 추적하는 도구가 아닙니다.
구글 클라우드(GCP)가 제공하는 대용량 데이터 창고입니다. GA4에서 발생하는 모든 클릭, 페이지뷰, 구매 이벤트를 샘플링 없이 원본 그대로 쌓아두고, SQL(데이터 조회 언어)로 원하는 방식으로 꺼내볼 수 있습니다.
같은 시기에 특정 행동을 한 사람들을 하나의 그룹으로 묶어 시간 흐름에 따라 추적하는 방법입니다. 예를 들어 "2025년 1월에 처음 구매한 고객 집단이 3개월 후에도 구매를 유지하는 비율은 몇 %인가?" 를 보는 것이 코호트 분석입니다.
고객 한 명이 처음 구매한 시점부터 지금까지 우리 쇼핑몰에 가져다준 총 누적 매출액입니다. 광고비 대비 실제 수익성을 판단하는 가장 중요한 지표 중 하나입니다.
user_pseudo_id란?GA4가 각 브라우저(기기)에 부여하는 비식별 익명 ID입니다. 개인정보를 담지 않으면서도 동일 유저의 행동을 연결해 추적할 수 있게 해줍니다.
비용 참고: 구글 클라우드는 매월 10GB 무료 저장과 1TB 무료 쿼리 연산을 제공합니다. 소규모 이커머스나 스타트업이라면 추가 비용 없이 분석 환경을 유지할 수 있습니다.
연동이 완료되면 빅쿼리에는 아래 형태로 일별 테이블이 생성됩니다.
프로젝트ID.analytics_XXXXXXXXX.events_YYYYMMDDevent_name = 'purchase'ecommerce.purchase_revenue주의: GA4 무료 속성은 하루 최대 100만 개 이벤트까지만 빅쿼리로 무료 내보내기가 됩니다. 트래픽이 많은 서비스라면 필요한 이벤트만 선별해 내보내기를 설정해야 합니다.
아래 SQL은 각 유저의 최초 방문월을 코호트 기준으로 삼아, 이후 개월차(Month 0, 1, 2...)별로 구매 활동성과 누적 LTV를 계산하는 쿼리입니다.
-- GA4 BigQuery 이커머스 코호트 리텐션 및 누적 LTV 산출 쿼리
WITH user_first_visit AS (
SELECT
user_pseudo_id,
MIN(PARSE_DATE('%Y%m%d', event_date)) AS first_visit_date,
DATE_TRUNC(MIN(PARSE_DATE('%Y%m%d', event_date)), MONTH) AS cohort_month
FROM `your-gcp-project.analytics_xxxxxxxxx.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20260331'
GROUP BY user_pseudo_id
),
cohort_sizes AS (
SELECT cohort_month, COUNT(DISTINCT user_pseudo_id) AS cohort_size
FROM user_first_visit
GROUP BY cohort_month
),
user_purchases AS (
SELECT
user_pseudo_id,
PARSE_DATE('%Y%m%d', event_date) AS purchase_date,
DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), MONTH) AS purchase_month,
COALESCE(ecommerce.purchase_revenue, 0) AS revenue
FROM `your-gcp-project.analytics_xxxxxxxxx.events_*`
WHERE event_name = 'purchase'
AND _TABLE_SUFFIX BETWEEN '20250101' AND '20260331'
),
cohort_purchase_history AS (
SELECT
f.cohort_month, f.user_pseudo_id, p.purchase_month,
DATE_DIFF(p.purchase_month, f.cohort_month, MONTH) AS month_number,
SUM(p.revenue) AS total_revenue
FROM user_first_visit f
JOIN user_purchases p ON f.user_pseudo_id = p.user_pseudo_id
WHERE p.purchase_date >= f.first_visit_date
GROUP BY 1, 2, 3, 4
)
SELECT
h.cohort_month, s.cohort_size, h.month_number,
COUNT(DISTINCT h.user_pseudo_id) AS active_buyers,
ROUND(SUM(h.total_revenue), 2) AS period_revenue,
ROUND(SUM(SUM(h.total_revenue)) OVER(PARTITION BY h.cohort_month ORDER BY h.month_number ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS cumulative_revenue,
ROUND(SUM(SUM(h.total_revenue)) OVER(PARTITION BY h.cohort_month ORDER BY h.month_number ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) / s.cohort_size, 2) AS cumulative_ltv
FROM cohort_purchase_history h
JOIN cohort_sizes s ON h.cohort_month = s.cohort_month
GROUP BY h.cohort_month, s.cohort_size, h.month_number
ORDER BY cohort_month, month_number;이 쿼리의 결과를 읽는 방법은 간단합니다. cohort_month가 '2025-01'이고 month_number가 2라면, 2025년 1월에 처음 방문한 고객들이 2개월 후(3월)에 발생시킨 누적 LTV가 cumulative_ltv 컬럼에 표시됩니다.
month_number), 세로축: 신규 방문 월(cohort_month), 값: 누적 LTV(cumulative_ltv)로 피벗 테이블 구성비용 절감 필수 팁: 원시 이벤트 테이블을 Looker Studio에 직접 연결하면 대시보드를 새로고침할 때마다 쿼리 비용이 발생합니다. 반드시 빅쿼리 예약 쿼리(Scheduled Query)로 집계 테이블을 별도 생성한 뒤 그 테이블을 연결하세요. 쿼리 비용을 1/100 이하로 줄일 수 있습니다.
_TABLE_SUFFIX BETWEEN 날짜 필터를 반드시 적용했는가? (비용 폭탄 방지)your-gcp-project와 analytics_xxxxxxxxx를 본인 프로젝트 ID로 수정했는가?utm_medium 유실 여부를 별도로 검증했는가?빅쿼리로 GA4 데이터를 적재할 때, 구글 애즈(gclid 파라미터) 유입 세션의 utm_medium이나 utm_campaign이 누락되거나 (organic)으로 잘못 기록되는 경우가 있습니다. 채널별 LTV를 분석할 때 이 오류를 모르면 "광고 효율이 생각보다 낮다"는 잘못된 결론을 내릴 수 있습니다.
해결: 구글 애즈 링크 데이터를 별도로 결합하거나, UTM 파라미터 전처리 보정 로직을 쿼리에 추가해야 합니다.
SELECT * 남발로 인한 비용 폭탄SELECT * FROM events_*처럼 날짜 필터 없이 전체 테이블을 조회하면 수백 GB를 스캔해 예상치 못한 비용이 발생합니다. _TABLE_SUFFIX 필터는 선택이 아닌 필수입니다.
개인정보 동의를 거부한 사용자의 데이터는 빅쿼리에 수집되지 않습니다. 이 갭을 무시하면 코호트 모수가 실제보다 작게 잡혀 LTV가 과대 추정될 수 있습니다. 내부 결제 DB와 빅쿼리 데이터를 교차 검증하는 설계가 2026년 기준 필수 트렌드로 자리 잡았습니다.
Q1. 빅쿼리 연동에 개발자가 꼭 필요한가요? GA4와 빅쿼리 연동 설정 자체는 GA4 관리자 권한과 GCP 프로젝트만 있으면 마케터도 직접 할 수 있습니다. 다만 SQL 쿼리 작성과 집계 테이블 자동화는 데이터 분석가나 에이전시의 도움을 받는 것이 효율적입니다.
Q2. 소규모 쇼핑몰도 빅쿼리 분석이 필요한가요? 월 구매 건수가 수백 건 이하라면 GA4 UI로도 충분할 수 있습니다. 하지만 장기 LTV 추적, 채널별 반복 구매 비교, 2년 이상 데이터 보존이 필요하다면 규모와 관계없이 빅쿼리 연동이 유리합니다. 구글 클라우드의 월 1TB 무료 쿼리 한도 내에서 소규모 쇼핑몰은 사실상 무료로 운영 가능합니다.
Q3. user_pseudo_id만으로 분석해도 정확한가요?
기기가 바뀌거나 쿠키가 삭제되면 동일 고객이 다른 ID로 잡힙니다. 가능하다면 로그인 기반 user_id를 GA4에 함께 수집해 분석 정확도를 높이는 것을 권장합니다.
Q4. 코호트 분석 결과를 어떻게 마케팅에 활용하나요? Month 0 대비 Month 3의 잔존율이 급격히 낮아지는 코호트를 찾아, 해당 시점에 리타겟팅 광고나 이메일 캠페인을 집중 투입하는 방식으로 활용합니다. 또한 LTV가 높은 코호트의 최초 유입 채널을 분석해 광고 예산을 재배분하는 근거로 씁니다.
Q5. 빅쿼리 ML로 LTV를 예측할 수도 있나요?
네. 2026년 현재 BigQuery ML 함수를 이용해 과거 구매 패턴을 학습시키고, 향후 30일 이내 고LTV 고객을 자동 분류하는 SQL 기반 예측 모델 구축이 대중화되고 있습니다. 기초 코호트 분석 데이터가 충분히 쌓인 이후 단계로 고려해볼 수 있습니다.
| 용어 | 설명 |
|---|---|
| 코호트(Cohort) | 같은 시기에 특정 행동을 한 사용자 집단. 예: '2025년 1월 첫 구매자 그룹' |
| LTV(Lifetime Value) | 고객 한 명이 생애 동안 발생시킨 총 누적 매출액 |
| 빅쿼리(BigQuery) | 구글 클라우드의 대용량 데이터 웨어하우스. SQL로 대규모 데이터를 분석 가능 |
| 샘플링(Sampling) | 전체 데이터 대신 일부만 추출해 분석하는 방식. GA4 UI에서 데이터 왜곡의 주원인 |
_TABLE_SUFFIX |
빅쿼리에서 날짜별 테이블을 필터링하는 파라미터. 쿼리 비용 절감에 필수 |
| 예약 쿼리(Scheduled Query) | 지정한 시간에 자동으로 SQL을 실행해 집계 테이블을 갱신하는 빅쿼리 기능 |
| 동의 모드(Consent Mode v2) | 사용자의 쿠키 동의 여부에 따라 데이터 수집 범위를 조정하는 구글의 개인정보 보호 정책 |
| 카디널리티(Cardinality) | 특정 필드의 고유값 개수. 값이 너무 많으면 GA4 보고서에서 (기타)로 묶임 |
_TABLE_SUFFIX 날짜 필터와 집계 테이블 예약 쿼리는 반드시 적용하세요.GA4 빅쿼리 연동 설정부터 코호트·LTV 분석 대시보드 구축까지, 처음 혼자 진행하면 설정 오류나 쿼리 비용 문제로 시간을 낭비하는 경우가 많습니다.
에이달(ADALL)은 GA4 데이터 파이프라인 설계부터 빅쿼리 기반 이커머스 분석 환경 구축, Looker Studio 대시보드 연동까지 실무 중심으로 지원합니다. 귀사의 데이터 현황을 먼저 진단하고, 실질적인 분석 방향을 제안드립니다.
무료 컨설팅 문의
무료 컨설팅 받아보고 싶다면?
무료 컨설팅 신청하기