Overview
BigQuery is Google's fully managed, NoOps, low cost analytics database. With BigQuery you can query terabytes and terabytes of data without having any infrastructure to manage or needing a database administrator. BigQuery uses SQL and can take advantage of the pay-as-you-go model. BigQuery allows you to focus on analyzing data to find meaningful insights.
We have a newly available ecommerce dataset that has millions of Google Analytics records for the Google Merchandise Store loaded into a table in BigQuery. In this lab, you use a copy of that dataset. Sample scenarios are provided, from which you look at the data and ways to remove duplicate information. The lab then steps you through further analysis the data.
To follow and experiment with the BigQuery queries provided to analyze the data, see Standard SQL Query Syntax.
What you'll do
In this lab, you use BigQuery to:
Access an ecommerce dataset
Look at the dataset metadata
Remove duplicate entries
Write and execute queries
Source:
This lab is from Qwiklabs.
Pin the Lab Project in BigQuery
In this section you add the data-to-insights project to your environment resources.
1. Click Navigation menu > BigQuery.
The Welcome to BigQuery in the Cloud Console message box opens.
The Welcome to BigQuery in the Cloud Console message box provides a link to the quickstart guide and UI updates.
2. Click Done.
BigQuery public datasets are not displayed by default in the BigQuery web UI. To open the public datasets project, open https://console.cloud.google.com/bigquery?project=data-to-insights&page=ecommerce in a new browser window.
In the left pane, in the Explorer section, hover over on data-to-insights and click on Click to pin icon.
5. Close this browser window.
6. Return to and refresh the first BigQuery browser window to refresh the BigQuery web UI.
The data-to-insights project is listed in the Explorer section.
Explore ecommerce data and identify duplicate records
Scenario: Your data analyst team exported the Google Analytics logs for an ecommerce website into BigQuery and created a new table of all the raw ecommerce visitor session data.
Explore the all_sessions_raw table data:
1. Click the Expand node icon near data-to-insights to expand the project.
2. Expand ecommerce.
3. Click all_sessions_raw.
In the right pane, a section opens that provides 3 views of the table data:
Schema tab: Field name, Type, Mode, and Description; the logical constraints used to organize the data
Details tab: Table metadata
Preview tab: Table preview
4. Click the Details tab to view the table metadata.
Identify duplicate rows
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.
Copy and paste the following query into the query EDITOR, then RUN query to find which records are duplicated across all columns.
#standardSQLSELECT COUNT(*) as num_duplicate_rows, * FROM`data-to-insights.ecommerce.all_sessions_raw`GROUP BYfullVisitorId, 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_optionHAVING num_duplicate_rows > 1;
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.
Analyze the new all_sessions table
In this section you use a deduplicated table called all_sessions.
Scenario: Your data analyst team has provided you with this query, and your schema experts have identified the key fields that must be unique for each record per your schema.
Run the query to confirm that no duplicates exist, this time in the all_sessions table:
#standardSQL# schema: https://support.google.com/analytics/answer/3437719?hl=enSELECTfullVisitorId, # the unique visitor IDvisitId, # a visitor can have multiple visitsdate, # session date stored as string YYYYMMDDtime, # time of the individual site hit (can be 0 to many per visitor session)v2ProductName, # not unique since a product can have variants like ColorproductSKU, # unique for each producttype, # 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 ordertransactionId, # unique identifier for revenue bearing transactionCOUNT(*) as row_countFROM`data-to-insights.ecommerce.all_sessions`GROUP BY 1,2,3 ,4, 5, 6, 7, 8, 9, 10,11,12HAVING row_count > 1 # find duplicates
The query returns zero records.
Note: In SQL, you can GROUP BY or ORDER BY the index of the column like using "GROUP BY 1" instead of "GROUP BY fullVisitorId"
Write basic SQL on ecommerce data
In this section, you query for insights on the ecommerce dataset.
Write a query that shows total unique visitors
Your query determines the total views by counting product_views and the number of unique visitors by counting fullVisitorID.
1. Click + Compose New Query.
2. Write this query in the editor:
#standardSQLSELECTCOUNT(*) AS product_views,COUNT(DISTINCT fullVisitorId) AS unique_visitorsFROM `data-to-insights.ecommerce.all_sessions`;
3. To ensure that your syntax is correct, click the real-time query validator icon.
4. Click Run. Read the results to view the number of unique visitors.
Results
Now write a query that shows total unique visitors(fullVisitorID) by the referring site (channelGrouping):
#standardSQLSELECTCOUNT(DISTINCT fullVisitorId) AS unique_visitors,channelGroupingFROM `data-to-insights.ecommerce.all_sessions`GROUP BY channelGroupingORDER BY channelGrouping DESC;
Results
Write a query to list all the unique product names (v2ProductName) alphabetically:
#standardSQLSELECT(v2ProductName) AS ProductNameFROM `data-to-insights.ecommerce.all_sessions`GROUP BY ProductNameORDER BY ProductName
Tip: In SQL, the ORDER BY clauses defaults to Ascending (ASC) A-->Z. If you want the reverse, try ORDER BY field_name DESC
Results
This query returns a total of 633 products (rows).
Write a query to list the five products with the most views (product_views) from all visitors (include people who have viewed the same product more than once). Your query counts number of times a product (v2ProductName) was viewed (product_views), puts the list in descending order, and lists the top 5 entries:
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'.
#standardSQLSELECTCOUNT(*) AS product_views,(v2ProductName) AS ProductNameFROM `data-to-insights.ecommerce.all_sessions`WHERE type = 'PAGE'GROUP BY v2ProductNameORDER BY product_views DESCLIMIT 5;
Results
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
Tip: You can use the SQL WITH clause to help break apart a complex query into multiple steps. Here we first create a query that finds each unique product per visitor and counts them once. Then the second query performs the aggregation across all visitors and products.
Results
Next, expand your previous query to include the total number of distinct products ordered and the total number of total units ordered (productQuantity):
#standardSQLSELECTCOUNT(*) AS product_views,COUNT(productQuantity) AS orders,SUM(productQuantity) AS quantity_product_ordered,v2ProductNameFROM `data-to-insights.ecommerce.all_sessions`WHERE type = 'PAGE'GROUP BY v2ProductNameORDER BY product_views DESCLIMIT 5;
Results
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)).
#standardSQLSELECTCOUNT(*) AS product_views,COUNT(productQuantity) AS orders,SUM(productQuantity) AS quantity_product_ordered,SUM(productQuantity) / COUNT(productQuantity) AS avg_per_order,(v2ProductName) AS ProductNameFROM `data-to-insights.ecommerce.all_sessions`WHERE type = 'PAGE'GROUP BY v2ProductNameORDER BY product_views DESCLIMIT 5;
Results
The 22 oz YouTube Bottle Infuser had the highest avg_per_order with 9.38 units per order.
Congratulations!
This concludes exploring the data-to-insights ecommerce dataset! You used BigQuery to view and query the data to gain meaningful insight on various aspects of product marketing.
Reference:
1. Qwiklabs
2. Google Cloud Certification - Associate Cloud Engineer
3. Standard SQL Query Syntax
4. Get Meaningful Insights with Google BigQuery | Google Cloud Labs
最初發表 / 最後更新: 2021.11.06 / 2021.11.06
0 comments:
張貼留言