비개발자를 위한 빅쿼리(BigQuery) #1 - BizSpring BLOG
핵심 요약
- 비개발자도 구글 빅쿼리(BigQuery)를 활용할 수 있도록 기본 쿼리문을 소개하는 콘텐츠입니다.
- GA4로 수집된 RAW 데이터가 빅쿼리에서 활용 가능해지면서 빅쿼리 활용 범위가 넓어질 것이라는 배경을 설명합니다.
- 테이블 구조, RECORD 데이터 유형(구조체·배열)과 실습용 google_analytics_sample 데이터세트를 소개합니다.
- SELECT, DISTINCT, COUNT, GROUP BY, AS, ORDER BY, DESC, WHERE, _TABLE_SUFFIX, BETWEEN, HAVING 등 기본 쿼리 문법을 예제로 설명합니다.
- 글 마지막에 실습 문제(Q1, Q2)를 제시하며 다음 콘텐츠에서 정답을 공개한다고 안내합니다.
비개발자를 위한 빅쿼리(BigQuery) #1 - BizSpring BLOG
콘텐츠로 건너뛰기
내비게이션 메뉴
인사이트
테크
활용 방법/사례
활용 방법/사례
성공사례
성공 사례 일반
광고/마케팅 에이전시
이커머스
미디어/콘텐츠
금융/핀테크
의료/헬스케어
통신/인터넷
뉴스/트렌드
릴리즈 노트
인터넷트렌드 ↗
웹사이트 ↗
# 비개발자를 위한 빅쿼리(BigQuery) #1
2020년 12월 04일 2023년 04월 14일
테크
이 콘텐츠는 구글 빅쿼리를 활용하고자 하는 분들을 위한 콘텐츠입니다. 비개발자 분들에게 좀 더 적합합니다.
비개발자분들을 대상으로 구글 빅쿼리(BigQuery)에 대해 이야기하는 이유는 두가지입니다.
① 필자와 같이 비개발자지만 적절한 시기에 필요한 데이터를 추출하여 활용하거나 데이터 접근 권한이 없더라도 개발자 분에게 데이터를 요청할 때, 보다 빠른 커뮤니케이션을 할 수 있도록 기본적인 쿼리문에 대해서 공유하고자 합니다.
② 최근 Google Analytics가 GA4로 업그레이드 되면서 GA로 수집되는 RAW 데이터를 빅쿼리에서 활용할 수 있기 때문에 앞으로 빅쿼리의 활용 범위가 점차 넓어질 것이기 때문입니다.
[테이블 구조]
먼저 테이블의 구조를 알아보겠습니다.
테이블은 컬럼과 로우로 구성되어 있습니다. 우리가 필요한 컬럼을 선택하여 필요한 데이터를 추출하게 됩니다.
[기본적인 데이터 유형]
대표적인 데이터 유형은 아래와 같습니다. 가장 많이 사용되는 데이터 유형입니다.
데이터 유형 중 꼭 알아야하는 데이터 유형은 RECORD 입니다.
RECORD 유형을 이해하기 위해서는 구조체(Structure)와 배열(Array)를 이해해야 합니다.
구조체는 서로 다른 데이터유형의 여러 변수를 그룹 지어 새로운 자료형을 정의하는 것입니다.
배열은 서로 같은 데이터유형의 연속성을 가진 여러 변수를 그룹 지어 새로운 자료형을 정의하는 것입니다.
[실습에 활용할 데이터세트]
그럼 직접 실습할 데이터세트는 “google_analytics_sample” 데이터입니다.
google_analytics_sample 데이터 세트의 데이터를 확인해 보겠습니다.
[실습에 활용할 google_analytics_sample 데이터세트]
출처 : https://support.google.com/analytics/answer/3437719?hl=ko
우리가 다룰 google_analytics_sample 데이터 세트의 데이터 스키마를 한번씩 살펴봐야 합니다.
google_analytics_sample 데이터세트에는 어떤 데이터가 존재하는지,
그리고 데이터 유형은 무엇인지 등 데이터에 대한 정책이 정의되어 있습니다.
자. 그럼 이제부터 데이터를 추출해 보도록 하겠습니다.
SELECT * FROM 테이블명
현재 Fruit_20201101이라는 테이블이 존재한다고 가정해보겠습니다.
Fruit_20201101 테이블의 전체 데이터를 추출하기 위해서는 SELECT절에 “*”를 입력하고 FROM 절에 “Fruit_20201101” 테이블명을 입력합니다.
SELECT 뒤에 “*”는 모든 컬럼을 선택하게 됩니다.
SELECT 컬럼명 FROM 테이블명
Fruit_20201101 테이블의 전체 컬럼이 아닌 내가 필요한 컬럼만 추출하고 싶다면
SELECT절에 추출하고 싶은 컬럼명을 입력합니다.
만약 여러 개의 컬럼을 추출하고 싶다면 쉼표(,)로 구분하여 여러 컬럼명을 입력합니다.
제가 Fruit_20201101 테이블의 데이터 중 “과일명” 컬럼의 데이터만 추출하고 싶다면
“SELECT 과일명 FROM Fruit_20201101” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트로 실습해 보도록 하겠습니다.
추출하고자 하는 데이터는 2017년 7월 1일 중복을 포함한 전체 방문자 리스트를 추출하고자 합니다.
DISTINCT 함수
추출하고자 하는 컬럼의 데이터 중 중복을 제거한 데이터만 추출하고자 하는 경우,
중복을 제거하고 싶은 컬럼명 앞에 “DISTINCT” 함수를 입력합니다.
Fruit_20201101 테이블의 “과일명” 컬럼의 데이터 중 중복을 제거한 과일명만 확인하고 싶다면
“SELECT DISTINCT 과일명 FROM Fruit_20201101” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일의 중복을 제거한 방문자 리스트를 추출해 보도록 하겠습니다.
COUNT 함수, GROUP BY 절
이번에는 과일명 테이블의 각 과일별로 개수를 추출하고자 합니다.
COUNT는 컬럼의 데이터수를 카운트하여 추출합니다.
GROUP BY 절은 선택한 컬럼의 같은 데이터끼리 묶어주게 됩니다.
그래서 각 과일별 개수 데이터를 추출하고 싶다면
“SELECT 과일명, COUNT(과일명) FROM Fruit_20201101 GROUP BY 과일명” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자별 방문횟수를 추출해 보도록 하겠습니다.
AS 절
아마 위의 “SELECT 과일명, COUNT(과일명) FROM Fruit_20201101 GROUP BY 과일명” 쿼리문을 실행시키면 COUNT(과일명) 컬럼의 컬럼명이 “f0_”로 보여집니다.
AS는 Aliases의 약자로 별칭을 뜻합니다.
즉 AS 절을 활용하여 별칭(가칭)을 만들 수 있습니다.
기존 f0_ 컬럼명을 AS 절을 활용하여 count로 추출하고 싶다면
“SELECT 과일명, COUNT(과일명) AS count FROM Fruit_20201101 GROUP BY 과일명” 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자별 방문횟수를 추출하고 방문횟수의 컬럼명은 “visit_count”로 추출해보겠습니다.
ORDER BY 절, DESC
ORDER BY절은 선택한 데이터를 정렬할 때 사용합니다.
DESC는 내림차순으로 정렬할 때 사용합니다.
그럼 위에서 과일명 별 개수를 추출했는데요. 개수가 많은 순으로 추출하고 싶다면
“SELECT 과일명, COUNT(과일명) AS count FROM Fruit_20201101 GROUP BY 과일명 ORDER BY count DESC” 쿼리문을 실행시키면 개수가 많은 과일명 순으로 추출됩니다.
그럼 google_anlaytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자별 방문횟수 중 visit_count가 많은 순으로 추출해 보도록 하겠습니다.
WHERE 절
WHERE 절은 추출하고자 하는 데이터의 조건을 넣어 추출할 때 사용됩니다.
만약 과일명과 농장명 데이터를 추출하는데 A농장 데이터만 추출하고 싶다면
SELECT 과일명, 농장명 FROM Fruit_20201101
WHERE 농장명 = ‘A농장’
GROUP BY 과일명, 농장명
쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 미국에서 접속한 방문자별 방문횟수를 “visit_count”가 많은 순으로 추출해 보도록 하겠습니다.
_TABLE_SUFFIX, BETWEEN a AND b 절
google_analytics_sample 데이터세트 뿐만 아니라 최근 업데이트된 GA4 데이터가 빅쿼리에 적재될 때 하루하루 데이터가 하나의 테이블로 생성됩니다.
google_analytics_sample 데이터세트의 테이블명을 보시면
.ga.sessions_2017-07-01 … .ga.sessions_2017-07-31 각각 테이블이 생성되어 있는 것을 확인할 수 있습니다.
이 때 FROM 절에 날짜 부분을 *로 입력한 후 WHERE 절에 _TABLE_SUFFIX와 BETWEEN 절을 사용하여 추출하고 싶은 기간의 데이터를 추출할 수 있습니다.
그럼 예시를 통해 원하는 기간의 데이터를 추출하는 방법을 알아보도록 하겠습니다.
2020년 11월 1일 ~ 2020년 11월 2일 간의 개수가 많은 과일명 별 개수를 추출하고 싶다면
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 전체 방문자별 방문횟수를 “visit_count”가 많은 순으로 추출해 보도록 하겠습니다.
HAVING 절
HAVING 절은 WHERE 절과 같이 조건을 넣어 추출할 때 사용됩니다.
WHERE 절과 HAVING 절의 차이는 무엇일까요?
WHERE은 FROM에 사용하는 TABLE의 데이터에서 조건을 추가할 때 사용됩니다.
HAVING은 GROUP BY절을 통해 그룹으로 묶인 데이터에서 조건을 추가할 때 사용됩니다.
예를 들어 “SELECT 과일명, COUNT(과일명) AS count FROM Fruit_20201101 GROUP BY 과일명”
쿼리문을 실행하면 과일명 별 count수를 확인할 수 있는데요.
이 중 count수가 1 초과한 과일명을 추출하고 싶다면 쿼리문을 실행시키면 아래와 같이 데이터가 추출됩니다.
참고
위의 쿼리문에서 WHERE절로 조건을 추가하는 경우 Fruit_20201101 테이블에는 count 컬럼이 없기 때문에 에러가 발생합니다.
그럼 google_analytics_sample 데이터 세트에서
2017년 7월 1일 전체 방문자 중 visit_count가 2이상인 방문자를 추출해 보겠습니다.
그럼 문제입니다.
Q1. google_analytics_sample 데이터세트에서 2017년 7월 1일 유입소스별 방문횟수 데이터를 추출해 보시기 바랍니다.
Q2. google_analytics_sample 데이터세트에서 2017년 7월 전체의 미국국가의 지역별 방문횟수를 추출해 보시기 바랍니다.
정답은 다음 콘텐츠에서 공개하겠습니다.
감사합니다.
Related Posts via Categories
구글애즈가 정확검색·구문검색 키워드까지 AI 모드에 노출하기 시작했다 — 자동화 상품 없이 AI 답변 화면에 들어가는 첫 실험
AI가 React 앱을 직접 디버깅 – Agent 기반 디버깅
ChatGPT 광고가 픽셀·전환 API·맞춤 오디언스를 갖췄다 — 답변 화면이 측정 가능한 광고 매체가 되는 순간
AI 시대, 데이터도 설명이 필요합니다
MCP 클라이언트는 Authorization Server를 어떻게 찾는가: 공식 MCP TypeScript SDK와 VS Code 구현 비교
RAG 시대의 GEO: AI가 콘텐츠를 읽는 방식과 스키마의 진짜 역할
AI 에이전트로 웹 데이터 수집 가능 여부 자동 검증하기
GA4 Intraday 실시간 테이블, 대시보드 원천 데이터로 바로 사용할 수 있을까?
워크플로우 오케스트레이션 플랫폼 Temporal 알아보기
iOS 14.5부터 GA4까지, 환경 변화가 가져온 업무 폭증을 해결하는 실무 전략은…?
다음에 대해 검색하기...
최신 글 둘러보기
기존 SEO와 무엇이 같고 무엇이 다른가
콘텐츠가 답인 걸 모르는 사람은 없습니다 — 문제는 지속입니다
광고비를 늘리지 않고 매출을 늘린 회사들은 무엇을 했나
코호트로 비교하면 광고 예산 판단이 달라집니다
GEO를 위한 글쓰기를 위한 작은 노하우
(해외동향) 광고주가 광고플랫폼 리포트에서 자체 측정으로 옮겨가고 있다.
구글애즈가 정확검색·구문검색 키워드까지 AI 모드에 노출하기 시작했다 — 자동화 상품 없이 AI 답변 화면에 들어가는 첫 실험
AI가 React 앱을 직접 디버깅 – Agent 기반 디버깅
ChatGPT 광고가 픽셀·전환 API·맞춤 오디언스를 갖췄다 — 답변 화면이 측정 가능한 광고 매체가 되는 순간
마케팅 자동화, 무엇부터 자동화해야 할까?
출처: https://blog.bizspring.co.kr/%ed%85%8c%ed%81%ac/%eb%b9%84%ea%b0%9c%eb%b0%9c%ec%9e%90-%eb%b9%85%ec%bf%bc%eb%a6%ac-bigquery-1/
자주 묻는 질문
이 콘텐츠는 어떤 사람들을 위한 내용인가요?
구글 빅쿼리를 활용하고자 하는 비개발자 분들을 위한 콘텐츠입니다. 개발자가 아니어도 필요한 데이터를 추출하거나 개발자와 커뮤니케이션할 때 도움이 되도록 기본 쿼리문을 다룹니다.
왜 지금 빅쿼리를 배워야 하나요?
Google Analytics가 GA4로 업그레이드되면서 GA로 수집되는 RAW 데이터를 빅쿼리에서 활용할 수 있게 되었기 때문입니다. 앞으로 빅쿼리의 활용 범위가 점차 넓어질 것이라고 설명합니다.
WHERE 절과 HAVING 절은 어떻게 다른가요?
WHERE은 FROM에 사용하는 테이블의 데이터에서 조건을 추가할 때 사용됩니다. HAVING은 GROUP BY 절로 그룹으로 묶인 데이터에서 조건을 추가할 때 사용됩니다.
실습에는 어떤 데이터세트를 사용하나요?
google_analytics_sample 데이터세트를 사용합니다. 이 데이터세트는 날짜별로 하루하루 각각의 테이블(예: ga.sessions_2017-07-01)로 구성되어 있습니다.
여러 날짜의 데이터를 한 번에 추출하려면 어떻게 하나요?
FROM 절에서 날짜 부분을 *로 입력하고, WHERE 절에 _TABLE_SUFFIX와 BETWEEN a AND b 절을 사용하면 원하는 기간의 데이터를 추출할 수 있습니다.