구글 빅쿼리를 활용한 데이터 분석 사례

구글 빅쿼리(BigQuery)는 방대한 데이터를 빠르게 분석할 수 있는 강력한 도구입니다. 특히 마케팅, 광고, 사용자 행동 분석 등에 활용하면 인사이트를 도출하는 데 유용합니다. 이번 포스팅에서는 빅쿼리에서 접근할 수 있는 예시 데이터를 활용하여 다양한 분석을 수행하는 방법을 알아보겠습니다.

1. 핸드폰 기종별 광고 유입 채널 분석

분석 목표

사용자들이 어떤 핸드폰 기종을 사용하며, 주로 어떤 광고 채널을 통해 유입되는지를 분석합니다. 이를 통해 광고 전략을 최적화할 수 있습니다.

사용할 데이터셋

구글 빅쿼리는 bigquery-public-data.ga4_obfuscated_sample_ecommerce 데이터셋을 제공합니다. 이 데이터셋은 GA4(Google Analytics 4)와 유사한 구조를 가지고 있으며, 전자상거래 데이터를 포함하고 있습니다.

필요한 테이블과 주요 필드

• bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*
  • device.category (사용자 기기 종류: 모바일, 태블릿, 데스크톱)
  • device.mobileModelName (핸드폰 모델명: iPhone 14, Galaxy S23 등)
  • traffic.source (유입 소스: Google, Facebook, Instagram 등)
  • traffic.medium (유입 매체: cpc, organic, referral 등)

분석 SQL 예제

SELECT
  device.mobileModelName AS phone_model,
  traffic.source AS ad_source,
  COUNT(*) AS user_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE device.category = 'mobile'
GROUP BY phone_model, ad_source
ORDER BY user_count DESC
LIMIT 50;

분석 결과 활용

• 특정 핸드폰 기종 사용자들이 어떤 광고 채널을 통해 유입되는지 파악하여 맞춤형 광고 전략을 수립할 수 있습니다.
• 광고 예산을 특정 채널이나 기기별로 최적화할 수 있습니다.

---

2. 방문자 지역별 인기 제품 분석

분석 목표

사용자들이 거주하는 지역별로 어떤 제품이 가장 많이 판매되는지를 분석하여 지역 맞춤형 마케팅 전략을 세웁니다.

사용할 데이터셋

• bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

필요한 테이블과 주요 필드

• geo.country (사용자 국가)
• geo.city (사용자 도시)
• ecommerce.items.item_name (구매한 제품명)
• ecommerce.transaction_id (구매 식별 ID)

분석 SQL 예제

SELECT
  geo.country AS country,
  geo.city AS city,
  ecommerce.items.item_name AS product_name,
  COUNT(DISTINCT ecommerce.transaction_id) AS purchase_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE ecommerce.transaction_id IS NOT NULL
GROUP BY country, city, product_name
ORDER BY purchase_count DESC
LIMIT 50;

분석 결과 활용

• 특정 지역에서 인기 있는 제품을 파악하여 지역 맞춤형 프로모션을 진행할 수 있습니다.
• 물류 및 재고 관리를 최적화할 수 있습니다.

---

3. 재방문율이 높은 광고 채널 분석

분석 목표

어떤 광고 채널을 통해 들어온 사용자가 재방문(리텐션)이 높은지를 분석하여 효과적인 광고 전략을 세웁니다.

사용할 데이터셋

• bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*

필요한 테이블과 주요 필드

• user_pseudo_id (사용자 고유 식별자)
• traffic.source (유입 소스)
• event_bundle_sequence_id (이벤트 순서 ID, 재방문 여부 분석에 활용 가능)
• event_timestamp (이벤트 발생 시간)

분석 SQL 예제

WITH first_visit AS (
  SELECT
    user_pseudo_id,
    traffic.source AS ad_source,
    MIN(event_timestamp) AS first_visit_time
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  GROUP BY user_pseudo_id, ad_source
),
repeat_visit AS (
  SELECT
    fv.ad_source,
    COUNT(DISTINCT rv.user_pseudo_id) AS repeat_users
  FROM first_visit fv
  JOIN `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` rv
  ON fv.user_pseudo_id = rv.user_pseudo_id
  WHERE rv.event_timestamp > fv.first_visit_time + 86400000 -- 1일 이후 재방문
  GROUP BY fv.ad_source
)
SELECT ad_source, repeat_users
FROM repeat_visit
ORDER BY repeat_users DESC;

분석 결과 활용

• 재방문율이 높은 광고 채널을 중심으로 마케팅 예산을 조정할 수 있습니다.
• 충성 고객을 유치할 수 있는 광고 전략을 수립할 수 있습니다.

---

마무리

이번 포스팅에서는 빅쿼리를 활용한 3가지 분석 사례를 다뤘습니다.

1. 핸드폰 기종별 광고 채널 분석: 핸드폰 모델별 유입 광고 채널을 분석하여 광고 최적화
2. 방문자 지역별 인기 제품 분석: 특정 지역에서 많이 판매되는 제품을 분석하여 지역별 마케팅 전략 수립
3. 재방문율이 높은 광고 채널 분석: 광고 채널별 재방문율을 분석하여 광고 효율성 극대화

이처럼 구글 빅쿼리는 방대한 데이터를 빠르게 분석하고, 데이터 기반 의사 결정을 내리는 데 유용한 도구입니다. 마케팅 및 사용자 분석에 활용하면 더욱 정교한 전략을 수립할 수 있으니, 직접 실습해 보길 추천합니다!