Introduction to e-Commerce Data Analysis Using SQL

Pada kesempatan kali ini, gw coba akan ngasi beberapa contoh query-query analisis data penjualan ecommerce pakai SQL. Oh ya, data yang gue pake formatnya tu .csv, nah supaya ga perlu export-export-an gw bakal nge-query pake Deepnote. Di sana, lu bisa jadiin sebuah file .csv as table di RDB.

Di Deepnote, kita ga perlu buat export data ke SQL, soalnya kita bisa langsung nge-query dari file CSV di kernelnya. Berikut adalah tautan buat sumber dataset yang gw pake https://www.kaggle.com/datasets/olistbr/brazilian-ecommerce. Dataset ini adalah dataset ecommerce Brazil.

Source: Kaggle

Di sesi kali ini, gw bakal bahas empat soal dan coba ngasi query dan hasilnya. Cakupan query pada sesi kali ini adalah

1. Select
2. Join Table
3. Group By
4. Window Function (Partition)

Tenang, gw ga bakal bikin query yang logicnya ribet, would very beginner friendly. Oke, langsung aja kita ke studi kasusnya.

Count the number of orders per year and month.

Buat solve kasus ini, kita cuman butuh satu tabel (olist_orders_dataset). Kita cuman perlu mecah timestampnya jadi year dan month, terus kita group by kedua entitas tersebut. Kalo mau, bisa juga diapply sorting year dan month, pake order by, biar lebih gampang liat trennya.

SELECT  
    EXTRACT(YEAR FROM order_purchase_timestamp) as year,
    EXTRACT(MONTH FROM order_purchase_timestamp) as month,    
    count(order_id) count_ourder  
FROM 'olist_orders_dataset.csv'  
GROUP BY year, month;

Top 20 product categories based on total revenue and total order (only for delivered orders).

Inti dari pertanyaan ini adalah dapetin revenue (total pendapatan) dan jumlah order per kategori produk, dari pesenan (order) yang udah delivered. Nah ini rada kompleks, kita perlu join beberapa table (‘olist_order_items_dataset.csv’, ‘olist_products_dataset.csv’ dan ‘olist_orders_dataset.csv’) terus kita perlu filter yang statusnya “deliverd”. Nah abis tu, baru kita group per kategori produk dan kita urutin deh dari total revenue. Total revenue, didapat dari total biaya dari semua transaksi.

select   
    sum(price) as total_revenue,   
    count(oi.order_id) as number_order,   
    p.product_category_name  
from 'olist_order_items_dataset.csv' oi  
join 'olist_products_dataset.csv' p on oi.product_id = p.product_id  
join 'olist_orders_dataset.csv' o on o.order_id = oi.order_id  
where o.order_status = 'delivered' 
group by p.product_category_name  
order by  
    total_revenue desc,   
    number_order desc 
limit 20;

The number of orders which are delivered late from the estimated time per year and month.

Ini rada mirip sama query pertama, kita cuman perlu nge-count dari tabel orderlist berapa order yang deliverynya late. Nah buat ngefilter delivery yang late itu gimana, kita bisa make “where order_delivered_customer_date > order_estimated_delivery_date” yang maknanya timestamp deliverednya lebih dari estimated timenya.

select 
    EXTRACT(YEAR FROM order_purchase_timestamp) as year, 
    EXTRACT(MONTH FROM order_purchase_timestamp) as month, 
    count(order_id) count_ourder 
from 'olist_orders_dataset.csv' o
where 
    order_delivered_customer_date > order_estimated_delivery_date
GROUP BY year, month
order by year, month;

The top 3 customers per year and per month based on total spending.

Ini bakal jadi query terasik, karena kita bakal make window function. Kalo lu belum familiar window function tu apa, intinya dia adalah salah satu fungsi di SQL buat ngelakuin hal kompleks dengan melakukan pengerjaannya pake bikin jendela-jendela (pembagian) berdasarkan kolom yang ditentuin. Ciri dari query ini adalah kata “OVER”.

Bedanya dengan group by apa? Kalo group by semua hasilnya bakal cuman dikumpulin jadi satu per group yang dibikin, sedangkan window function ini coba buat mertahanin baris aslinya. Buat kamu yang pengen bikin perangkingan, fungsi ini cocok banget.

Di kasus ini, yang bakal kita jadiin window atau pengelompokan fungsi adalah tahun dan bulan baut dapetin customer dengan rank 1–3 per bulan per tahunnya. Nah kaya query awal, kita ekstrak dulu tuh timestmap-nya. Abis tu, kita generate spending pake jumlah harga + biaya pengiriman. Dari sana bakal kita pake buat window funcitonnya.

Terakhir kita bakal group per bulan dan per tahun baut dapetin spendingnnya, dari spending yang didapetin itu, kemudian kita rank per customer. Dari sini bakal keliatan nih rank 1–3 per tahun per bulannya.

select *
from
    (select 
        EXTRACT(YEAR FROM order_purchase_timestamp) as year, 
        EXTRACT(MONTH FROM order_purchase_timestamp) as month, 
        sum(oi.price + oi.freight_value) spending,
        RANK() OVER(PARTITION BY year, month ORDER BY spending DESC) AS spending_rank,
        customer_id
    from 'olist_orders_dataset.csv' as o
    join 'olist_order_items_dataset.csv' as oi on o.order_id = oi.order_id
    GROUP BY year, month, customer_id
    order by 1,2 asc)
where 
    spending_rank <= 3;

Ini lanjutannya masi banyak, cman gw cut di sini yagesya

Coba kita bandingin kalo make query group by aja berikut,

select 
    EXTRACT(YEAR FROM order_purchase_timestamp) as year, 
    EXTRACT(MONTH FROM order_purchase_timestamp) as month, 
    sum(oi.price + oi.freight_value) spending,
    customer_id
from 'olist_orders_dataset.csv' as o
join 'olist_order_items_dataset.csv' as oi on o.order_id = oi.order_id
GROUP BY year, month, customer_id
order by 3 desc
limit 3

Kita jadi ga bisa dapetin rank per bulannya, cuman customer dengan spending pada sebuah bulan terbesar yang muncul.

Conclusion

Gue yakin, introduction yang gue buat ini sangat banyak missing knowledge-nya. Nah, buat itu, lu bisa nih buat eksplor lebih lagi, dan nyoba-nyoba buat bikin soal sendiri. Harapannya dari adanya artikel ini, bisa jadi pemantik buat eksplor lebih jauh.

By the way, kalo bingung, lu bisa coba DM gw atau prompt ke ChatGPT, they smart enough for simple query. Thank you!!!