Data Analysis for E-commerce Challenge 📱

Hi there! This is my first project using SQL from DQLab Module to analysis data for e-commerce environment. In this project, I using BigQuery for SQL tools and LucidChart for draw the entity relationship diagram (erd).

Data Preparation

The datasets used is data from the DQLab Store is an e-commerce where buyers and sellers meet each other. Users can buy goods from other users who sell them. Each user can be both a buyer and a seller. There are 4 tables:

1. users : detailed user data

2. product : detailed data of the product sold

3. orders : purchase transactions from buyers to sellers

4. orders_detail : details of of the items purchased during the transaction

Chapter 1

Which statements are true about data 'products' ?

Let's check and explore the data, to know the dimension (row and column), null value, and product categories.

-- view the data
SELECT * 
/* this code meaning that I create "bismilla-272813" project, 
   use "dqlab" database and run query from the "products" table */
FROM `bismilla-272813.dqlab.products`;
-- identify the null value
SELECT *
FROM `bismilla-272813.dqlab.products` 
WHERE product_id is null or desc_product is null or category is null or base_price is null;
-- to know how many product category
SELECT DISTINCT category
FROM `bismilla-272813.dqlab.products`;

From that code, It knows that table contains 4 columns and 1145 rows, didn't contain the null value and It have 12 product category.

Which statements are true about data 'orders' ?

Let's check and explore the data again, same action for the question before.

-- view the data
SELECT * 
FROM `bismilla-272813.dqlab.orders`;
-- identify the null value
SELECT *
FROM `bismilla-272813.dqlab.orders` 
WHERE paid_at="NA";

It shows that orders table have 10 columns which are contains 3 columns with date datatype and 3 columns with amount data, and have 74847 rows.

Which statement are true about the monthly summary of monthly transaction data?

This question is ask about summary the total of transaction by month, so for the query I count the transaction that it paid by month of the date.

SELECT FORMAT_DATE("%Y-%m", created_at) as month, count(order_id) as transactions
FROM `bismilla-272813.dqlab.orders` 
GROUP BY 1
ORDER BY 1;

From this result, statement that true are :

• 4,327 transactions in September 2019

• 5,062 transactions in January 2020

• 7,323 transaction in March 2020

• 10,026 transaction in May 2020

It also known that the biggest transaction happened in December 2019 at the end of the year and following with transaction in May 2020

Which of the following statements are true about transaction summary data?

Summary about transaction data that is specific about the unpaid transaction, complete payment, and delivery status.

-- unpaid transactions
SELECT count(order_id) as unpaid_transaction
FROM `bismilla-272813.dqlab.orders` 
WHERE paid_at="NA";
-- transactions that have been paid but not sent
SELECT count(order_id) as paid_but_not_sent
FROM `bismilla-272813.dqlab.orders` 
WHERE paid_at!="NA" and delivery_at="NA";
-- transactions that were not sent, whether paid or not 
SELECT count(order_id) as not_sent 
FROM `bismilla-272813.dqlab.orders`
WHERE delivery_at="NA";
-- transactions sent on the same day as the payment date 
SELECT count(order_id) as same_day 
FROM `bismilla-272813.dqlab.orders`
WHERE delivery_at=paid_at;

From this result, statement that true are :

• 5,046 unpaid transactions

• There is total 9,790 transactions that not sent, whether paid or not

Which of the following statements is true about summary data of users and transactions?

The summary specific about calculation of each user can be both a buyer and a seller or be one of them.

-- total users
SELECT count(user_id) as total_users
FROM `bismilla-272813.dqlab.users`;
-- users who have transacted as buyers
SELECT count(user_id) as buyers
FROM `bismilla-272813.dqlab.users`
WHERE user_id IN (SELECT buyer_id FROM `bismilla-272813.dqlab.orders`);
-- -- users who have transacted as sellers
SELECT count(user_id) as sellers
FROM `bismilla-272813.dqlab.users`
WHERE user_id IN (SELECT seller_id FROM `bismilla-272813.dqlab.orders`);
-- users who have transacted as buyers and have been sellers
SELECT count(user_id) as both
FROM `bismilla-272813.dqlab.users`
WHERE user_id IN (SELECT seller_id FROM `bismilla-272813.dqlab.orders`) AND 
user_id IN (SELECT buyer_id FROM `bismilla-272813.dqlab.orders`);
-- users who never transacted as buyers and as sellers
SELECT count(user_id) as never
FROM `bismilla-272813.dqlab.users`
WHERE user_id NOT IN (SELECT seller_id FROM `bismilla-272813.dqlab.orders`) AND 
user_id NOT IN (SELECT buyer_id FROM `bismilla-272813.dqlab.orders`);

From this result, statement that true are :

• 17,877 users who have transacted as buyers

• 69 users who have transacted as sellers

• 69 users who have transacted both (as sellers and as buyers)

It show that 59 users are passive because they never transacted either as buyer or seller. It recommend for the company to investigate that users and evaluate the market.

Top Buyer All Time

Which are the 5 buyers with the largest total purchases (based on the total price of goods after discount)?

SELECT buyer_id, nama_user as nama_user, sum(total) as total
FROM `bismilla-272813.dqlab.orders` as orders
LEFT JOIN `bismilla-272813.dqlab.users` as users
ON orders.buyer_id=users.user_id
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 5;

This result show that the largest total price after discount are 54,102,250 rupiah from users id 14411 (Jaga Puspasari).

Frequent Buyer

Which users have never used discounts when buying goods and are the 5 buyers with the most transactions?

SELECT buyer_id, nama_user as nama_user, count(order_id) as transactions
FROM `bismilla-272813.dqlab.orders` as orders
LEFT JOIN `bismilla-272813.dqlab.users` as users
ON orders.buyer_id=users.user_id
WHERE discount=0
GROUP BY 1,2
ORDER BY 3 DESC, 2 ASC
LIMIT 5;

'Yessi Wibisono' is user with the most total transactions, following by buyer id number 10977 and 12577.

Big Frequent Buyer 2020

From user email, which users make transactions at least 1 time every month in 2020 with an average total amount per transaction of more than 1 million?

• For this question, it use subquery to get the condition and it save as summary table. It is include the count of month at least 5 month because as we know before, that in 2020 only have data between January and May.

• So, to get user who make transaction at least 1 per month, the transaction per user must have at least count of the month

SELECT user_id, email
FROM `bismilla-272813.dqlab.users` as users
INNER JOIN (
    SELECT buyer_id, count(extract(month from created_at)) as month,    
    count(order_id) as transaction,
    round(avg(total),2) as total_amount
    FROM `bismilla-272813.dqlab.orders`
    WHERE created_at >= "2020-01-01"
    GROUP BY 1
    HAVING month >= 5 AND transaction >= month AND 
    total_amount > 1000000
    ) as summary
ON users.user_id=summary.buyer_id
ORDER BY 1;

From the query, it return the 195 users who make transaction at least 1 per month and total amount > 1 million

Domain Email from Seller

Which is the email domain of the seller in the DQLab Store?

Domain is a unique name after the @ sign in email.

SELECT DISTINCT REGEXP_EXTRACT(email, r'@(.+)') AS domain
FROM `bismilla-272813.dqlab.users`
WHERE user_id IN (SELECT seller_id 
FROM `bismilla-272813.dqlab.orders`)
ORDER BY 1;

From this result, statement that true are :

• cv.web.id

• pt.net.id

It knows that mostly email domain of seller end with .id and only 3 domain end with .com

Top 5 Product in December 2019

Which are the top 5 products purchased in December 2019 based on total quantity?

SELECT desc_product, sum(quantity) as quantity
FROM `bismilla-272813.dqlab.products` as product
INNER JOIN `bismilla-272813.dqlab.order_details` as order_details
USING (product_id)
INNER JOIN `bismilla-272813.dqlab.orders` as orders
USING (order_id)
WHERE created_at between "2019-12-01" and "2019-12-31"
GROUP BY 1
ORDER BY 2 DESC 
LIMIT 5;

From this result, knows that Queen Cefa Bracelet Leather is the most sold product by quantity in December 2019.

Chapter 2

Best Selling Product Category in 2020

Write a query for displaying 5 Categories with the highest total quantity in 2020, only for transactions that have been sent to buyers. Show category, total quantity, total price.

SELECT category, count(order_id) as total_quantity, sum(price) as total_price
FROM `bismilla-272813.dqlab.products`
INNER JOIN`bismilla-272813.dqlab.order_details`
USING (product_id)
INNER JOIN `bismilla-272813.dqlab.orders`
USING (order_id)
WHERE created_at >= "2020-01-01" AND delivery_at != "NA"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

It shows that 'Kebersihan Diri' is the most sold product by category in 2020 with total of quantity 38,178 products.

Biggest Transactions of User ID 12476

Write query for 10 transactions from purchases as buyers from user_id 12476 and sorted by the largest transaction value.

SELECT seller_id, buyer_id, total as nilai_transaksi, created_at as tanggal_transaksi
FROM `bismilla-272813.dqlab.orders`
WHERE buyer_id = 12476
ORDER BY 3 desc
LIMIT 10;

It knows that the last time user id 12476 make transaction in 23 December 2020 with the large amount 12,014,000 rupiah and make purchase from different sellers.

Transactions per month

Explore the summary of transactions per month in 2020.

SELECT FORMAT_DATE("%Y-%m", created_at) as tahun_bulan, count(order_id) as jumlah_transaksi, sum(total) as total_nilai_transaksi
FROM `bismilla-272813.dqlab.orders`
WHERE created_at >= '2020-01-01'
GROUP BY 1
ORDER BY 1;

The quantity and spending amount in 2020 have exponential distribution which it increase continuously by month.

Biggest transaction average in January 2020

Find out the 10 buyers with the largest average transaction value who transacted at least 2 times in January 2020.

SELECT buyer_id, count(order_id) as jumlah_transaksi, avg(total) as avg_nilai_transaksi
FROM `bismilla-272813.dqlab.orders`
WHERE created_at>='2020-01-01' and created_at<'2020-02-01'
GROUP BY 1
HAVING jumlah_transaksi >= 2 
ORDER BY 3 DESC
LIMIT 10;

Buyer ID 11140 has the most average amount transacted and mostly users only have two transactions in January 2020,.

Big Deal in December 2020

All transaction values of at least 20,000,000 in December 2019 and sort buyer alphabetically.

SELECT nama_user as nama_pembeli, total as nilai_transaksi, created_at as tanggal_transaksi
FROM `bismilla-272813.dqlab.orders`
INNER JOIN `bismilla-272813.dqlab.users`
ON buyer_id = user_id
WHERE created_at >= '2019-12-01' and created_at < '2020-01-01' and total >= 20000000
ORDER BY 1;

• The last time transacted with values more than 20 Million rupiah in 28 December

• The largest value transaction in December 2020 is 29,930,000 by dr.Yulia Waskita

Chapter 3

High Value Buyers

Write an SQL query to find buyers who have transacted more than 5 times, and each transaction is more than 2,000,000.

SELECT nama_user as nama_pembeli, count(order_id) as jumlah_transaksi, sum(total) as total_nilai_transaksi, min(total) as min_nilai_transaksi
FROM `bismilla-272813.dqlab.orders` as orders
INNER JOIN `bismilla-272813.dqlab.users` as users
ON orders.buyer_id = users.user_id
GROUP BY user_id, nama_user
HAVING jumlah_transaksi > 5 AND min_nilai_transaksi > 2000000ORDER BY 3 DESC;

Dr. Sidiq Thamrin is the most valuable users who spend large amount with minimum of transaction 2,088,000 rupiah.

Looking for Dropshipper

Find out users who are dropshippers, are buyers who buy goods but are sent to someone else and have characteristics:

• Many transactions, at least 10 transaction

• Each transaction have different addresses

So, for the query it must calculated the number of postal code (kodepos) by each users to know that it sent in different addresses and validate with condition where number of transaction equal to number of postal code.

SELECT nama_user as nama_pembeli, count(order_id) as jumlah_transaksi, 
count(distinct orders.kodepos) as distinct_kodepos, sum(total) as total_nilai_transaksi, 
round(avg(total)) as avg_nilai_transaksi
FROM `bismilla-272813.dqlab.orders` as orders
INNER JOIN `bismilla-272813.dqlab.users` as users
ON buyer_id = user_id
GROUP BY user_id, nama_user
HAVING jumlah_transaksi >= 10 AND jumlah_transaksi=distinct_kodepos
ORDER BY 2 DESC;

Looking for Offline Reseller

Find out the types of users who are offline resellers or have offline stores, are buyers who often buy goods and sent to the same addresses, and have characteristics:

• Purchase with a large quantity of products with average more than 10

• Buyers who have 8 or more transactions

• Each transaction have same addresses

For the query, it use subquery into join statement to get data with aggregate total quantity per users. Then, conditional query to know it sent in same addresses by comparing the postal code between orders table and users table.

SELECT nama_user as nama_pembeli, count(order_id) as jumlah_transaksi, sum(total) as total_nilai_transaksi, 
round(avg(total)) as avg_nilai_transaksi, round(avg(total_quantity)) as avg_quantity_per_transaksi
FROM `bismilla-272813.dqlab.orders` as orders
INNER JOIN `bismilla-272813.dqlab.users` as users
ON buyer_id=user_id
INNER JOIN (
  SELECT order_id, sum(quantity) as total_quantity
  FROM `bismilla-272813.dqlab.order_details`
  GROUP BY 1
  ) as summary_order
USING (order_id)
WHERE orders.kodepos = users.kodepos
GROUP BY user_id, nama_user
HAVING jumlah_transaksi >= 8 and avg_quantity_per_transaksi > 10
ORDER BY 3 desc;

There are 26 users as offline resellers who have a large amount and quantity and sent in same or their address. So it is possible that this item will be sold again.

Looking for Buyers and Sellers

Find out sellers who has also transacted as a buyers at least 7 times transaction.

So, query must use subquery inside of inner join statement. First, to know users who transacted as buyers and then used another subquery to know users as sellers and match their relationship key with users table.

SELECT nama_user as nama_pengguna, jumlah_transaksi_beli, jumlah_transaksi_jual
from users
inner join (
  select buyer_id, count(1) as jumlah_transaksi_beli
  from orders
  group by 1) as buyer
on buyer_id=user_id
inner join (
  select seller_id, count(1) as jumlah_transaksi_jual
  from orders
  group by 1) as seller
on seller_id=user_id
where jumlah_transaksi_beli >= 7
order by 1;

It shows correlation that the more number of transactions as a seller, the less number of transactions as a buyer.

How long transacted is paid?

Want to know how the trend of the length of time the transaction is paid since it is made.

• Calculate the average length of time from transactions made to paid and grouped by month.

From this result, know that average of days between the transaction created until paid, it's takes tame in 7 -8 days, with minimum in one day and maximum in two weeks.

Summary

Based on the data that we have obtained through SQL queries, we can conclude that:

1. The largest revenue happened in December 2019 and May 2020. So, the company can investigate the factor that can increase revenue, maybe the holiday or summer time can be one of that factor.

2. 59 users are passive because they never transacted either as buyer or seller. It recommend for the company to investigate that users and evaluate the market.

3. The frequent buyer, high value buyer, biggest transaction can be the loyal customers group because they often transacted and large spending amount. So, it can be good to segmentation for new product.

4. In 2020, total order and revenue has increased by month, it is good news because the strategy that company implemented, it's work and let's keep it up!