Basket size analysis

Basket Size Analysis, bagaimana caranya?

August 6, 2023 0 comments Article Analisis Tabel Kompleks

Jika berbicara tentang data penjualan retail dan informasi terkait order, salah satu analisis populer yang bisa dilakukan adalah analisis basket size. Analisis ini berfokus pada Berapakah nilai/total barang rata rata dari setiap order/pembelian oleh customer. Selain itu, bisa dikembangkan analisis basket size tapi bukan berbasis customer, tapi berbasis employee (analisis beban kerja), shipper (kinerja pengiriman), produk (kinerja penjualan per produk.

Oleh karena itu, ini beberapa insight yang dapat dikeluarkan:

  1. Customer, Jumlah order, total produk dibeli, dan Nilai produk dibeli
  2. Employee, Jumlah order di handle, total produk di handle, dan Nilai produk di handle
  3. Shipper, Jumlah order di handle, total produk di handle, dan Nilai produk di handle
  4. Produk, Jumlah order, total produk terjual, dan Nilai produk terjual

Untuk itu kita bedah tabel Orders yang merupakan tabel inti transaksional dari E-Commerce Northwind yang menyimpan data penjualan produk dan pengirimannya.

Pada case Database Northwind, Tabel Orders memiliki kolom: OrderID, CustomerID, EmployeeID, OrderDate, ShipperID

Tabel Orders berelasi dengan tabel:

  • Customers melalui kolom CustomerID
  • Employees melalui kolom EmployeeID
  • Shippers melalui kolom ShipperID

Selain merefer pada tabel lain, Tabel Orders juga di refer oleh Tabel OrderDetails.

Tabel OrderDetails memiliki kolom: OrderDetailID, OrderID, ProductID, Quantity

Tabel OrderDetails berelasi dengan tabel:

  • Orders melalui kolom OrderID
  • Products melalui kolom ProductID

Versi SQL

Customer Basket Size (Jumlah order, total produk dibeli, dan Nilai produk dibeli)

Dalam dunia Retail, ini dinamakan Basket Size Analysis

select tot.Year, tot.CustomerName, tot.Total_Qty, tot.Nilai_Pembelian, ord.Jml_Order, round(tot.Total_Qty/ord.Jml_Order) as Avg_Qty_per_Order, round(tot.Nilai_Pembelian/ord.Jml_Order) as Avg_Nilai_per_Order from
(
SELECT YEAR(o.OrderDate) as Year, o.CustomerID, count(1) as Jml_Order
FROM Orders o
group by 1,2
) as ord
join
(
SELECT YEAR(o.OrderDate) as Year, c.CustomerID, c.CustomerName, sum(d.Quantity) as Total_Qty, sum(d.Quantity*p.Price) as Nilai_Pembelian
FROM Orders o join OrderDetails d on (o.OrderID=d.OrderID)
join Customers c on (c.CustomerID=o.CustomerID)
join Products p on (p.ProductID=d.ProductID)
group by 1,2,3
) as tot
on (ord.Year=tot.Year and ord.CustomerID=tot.CustomerID)
order by tot.CustomerName, tot.Year

Employee Load (Jumlah order di handle, total produk di handle, dan Nilai produk di handle)

select tot.Year, tot.EmployeeName, tot.Total_Qty, tot.Nilai_Pembelian, ord.Jml_Order, round(tot.Total_Qty/ord.Jml_Order) as Avg_Qty_per_Order, round(tot.Nilai_Pembelian/ord.Jml_Order) as Avg_Nilai_per_Order from
(
SELECT YEAR(o.OrderDate) as Year, o.EmployeeID, count(1) as Jml_Order
FROM Orders o
group by 1,2
) as ord
join
(
SELECT YEAR(o.OrderDate) as Year, e.EmployeeID, concat(e.FirstName, ' ', e.LastName) as EmployeeName, sum(d.Quantity) as Total_Qty, sum(d.Quantity*p.Price) as Nilai_Pembelian
FROM Orders o join OrderDetails d on (o.OrderID=d.OrderID)
join Employees e on (e.EmployeeID=o.EmployeeID)
join Products p on (p.ProductID=d.ProductID)
group by 1,2,3
) as tot
on (ord.Year=tot.Year and ord.EmployeeID=tot.EmployeeID)
order by tot.EmployeeName, tot.Year

Shipper Load (Jumlah order di handle, total produk di handle, dan Nilai produk di handle)

select tot.Year, tot.ShipperName, tot.Total_Qty, tot.Nilai_Pembelian, ord.Jml_Order, round(tot.Total_Qty/ord.Jml_Order) as Avg_Qty_per_Order, round(tot.Nilai_Pembelian/ord.Jml_Order) as Avg_Nilai_per_Order from
(
SELECT YEAR(o.OrderDate) as Year, o.ShipperID, count(1) as Jml_Order
FROM Orders o
group by 1,2
) as ord
join
(
SELECT YEAR(o.OrderDate) as Year, s.ShipperID, s.ShipperName, sum(d.Quantity) as Total_Qty, sum(d.Quantity*p.Price) as Nilai_Pembelian
FROM Orders o join OrderDetails d on (o.OrderID=d.OrderID)
join Shippers s on (s.ShipperID=o.ShipperID)
join Products p on (p.ProductID=d.ProductID)
group by 1,2,3
) as tot
on (ord.Year=tot.Year and ord.ShipperID=tot.ShipperID)
order by tot.ShipperName, tot.Year

Produk Basket Size (Jumlah order, total produk terjual, dan Nilai produk terjual)

SELECT YEAR(o.OrderDate) as Year, p.ProductName, sum(d.Quantity) as Total_Qty, sum(d.Quantity*p.Price) as Nilai_Pembelian, count(1) as Jml_Order, round(sum(d.Quantity)/count(1)) as Avg_Qty_per_Order, round(sum(d.Quantity*p.Price)/count(1)) as Avg_Nilai_per_Order
FROM Orders o join OrderDetails d on (o.OrderID=d.OrderID)
join Products p on (p.ProductID=d.ProductID)
group by 1,2
order by 2,1

Versi Excel

/* Segera di update */

Versi GoogleSheet

/* Segera di update */

Kesimpulan

/* Segera di update */

Resources

File Excel :/* Segera di update */

GoogleSheet :/* Segera di update */

File Master :https://optimasikerjaid.sugihartono.web.id/pengumuman/introduksi-kelasreporting-id/

Leave a Reply

Your email address will not be published. Required fields are marked *