arrow_back

Exploring Your Ecommerce Dataset with SQL in Google BigQuery

Join Sign in

Exploring Your Ecommerce Dataset with SQL in Google BigQuery

30 minutes Free

GSP407

Google Cloud self-paced labs logo

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 the BigQuery Query Syntax Reference.

What you'll learn

In this lab, you use BigQuery to:

  • Access an ecommerce dataset

  • Look at the dataset metadata

  • Remove duplicate entries

  • Write and execute queries

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

  • Access to a standard internet browser (Chrome browser recommended).
Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.
  • Time to complete the lab---remember, once you start, you cannot pause a lab.
Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

How to start your lab and sign in to the Google Cloud Console

  1. Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:

    • The Open Google Console button
    • Time remaining
    • The temporary credentials that you must use for this lab
    • Other information, if needed, to step through this lab
  2. Click Open Google Console. The lab spins up resources, and then opens another tab that shows the Sign in page.

    Tip: Arrange the tabs in separate windows, side-by-side.

    Note: If you see the Choose an account dialog, click Use Another Account.
  3. If necessary, copy the Username from the Lab Details panel and paste it into the Sign in dialog. Click Next.

  4. Copy the Password from the Lab Details panel and paste it into the Welcome dialog. Click Next.

    Important: You must use the credentials from the left panel. Do not use your Google Cloud Skills Boost credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.
  5. Click through the subsequent pages:

    • Accept the terms and conditions.
    • Do not add recovery options or two-factor authentication (because this is a temporary account).
    • Do not sign up for free trials.

After a few moments, the Cloud Console opens in this tab.

Note: You can view the menu with a list of Google Cloud Products and Services by clicking the Navigation menu at the top-left. Navigation menu icon

Task 1. 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.

Note: The Welcome to BigQuery in the Cloud Console message box provides a link to the quickstart guide and UI updates.
  1. Click Done.
  2. BigQuery public datasets are not displayed by default in the BigQuery web UI. To open the public datasets project, copy data-to-insights.
  3. Click Add Data > Pin a project > Enter Project Name, then set the name to data-to-insights. Click Pin.

The Explorer section now lists the data-to-insights project.

Task 2. 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

  1. Click the Details tab to view the table metadata.

Questions:

Identify duplicate rows

Seeing a sample amount of data may give you greater intuition for what is included in the dataset.

  1. To preview sample rows from the table without using SQL, click the preview tab.

  2. 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.

  3. 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.

  1. 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;

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.

Click Check my progress to verify the objective. Identify duplicate rows

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=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.

Task 3. 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:

#standardSQL SELECT COUNT(*) AS product_views, COUNT(DISTINCT fullVisitorId) AS unique_visitors FROM `data-to-insights.ecommerce.all_sessions`;
  1. To ensure that your syntax is correct, confirm that the real-time query validator shows the Green check icon.
  2. Click Run. Read the results to view the number of unique visitors.

Results:

A three-column table showing the number of rows, product_views, and unique_visitors.

  1. Now write a query that shows total unique visitors(fullVisitorID) by the referring site (channelGrouping):

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

Results:

A three-column table showing several rows of unique_visitors and channelGrouping.

  1. Write a query to list all the unique product names (v2ProductName) alphabetically:

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

Tip: In SQL, the ORDER BY clauses defaults to Ascending (ASC) A to Z. If you want the reverse, try ORDER BY field_name DESC.

Results:

The Results tabbed page displays a table containing numerous rows of ProductName.

This query returns a total of 633 products (rows).

  1. Write a query to list the five products with the most views (product_views) from all visitors (including 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;

Results:

The Results tabbed page displays a table containing five rows of product_views and ProductName.

  1. 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:

The Results tabbed page displays a table containing five rows of unique_view_count and ProductName.

  1. 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;

Results:

A table containing five rows of product_views, orders, quantity_product_ordered_, and v2ProductName.

Questions:

  1. 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;

Results

A table containing five rows of product_views, orders, quantity_product_ordered_, avh_per_order, and v2ProductName.

Question:

The 22 oz YouTube Bottle Infuser had the highest avg_per_order with 9.38 units per order.

Click Check my progress to verify the objective. Write basic SQL on ecommerce data

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.

Finish your quest

This self-paced lab is part of the BigQuery for Marketing Analysts and BigQuery Basics for Data Analysts quests. A quest is a series of related labs that form a learning path. Completing a quest earns you a badge to recognize your achievement. You can make your badge or badges public and link to them in your online resume or social media account. Enroll in any quest that contains this lab and get immediate completion credit. See the Google Cloud Skills Boost catalog to see all available quests.

Take your next lab

Continue your Quest with the next lab, Troubleshooting Common SQL Errors with BigQuery.

Check out other labs to learn more about BigQuery, for example, Weather Data in BigQuery.

Next steps/Learn more

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated: July 21, 2022

Lab Last Tested: April 12, 2022

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.