GSP407

개요

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

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

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

목표

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

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

설정 및 요건

실습 시작 버튼을 클릭하기 전에

다음 안내를 확인하세요. 실습에는 시간 제한이 있으며 일시중지할 수 없습니다. 실습 시작을 클릭하면 타이머가 시작됩니다. 이 타이머에는 Google Cloud 리소스를 사용할 수 있는 시간이 얼마나 남았는지 표시됩니다.

실무형 실습을 통해 시뮬레이션이나 데모 환경이 아닌 실제 클라우드 환경에서 직접 실습 활동을 진행할 수 있습니다. 실습 시간 동안 Google Cloud에 로그인하고 액세스하는 데 사용할 수 있는 새로운 임시 사용자 인증 정보가 제공됩니다.

이 실습을 완료하려면 다음을 준비해야 합니다.

• 표준 인터넷 브라우저 액세스 권한(Chrome 브라우저 권장)

참고: 이 실습을 실행하려면 시크릿 모드 또는 시크릿 브라우저 창을 사용하세요. 개인 계정과 학생 계정 간의 충돌로 개인 계정에 추가 요금이 발생하는 일을 방지해 줍니다.

• 실습을 완료하기에 충분한 시간---실습을 시작하고 나면 일시중지할 수 없습니다.

참고: 계정에 추가 요금이 발생하지 않도록 하려면 개인용 Google Cloud 계정이나 프로젝트가 이미 있어도 이 실습에서는 사용하지 마세요.

실습을 시작하고 Google Cloud 콘솔에 로그인하는 방법

1. 실습 시작 버튼을 클릭합니다. 실습 비용을 결제해야 하는 경우 결제 수단을 선택할 수 있는 팝업이 열립니다. 왼쪽에는 다음과 같은 항목이 포함된 실습 세부정보 패널이 있습니다.

   • Google Cloud 콘솔 열기 버튼
   • 남은 시간
   • 이 실습에 사용해야 하는 임시 사용자 인증 정보
   • 필요한 경우 실습 진행을 위한 기타 정보

2. Google Cloud 콘솔 열기를 클릭합니다(Chrome 브라우저를 실행 중인 경우 마우스 오른쪽 버튼으로 클릭하고 시크릿 창에서 링크 열기를 선택합니다).

   실습에서 리소스가 가동되면 다른 탭이 열리고 로그인 페이지가 표시됩니다.

   팁: 두 개의 탭을 각각 별도의 창으로 나란히 정렬하세요.

   참고: 계정 선택 대화상자가 표시되면 다른 계정 사용을 클릭합니다.

3. 필요한 경우 아래의 사용자 이름을 복사하여 로그인 대화상자에 붙여넣습니다.

   {{{user_0.username | "Username"}}}

   실습 세부정보 패널에서도 사용자 이름을 확인할 수 있습니다.

4. 다음을 클릭합니다.

5. 아래의 비밀번호를 복사하여 시작하기 대화상자에 붙여넣습니다.

   {{{user_0.password | "Password"}}}

   실습 세부정보 패널에서도 비밀번호를 확인할 수 있습니다.

6. 다음을 클릭합니다.

   중요: 실습에서 제공하는 사용자 인증 정보를 사용해야 합니다. Google Cloud 계정 사용자 인증 정보를 사용하지 마세요. 참고: 이 실습에 자신의 Google Cloud 계정을 사용하면 추가 요금이 발생할 수 있습니다.

7. 이후에 표시되는 페이지를 클릭하여 넘깁니다.

   • 이용약관에 동의합니다.
   • 임시 계정이므로 복구 옵션이나 2단계 인증을 추가하지 않습니다.
   • 무료 체험판을 신청하지 않습니다.

잠시 후 Google Cloud 콘솔이 이 탭에서 열립니다.

참고: Google Cloud 제품 및 서비스 목록이 있는 메뉴를 보려면 왼쪽 상단의 탐색 메뉴를 클릭합니다.

작업 1. BigQuery에서 실습 프로젝트 고정

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

1. 탐색 메뉴 > BigQuery를 클릭합니다.

'Cloud 콘솔의 BigQuery에 오신 것을 환영합니다.'라는 메시지 상자가 열립니다.

참고: 'Cloud 콘솔의 BigQuery에 오신 것을 환영합니다.' 메시지 상자에서는 빠른 시작 가이드 및 UI 업데이트 링크를 확인할 수 있습니다.

2. 완료를 클릭합니다.

BigQuery 웹 UI에서는 기본적으로 BigQuery 공개 데이터 세트가 표시되지 않습니다. 공개 데이터 세트 프로젝트를 열려면 'data-to-insights'를 복사합니다.

3. + 추가 > 이름으로 프로젝트에 별표표시를 클릭한 후 이름을 data-to-insights로 설정합니다. 별표표시를 클릭합니다.

탐색기 섹션에 data-to-insights 프로젝트가 표시됩니다.

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

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

다음 단계를 따라 all_sessions_raw 테이블 데이터를 탐색합니다.

1. data-to-insights 가까이에 있는 노드 펼치기 아이콘을 클릭하여 프로젝트를 펼칩니다.
2. ecommerce를 펼칩니다.
3. all_sessions_raw를 클릭합니다.

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

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

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

질문:

중복 행 식별

샘플 데이터를 보면 데이터 세트에 무엇이 포함되어 있는지 더 잘 이해할 수 있습니다.

1. SQL을 사용하지 않고 테이블의 샘플 행을 미리 보려면 미리보기 탭을 클릭합니다.

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

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

• 모든 필드가 고유한 경우 모든 필드에 동일한 값을 가진 다른 행 그룹이 없으므로 COUNT는 1을 반환합니다.
• 모든 필드에 동일한 값을 갖는 행이 여러 개 있는 경우 이러한 행은 그룹화되며 COUNT는 1보다 큰 값을 반환합니다.

쿼리의 마지막 부분은 HAVING을 사용하여 1보다 큰 중복 COUNT가 있는 결과만 표시하는 집계 필터입니다. 따라서 중복이 있는 레코드의 수는 결과 테이블의 행 수와 동일합니다.

4. 다음 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 테이블을 사용합니다.

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

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

#standardSQL # schema: https://support.google.com/analytics/answer/3437719?hl=en 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. '+'(새 쿼리 작성) 아이콘을 클릭합니다.
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'를 사용해 보세요.

결과:

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

7. 전체 방문자(동일한 제품을 두 번 이상 본 사람 포함)로부터 많이 조회된(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;

결과:

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

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

결과:

9. 그런 다음 주문된 고유 제품의 총수와 주문된 총 단위의 총수(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;

결과:

질문:

10. 주문당 평균 제품 수량(주문된 단위 총수/총 주문 수, 또는 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단위로 가장 높았습니다.

내 진행 상황 확인하기를 클릭하여 목표를 확인합니다. 전자상거래 데이터에 대한 기본 SQL 작성

수고하셨습니다

수고하셨습니다. 이 실습에서는 BigQuery를 사용해 데이터를 보고 쿼리하여 제품 마케팅에 유용하게 활용할 수 있는 여러 측면의 인사이트를 얻었습니다. 전자상거래 데이터 세트에 액세스하는 방법, 데이터 세트 메타데이터를 확인하는 방법, 중복 항목을 삭제하는 방법, 쿼리를 작성하고 실행하는 방법을 알아봤습니다.

다음 단계/더 학습하기

• BigQuery 공개 데이터 세트를 살펴보세요.
• 이미 Google 애널리틱스 계정이 있고 BigQuery에서 자체 데이터 세트를 쿼리하고 싶으신가요? 이 내보내기 가이드를 따르세요.
• BigQuery와 관련해 잘 알려지지 않은 15가지 놀라운 사실을 확인하세요.
• 다른 실습도 확인하여 BigQuery에 대해 더 알아보세요.
  • BigQuery로 일반적인 SQL 오류 문제 해결하기
  • BigQuery의 날씨 데이터

Google Cloud 교육 및 자격증

Google Cloud 기술을 최대한 활용하는 데 도움이 됩니다. Google 강의에는 빠른 습득과 지속적인 학습을 지원하는 기술적인 지식과 권장사항이 포함되어 있습니다. 기초에서 고급까지 수준별 학습을 제공하며 바쁜 일정에 알맞은 주문형, 실시간, 가상 옵션이 포함되어 있습니다. 인증은 Google Cloud 기술에 대한 역량과 전문성을 검증하고 입증하는 데 도움이 됩니다.

설명서 최종 업데이트: 2024년 4월 2일

실습 최종 테스트: 2024년 4월 2일

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