Data Analysis for Retail: Sales Performance Report using SQLite and Google Data Studio
Sales Performance Report atau Laporan Kinerja Penjualan adalah sebuah laporan yang menyediakan informasi tentang total dan perkiraan penjualan (pada kasus ini: penjualan pada retail).
Project ini merupakan salah satu modul project-based yang telah saya selesaikan di DQLab Academy. Project ini akan membahas overall sales performance secara umum dan per sub kategori, efektivitas dan efisiensi promosi yang dilakukan selama ini, dan analisis terhadap customer. Analisis akan dilakukan menggunakan SQL dengan RDBMS SQLite. Klik di sini untuk melihat Full SQL script.
Dataset Brief
Dataset yang digunakan berisi transaksi dari tahun 2009 sampai dengan tahun 2012 dengan jumlah raw data sebanyak 5499, termasuk di dalamnya order status yang terbagi menjadi order finished, order returned, dan order cancelled.
Adapun dataset yang akan digunakan pada project ini adalah tabel dqlab_sales_store yang berisi data sebagai berikut:
- order_id
- order_status
- customer
- order_date
- order_quantity
- sales
- discount
- discount_value
- prorduct_category
- product_sub_category
1A. Overall Sales Performance by Years
Buatlah Query dengan menggunakan SQL untuk mendapatkan total penjualan (sales) dan jumlah order (number_of_order) dari tahun 2009 sampai 2012 (years).
- Format data yang terdapat pada database adalah format standar yaitu tahun-bulan-hari atau ‘YYYY-mm-dd’. Untuk mendapatkan tahunnya, gunakan fungsi strftime(‘%Y’, order_date).
SELECT
DISTINCT strftime('%Y', order_date) AS years,
sum(sales) AS sales,
count(order_id) AS number_of_order
FROM
dqlab_sales_store
WHERE
order_status = 'Order Finished'
GROUP BY
1
ORDER BY
1;
1B. Overall Sales Performance by Product Sub Category
Buatlah Query dengan menggunakan SQL untuk mendapatkan total penjualan (sales) berdasarkan sub category dari produk (product_sub_category) pada tahun 2011 dan 2012 saja (years).
- Untuk melakukan grouping berdasarkan sub category output, gunakan sintaks GROUP BY berdasarkan product_sub_category atau 2 (mereferensikan kepada kolom ke-2 pada output).
SELECT
strftime('%Y', order_date) AS years,
product_sub_category,
sum(sales) AS sales
FROM
dqlab_sales_store
WHERE
(strftime('%Y', order_date) BETWEEN '2011' AND '2012')
AND
order_status = 'Order Finished'
GROUP BY
1,2
ORDER BY
1,3 DESC;
2A. Promotion Effectiveness and Efficiency by Years
Pada bagian ini saya akan menganalisis efektifitas dan efisiensi dari promosi yang sudah dilakukan selama ini.
Efektifitas dan efisiensi dari promosi yang dilakukan akan dianalisis berdasarkan Burn Rate yaitu dengan membandingkan total value promosi yang dikeluarkan terhadap total sales yang diperoleh.
DQLab berharap bahwa burn rate tetap berada diangka maksimum 4.5%.
Burn Rate = (total discount / total sales) * 100
Buatkan Derived Tables untuk menghitung total sales (sales) dan total discount (promotion_value) berdasarkan tahun (years) dan formulasikan persentase burn rate nya (burn_rate_percentage).
- Gunakan fungsi ROUND(nama_table, n) untuk melakukan pembulatan nilai persentase burn rate.
SELECT
DISTINCT strftime('%Y', order_date) AS years,
sum(sales) AS sales,
sum(discount_value) AS promotion_value,
round((round(sum(discount_value),2) / round(sum(sales),2))*100,2) AS burn_rate_percentage
FROM
dqlab_sales_store
WHERE
order_status = 'Order Finished'
GROUP BY
1
ORDER BY
1;
2B. Promotion Effectiveness and Efficiency by Product Sub Category
Pada bagian ini saya akan menganalisis efektifitas dan efisiensi dari promosi yang sudah dilakukan selama ini seperti pada bagian sebelumnya.
Akan tetapi, ada kolom yang harus ditambahkan, yaitu : product_sub_category dan product_category. Dan tampilkan analisis untuk tahun 2012 saja.
Mirip seperti query sebelumnya, namun kali ini tambahkan kolom product_sub_category dan product_category. Jangan lupa lakukan grouping pada sesuai kolom yang digunakan.
SELECT
strftime('%Y', order_date) AS years,
product_sub_category,
product_category,
sum(sales) AS sales,
sum(discount_value) AS promotion_value,
round((round(sum(discount_value),2) / round(sum(sales),2))*100,2) AS burn_rate_percentage
FROM
dqlab_sales_store
WHERE
order_status = 'Order Finished'
AND
strftime('%Y', order_date) = '2012'
GROUP BY
3,2,1
ORDER BY
4 DESC;
3A. Customers Transactions per Year
DQLab Store ingin mengetahui jumlah customer (number_of_customer) yang bertransaksi setiap tahun dari 2009 sampai 2012 (years).
- Gunakan sintaks DISTINCT dan juga fungsi lower() untuk menghitung jumlah customer unik yang melakukan transaksi.
SELECT
strftime('%Y', order_date) AS years,
count(DISTINCT lower(customer)) AS number_of_customer
FROM
dqlab_sales_store
WHERE
order_status = 'Order Finished'
GROUP BY
1
ORDER BY
1
3B. New Customers Transaction per Year
Berikut adalah analisis terhadap jumlah customer (number_of_new_customer) yang melakukan transaksi pertamanya untuk tahun 2009 sampai 2012 (years).
SELECT
strftime('%Y', first_trx_date) AS years,
count(customer) AS number_of_new_customer
FROM
(
SELECT
DISTINCT lower(customer) AS customer,
min(order_date) AS first_trx_date
FROM
dqlab_sales_store
WHERE
order_status = 'Order Finished'
GROUP BY
1
) AS first_trx
GROUP BY
1
ORDER BY
1;
3C. Customer Retention
Customer Retention mengacu pada kemampuan perusahan atau produk untuk mempertahankan customer dalam beberapa periode tertentu. Tingginya customer retention artinya customer pada perusahaan atau produk cenderung kembali untuk membeli produk tersebut.
Untuk mendapatkan customer retention dilakukan Cohort Analyis. Cohort analysis dilakukan pada data tahun 2009.
Cohort analysis ini dilakukan dengan melakukan grouping customer berdasarkan kapan customer tersebut melakukan transaksi pertamanya. Berikut ini adalah query yang dijalankan:
WITH trx_2009 AS (
SELECT
*
FROM
dqlab_sales_store
WHERE
strftime('%Y', order_date) = '2009'
AND
order_status = 'Order Finished'
)
,
cohort_items AS (
SELECT
DISTINCT customer,
strftime('%m', min(order_date)) AS cohort_month
FROM
trx_2009
GROUP BY
1
ORDER BY
1,2
),
user_activities AS (
SELECT
T.customer,
(strftime('%m', order_date) - cohort_month) AS month_number
FROM
trx_2009 T
LEFT JOIN
cohort_items C
ON
T.customer = C.customer
GROUP BY
1,2
),
cohort_size AS (
SELECT
cohort_month,
count(1) AS num_users
FROM
cohort_items
GROUP BY
1
ORDER BY
1
),
retention_table AS (
SELECT
C.cohort_month,
A.month_number,
count(1) AS num_users
FROM
user_activities A
LEFT JOIN
cohort_items C
ON
A.customer = C.customer
GROUP BY
1,2
)SELECT
B.cohort_month,
S.num_users AS total_users,
B.month_number,
round(round(B.num_users,2) * 100 / round(S.num_users,2),2) AS percentage
FROM
retention_table B
LEFT JOIN
cohort_size S
ON
B.cohort_month = S.cohort_month
WHERE
B.cohort_month IS NOT NULL
ORDER BY
1,3;
Untuk mendapatkan gambaran yang lebih jelas, saya melakukan visualisasi data di atas menggunakan Google Data Studio. Jenis visualisasi yang digunakan adalah Pivot Table with heatmap.
Kesimpulan
- Overall Sales performance per tahun berdasarkan total sales terjadi penurunan pada tahun 2010, namun kembali meningkat pada tahun selanjutnya. Sedangkan berdasarkan jumlah transaksi, terjadi penurunan jumlah transaksi pada tahun 2011, lalu terjadi peningkatan kembali di tahun 2012.
- Penjualan tertinggi berdasarkan sub kategori produk untuk tahun 2011 diperoleh dari penjualan Chairs & Chairmats sebesar 622,962,720 dan untuk tahun 2012 diperolah dari penjualan Office Machines sebesar 811,427,140. Sedangkan secara kumulatif, penjualan tertinggi untuk tahun 2011–2012 diperoleh dari penjualan Office Machines sebesar 1,357,283,420.
- Berdasarkan burn rate percentage, promosi yang dilakukan untuk tahun 2009–2012 kurang efektif dan efisien karena tidak berhasil memenuhi target maksimal burn rate yang ditetapkan DQLab yaitu sebesar 4.5%.
- Jumlah customer yang melakukan transaksi untuk tahun 2009–2012 terjadi penurunan pada tahun 2011, namun tidak ada perubahan yang signifikan karena jumlahnya berada di rentang angka kurang lebih 580 customer tiap tahunnya. Sedangkan untuk jumlah customer baru yang melakukan transaksi terjadi penurunan yang signifikan setiap tahunnya.
- Berdasarkan cohort analysis yang telah dilakukan dapat diukur customer retention atau berapa banyak customer aktif yang terus melakukan transaksi pada bulan berikutnya. Dari data transaksi tahun 2009, dapat disimpulkan bahwa dari sejumlah customer yang telah melakukan transaksi pertama, tidak banyak yang tetap melakukan transaksi pada bulan selanjutnya.
- Pada Januari 2009, dari total 107 customer, ada 9.35% customer yang terus melakukan transaksi di bulan ke-11 sejak transaksi pertamanya.