Data Analysis for Retail: Sales Performance Report using SQLite and Google Data Studio

Photo by Lukas Blazek on Unsplash

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:

1. order_id
2. order_status
3. customer
4. order_date
5. order_quantity
6. sales
7. discount
8. discount_value
9. prorduct_category
10. 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;

Total penjualan (sales) dan jumlah order (number_of_order) dari tahun 2009 sampai 2012 (years).

Overall sales performance dilihat dari jumlah sales dan transaksi untuk tahun 2009–2012.

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;

Total penjualan (sales) berdasarkan sub category dari produk (product_sub_category) pada tahun 2011 dan 2012 saja (years).

Overall sales performance tiap sub kategori produk untuk masing-masing tahun 2011 dan 2012.

Overall sales performance tiap sub kategori produk tahun kumulatif 2011–2012.

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;

Total sales (sales) dan total discount (promotion_value) berdasarkan tahun (years) dan diformulasikan dalam burn_rate_percentage.

Burn rate percentage per tahun untuk tahun 2009–2012.

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;

Total sales (sales) dan total discount (promotion_value) berdasarkan tahun (years), product_sub_category, dan product_category. Lalu diformulasikan dalam burn_rate_percentage.

Burn rate percentage untuk tiap sub kategori produk pada tahun 2012.

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

Jumlah customer (number_of_customer) yang bertransaksi setiap tahun dari 2009 sampai 2012 (years).

Jumlah customer yang melakukan transksi tiap tahunnya untuk rentang tahun 2009–2012.

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;

Jumlah customer (number_of_new_customer) yang melakukan transaksi pertamanya untuk tahun 2009 sampai 2012 (years).

Jumlah customer yang melakukan transaksi pertamanya pada tahun 2009–2012.

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;

Cohort Analysis menggunakan SQL.

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.

Customer Retention pada tahun 2009.

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.