개요

BigQuery는 Google의 완전 관리형, 노옵스(NoOps), 저비용 분석 데이터베이스입니다. BigQuery를 사용하면 관리할 인프라나 데이터베이스 관리자가 없어도 테라바이트 단위의 대규모 데이터를 쿼리할 수 있습니다. BigQuery는 SQL을 사용하므로 사용한 만큼만 지불하는 모델의 장점을 활용할 수 있습니다. BigQuery는 데이터를 분석하여 의미 있고 유용한 정보를 찾는 데 집중할 수 있게 해줍니다.

Google Merchandise Store의 Google 애널리틱스 레코드 수백만 개가 포함된 전자상거래 데이터 세트가 BigQuery의 테이블에 로드된 상태로 새롭게 마련되어 있습니다. 이 실습에서는 해당 데이터 세트의 복사본을 사용하며, 제공된 샘플 시나리오에서 데이터를 살펴보고 중복 정보를 삭제하는 방법을 확인합니다. 그런 다음 추가적인 데이터 분석을 수행하는 방법을 확인합니다.

데이터 분석을 위해 제공된 BigQuery 쿼리를 따라 하고 실험도 수행하려면 표준 SQL 쿼리 문법을 확인하세요.

실습할 내용

이 실습에서는 BigQuery를 사용하여 다음 작업을 수행합니다.

• 전자상거래 데이터 세트에 액세스
• 데이터 세트 메타데이터 확인
• 중복된 항목 삭제
• 쿼리 작성 및 실행

설정 및 요건

각 실습에서는 정해진 기간 동안 새 Google Cloud 프로젝트와 리소스 집합이 무료로 제공됩니다.

1. 시크릿 창을 사용하여 Qwiklabs에 로그인합니다.

2. 실습 사용 가능 시간(예: 1:15:00)을 참고하여 해당 시간 내에 완료합니다.
   일시중지 기능은 없습니다. 필요한 경우 다시 시작할 수 있지만 처음부터 시작해야 합니다.

3. 준비가 되면 실습 시작을 클릭합니다.

4. 실습 사용자 인증 정보(사용자 이름 및 비밀번호)를 기록해 두세요. Google Cloud Console에 로그인합니다.

5. Google Console 열기를 클릭합니다.

6. 다른 계정 사용을 클릭한 다음, 안내 메시지에 이 실습에 대한 사용자 인증 정보를 복사하여 붙여넣습니다.
   다른 사용자 인증 정보를 사용하는 경우 오류가 발생하거나 요금이 부과됩니다.

7. 약관에 동의하고 리소스 복구 페이지를 건너뜁니다.

작업 1. BigQuery에서 실습 프로젝트에 별표표시하기

이 섹션에서는 data-to-insights 프로젝트를 환경 리소스에 추가합니다.

BigQuery 콘솔 열기

1. Google Cloud 콘솔의 탐색 메뉴에서 BigQuery를 클릭합니다.
   Cloud 콘솔의 BigQuery에 오신 것을 환영합니다라는 대화상자가 열립니다. 이 대화상자에서는 빠른 시작 가이드 링크 및 UI 업데이트 목록을 확인할 수 있습니다.

2. 완료를 클릭하여 대화상자를 닫습니다.

BigQuery 공개 데이터 세트는 기본적으로 BigQuery 웹 UI에서 표시되지 않으므로 공개 데이터 세트 프로젝트를 열어야 합니다.

4. + 데이터 추가를 클릭합니다.

5. 이름으로 프로젝트에 별표표시를 선택합니다.

6. 프로젝트 이름에 data-to-insights를 입력합니다.

7. 별표를 클릭합니다.

8. 탐색기 창에 별표표시된 data-to-insights 프로젝트가 표시됩니다.

작업 2. 전자상거래 데이터 탐색 및 중복 레코드 식별

시나리오: 데이터 분석가팀이 전자상거래 웹사이트에 대한 Google 애널리틱스 로그를 BigQuery로 내보내고 모든 전자상거래 방문자 세션의 원시 데이터를 사용하여 새 테이블을 만들었습니다.

기존 탐색기 섹션으로 돌아가 all_sessions_raw 테이블 데이터를 탐색합니다.

1. data-to-insights 프로젝트를 펼칩니다.
2. ecommerce를 펼칩니다.
3. all_sessions_raw를 클릭합니다.

오른쪽 창에 테이블 데이터에 대한 3가지 뷰를 제공하는 섹션이 열립니다.

• 스키마 탭: 필드 이름, 유형, 모드, 설명, 데이터를 구성하는 데 사용되는 논리적 제약조건
• 세부정보 탭: 테이블 메타데이터
• 미리보기 탭: 테이블 미리보기

4. 세부정보 탭을 클릭하여 테이블 메타데이터를 확인합니다.

질문:

중복 행 식별

샘플 데이터를 보면 데이터 세트에 무엇이 포함되어 있는지 더 잘 이해할 수 있습니다. SQL을 사용하지 않고 테이블의 샘플 행을 미리 보려면 미리보기 탭을 클릭합니다.

행을 스크롤하며 검사합니다. 행을 고유하게 식별하는 단일 필드는 없으므로 중복 행을 식별하려면 고급 로직이 필요합니다.

쿼리는 모든 필드에 SQL GROUP BY 함수를 사용하고 모든 필드에서 동일한 값을 가진 행의 수를 셉니다(COUNT).

• 모든 필드가 고유한 경우 모든 필드에 동일한 값을 가진 다른 행 그룹이 없으므로 COUNT는 1을 반환합니다.

• 모든 필드에 동일한 값을 가진 행이 있는 경우 이러한 행은 그룹화되며 COUNT는 1보다 큰 값을 반환합니다. 쿼리의 마지막 부분은 HAVING을 사용하여 1보다 큰 중복 COUNT가 있는 결과만 표시하는 집계 필터입니다.

• 다음 쿼리를 복사하여 쿼리 편집기에 붙여넣은 후 실행하여 모든 열 필드에서 중복 레코드를 찾습니다. 편집기 탭이 표시되지 않으면 + SQL 쿼리를 클릭합니다.

#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, GROUP BY, HAVING 함수를 사용하여 행이 고유한지 확인하는 것이 좋습니다.

새로운 all_sessions 테이블 분석

이 섹션에서는 중복 삭제된 all_sessions 테이블을 사용합니다.

시나리오: 데이터 분석가팀이 이 쿼리를 제공했으며, 스키마 전문가는 스키마에 따라 각 레코드에 대해 고유해야 하는 주요 필드를 식별했습니다.

• 쿼리를 실행하여 이번에는 all_sessions 테이블에 중복된 항목이 없는지 확인합니다.

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=ko SELECT fullVisitorId, # the unique visitor ID visitId, # a visitor can have multiple visits date, # session date stored as string YYYYMMDD time, # time of the individual site hit (can be 0 to many per visitor session) v2ProductName, # not unique since a product can have variants like Color productSKU, # unique for each product type, # a visitor can visit Pages and/or can trigger Events (even at the same time) eCommerceAction_type, # maps to ‘add to cart', ‘completed checkout' eCommerceAction_step, eCommerceAction_option, transactionRevenue, # revenue of the order transactionId, # unique identifier for revenue bearing transaction 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 HAVING row_count > 1 # find duplicates

쿼리는 레코드를 반환하지 않습니다.

참고: SQL에서는 열 색인에 GROUP BY 또는 ORDER BY 함수를 사용할 수 있습니다. 예를 들어 'GROUP BY fullVisitorId' 대신 'GROUP BY 1'을 사용할 수 있습니다.

작업 3. 전자상거래 데이터에 대한 기본 SQL 작성

이 섹션에서는 통계를 얻기 위해 전자상거래 데이터 세트를 쿼리합니다.

전체 순 방문자수를 알려주는 쿼리 작성

이 쿼리는 product_views를 셈하여 총조회수를, fullVisitorID를 셈하여 순 방문자수를 파악합니다.

1. + SQL 쿼리를 클릭합니다.
2. 편집기에서 다음 쿼리를 작성합니다.

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

3. 문법이 정확한지 확인하려면 실시간 쿼리 검사기 아이콘을 클릭합니다.
4. 실행을 클릭합니다. 결과에서 순 방문자수를 확인합니다.

결과

5. 이제 추천 사이트별(channelGrouping)로 총 순 방문자수(fullVisitorID)를 표시하는 쿼리를 작성합니다.

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

결과

6. 모든 고유 제품 이름(v2ProductName)을 알파벳순으로 나열하는 쿼리를 작성합니다.

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

팁: SQL에서 ORDER BY 절은 기본적으로 A~Z 오름차순(ASC)입니다. 내림차순으로 바꾸려면 'ORDER BY field_name DESC'를 사용해 보세요.

결과

7. 이 쿼리는 총 633개의 제품(행)을 반환합니다.

8. 전체 방문자(동일한 제품을 두 번 이상 본 사람 포함)로부터 가장 많이 조회된(product_views) 5개 제품을 나열하는 쿼리를 작성합니다. 쿼리는 제품(v2ProductName)이 조회된 횟수(product_views)를 셈하여 내림차순으로 나열하고 상위 5개 항목을 표시합니다.

팁: Google 애널리틱스에서 방문자는 'page', 'screenview', 'event', 'transaction', 'item', 'social', 'exception', 'timing' 등 유형의 상호작용을 하는 동안 제품을 '조회'할 수 있습니다. 여기에서는 실습을 위해 'PAGE' 유형만 필터링하겠습니다.

#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;

결과

9. 추가 활동: 제품을 여러 번 조회한 방문자의 제품 조회수를 중복해서 세지 않도록 쿼리를 미세 조정하세요. 각 고유 제품의 조회수는 방문자당 한 번만 집계되어야 합니다.

WITH unique_product_views_by_person AS ( -- find each unique product viewed by each visitor SELECT fullVisitorId, (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE type = 'PAGE' GROUP BY fullVisitorId, v2ProductName ) -- aggregate the top viewed products and sort them SELECT COUNT(*) AS unique_view_count, ProductName FROM unique_product_views_by_person GROUP BY ProductName ORDER BY unique_view_count DESC LIMIT 5

팁: SQL의 WITH 절을 사용하면 복잡한 쿼리를 여러 단계로 나눌 수 있습니다. 여기서는 먼저 방문자당 각 고유 제품을 찾아 한 번만 세는 쿼리를 만듭니다. 그런 다음 두 번째 쿼리는 모든 방문자와 제품에 대한 집계를 수행합니다.

결과

10. 그런 다음 주문된 고유 제품의 총수와 주문된 총 단위의 총수(productQuantity)를 포함하도록 이전 쿼리를 확장합니다.

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

결과

질문:

11. 주문당 평균 제품 수량(주문된 단위 총수/총 주문 수, 또는 SUM(productQuantity)/COUNT(productQuantity))을 포함하도록 쿼리를 확장합니다.

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

결과

질문:

'22 oz YouTube Bottle Infuser'의 avg_per_order가 주문당 9.38단위로 가장 높았습니다.

작업 4. SQL 실습

SQL 기술을 테스트해 볼 준비가 되셨나요? 다음 과제 질문을 풀어 보세요.

과제 1: 전환율 계산하기

1. 다음과 같은 특성을 지닌 제품에 대한 전환율 쿼리를 작성하세요.

• 장바구니에 1,000개 이상의 단위가 추가되거나 주문됨
• '프리스비'가 아님

2. 다음 질문에 답합니다.

• 제품이 주문(완료 또는 미완료 주문)에 포함된 횟수는 몇 번인가요?
• 주문(완료 또는 미완료)에 포함된 제품의 총 단위 수는 얼마인가요?
• 전환율이 가장 높은 제품은 무엇인가요?

3. 다음의 부분 쿼리를 완성하세요.

#standardSQL SELECT COUNT(*) AS product_views, COUNT(productQuantity) AS potential_orders, SUM(productQuantity) AS quantity_product_added, v2ProductName FROM `data-to-insights.ecommerce.all_sessions` WHERE v2ProductName NOT LIKE 'frisbee' GROUP BY v2ProductName HAVING quantity_product_added > ORDER BY conversion_rate LIMIT 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;

과제 2: 방문자의 결제 진행 상황 추적하기

• 각 유형과 연결된 eCommerceAction_type 및 fullVisitorId의 고유 개수를 보여주는 쿼리를 작성합니다.

예시 답안:

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

추가 활동

작업 유형의 매핑은 다음과 같습니다. Unknown = 0 Click through of product lists = 1 Product detail views = 2 Add product(s) to cart = 3 Remove product(s) from cart = 4 Check out = 5 Completed purchase = 6 Refund of purchase = 7 Checkout options = 8

• Case 문을 사용해 이전 쿼리에 새 열을 추가하고 eCommerceAction_type 라벨('구매 완료' 등)을 표시합니다.

예시 답안:

#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS number_of_unique_visitors, eCommerceAction_type, CASE eCommerceAction_type WHEN '0' THEN 'Unknown' WHEN '1' THEN 'Click through of product lists' WHEN '2' THEN 'Product detail views' WHEN '3' THEN 'Add product(s) to cart' WHEN '4' THEN 'Remove product(s) from cart' WHEN '5' THEN 'Check out' WHEN '6' THEN 'Completed purchase' WHEN '7' THEN 'Refund of purchase' WHEN '8' THEN 'Checkout options' ELSE 'ERROR' END AS eCommerceAction_type_label FROM `data-to-insights.ecommerce.all_sessions` GROUP BY eCommerceAction_type ORDER BY eCommerceAction_type;

장바구니에 제품을 추가한 방문자 중 구매를 완료한 비율은 얼마인가요?

정답: 19988 / 56010 = 0.3568 또는 35.68%

과제 3: 고품질 세션에서 장바구니 이탈 추적하기

• 집계 함수를 사용하여 쿼리를 작성하고 장바구니에 제품을 추가했지만 결제를 완료하지 않은(장바구니 이탈) 방문자의 고유 세션 ID를 반환합니다.

예시 답안:

#standardSQL # high quality abandoned carts SELECT #unique_session_id CONCAT(fullVisitorId,CAST(visitId AS STRING)) AS unique_session_id, sessionQualityDim, SUM(productRevenue) AS transaction_revenue, MAX(eCommerceAction_type) AS checkout_progress FROM `data-to-insights.ecommerce.all_sessions` WHERE sessionQualityDim > 60 # high quality session GROUP BY unique_session_id, sessionQualityDim HAVING checkout_progress = '3' # 3 = added to cart AND (transaction_revenue = 0 OR transaction_revenue IS NULL)

실습 종료

실습을 완료하면 실습 종료를 클릭합니다. Qwiklabs에서 사용된 리소스를 자동으로 삭제하고 계정을 지웁니다.

실습 경험을 평가할 수 있습니다. 해당하는 별표 수를 선택하고 의견을 입력한 후 제출을 클릭합니다.

별점의 의미는 다음과 같습니다.

• 별표 1개 = 매우 불만족
• 별표 2개 = 불만족
• 별표 3개 = 중간
• 별표 4개 = 만족
• 별표 5개 = 매우 만족

의견을 제공하고 싶지 않다면 대화상자를 닫으면 됩니다.

의견이나 제안 또는 수정할 사항이 있다면 지원 탭을 사용하세요.

Copyright 2020 Google LLC All rights reserved. Google 및 Google 로고는 Google LLC의 상표입니다. 기타 모든 회사명 및 제품명은 해당 업체의 상표일 수 있습니다.