Explore-Ecommerce-Dataset

1. Introduction

This is an eCommerce dataset exploration project using SQL on Google BigQuery. The dataset is based on the Google Analytics public dataset and contains data from an eCommerce website.

2. Requirements

• Google Cloud Platform account
• Google BigQuery API eneble
• SQL query editor or IDE

3. Dataset access

The Ecommerce dataset is stored in the public Google BigQuery dataset. Follow these steps to access the dataset on Google Big Query:

1. Sign in to your Google Cloud Platform account and create a new project.
2. Navigate to the BigQuery dashboard and select your newly created project.
3. In the navigation panel, select "Add Data" and then select "Search Projects".
4. Enter the project ID "bigquery-public-data.google_analytics_sample.ga_sessions" and click "Enter".
5. Click on the table "ga_sessions_" to the dataset.

4. Exploring the Dataset

Use SQL in Google BigQuery against the Google Analytics data set to write and execute queries to find the desired data for the purpose of answering business questions

Query 01: calculate total visit, pageview, transaction for Jan, Feb and March 2017 (order by month).

Link to Bigquery

SQL code

Query Results

Query 02: Bounce rate per traffic source in July 2017 (Bounce_rate = num_bounce/total_visit) (order by total_visit DESC).

Link to Bigquery

SQL code

Query Results

Query 03: Revenue by traffic source by week, by month in June 2017.

Link to Bigquery

SQL code

Query Results

Query 04: Average number of pageviews by purchaser type (purchasers vs non-purchasers) in June, July 2017.

Link to Bigquery

SQL code

Query Results

Query 05: Average number of transactions per user that made a purchase in July 2017

Link to Bigquery

SQL code

Query Results

Query 06: Average amount of money spent per session. Only include purchaser data in July 2017

Link to Bigquery

SQL code

Query Results

Query 07: Other products purchased by customers who purchased product "YouTube Men's Vintage Henley" in July 2017. Output should show product name and the quantity was ordered.

Link to Bigquery

SQL code

Query Results

Query 08: Calculate cohort map from product view to addtocart to purchase in Jan, Feb and March 2017. For example, 100% product view then 40% add_to_cart and 10% purchase. Add_to_cart_rate = number product add to cart/number product view. Purchase_rate = number product purchase/number product view. The output should be calculated in product level.

Link to Bigquery

SQL code

Query Results