Project Data Analysis for B2B Retail: Customer Analytics Report

Mentor: Trisna Yulia Junita — Data Scientist at PT. BUMA

Figure 1. List of tables
SELECT * FROM orders_1 LIMIT 5;
Figure 2. Output result for orders_1 table
SELECT * FROM orders_2 LIMIT 5;
Figure 3. Output result for orders_2 table
SELECT * FROM customer LIMIT 5;
Figure 4. Output result for customer table
SELECT quarter,
SUM(quantity) AS total_penjualan,
SUM(quantity*priceeach) AS revenue
FROM (
SELECT
orderNumber,
status,
quantity,
priceeach,
‘1’ AS quarter
FROM orders_1
WHERE status = ‘Shipped’
UNION
SELECT
orderNumber,
status,
quantity,
priceeach,
‘2’ AS quarter
FROM orders_2
WHERE status = ‘Shipped’
) AS tabel_a
GROUP BY quarter;
Figure 5. Output result for total sales and revenue
SELECT *
FROM (
SELECT
categoryID,
COUNT(DISTINCT orderNumber) AS total_order,
SUM(quantity) AS total_penjualan
FROM (
SELECT
productCode,
orderNumber,
quantity,
status,
LEFT(productCode,3) AS categoryID
FROM orders_1
WHERE status = “Shipped”
UNION
SELECT
productCode,
orderNumber,
quantity,
status,
LEFT(productCode,3) AS categoryID
FROM orders_2
WHERE status = “Shipped”
) AS tabel_c
GROUP BY categoryID) tabel_d
ORDER BY total_order DESC;
Figure 6. Output result for total sales and revenue by product category
SELECT quarter, 
COUNT(DISTINCT customerID) AS total_customers
FROM (
SELECT
customerID,
createDate,
QUARTER(createDate) AS quarter
FROM customer
WHERE createDate BETWEEN ‘2004–01–01’ AND ‘2004–06–30’
) AS tabel_b
GROUP BY quarter;
Figure 7. Output result for the number of registered consumers
SELECT quarter, 
COUNT(DISTINCT customerID) AS total_customers
FROM (
SELECT customerID,
createDate,
QUARTER(createDate) AS quarter
FROM customer
WHERE createDate BETWEEN ‘2004–01–01’ AND ‘2004–06–30’
) AS tabel_b
WHERE customerID IN (
SELECT DISTINCT customerID
FROM orders_1
UNION
SELECT DISTINCT customerID
FROM orders_2
)
GROUP BY quarter;
Figure 8. Output result for the number of registered customer who made transaction
#Menghitung total unik customers yang transaksi di quarter_1
SELECT COUNT(DISTINCT customerID) as total_customers FROM orders_1;
#output = 25
SELECT “1” AS quarter,
(COUNT(DISTINCT customerID)/25)*100 AS Q2
FROM orders_1
WHERE customerID IN (
SELECT DISTINCT customerID AS Q2
FROM orders_2
);
Figure 9. Output result for customer retention rate

Be happy

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store