BigQuery 설명서

이 기능은 Google Marketing Platform에 포함된 애널리틱스 360에서만 사용할 수 있습니다.
Google Marketing Platform에 대해 자세히 알아보기

이 페이지에는 BigQuery로 내보낸 애널리틱스 데이터에 대한 쿼리를 구성하는 방법이 예와 함께 나와 있습니다. 쿼리를 연습할 때 사용할 수 있는 샘플 데이터 세트도 나와 있으니 참고하세요.

이 도움말에서는 다음 내용을 다룹니다.

쿼리 최적화

실행되는 모든 쿼리가 월간 데이터 처리 허용량에 영향을 줍니다. 불필요한 필드가 선택된 경우에는 매월 처리해야 하는 데이터가 늘어나므로 사용하는 데이터의 양이 실제 필요한 수준보다 더 많아지게 됩니다. 최적화된 쿼리를 이용하면 월간 데이터 처리 허용량을 효과적으로 활용할 수 있습니다.

가격 책정에 대해 자세히 알아보기

필요한 필드만 선택

쿼리를 구성할 때 SELECT 구문에서 관련 필드를 선택하세요. 불필요한 필드를 호출하지 않으면 쿼리를 처리하는 데 필요한 데이터의 양과 시간이 줄어듭니다.

예: 와일드 카드 연산자를 사용하지 마세요.

나쁜 형식: 와일드 카드 연산자 사용
SELECT *
FROM [table name];

 

권장 형식: 불필요한 처리를 없애기 위해 필드 이름 사용
SELECT field1, field2
FROM [table name];

캐싱 허용

가능하면 함수를 필드로 사용하지 마세요. 함수(예: NOW(), TODAY())를 이용하면 변수 결과가 반환되므로 쿼리가 캐싱을 통한 신속한 반환이 이루어지지 못합니다. 함수 대신 구체적인 시간과 날짜를 이용하세요.

현재는 Use Cached Results(캐시 처리된 결과 사용) 옵션을 선택하더라도 와일드 카드를 사용하는 여러 테이블을 대상으로 하는 쿼리에서 캐시된 결과가 지원되지 않습니다. 동일한 와일드 카드 쿼리를 여러 번 실행하면, 각 쿼리에 대해 요금이 청구됩니다. 자세히 알아보기

자주 사용되는 하위 쿼리에 대해 중간 테이블 사용

특정 쿼리를 하위 쿼리로 반복적으로 사용하고 있다면 쿼리 결과 위에서 테이블로 저장을 클릭하여 해당 쿼리를 중간 테이블로 저장할 수 있습니다. 그런 다음 쿼리의 FROM 섹션에서 이 테이블을 참고할 수 있으며, 이렇게 하면 처리해야 하는 데이터의 양과 처리에 필요한 시간이 모두 줄어듭니다.

중간 테이블 사용
SELECT field1, field2
FROM [Dataset name.table name];

쿼리 디버깅

BigQuery에서는 코드를 만들 때 디버깅합니다. 구성 창에서 쿼리 바로 아래에 디버깅이 표시됩니다. 또한 dryRun 플래그가 있는 API를 통해서도 디버깅이 제공됩니다.

유효한 쿼리에는 초록색 기호가 나타나며, 이를 클릭하면 해당 쿼리에서 처리하는 데이터의 양이 표시됩니다. 이 기능을 이용하면 쿼리를 실행하기 전에 데이터를 최적화하여 불필요한 데이터 처리를 방지할 수 있습니다.

Query Debugging - Success

 

유효하지 않은 쿼리에는 빨간색 기호가 나타나며, 이를 클릭하면 오류에 대한 정보와 함께 오류가 발생한 행과 열이 표시됩니다. 아래의 예에서는 GROUP BY 구문이 빈칸이고, 이에 대한 오류가 지적되었습니다.

Query Debugging - Error

 

팁 및 권장사항

샘플 데이터 세트 사용

다음 예에서는 Google 애널리틱스 샘플 데이터 세트를 사용합니다.

고유 데이터에서 쿼리를 사용하려면 예에 있는 프로젝트 및 데이터 세트 이름을 고유 프로젝트 및 데이터 세트 이름으로 바꿉니다.

표준 SQL 및 legacy SQL 사용

BigQuery는 두 가지 SQL 언어를 지원합니다.

표준 SQL로의 마이그레이션에서는 두 언어 간의 차이점을 설명합니다.

표준 SQL은 현재 BigQuery에 저장된 데이터 쿼리에 선호되는 SQL 언어입니다.

BigQuery UI, CLI, API 또는 사용 중인 기타 인터페이스에서 표준 SQL을 사용하는 것에 관한 정보는 표준 SQL 사용을 참고하세요.

가장 쉬운 시작 방법은 다음 예에 표시된 표준 SQL 쿼리 상단에 'standardSQL' 주석을 포함하는 것입니다.

Legacy SQL을 사용하면 Google 애널리틱스 360 데이터가 매일 새 테이블에 전달됩니다. 한 번에 여러 테이블을 쿼리하려면 다음 예에서와 같이 테이블 이름을 쉼표로 구분하거나, TABLE_DATE_RANGE 테이블 와일드 카드 함수를 사용하거나, 쉼표로 구분된 여러 TABLE_DATE_RANGE 함수를 사용하세요.

여러 테이블 쿼리

다음 예는 동일한 데이터에 대한 표준 SQL 및 legacy SQL 쿼리를 보여줍니다.

3일

표준 SQL
UNION ALL을 사용한 3일
#standardSQL
WITH ga_tables AS (
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160801`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160802`
GROUP BY date

UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20160803`
GROUP BY date

)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC
Legacy SQL
쉼표로 구분된 테이블 이름을 사용한 3일
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
[bigquery-public-data.google_analytics_sample.ga_sessions_20160801],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160802],
[bigquery-public-data.google_analytics_sample.ga_sessions_20160803]
GROUP BY
date
ORDER BY
date ASC

 

최근 1,095일

표준 SQL
_TABLE_SUFFIX를 사용한 최근 1,095일
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1095 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Legacy SQL
TABLE_DATE_RANGE를 사용한 최근 1,095일
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -1095, 'DAY'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

최근 36개월

표준 SQL
_TABLE_SUFFIX를 사용한 최근 36개월
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 36 MONTH))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Legacy SQL
TABLE_DATE_RANGE를 사용한 최근 36개월
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -36, 'MONTH'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

최근 3년

표준 SQL
_TABLE_SUFFIX를 사용한 최근 3년
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
ORDER BY date ASC
Legacy SQL
TABLE_DATE_RANGE를 사용한 최근 3년
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY')))
GROUP BY
date
ORDER BY
date ASC

 

기간 설정

표준 SQL
_TABLE_SUFFIX를 사용한 기간 설정
#standardSQL
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20160801' AND '20170731'
GROUP BY date
ORDER BY date ASC
Legacy SQL
TABLE_DATE_RANGE를 사용한 기간 설정
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2016-08-01'), TIMESTAMP('2017-07-31')))
GROUP BY
date
ORDER BY
date ASC

최근 3년과 오늘(일중)의 데이터 합산

표준 SQL
UNION ALL 및 _TABLE_SUFFIX를 사용한 최근 3년과 오늘(일중)의 데이터 합산
참고: 현재 일중 테이블이 없으므로 이 쿼리 예는 Google 애널리틱스 공개 데이터 세트에서 작동하지 않습니다.
#standardSQL
WITH ga_tables AS ( SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
GROUP BY date
UNION ALL

SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
AND
FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
GROUP BY date
)
SELECT
date,
visits,
pageviews,
transactions,
revenue,
FROM ga_tables
ORDER BY date ASC

Legacy SQL

TABLE_DATE_RANGE를 여러 개 사용한 최근 3년과 오늘(일중)의 데이터 합산
참고: 현재 일중 테이블이 없으므로 이 쿼리 예는 Google 애널리틱스 공개 데이터 세트에서 작동하지 않습니다.
SELECT
date,
SUM(totals.visits) AS visits,
SUM(totals.pageviews) AS pageviews,
SUM(totals.transactions) AS transactions,
SUM(totals.transactionRevenue)/1000000 AS revenue
FROM
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_],
DATE_ADD(CURRENT_TIMESTAMP(), -3, 'YEAR'), DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'))),
(TABLE_DATE_RANGE([bigquery-public-data.google_analytics_sample.ga_sessions_intraday_],
DATE_ADD(CURRENT_TIMESTAMP(), -1, 'DAY'), CURRENT_TIMESTAMP()))
GROUP BY
date
ORDER BY
date ASC

 

 

기본 쿼리의 예

이 섹션에는 예시 애널리틱스 데이터의 측정항목과 측정기준을 사용하여 기본 쿼리를 만드는 방법이 나와 있습니다.

[측정기준]당 [측정항목] 총계는?

다음은 해당 질문에 대한 스크립트 예입니다. 2017년 7월에 기기 브라우저별로 발생한 총 거래 건수는?

표준 SQL

2017년 7월의 기기 브라우저별 총 거래
#standardSQL
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
device.browser
ORDER BY
total_transactions DESC

Legacy SQL

2017년 7월의 기기 브라우저별 총 거래
SELECT
device.browser,
SUM ( totals.transactions ) AS total_transactions
FROM TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
device.browser
ORDER BY
total_transactions DESC

 

 

[측정기준]당 평균 이탈률은?

실제 이탈률은 페이지 조회수가 1회인 방문의 비율로 정의됩니다. 다음은 질문에 대한 스크립트 예입니다. 트래픽 소스당 실제 이탈률은?

표준 SQL

2017년 7월의 트래픽 소스당 이탈률
#standardSQL
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
GROUP BY
source )
ORDER BY
total_visits DESC

Legacy SQL

2017년 7월의 트래픽 소스당 이탈률
SELECT
source,
total_visits,
total_no_of_bounces,
( ( total_no_of_bounces / total_visits ) * 100 ) AS bounce_rate
FROM (
SELECT
trafficSource.source AS source,
COUNT ( trafficSource.source ) AS total_visits,
SUM ( totals.bounces ) AS total_no_of_bounces
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
GROUP BY
source )
ORDER BY
total_visits DESC

 

 

구매자 유형별 평균 제품 페이지 조회수(구매자 vs 비구매자)

다음은 해당 질문에 대한 스크립트 예입니다. 2017년 7월에 구매한 사용자의 평균 제품 페이지 조회수는?

표준 SQL

2017년 7월에 구매한 사용자의 평균 제품 페이지 조회수
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions >=1
GROUP BY
users )

Legacy SQL

2017년 7월에 구매한 사용자의 평균 제품 페이지 조회수
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions >=1
GROUP BY
users )

 

 

다음은 해당 질문에 대한 스크립트 예입니다. 2017년 7월에 구매하지 않은 사용자의 평균 제품 페이지 조회수는?

표준 SQL

2017년 7월에 구매하지 않은 사용자의 평균 제품 페이지 조회수
#standardSQL
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.transactions IS NULL
GROUP BY
users )

Legacy SQL

2017년 7월에 구매하지 않은 사용자의 평균 제품 페이지 조회수
SELECT
( SUM(total_pagesviews_per_user) / COUNT(users) ) AS avg_pageviews_per_user
FROM (
SELECT
fullVisitorId AS users,
SUM(totals.pageviews) AS total_pagesviews_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') ) WHERE
totals.transactions IS NULL
GROUP BY
users )

 

 

구매자 유형당 평균 거래수

다음은 해당 질문에 대한 스크립트 예입니다. 2017년 7월에 구매한 사용자당 평균 총 거래수는?

표준 SQL

2017년 7월에 구매한 사용자당 평균 거래수
#standardSQL
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

Legacy SQL

2017년 7월에 구매한 사용자당 평균 거래수
SELECT
(SUM (total_transactions_per_user) / COUNT(fullVisitorId) ) AS avg_total_transactions_per_user
FROM (
SELECT
fullVisitorId,
SUM (totals.transactions) AS total_transactions_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.transactions IS NOT NULL
GROUP BY
fullVisitorId )

 

 

세션당 평균 지출 금액

다음은 해당 질문에 대한 스크립트 예입니다. 2017년 7월 세션당 평균 지출 금액은?

표준 SQL

2017년 7월 세션당 평균 지출 금액
#standardSQL
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

Legacy SQL

2017년 7월 세션당 평균 지출 금액
SELECT
( SUM(total_transactionrevenue_per_user) / SUM(total_visits_per_user) ) AS
avg_revenue_by_user_per_visit
FROM (
SELECT
fullVisitorId,
SUM( totals.visits ) AS total_visits_per_user,
SUM( totals.transactionRevenue ) AS total_transactionrevenue_per_user
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
totals.visits > 0
AND totals.transactions >= 1
AND totals.transactionRevenue IS NOT NULL
GROUP BY
fullVisitorId )

 

 

조회 순서

다음은 해당 질문에 대한 스크립트 예입니다. 조회한 페이지 순서는?

표준 SQL

2017년 7월에 사용자가 조회한 페이지 순서
#standardSQL
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
`bigquery-public-data.google_analytics_sample.ga_sessions_*`,
UNNEST(hits) as hits
WHERE
_TABLE_SUFFIX BETWEEN '20170701' AND '20170731'
AND
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

Legacy SQL

2017년 7월에 사용자가 조회한 페이지 순서
SELECT
fullVisitorId,
visitId,
visitNumber,
hits.hitNumber AS hitNumber,
hits.page.pagePath AS pagePath
FROM
TABLE_DATE_RANGE( [bigquery-public-data.google_analytics_sample.ga_sessions_],
TIMESTAMP('2017-07-01'), TIMESTAMP('2017-07-31') )
WHERE
hits.type="PAGE"
ORDER BY
fullVisitorId,
visitId,
visitNumber,
hitNumber

이 쿼리에서는 조회 유형을 PAGES로 제한하여 이벤트 또는 거래 상호작용이 나타나지 않게 합니다. 출력의 각 행은 페이지 조회를 나타내며 SELECT 구문의 기본 필드 순서에 따라 표시됩니다.

 

 

조회 또는 세션 수준의 복수 맞춤 측정기준

조회 수준의 맞춤 측정기준
SELECT fullVisitorId, visitId, hits.hitNumber, hits.time,
MAX(IF(hits.customDimensions.index=1,
hits.customDimensions.value,
NULL)) WITHIN hits AS customDimension1,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

 

세션 수준의 맞춤 측정기준
SELECT fullVisitorId, visitId,
MAX(IF(customDimensions.index=2,
customDimensions.value,
NULL)) WITHIN RECORD AS customDimension2,
FROM [tableID.ga_sessions_20150305]
LIMIT 100

각 쿼리의 내용은 다음과 같습니다.

관련 측정기준 및 측정항목 필드에 대한 SELECT 구문 쿼리

MAX 함수:

  • 맞춤 측정기준을 새 열로 반환합니다. 이 함수를 반복하여 여러 개의 맞춤 측정기준을 새 열로 반환할 수 있습니다.
  • WITHIN hits 및 WITHIN RECORD는 BigQuery에서 반복되는 필드 안에 있는 조건을 평가합니다.
  • MAX 내부의 조건은 맞춤 측정기준별로 평가되지만 index=1 (hits) 또는 index=2 (sessions)가 아닌 측정기준에 대해서는 NULL을 반환합니다.
  • 다른 모든 값이 NULL이므로 최댓값(조회의 경우 맞춤 측정기준 1, 세션의 경우 맞춤 측정기준 2의 값)을 반환합니다.

고급 쿼리의 예

이상으로 간단한 쿼리에 대해 살펴보았고, BIgQuery에서 제공되는 고급 함수와 기능을 이용해 쿼리를 구성하는 방법에 대해서도 알아보도록 하겠습니다.

제품 A를 구매한 고객이 구매한 제품(기본 전자상거래)

다음은 제품 A를 구매한 고객이 구매한 다른 제품은?이란 질문에 대한 스켈레톤 스크립트입니다.

제품 A를 구매한 고객이 구매한 제품(기본 전자상거래)
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [‘Dataset Name’ ]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [‘Dataset Name’ ]
  WHERE hits.item.productName CONTAINS 'Product Item Name A'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName != 'Product Item Name A'
GROUP BY other_purchased_products
ORDER BY quantity DESC;
  1. 첫 번째 행에서는 사용자가 구매한 다른 모든 품목을 선택하며, 집계 함수인 COUNT()가 구매한 각 품목의 수량을 계산하는 데 사용됩니다. 그 결과가 other_purchased_products로 표시된 제품 필드의 연결된 품목과 함께 quantity 필드에 표시됩니다.
  2. 회색 하위 쿼리에서는 거래를 수행(totals.transactions>=1)한 순 사용자(fullVisitorId)와 거래 시 제품 A를 구매한 사용자가 표시됩니다(WHERE hits.item.productName CONTAINS 'Product Item Name A').

최상위 수준의 초록색 쿼리에 있는 규칙(WHERE 및 AND 구문)은 hits.item.productName에서 null이면서 제품 A를 포함하는 값을 무시합니다.

다음은 Brighton Metallic Pens 4세트를 구매한 고객이 구매한 다른 제품은?이란 쿼리에 대한 예입니다.

2013년 6월 24일에 'Brighton Metallic Pens' 4세트를 구매한 고객이 구매한 다른 제품
SELECT hits.item.productName AS other_purchased_products, COUNT(hits.item.productName) AS quantity
FROM [GoogleStore.ga_sessions_20130624]
WHERE fullVisitorId IN (
  SELECT fullVisitorId
  FROM [GoogleStore.ga_sessions_20130624]
  WHERE hits.item.productName CONTAINS 'Brighton Metallic Pens - Set of 4'
   AND totals.transactions>=1
  GROUP BY fullVisitorId )
 AND hits.item.productName IS NOT NULL
 AND hits.item.productName !='Brighton Metallic Pens - Set of 4'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

Dremel/BigQuery에서 WHERE expr IN을 사용하면 JOIN이 실행되고 크기 제한이 적용됩니다. 구체적으로는 JOIN의 우측면 크기(이 경우 방문자의 수)가 8MB 미만이어야 합니다. Dremel에서는 이를 broadcast JOIN이라고 합니다. 이 크기가 8MB를 초과하면 shuffled JOIN을 실행해야 하는데 JOIN EACH 문법을 사용하면 됩니다. 이 실행은 IN을 사용해서는 불가능하지만 JOIN을 이용해 동일한 쿼리를 재작성할 수 있습니다.

제품 A를 구매한 고객이 구매한 제품(향상된 전자상거래)

다음은 이전의 스켈레톤 쿼리와 유사하지만 향상된 전자상거래에만 작동합니다. 또한 TABLE_DATE_RANGE를 활용하여 며칠간의 데이터를 쿼리합니다.

제품 A를 구매한 고객이 구매한 제품(향상된 전자상거래)
SELECT hits.product.productSKU AS other_purchased_products, COUNT(hits.product.productSKU) AS quantity
FROM SELECT fullVisitorId, hits.product.productSKU, hits.eCommerceAction.action_type FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14')))
WHERE fullVisitorId IN (
  SELECT fullVisitorId
FROM TABLE_DATE_RANGE([73156703.ga_sessions_], TIMESTAMP('2015-07-01'), TIMESTAMP('2015-07-14'))
WHERE hits.product.productSKU CONTAINS '10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY fullVisitorId )
AND hits.product.productSKU IS NOT NULL
AND hits.product.productSKU !='10 15103'
AND hits.eCommerceAction.action_type = '6'
GROUP BY other_purchased_products
ORDER BY quantity DESC;

구매 전 평균 사용자 상호작용 횟수

다음은 애널리틱스 데이터에만 의존하는 JOIN() [...] ON 명령어의 쿼리 예입니다.

다음은 구매 전 사용자 상호작용의 평균 횟수는?이란 질문에 대한 스켈레톤 스크립트입니다.

구매 전 사용자 상호작용의 횟수
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_1’
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [‘GA Dataset Name’ ]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS ‘Alias_Name_2’
ON Alias_Name_1.hits.item.productSku = Alias_Name_2.hits.item.productSku;
  1. 첫 번째 행에서는 제품당 평균 사용자 상호작용수를 찾기 위한 수학 연산이 실행되며, 이 쿼리를 통해 두 하위 쿼리인 ‘Alias_Name_1’과 ‘Alias_Name_2’ 사이에 결합이 형성됩니다.
  2. ‘Alias_Name_1’은 제품에 대해 기록되는 총 조회수를 집계하는 함수인 SUM()을 사용하는 필드를 생성하는 데 사용됩니다.
  3. ‘Alias_Name_2’는 COUNT() 함수를 사용하여 제품당 사용자의 조회수를 찾는 데 사용됩니다.
  4. 마지막 행에는 결합에서 2개의 데이터 세트가 공유하는 공통 필드(hits.item.productSku)가 표시됩니다.

다음은 구매 전인 2013년 9월 10일의 평균 사용자 상호작용 횟수는?이란 쿼리에 대한 예입니다.

구매 전인 2013년 9월 10일의 사용자 상호작용 횟수
SELECT one.hits.item.productSku AS ProductSku, ( sum_of_hit_number / total_hits ) AS avg_hit_number
FROM (
 SELECT hits.item.productSku, SUM(hits.hitNumber) AS sum_of_hit_number
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku  ) AS one
JOIN (
 SELECT hits.item.productSku, COUNT( fullVisitorId ) AS total_hits
 FROM [GoogleStore.ga_sessions_20130728]
 WHERE hits.item.productSku IS NOT NULL
  AND totals.transactions>=1
 GROUP BY hits.item.productSku ) AS two
ON one.hits.item.productSku = two.hits.item.productSku;

제품당 판매된 재고의 비율

다음은 애널리틱스 데이터뿐만 아니라 애널리틱스 이외 데이터도 사용하는 쿼리의 예입니다. 두 데이터 세트를 결합하면 사용자 행동을 더욱 세부적인 수준으로 확인할 수 있습니다. 애널리틱스 이외 데이터를 BigQuery로 가져올 수 있지만 매월 데이터 저장 비용이 발생할 것입니다.

다음은 판매된 제품당 재고 비율은?이란 질문에 대한 스켈레톤 스크립트입니다.

제품당 판매된 재고의 비율
SELECT AnalyticsImport.product_data_20130728.productId, ((( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold )) * 100 ) AS percentage_of_stock_sold
FROM [ ‘Imported_DataSet’ ]
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM [‘GA Dataset’ ]
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS ‘Alias_Name’
ON Imported_DataSet.’productId_field’ = Alias_Name.hits.item.productSku;
  1. 첫 번째 행에서는 모든 제품 ID를 포함하는 필드와 판매된 제품 ID별 재고 비율을 보여주는 수학 연산 필드가 산출됩니다.
  2. 이 쿼리에는 두 데이터 세트가 필요하므로 JOIN() ... ON 함수를 사용해야 합니다. 이 명령에서는 두 데이터 세트의 공통 필드를 기반으로 데이터 세트의 행을 결합합니다. 이 경우 두 데이터 세트는 [ ‘Imported_DataSet’ ]와 ‘Alias_Name’입니다.
  3. [ ‘Imported_DataSet’ ]는 애널리틱스 외 데이터입니다, 이는 남은 재고량에 대한 측정항목 필드(Imported DataSet.’stock_left_field’)와 제품 ID 측정기준 필드(Imported_DataSet.’productId_field’)를 포함하는 데이터 세트입니다.
  4. ‘Alias_Name’은 회색 하위 쿼리에서 반환된 데이터에 할당된 이름입니다. 이 하위 쿼리에서는 제품당 판매된 품목의 총 수량을 찾기 위해 애널리틱스를 데이터를 사용합니다.
  5. 마지막 행에서는 두 데이터 세트의 공통 필드와 두 데이터 세트의 결합 위치를 보여주는 ON 구문이 사용됩니다.

이 쿼리에서 많은 변수가 데이터 세트 이름이 접두사로 추가됩니다(예: Imported_DataSet.’productId_field’, Alias_Name.quantity_sold). 이를 통해 선택하는 필드와 이 필드가 속한 데이터 세트를 명확히 알리게 됩니다.

다음은 2013년 7월 28일의 판매 제품당 재고 비율은?이란 쿼리에 대한 예입니다.

2013년 7월 28일의 판매 제품당 재고 비율
SELECT AnalyticsImport.product_data_20130728.productId, ( ( ( one.quantity_sold ) / ( AnalyticsImport.product_data_20130728.productstock + one.quantity_sold ) ) * 100 ) AS percentage_of_stock_sold
FROM AnalyticsImport.product_data_20130728
JOIN (
  SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity_sold
  FROM GoogleStore.ga_sessions_20130728
  WHERE hits.item.productSku IS NOT NULL
   AND totals.transactions>=1
  GROUP BY hits.item.productSku ) AS one
ON AnalyticsImport.product_data_20130728.productId = one.hits.item.productSku
ORDER BY percentage_of_stock_sold DESC;

제품별 수익

다음은 제품별 수익은?이란 질문에 대한 스켈레톤 스크립트입니다.

제품별 수익
SELECT Alias_Name.hits.item.productSku, ( Imported_DataSet.’product profit field’ * Alias_Name.quantity ) AS profit
FROM (
  SELECT Alias_Name.hits.item.productSku, Imported_DataSet.’product profit field’
  FROM [ ‘Imported Data Set’ ]
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM [ ‘GA Dataset Name’ ]
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS ‘Alias_Name’
  ON Imported_DataSet.productId = Alias_Name.hits.item.productSku );
  1. 첫 번째 행에는 각 제품의 총 수익을 계산하는 수학 연산이 포함됩니다.
  2. 회색의 하위 쿼리에서는 애널리틱스 이외의 데이터가 사용되는데, 여기에서는 제품이 판매될 때의 수익에 대한 데이터를 수집합니다.
  3. 빨간색 하위 쿼리는 애널리틱스 데이터 하위 쿼리이며, 이는 애널리틱스 이외의 데이터와 결합됩니다. 여기에서는 판매된 제품당 품목의 개수가 계산됩니다.
  4. 마지막 행에서는 두 데이터 세트가 공유하는 필드를 명확히 알리기 위해 ON 구문을 사용합니다. 이 경우에는 제품 ID 번호입니다.

다음은 2013년 7월 28일의 제품별 수익은?이란 쿼리에 대한 예입니다.

2013년 7월 28일의 제품별 수익
SELECT two.hits.item.productSku, ((AnalyticsImport.product_data_20130728.productprice-AnalyticsImport.product_data_20130728.productcost)*two.quantity) AS profit
FROM (
  SELECT two.hits.item.productSku, AnalyticsImport.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice
  FROM AnalyticsImport.product_data_20130728
  JOIN (
    SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
    FROM GoogleStore.ga_sessions_20130728
    WHERE hits.item.productSku IS NOT NULL
    GROUP BY hits.item.productSku ) AS two
  ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku );

수익은 제품의 판매 가격에서 제조 비용을 뺀 금액입니다. 이 정보는 Google 애널리틱스 이외의 데이터 세트에 저장됩니다.

제품별 실제 수익(환불 금액 반영)

다음은 제품별 실제 수익은?이란 질문에 대한 스켈레톤 스크립트입니다.

제품별 실제 수익
SELECT Alias_Name.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT Alias_Name.hits.item.productSku, ( ( Imported_DataSet.productprice - Imported_DataSet.productcost ) * Alias_Name.quantity ) AS gross_profit, ( ( Imported_DataSet.refunddeliveryprice + Imported_DataSet.productprice ) * Imported_DataSet.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT Alias_Name.hits.item.productSku, Imported_DataSet.productcost, Alias_Name.quantity, Imported_DataSet.productprice, Imported_DataSet.refunddeliveryprice, Imported_DataSet.refundquantity
    FROM [ ‘Imported DataSet Name’ ] AS 'Imported_DataSet'
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM [‘GA Dataset Name’ ]
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS 'Alias_Name'
    ON Imported_DataSet.productId = Alias_Name.hits.item.productSku ) );
  1. 이 쿼리는 제품별 수익은?이란 쿼리와 매우 유사합니다. 단지 회색 하위 쿼리에 들어간 애널리틱스 이외의 데이터 세트와 첫 번째 행에서 실제 수익을 계산하는 수학 연산이 다를 뿐입니다.
  2. 애널리틱스 이외의 데이터 세트에서는 빨간색 하위 쿼리의 SELECT 구문에서 총 환불 금액도 계산합니다.
  3. 그런 다음 첫 번째 행의 수학 연산을 통해 총 수익에서 환불 금액을 빼서 실제 수익을 계산합니다.

이 쿼리에 대해 자세히 알아보려면 제품별 수익 섹션을 참고하세요.

다음은 2013년 7월 28일의 제품별 실제 수익은?이란 쿼리에 대한 예입니다.

2013년 7월 28일의 제품별 실제 수익
SELECT two.hits.item.productSku, (gross_profit-total_refund_revenue) AS real_profit
FROM (
  SELECT two.hits.item.productSku, ( ( AnalyticsImport.product_data_20130728.productprice - AnalyticsImport.product_data_20130728.productcost ) * two.quantity ) AS gross_profit, ( ( AnalyticsImport.product_data_20130728.refunddeliveryprice + AnalyticsImport.product_data_20130728.productprice ) * AnalyticsImport.product_data_20130728.refundquantity ) AS total_refund_revenue
  FROM (
    SELECT two.hits.item.productSku, Analytics.product_data_20130728.productcost, two.quantity, AnalyticsImport.product_data_20130728.productprice, AnalyticsImport.product_data_20130728.refunddeliveryprice, AnalyticsImport.product_data_20130728.refundquantity
    FROM AnalyticsImport.product_data_20130728
    JOIN (
      SELECT hits.item.productSku, SUM( hits.item.itemQuantity ) AS quantity
      FROM GoogleStore.ga_sessions_20130728
      WHERE hits.item.productSku IS NOT NULL
       AND totals.transactions >=1
      GROUP BY hits.item.productSku ) AS two
    ON AnalyticsImport.product_data_20130728.productId = two.hits.item.productSku ) );

실제 수익에서는 환불 제품을 고려하여 제품의 수익을 계산합니다. 제품의 총 환불 수익을 계산하는 방법은 다음과 같습니다.

제품의 총 환불 수익 = (제품의 가격 + 제품의 환불 배송비 ) * 환불 제품의 개수

도움이 되었나요?

어떻게 하면 개선할 수 있을까요?

도움이 더 필요하신가요?

다음 단계를 시도해 보세요.

도움말 커뮤니티에 게시하기 커뮤니티 회원의 답변 받기
문의하기 자세히 알려주시면 도움을 드리겠습니다.
true
learning path
나에게 맞는 학습 과정 선택하기

Google 애널리틱스 4를 최대한 활용하는 데 도움이 되는 새로운 리소스인 google.com/analytics/learn을 확인해보세요. 새 웹사이트는 동영상, 도움말, 안내 가이드와 함께 Google 애널리틱스 Discord, 블로그, YouTube 채널, GitHub 저장소 링크를 제공합니다.

지금 학습 시작하기

검색
검색어 지우기
검색 닫기
기본 메뉴
17598361269898733981
true
도움말 센터 검색
true
true
true
true
true
69256
false
false