1. Identify Duplicate Rows
a. Query Syntax
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax
Seeing a sample amount of data may give you greater intuition for what is included in the dataset.
- To preview sample rows from the table without using SQL, click the preview tab.
- Scan and scroll through the rows. There is no singular field that uniquely identifies a row, so you need advanced logic to identify duplicate rows.
- The query you'll use (below) uses the SQL GROUP BY function on every field and counts (COUNT) where there are rows that have the same values across every field:
- If every field is unique, the COUNT returns 1 as there are no other groupings of rows with the exact same value for all fields.
- If there are multiple rows with the same values for all fields, these rows are grouped together and the COUNT will be greater than 1.
The last part of the query is an aggregation filter using HAVING to only show the results that have a COUNT of duplicates greater than 1. Therefore, the number of records that have duplicates will be the same as the number of rows in the resulting table.
#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;
Note: In your own datasets, even if you have a unique key, it is still beneficial to confirm the uniqueness of the rows with COUNT, GROUP BY, and HAVING before you begin your analysis.
2. BigQuery Export Schema
a. 공식문서
https://support.google.com/analytics/answer/3437719?hl=en
b. Write a query that shows total unique visitors
Tip: In Google Analytics, a visitor can "view" a product during the following interaction types: 'page', 'screenview', 'event', 'transaction', 'item', 'social', 'exception', 'timing'. For our purposes, simply filter for only type = '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;
Bonus: Now refine the query to no longer double-count product views for visitors who have viewed a product many
times. Each distinct product view should only count once per visitor:
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
Next, expand your previous query to include the total number of distinct products ordered and the total number of total units ordered (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;
Expand the query to include the average amount of product per order (total number of units ordered/total number of orders, or 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;
'CS > DataEngineering' 카테고리의 다른 글
Data Lake vs Data Warehouse (0) | 2022.12.10 |
---|---|
프로그래머스 데이터 엔지니어링 컨퍼런스 | 뱅크샐러드 데이터 엔지니어의 눈물! 호락호락하지 않은 공공데이터와의 전쟁 (0) | 2022.11.29 |
SQL On Hadoop 분석 도구인 Hive와 Impala는 어떤 차이가 있을까? (0) | 2022.08.21 |
DATA ENGINEERING EXPLAINED (0) | 2022.08.14 |
데이터 중심 애플리케이션 설계 (0) | 2022.06.28 |
댓글