Author: admin

Analisis data Penjualan dengan SQL

Beberapa waktu lalu saya menjumpai sebuah situs analisis data penjualan yang menarik minat saya di sebuah blog di medium . Kemudian saya ketahui bahwa sumber utama blog itu ada ada di Youtube Keith gali ini.

Dalam tulisan ini, saya menggunakan  sources yang ada di sini https://github.com/KeithGalli/Pandas-Data-Science-Tasks , untuk menggenerasi data.

Saya ingin melakukan analisis serupa, tapi hanya menggunakan SQLite via SQLiteStudio.

Iseng iseng saya menggenerate kembali dataset yang digunakan dengan pendekatan ke-Indonesiaan, yaitu dengan memodifikasi kota dan mata uang yang digunakan.

Saya menyatukan data bulanan berbentuk CSV yang dihasilkan oleh data generator yg dibuat Om Keith Galli, dan membersihkan datanya dengan Python, walaupun  untuk urusan membersihkan data, dengan SQL bisa juga, dimana kita cari kolom yang NULL lalu kita hapus.

Ok kita mulai.

Data Set

Data Set berupa data penjualan simulasi yang sudah siap pakai yang saya generasi dari skrip Pythonnya Keith Galli dengan beberapa modifikasi.

Dari data set diatas saya melakukan ekstraksi pada kolom Order_Date dan Purchase_Address agar mudah dianalisis. Saya menggunakan bantuan Library Regex pada SQLean .

SELECT  *, 
        REGEXP_CAPTURE(Purchase_Address, '^Jalan .+, (\w+?), .+', 1) || ' ' || 
        REGEXP_CAPTURE(Purchase_Address, ' (\w+) \d+$', 1) AS City, 
        REGEXP_CAPTURE(Order_Date, '^(\d{2})/', 1) AS Month,
        REGEXP_CAPTURE(Order_Date, '^\d{2}/(\d{2})', 1) AS Date,
        REGEXP_CAPTURE(Order_Date, '(\d{2}):\d{2}$', 1) AS Hour   
FROM sales

Hasilnya menjadi seperti ini

Lalu saya menyimpannya di view dengan nama sales2. Lalu view ini saya jadikan basis Query. Namun SQLiteStudio mempunyai kelemahan karena tidak bisa langsung menggenerasi Chart.

SQLite manager yang mampu menggenerate Chart adalah SqliteViz, namun Library SQLiteStudio  tidak bisa berjalan di SQLiteViz.

Kalau ingin menggenerasi chart lewat SQLiteViz, maka cara yang mudah adalah dengan menggenerate view table sales2 menjadi tabel yang sebenarnya. Bisa menggunakan Export CSV pada SQLite Studio dan langsung Load di SQLiteViz.

SQLiteViz bisa membaca dan melakukan query langsung dari CSV sekaligus bisa mengkonversi format CSV ke format SQLite dengan lebih baik daripada SQLiteStudio.

 Penjualan Bulanan

SELECT Month,
SUM(Quantity_Ordered * Price_Each) AS Sales
FROM sales2
GROUP BY Month

Menghasilkan output sebagai berikut:

Dengan grafik seperti ini

Mencari kota dengan tingkat penjualan tertinggi

SELECT  City, SUM(Quantity_Ordered * Price_Each) AS Sales FROM Sales2
GROUP BY City

Hasilnya sebagai berikut:

Dengan grafik sebagai berikut:

 

Analisis Waktu Penjualan

SELECT Hour, SUM(Quantity_Ordered * Price_Each) AS Sales  FROM sales2
Group BY Hour

Hasilnya sebagai berikut:

Dan nampak dalam grafik sbb:

Mencari produk yang terjual secara bersamaan (10 tertinggi)

Produk yang terjual secara bersamaan bisa dicari menggunakan SQL berikut

SELECT a.Product AS Product1, b.Product AS Product2, COUNT(a.Order_ID) AS Jumlah
FROM sales a
JOIN sales b ON a.Order_ID = b.Order_ID
WHERE a.Product < b.Product
GROUP BY Product1, Product2
ORDER BY Jumlah DESC

Menghasilkan hasil sebagai berikut:

Produk dengan Penjualan Tertinggi

SELECT Product, SUM(Quantity_Ordered) AS Quantity_Ordered,
CAST(AVG(Price_Each) AS INTEGER) AS Price
FROM sales2
GROUP BY Product

Menghasilkan output berikut:

Grafiknya akan seperti ini

Demikian, btw ini masih berupa output  yang belum dikasih komentar ya, nanti kalau ada waktu akan kita update 🙂

Menggunakan REGEX di SQLite

Saya ingin menjelaskan implementasi REGEX pada SQLite. Tapi sintak yang saya gunakan bukan library native tapi menggunakan library regex pada sqlean.

OK, langsung saja.

Saya mempunyai data sebagai berikut:

Saya ingin mencari

  1. Distribusi penjualan berdasarkan kota
  2. Distribusi penjualan berdasarkan bulan, tanggal, dan jam.

Tentu saja kita harus melakukan ekstraksi data pada kolom Purchase_Address, dan kolom Order_Date menggunakan fungsi Regex sbb:

1
2
3
4
5
6
7

SELECT  *, 
        REGEXP_CAPTURE(Purchase_Address, '^Jalan .+, (\w+?), .+', 1) || ' ' || 
        REGEXP_CAPTURE(Purchase_Address, ' (\w+) \d+$', 1) AS City, 
        REGEXP_CAPTURE(Order_Date, '^(\d{2})/', 1) AS MONTH,
        REGEXP_CAPTURE(Order_Date, '^\d{2}/(\d{2})', 1) AS DATE,
        REGEXP_CAPTURE(Order_Date, '(\d{2}):\d{2}$', 1) AS HOUR   
FROM sales

Lalu simpan dalam view agar mudah kita query kembali.

Dan cuplikan datanya bisa menjadi seperti ini

SQL Exercises

Menemukan SQL Exercises di GitHub. Kita bookmark dulu kapan-kapan kita kulik. BTW, walaupun SQL ini sering diartikan sebagai cara mengambil data secara mudah, namun pada level tertentu ya gak mudah juga.

Tapi jangan khawatir. Menguasai SQL sederhana saja kamu sudah mendapatkan insight banyak dari data.

 

 

SQLiteViz

Selama ini saya memang mencari-cari freeware yang mendukung visualisasi hasil query SQLite. SQLite Studio adalah versi terbaik dalam pengelolaan database sqlite. Memenuhi aspek kelengkapan dan kesederhanaan. Semua model pengolahan data mungkin bisa terpenuhi, tetapi masih dalam bentuk angka.

Sebenarnya akan menjadi sempurna apabila ditambahkan modul visualisasi. Tapi mungkin memang tidak semudah itu.

Tadi menemukan SQliteViz yang menawarkan visualisasi yang cukup lumayan, walaupun tidak menyertakan manajemen database yang baik. Jadinya SQLiteFiz melengkapi SQLiteStudio.

Aplikasi ini berbasis PWA, Progressive Web Application. Situs yang berbasis Javascript, bekerja di level lokal, dan bisa diinstall di Chrome, dan bisa bekerja secara Offline.

Kelebihan aplikasi ini adalah.

  1. Instalasinya mudah (seperti tidak melakukan instalasi)
  2. Mendukung SQLite
  3. Mendukung CSV dan bahkan data CSV bisa diquery
  4. Bisa mengeksport CSV ke Sqlite secara langsung dan otomatis.
  5. Bisa melakukan visualisasi hasil Query melalui library Plotly

Kelemahannya

  1. Hanya bisa menampilkan satu database dalam satu workspace.
  2. Apabila aplikasi di tutup, tidak bisa “keep” database sebelumnya dan model penyimpanan hasil olah data dalam menu inquiry masih terlalu sederhana dan kurang asik.

Demikian 🙂

 

Membuat Fungsi di SQLite Studio

Kadangkala kita mempunyai  kepentingan untuk membuat fungsi di SQLite yang belum pernah ada di fungsi builtin, dan kita terpaksa membuat sendiri.

Ada dua macam fungsi disitu yaitu fungsi skalar dan aggregate, namun disini saya akan membuat fungsi skalarnya saja, jika kita membutuhkan agregasi, ya kita bungkus aja dengan fungsi agregasi builtin-nya.

Sebagai contoh saya mempunyai tabel yang ada nilai rekomendasinya 0 – 10. Dan akan saya buatkan fungsi untuk mengetahui status NPSnya .

Menggunakan Perintah SQL

Misalnya kita membuat fungsi yang namanya fnps menggunakan SQL sbb:

Menggunakan Javascript

Satunya lagi menggunakan Javascript sbb:

Implementasi Fungsi

Maka fungsi-fungsi itu bisa kita gunakan sebagai berikut:

atau jika kita terapkan bersama fungsi agregasi bisa seperti ini

Menggunakan SQL untuk fungsi dalam batasan tertentu mungkin lebih simpel, namun untuk mekanisme yang lebih rumit mungkin perlu menggunakan Javascript sebagai bahasa fungsi  karena Javascript lebih banyak fiturnya.

Okey, enjoy SQL