BigQuery로 마케팅 데이터 분석하기: SQL 쿼리 예시와 실전 활용법

왜 마케터가 BigQuery를 알아야 하는가

GA4 대시보드로 볼 수 있는 데이터는 빙산의 일각입니다.

GA4 UI에서는:

• 세그먼트 비교가 4개로 제한
• 데이터가 많으면 샘플링되어 정확도 하락
• 커스텀 퍼널을 자유롭게 구성하기 어려움
• 채널 간 크로스 분석이 불가능
• 데이터 보존 기간이 14개월

BigQuery에서는:

• 세그먼트 무제한 비교
• 원본 데이터 100% 분석 (샘플링 없음)
• SQL로 어떤 분석이든 가능
• 네이버, 카카오 등 외부 데이터와 조인
• 데이터 영구 보존

이 글에서는 마케터가 실제로 사용할 수 있는 BigQuery SQL 쿼리와 분석 방법론을 소개합니다.

---

BigQuery 시작하기: GA4 데이터 연동

비용: 대부분 무료

항목	무료 범위	초과 시 비용
저장	10GB/월	$0.02/GB
쿼리	1TB/월	$5/TB
GA4 내보내기	무료	-

일반적인 기업 사이트 기준: 월 100만 PV 이하라면 저장과 쿼리 모두 무료 범위 내입니다.

연동 방법 (5분)

1. GA4 관리 → BigQuery 연결 → BigQuery 프로젝트 선택
2. 내보내기 유형: "일별" 선택 (실시간도 가능하지만 비용 발생)
3. 24시간 후 데이터 테이블 생성 시작

연동하면 analytics_XXXXXXXX.events_YYYYMMDD 형태의 테이블이 매일 자동 생성됩니다.

---

실전 SQL 쿼리 모음

1. 기본: 일별 유저/세션/PV 추이

SELECT
  event_date,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNT(DISTINCT CONCAT(user_pseudo_id,
    CAST((SELECT value.int_value FROM UNNEST(event_params)
    WHERE key = 'ga_session_id') AS STRING))) AS sessions,
  COUNTIF(event_name = 'page_view') AS pageviews
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250201' AND '20250228'
GROUP BY event_date
ORDER BY event_date

2. 채널별 전환 분석

어떤 유입 채널이 실제 전환(문의, 구매)을 발생시키는지:

WITH conversions AS (
  SELECT
    user_pseudo_id,
    event_date,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'medium') AS medium,
    event_name
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250201' AND '20250228'
)
SELECT
  COALESCE(source, '(direct)') AS source,
  COALESCE(medium, '(none)') AS medium,
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  COUNTIF(event_name = 'lead_submit') AS conversions,
  SAFE_DIVIDE(
    COUNTIF(event_name = 'lead_submit'),
    COUNT(DISTINCT user_pseudo_id)
  ) * 100 AS conversion_rate_pct
FROM conversions
GROUP BY source, medium
HAVING total_users >= 10
ORDER BY conversions DESC

3. 퍼널 분석 (단계별 이탈률)

홈 → 서비스 페이지 → 문의 페이지 → 문의 제출 퍼널:

WITH user_events AS (
  SELECT
    user_pseudo_id,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'page_location') AS page_url
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250201' AND '20250228'
)
SELECT
  'Step 1: 홈페이지 방문' AS funnel_step,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM user_events WHERE page_url LIKE '%hypemarc.com/'

UNION ALL

SELECT
  'Step 2: 서비스 페이지' AS funnel_step,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM user_events
WHERE page_url LIKE '%/agents%'
   OR page_url LIKE '%/hypemarc-ai%'
   OR page_url LIKE '%/marketing%'

UNION ALL

SELECT
  'Step 3: 문의 페이지' AS funnel_step,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM user_events WHERE page_url LIKE '%/contact%'

UNION ALL

SELECT
  'Step 4: 문의 제출' AS funnel_step,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM user_events WHERE event_name = 'lead_submit'

ORDER BY funnel_step

4. 코호트 분석 (첫 방문 주 기준 리텐션)

WITH first_visit AS (
  SELECT
    user_pseudo_id,
    MIN(PARSE_DATE('%Y%m%d', event_date)) AS first_date
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250228'
  GROUP BY user_pseudo_id
),
activity AS (
  SELECT DISTINCT
    e.user_pseudo_id,
    PARSE_DATE('%Y%m%d', e.event_date) AS activity_date,
    f.first_date,
    DATE_DIFF(PARSE_DATE('%Y%m%d', e.event_date), f.first_date, WEEK) AS week_number
  FROM `project.analytics_XXXXXX.events_*` e
  JOIN first_visit f ON e.user_pseudo_id = f.user_pseudo_id
  WHERE _TABLE_SUFFIX BETWEEN '20250101' AND '20250228'
)
SELECT
  FORMAT_DATE('%Y-W%V', first_date) AS cohort_week,
  week_number,
  COUNT(DISTINCT user_pseudo_id) AS active_users
FROM activity
WHERE week_number <= 8
GROUP BY cohort_week, week_number
ORDER BY cohort_week, week_number

5. 네이버 vs 구글 유입 비교

SELECT
  CASE
    WHEN source LIKE '%naver%' THEN '네이버'
    WHEN source LIKE '%google%' THEN '구글'
    WHEN source LIKE '%kakao%' THEN '카카오'
    WHEN source LIKE '%meta%' OR source LIKE '%facebook%'
         OR source LIKE '%instagram%' THEN '메타'
    WHEN medium = '(none)' THEN '직접 유입'
    ELSE '기타'
  END AS channel,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNTIF(event_name = 'page_view') AS pageviews,
  COUNTIF(event_name = 'lead_submit') AS leads,
  ROUND(SAFE_DIVIDE(
    COUNTIF(event_name = 'lead_submit'),
    COUNT(DISTINCT user_pseudo_id)
  ) * 100, 2) AS lead_rate_pct
FROM (
  SELECT
    user_pseudo_id,
    event_name,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'source') AS source,
    (SELECT value.string_value FROM UNNEST(event_params)
     WHERE key = 'medium') AS medium
  FROM `project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20250201' AND '20250228'
)
GROUP BY channel
ORDER BY users DESC

6. 시간대별 전환 패턴

최적의 광고 송출 시간을 찾기 위한 분석:

SELECT
  EXTRACT(HOUR FROM TIMESTAMP_MICROS(event_timestamp)
    AT TIME ZONE 'Asia/Seoul') AS hour_kst,
  COUNT(DISTINCT user_pseudo_id) AS users,
  COUNTIF(event_name = 'lead_submit') AS conversions,
  ROUND(SAFE_DIVIDE(
    COUNTIF(event_name = 'lead_submit'),
    COUNT(DISTINCT user_pseudo_id)
  ) * 100, 2) AS conversion_rate
FROM `project.analytics_XXXXXX.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20250201' AND '20250228'
GROUP BY hour_kst
ORDER BY hour_kst

---

광고 데이터 통합 분석

네이버 + 구글 + 카카오 광고 데이터를 BigQuery에서 통합

GA4 데이터만으로는 **광고비 대비 성과(ROAS)**를 정확히 계산할 수 없습니다. 각 광고 플랫폼의 비용 데이터를 BigQuery에 함께 저장해야 합니다.

-- 광고 플랫폼 데이터가 BigQuery에 별도 테이블로 저장되어 있다고 가정
WITH ad_costs AS (
  SELECT date, 'naver' AS platform,
         SUM(cost) AS cost, SUM(clicks) AS clicks
  FROM `project.ads.naver_daily`
  WHERE date BETWEEN '2025-02-01' AND '2025-02-28'
  GROUP BY date

  UNION ALL

  SELECT date, 'google' AS platform,
         SUM(cost) AS cost, SUM(clicks) AS clicks
  FROM `project.ads.google_daily`
  WHERE date BETWEEN '2025-02-01' AND '2025-02-28'
  GROUP BY date

  UNION ALL

  SELECT date, 'kakao' AS platform,
         SUM(cost) AS cost, SUM(clicks) AS clicks
  FROM `project.ads.kakao_daily`
  WHERE date BETWEEN '2025-02-01' AND '2025-02-28'
  GROUP BY date
),
conversions AS (
  SELECT
    PARSE_DATE('%Y%m%d', event_date) AS date,
    CASE
      WHEN source LIKE '%naver%' THEN 'naver'
      WHEN source LIKE '%google%' THEN 'google'
      WHEN source LIKE '%kakao%' THEN 'kakao'
    END AS platform,
    COUNT(*) AS conversion_count
  FROM (
    SELECT event_date, event_name,
      (SELECT value.string_value FROM UNNEST(event_params)
       WHERE key = 'source') AS source
    FROM `project.analytics_XXXXXX.events_*`
    WHERE event_name = 'lead_submit'
      AND _TABLE_SUFFIX BETWEEN '20250201' AND '20250228'
  )
  GROUP BY date, platform
)
SELECT
  a.platform,
  SUM(a.cost) AS total_cost,
  SUM(a.clicks) AS total_clicks,
  SUM(c.conversion_count) AS total_conversions,
  ROUND(SAFE_DIVIDE(SUM(a.cost), SUM(c.conversion_count))) AS cost_per_conversion,
  ROUND(SAFE_DIVIDE(SUM(a.cost), SUM(a.clicks))) AS avg_cpc
FROM ad_costs a
LEFT JOIN conversions c ON a.date = c.date AND a.platform = c.platform
GROUP BY a.platform
ORDER BY total_cost DESC

이 쿼리 하나로 네이버, 구글, 카카오의 CPA를 직접 비교할 수 있습니다. GA4 UI에서는 절대 불가능한 분석입니다.

---

BigQuery의 한계와 하이프마크.AI의 역할

BigQuery는 강력하지만...

과제	설명
SQL 필요	마케터 전원이 SQL을 쓸 수 있는 건 아님
데이터 파이프라인	네이버/카카오 API → BigQuery 자동화 직접 구축 필요
시각화	BigQuery 자체에는 대시보드 기능 없음 (Looker Studio 별도)
인사이트	데이터를 보여줄 뿐, "왜?"와 "다음에 뭘 해야 하는지?"는 알려주지 않음
실시간 알림	이상 탐지와 자동 알림 시스템 별도 구축 필요

하이프마크.AI는 BigQuery 위에 얹는 인텔리전스 레이어

하이프마크.AI 대시보드는 BigQuery를 백엔드로 사용하면서, 마케터에게 필요한 모든 것을 추가합니다:

1. SQL 없이 분석 — 위의 복잡한 쿼리들이 클릭 한 번으로 실행
2. 자동 데이터 수집 — 네이버, 카카오, 메타 API 연동을 자동으로 처리
3. AI 내러티브 — "이번 주 네이버 ROAS가 15% 하락한 이유는 키워드 X의 CPC 급등 때문입니다. 대안 키워드 Y로 전환을 권장합니다."
4. 실시간 대시보드 — 채널별 성과를 한 화면에서 실시간 확인
5. 자동 알림 — 예산 소진, 전환율 급감, ROAS 하락 시 즉시 슬랙/이메일 알림
6. 세그먼트 인사이트 — 50개 이상의 사전 정의 세그먼트 + 커스텀 생성

BigQuery의 파워 + 마케터 친화적 인터페이스 + AI 자동 분석 = 하이프마크.AI

---

시작하기

BigQuery를 직접 활용하고 싶다면 이 글의 SQL 쿼리를 복사해서 시작하세요. GA4 → BigQuery 연동은 5분이면 됩니다.

SQL 없이 BigQuery급 분석을 원한다면, 하이프마크.AI 대시보드를 체험해보세요. 설치 없이 데이터를 연동하면 바로 인사이트를 받아볼 수 있습니다.

하이프마크.AI 무료 체험 신청 →