GCP BigQuery에서 전자상거래 데이터 분석하기: 중복 제거부터 전환율 계산까지 실습 가이드

이 글은 BigQuery를 처음 사용하는 데이터 분석 입문자를 위한 실습 기록입니다.
문제는 SQL 문법을 조금 알아도 실제 전자상거래 데이터셋에서 무엇부터 확인하고 어떤 순서로 분석해야 하는지 감이 잘 안 잡힌다는 점입니다.
이 글을 통해 BigQuery에서 전자상거래 데이터를 분석하고, 중복 데이터를 확인한 뒤, 방문자 수·상품 조회수·전환율까지 계산하는 기본 흐름을 실제 실습 기준으로 따라 할 수 있습니다.

이 글의 핵심 질문
BigQuery에서 전자상거래 데이터를 분석할 때, 중복 데이터를 제거하고 전환율을 정확하게 계산하려면 어떻게 해야 하는가?

---

실습 환경

• Cloud: Google Cloud Platform
• 서비스: BigQuery
• 데이터셋: data-to-insights.ecommerce
• 테이블: all_sessions_raw, all_sessions
• 쿼리 방식: Standard SQL
• 분석 대상: Google Analytics 기반 전자상거래 웹 로그

---

아키텍처

이번 실습의 구조는 단순합니다. 전자상거래 사이트의 로그가 Google Analytics를 통해 수집되고, BigQuery의 원본 테이블과 정제 테이블로 들어온 뒤, SQL 분석을 거쳐 보고서나 시각화 도구로 연결되는 흐름입니다.

Google Analytics Logs → BigQuery all_sessions_raw → BigQuery all_sessions → SQL Analysis → Report / Visualization

---

실습 목표

이 실습의 목표는 단순히 SELECT 문을 실행하는 것이 아닙니다. 실제 분석 전에 데이터를 검증하고, raw 데이터와 정제 데이터를 구분한 뒤, 분석 지표를 올바르게 계산하는 흐름을 익히는 것입니다.

특히 다음 3가지를 얻는 것이 핵심입니다.

• 중복 데이터가 분석 결과를 어떻게 왜곡하는지 이해하기
• 정제된 테이블을 기준으로 방문자 수와 조회수를 계산하기
• 상품별 전환율을 SQL로 계산하기

---

■ 강사 설명

실습은 먼저 all_sessions_raw 테이블에서 중복 가능성을 확인하고, 이후 중복이 제거된 all_sessions 테이블을 기준으로 분석을 진행하도록 설계되어 있습니다.

즉, “바로 분석”이 아니라 데이터 품질 확인 → 분석용 테이블 선택 → 핵심 지표 계산의 순서를 익히는 실습입니다.

---

■ 내가 이해한 핵심

이 실습에서 가장 중요한 건 SQL 문법이 아니라 분석 전에 데이터를 믿지 말고 먼저 검증해야 한다는 점입니다.

실무에서는 raw 데이터를 바로 집계하면 결과가 커 보일 수 있습니다. 하지만 그 값이 실제보다 부풀려졌는지, 중복 때문에 왜곡됐는지는 먼저 확인하지 않으면 알 수 없습니다.

즉, 좋은 분석은 복잡한 함수가 아니라 좋은 시작 순서에서 나옵니다.

---

■ 내가 실제로 겪은 문제

처음에 raw 테이블을 바로 조회했을 때는 방문자 수와 조회수가 꽤 크게 나왔습니다. 처음에는 “데이터가 원래 이런가 보다”라고 생각했습니다.

하지만 실습을 따라가며 중복 검사를 해보니 원인은 분명했습니다. 원본 로그에는 중복 레코드가 존재할 수 있고, 이를 그대로 집계하면 조회수와 전환율 해석이 틀어질 수 있다는 점입니다.

---

실습 단계

1단계. BigQuery에서 공개 프로젝트 연결하기

목적: 분석할 전자상거래 데이터셋을 내 BigQuery 환경에서 보이게 만드는 단계입니다.

먼저 BigQuery 콘솔로 이동한 뒤 + Add를 클릭합니다. 그리고 Star a project by name를 선택한 후 아래 프로젝트명을 입력합니다.

data-to-insights

이 과정을 거치면 탐색기(Explorer)에서 data-to-insights 프로젝트가 표시되고, 그 안의 ecommerce 데이터셋과 테이블들을 확인할 수 있습니다.

처음 BigQuery를 사용하는 입장에서는 이 단계부터 헷갈릴 수 있습니다. 데이터셋이 기본 화면에 자동으로 보이지 않기 때문입니다. 그래서 공개 프로젝트를 수동으로 연결해야 합니다.

---

2단계. raw 테이블 구조 먼저 확인하기

목적: 분석 전에 데이터 구조와 문제 가능성을 파악하는 단계입니다.

이제 data-to-insights.ecommerce.all_sessions_raw 테이블을 선택합니다. 여기서 최소 3개 탭을 꼭 확인하는 것이 좋습니다.

• Schema: 어떤 컬럼이 있는지
• Details: 테이블 메타데이터
• Preview: 실제 샘플 행

샘플 행을 조금만 스크롤해 봐도 이 테이블이 단순한 고객 목록이 아니라 웹 세션, 페이지, 상품 이벤트 등이 섞여 있는 로그 데이터라는 점을 알 수 있습니다. 즉, 한 방문자도 여러 행을 만들 수 있고, 한 상품도 여러 번 등장할 수 있습니다.

여기서 중요한 관찰은 한 줄을 유일하게 식별하는 단일 키가 보이지 않는다는 점입니다. 이 말은 곧, 중복 여부를 확인하려면 단순 ID 하나만 보는 것이 아니라 여러 컬럼을 함께 묶어서 확인해야 한다는 뜻입니다.

---

3단계. raw 테이블의 중복 데이터 찾기

목적: 분석 전에 데이터 품질 문제를 직접 확인하는 단계입니다.

이 실습의 첫 번째 진짜 핵심이 바로 여기입니다. 중복 여부를 확인하기 위해 실습에서는 모든 주요 컬럼을 기준으로 GROUP BY를 수행하고, 같은 값 조합이 두 번 이상 나타나는 행만 찾습니다.

#standardSQL
SELECT COUNT(*) as num_duplicate_rows, * FROM
`data-to-insights.ecommerce.all_sessions_raw`
GROUP BY
fullVisitorId, channelGrouping, time, country, city,
totalTransactionRevenue, transactions, timeOnSite, pageviews,
sessionQualityDim, date, visitId, type, productRefundAmount,
productQuantity, productPrice, productRevenue, productSKU,
v2ProductName, v2ProductCategory, productVariant, currencyCode,
itemQuantity, itemRevenue, transactionRevenue, transactionId,
pageTitle, searchKeyword, pagePathLevel1, eCommerceAction_type,
eCommerceAction_step, eCommerceAction_option
HAVING num_duplicate_rows > 1;

이 쿼리의 핵심은 “고유키가 없어도 중복을 찾을 수 있다”는 데 있습니다. 모든 필드를 묶었을 때 같은 조합이 두 번 이상 나오면, 적어도 분석 관점에서는 중복 레코드로 볼 수 있습니다.

초보자가 여기서 자주 놓치는 점은, 단순히 COUNT(*)만 하는 것이 아니라 HAVING num_duplicate_rows > 1로 중복만 걸러낸다는 것입니다. 즉, SQL은 단순 조회만이 아니라 데이터 검증 도구로도 강력하게 쓸 수 있습니다.

실무에서도 이 단계는 아주 중요합니다. 설령 고유키가 있다고 해도, 실제 적재 과정에서 중복이 생길 수 있기 때문에 분석 전 확인은 거의 필수에 가깝습니다.

---

4단계. 정제 테이블에서 중복이 없는지 다시 확인하기

목적: 이제 실제 분석에 사용할 수 있는 테이블인지 검증하는 단계입니다.

실습에서는 중복을 제거한 테이블인 all_sessions를 별도로 제공합니다. 하지만 “정제 테이블이니까 무조건 깨끗하겠지”라고 생각하면 안 됩니다. 실제로 다시 한 번 확인해봐야 합니다.

#standardSQL
SELECT
fullVisitorId,
visitId,
date,
time,
v2ProductName,
productSKU,
type,
eCommerceAction_type,
eCommerceAction_step,
eCommerceAction_option,
transactionRevenue,
transactionId,
COUNT(*) as row_count
FROM
`data-to-insights.ecommerce.all_sessions`
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12

실습 문서 기준으로 이 쿼리는 중복 결과를 반환하지 않습니다. 즉, 분석은 이제 all_sessions_raw가 아니라 all_sessions를 기준으로 해야 합니다.

이 단계에서 얻는 가장 큰 교훈은 명확합니다. 분석용 테이블은 raw가 아니라 검증된 테이블이어야 한다는 점입니다.

---

5단계. 전체 조회수와 고유 방문자 수 계산하기

목적: 가장 기본적인 사이트 규모 지표를 계산하는 단계입니다.

#standardSQL
SELECT
 COUNT(*) AS product_views,
 COUNT(DISTINCT fullVisitorId) AS unique_visitors
FROM `data-to-insights.ecommerce.all_sessions`;

이 쿼리에서 COUNT(*)는 전체 행 수를 기준으로 조회수를 계산하고, COUNT(DISTINCT fullVisitorId)는 실제 중복 없는 고유 방문자 수를 계산합니다.

처음 SQL을 배울 때는 둘 다 단순 count처럼 보일 수 있지만, 의미는 완전히 다릅니다. 하나는 “이벤트 수”이고, 다른 하나는 “사람 수”입니다. 전자상거래 분석에서는 이 차이를 정확히 구분해야 합니다.

---

6단계. 채널별 고유 방문자 수 비교하기

목적: 어떤 유입 채널이 더 많은 실제 방문자를 데려오는지 확인하는 단계입니다.

#standardSQL
SELECT
 COUNT(DISTINCT fullVisitorId) AS unique_visitors,
 channelGrouping
FROM `data-to-insights.ecommerce.all_sessions`
GROUP BY channelGrouping
ORDER BY channelGrouping DESC;

이 쿼리를 실행하면 채널 그룹별 방문자 수를 비교할 수 있습니다. 예를 들어 Organic Search, Referral, Direct 같은 채널이 실제로 얼마나 다른 성과를 내는지 확인하는 기초가 됩니다.

이 단계는 단순 집계처럼 보이지만, 이후 광고 성과 분석, 유입 효율 비교, 전환 채널 분석으로 확장되는 시작점입니다.

---

7단계. 전체 상품 목록과 대표 상품 확인하기

목적: 데이터셋에 어떤 상품들이 들어 있는지 파악하는 단계입니다.

#standardSQL
SELECT
 (v2ProductName) AS ProductName
FROM `data-to-insights.ecommerce.all_sessions`
GROUP BY ProductName
ORDER BY ProductName

실습 문서 기준으로 이 쿼리는 총 633개 상품 행을 반환합니다. 즉, 이 데이터셋이 생각보다 다양한 상품군을 포함하고 있다는 뜻입니다. 이렇게 전체 상품 목록을 한 번 훑어보는 작업은 이후 인기 상품 분석이나 카테고리 분류 전에 매우 유용합니다. :contentReference[oaicite:0]{index=0}

---

8단계. 가장 많이 조회된 상품 Top 5 구하기

목적: 사이트 내에서 사용자 관심이 가장 많이 모인 상품을 찾는 단계입니다.

#standardSQL
SELECT
 COUNT(*) AS product_views,
 (v2ProductName) AS ProductName
FROM `data-to-insights.ecommerce.all_sessions`
WHERE type = 'PAGE'
GROUP BY v2ProductName
ORDER BY product_views DESC
LIMIT 5;

여기서 WHERE type = 'PAGE' 조건이 중요합니다. Google Analytics 로그에는 page 외에도 event, transaction, item 등 여러 interaction type이 섞여 있으므로, “상품을 봤다”는 의미를 더 명확히 하려면 page 유형만 필터링하는 것이 좋습니다. 실습 문서도 이 기준을 제시합니다. :contentReference[oaicite:1]{index=1}

즉, 단순히 COUNT만 하는 것이 아니라 무엇을 조회로 볼 것인지 먼저 정의하는 것이 중요합니다.

---

9단계. 같은 사용자의 반복 조회를 제거한 상품 조회수 계산

목적: 상품 조회수를 더 보수적이고 현실적으로 해석하는 단계입니다.

앞선 쿼리는 같은 사용자가 같은 상품을 여러 번 본 경우도 모두 조회수로 계산합니다. 그런데 경우에 따라서는 “한 사람이 한 상품을 봤는지 여부”가 더 중요할 수 있습니다. 이럴 때는 WITH 구문으로 중간 결과를 먼저 만들어 처리합니다.

WITH unique_product_views_by_person AS (
SELECT
fullVisitorId,
(v2ProductName) AS ProductName
FROM `data-to-insights.ecommerce.all_sessions`
WHERE type = 'PAGE'
GROUP BY fullVisitorId, v2ProductName
)
SELECT
 COUNT(*) AS unique_view_count,
 ProductName
FROM unique_product_views_by_person
GROUP BY ProductName
ORDER BY unique_view_count DESC
LIMIT 5

이 방식의 핵심은 “한 사람당 한 상품은 한 번만 센다”는 데 있습니다. 즉, 단순 인기도가 아니라 도달 범위를 보는 데 더 적합합니다.

초보자 입장에서는 여기서 WITH가 낯설 수 있지만, 복잡한 분석을 단계별로 쪼개는 데 매우 강력한 방식입니다. 실무 SQL에서 자주 쓰이므로 반드시 익혀둘 가치가 있습니다.

---

10단계. 주문 수량과 전환율 계산하기

목적: 상품 조회가 실제 주문으로 얼마나 이어졌는지 계산하는 단계입니다.

#standardSQL
SELECT
 COUNT(*) AS product_views,
 COUNT(productQuantity) AS potential_orders,
 SUM(productQuantity) AS quantity_product_added,
 (COUNT(productQuantity) / COUNT(*)) AS conversion_rate,
 v2ProductName
FROM `data-to-insights.ecommerce.all_sessions`
WHERE LOWER(v2ProductName) NOT LIKE '%frisbee%'
GROUP BY v2ProductName
HAVING quantity_product_added > 1000
ORDER BY conversion_rate DESC
LIMIT 10;

이 쿼리는 실습의 핵심 지표 계산 단계입니다. 여기서 의미를 정확히 해석해야 합니다.

• product_views: 상품이 노출된 총 횟수
• potential_orders: 주문에 연결된 횟수
• quantity_product_added: 총 수량
• conversion_rate: 주문 횟수 / 조회수

여기서 frisbee를 제외한 이유는 특정 상품군이 분석 결과를 왜곡할 수 있기 때문입니다. 또한 HAVING quantity_product_added > 1000 조건으로 충분한 표본이 있는 상품만 남겨, 우연한 결과가 아니라 의미 있는 비교가 가능하게 합니다. :contentReference[oaicite:2]{index=2}

이 단계에서 가장 중요한 것은 “전환율은 수식이 아니라 정의”라는 점입니다. 어떤 이벤트를 조회로 보고, 어떤 값을 주문으로 볼지에 따라 전환율은 완전히 달라질 수 있습니다. SQL 실력보다 더 중요한 것은 이 정의를 명확히 잡는 사고방식입니다.

---

실습 증거

1. raw 테이블에서 중복 확인

중복 확인 쿼리는 num_duplicate_rows > 1인 결과를 반환하도록 설계되어 있습니다. 이 자체가 raw 데이터는 바로 분석에 쓰면 안 된다는 증거입니다. :contentReference[oaicite:3]{index=3}

2. 정제 테이블에서 중복 없음 확인

all_sessions 테이블 검증 쿼리는 중복이 없음을 보여줍니다. 실습 문서도 이 쿼리가 0개의 중복 레코드를 반환한다고 설명합니다. :contentReference[oaicite:4]{index=4}

3. 상품 목록과 인기 상품 분석

상품명 전체 목록 조회는 633개 상품을 반환하며, 페이지 6의 결과 화면은 상품명 리스트와 조회수 기반 분석 예시를 보여줍니다. :contentReference[oaicite:5]{index=5}

---

트러블슈팅

문제 증상:
조회수나 주문 관련 수치가 예상보다 비정상적으로 크거나 해석이 애매하다.

원인 분석:
raw 데이터의 중복, 동일 사용자의 반복 조회, PAGE 이외의 interaction type 포함 등이 원인일 수 있다.

확인 방법:
GROUP BY + HAVING으로 중복을 먼저 확인하고, type 필터와 DISTINCT 여부를 검토한다.

해결 방법:
정제된 all_sessions 테이블을 기준으로 분석하고, 필요 시 방문자 단위 중복 제거 쿼리를 별도로 작성한다.

재발 방지 방법:
분석 시작 전에 항상 “테이블 신뢰도 확인 → 이벤트 정의 확인 → 집계 기준 확인” 순서로 점검한다.

---

실무 핵심 포인트

이번 실습에서 가장 중요한 교훈은 명확합니다.

SQL은 숫자를 뽑는 도구가 아니라, 데이터의 신뢰성을 검증하고 의미 있는 지표를 정의하는 도구입니다.

같은 데이터셋이라도 raw를 쓰느냐, 정제 테이블을 쓰느냐, 동일 사용자의 반복 조회를 제거하느냐에 따라 결과는 크게 달라집니다. 그래서 실무에서는 쿼리 한 줄보다 먼저 “이 수치가 무엇을 의미하는가?”를 정의해야 합니다.

---

결론

핵심 원칙
전자상거래 데이터 분석은 조회부터 시작하는 것이 아니라, 중복과 이벤트 정의를 검증하는 것부터 시작해야 합니다.

실무 적용 시 주의점

• raw 테이블을 바로 집계하지 말 것
• 중복 여부를 먼저 검증할 것
• 조회수와 방문자 수, 주문 수의 기준을 분명히 나눌 것
• 전환율 계산 시 표본 수가 충분한 상품만 비교할 것

다음 학습 단계 제안
다음에는 이 데이터를 BigQuery ML로 연결해 구매 가능성을 예측하거나, Looker Studio와 연결해 대시보드로 확장해보면 좋습니다.