First thing first We will discuss what is Bigquery and why we are choosing Bigquery…..
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.
What we are doing here trust me the lab is exactly like the analysis you do for your ecommerce site so it is better that you will take advantage of bigquery free tier and follow along with me from here so that you could put your skills in your Resume and become the future of your past.
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.
What you’ll do
you use BigQuery to:
- Access an ecommerce dataset
- Look at the dataset metadata
- Remove duplicate entries
- Write and execute queries(quality query)
so log into google cloud console
A glimpse of your dashboard
In this section you add the data-to-insights project to your environment resources
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?p=data-to-insights&page=ecommerce in a new browser window. In the left pane, in the Resource section, click data-to-insights. In the right pane, click Pin Project.
Close this browser window. Return to and refresh the first BigQuery browser window to refresh the BigQuery web UI. The
data-to-insights project is listed in the Resource section. Close this browser window. Return to and refresh the first BigQuery browser window to refresh the BigQuery web UI.
Click data-to-insights to expand the project.Click ecommerce.Click all_sessions_raw.
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
- 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
COUNTreturns 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
COUNTwill 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.
#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;
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
In this section you use a deduplicated table called
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
#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
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
- Click + Compose New Query.
- Write this query in the editor:
#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;
- To ensure that your syntax is correct, click the real-time query validator icon.
- Click Run Query. Read the results to view the number of unique visitors.
Now write a query that shows total unique visitors(
fullVisitorID) by the referring site (
#standardSQL SELECT COUNT(DISTINCT fullVisitorId) AS unique_visitors, channelGrouping FROM `data-to-insights.ecommerce.all_sessions` GROUP BY channelGrouping ORDER BY channelGrouping DESC;
Write a query to list all the unique product names (
#standardSQL SELECT (v2ProductName) AS ProductName FROM `data-to-insights.ecommerce.all_sessions` GROUP BY ProductName ORDER BY ProductName
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’.
#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
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.
Next, expand your previous query to include the total number of distinct products ordered and the total number of total units ordered (
#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
#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;
The 22 oz YouTube Bottle Infuser had the highest avg_per_order with 9.38 units per order.
congratulations you just unpack the art of Questioning in next section we will ensure analysis with the query.