1 of 19

Superstore Monthly Promotion Program Analysis

By Ade Desfrianto

Thursday, January 26, 2023

2 of 19

Table of contents

Case Study

1

Data Understanding & Query SQL

2

Analysis

3

Conclusion & Recommendation

4

3 of 19

Case Study

1

Tim Marketing dan Tim Central Strategy ingin mengetahui program promosi bulanan yang paling berhasil. Terdapat beberapa metrics yang ingin dieksplorasi sebagai tolak ukur kesuksesan program promosi bulanan. Dimana setiap bulan terdapat program promosi marketing yang berbeda. Sebagai BI Analyst, kamu diminta untuk memperoleh metrics di bawah ini:

  • Monthly organic order. Jumlah OrderID yang tidak terdapat Discount.
  • Monthly inorganic order. Jumlah OrderID yang terdapat Discount.
  • Monthly organic order ratio. Jumlah organic order dibandingkan dengan total order.
  • Monthly total discount against sales. Jumlah discount yang dikeluarkan dan total sales per bulan. Dimana setiap discount yang diberikan adalah bagian dari program promo Marketing.

4 of 19

Data Understanding

2

Source data: Dataset Superstore

Tabel Order

Tabel ID: ade-test-data.Ade_data.ade_binar_superstore

Nama Kolom

Tipe Data

Deskripsi

Order ID

STRING

Kode unik untuk setiap order (pemesanan)

Order Date

DATE

Tanggal saat order (pemesanan) dibuat

Ship Date

DATE

Tanggal pengiriman dilakukan

Ship Mode

STRING

Jenis layanan pengiriman yang digunakan

Customer ID

STRING

Kode unik untuk setiap customer

Country

STRING

Negara tujuan pengiriman produk (dalam kasus ini hanya “United States”)

City

STRING

Kota tujuan pengiriman produk

State

STRING

Negara bagian Amerika Serikat tujuan pengiriman produk

Postal Code

INTEGER

Kode pos tujuan pengiriman produk

Region

STRING

Region tujuan pengiriman produk

Product ID

STRING

Kode unik untuk setiap product

Sales

FLOAT

Harga dari pemesanan (dalam dolar Amerika Serikat)

Quantity

INTEGER

Jumlah produk yang dipesan

Discount

FLOAT

Diskon yang dikenakan pada pemesanan

Profit

FLOAT

Keuntungan dari pemesanan (dalam dolar Amerika Serikat)

Tabel Categories

Tabel ID:ade-test-data.Ade_data.Categories

Nama Kolom

Tipe Data

Deskripsi

Product ID

STRING

Kode unik untuk setiap product

Category

STRING

Kategori dari produk yang dipesan

Sub-Category

STRING

Sub-kategori dari produk yang dipesan

Product Name

STRING

Nama produk yang dipesan

Tabel Customer

Tabel ID: ade-test-data.Ade_data.Customer_View

Nama Kolom

Tipe Data

Deskripsi

Customer ID

STRING

Kode unik untuk setiap customer

Segment

STRING

Segmen dari customer (pelanggan) yang melakukan pemesanan

Customer Name

STRING

Nama dari customer (pelanggan) yang melakukan pemesanan

5 of 19

Data Understanding

2

Order ID

Order Date

Ship Date

Ship Mode

Customer ID

CA-2017-115427

12/30/2017

1/3/2018

Standard Class

EB-13975

CA-2017-156720

12/30/2017

1/3/2018

Standard Class

JM-15580

CA-2017-118885

12/29/2017

1/2/2018

Standard Class

JG-15160

CA-2017-118885

12/29/2017

1/2/2018

Standard Class

JG-15160

  • Untuk melakukan analisis perbulanan, pada kolom Order Date akan dilakukan fungsi DATE TRUNC untuk mengubah data harian menjadi data bulanan. Kemudian fungsi GROUP BY digunakan untuk mengelompokkan hasil berdasarkan bulan.
  • Kemudian karena dalam basis data memiliki granularitas produk yang dapat menyebabkan adanya Order ID yang duplikat, fungsi COUNT DISTINCT akan digunakan untuk menghitung jumlah Order ID yang unik.

6 of 19

Query SQL

2

  • Menghasilkan matrics Monthly organic order. Jumlah OrderID yang tidak terdapat Discount.
  • Menggunakan fungsi DATE_TRUNC untuk mengubah tanggal order menjadi bulan
  • Menggunakan COUNT(DISTINCT) untuk menghitung jumlah order unik yang tidak mendapatkan diskon. Dan menggunakan CASE WHEN untuk mengecek apakah order tersebut memiliki diskon ( = 0 ).
  • Menggunakan GROUP BY untuk mengelompokkan hasil perhitungan berdasarkan bulan.

7 of 19

Query SQL

2

  • Menghasilkan matrics Monthly inorganic order. Jumlah OrderID yang terdapat Discount.
  • Menggunakan fungsi DATE_TRUNC untuk mengubah tanggal order menjadi bulan
  • Menggunakan COUNT(DISTINCT) untuk menghitung jumlah order unik yang tidak mendapatkan diskon. Dan menggunakan CASE WHEN untuk mengecek apakah order tersebut memiliki diskon ( > 0 ).
  • Menggunakan GROUP BY untuk mengelompokkan hasil perhitungan berdasarkan bulan.

8 of 19

Query SQL

2

  • Menghasilkan matrics Monthly organic order ratio
  • Menggunakan fungsi DATE_TRUNC untuk mengubah tanggal order menjadi bulan
  • Menggunakan COUNT(DISTINCT) untuk menghitung jumlah order unik yang tidak mendapatkan diskon. Dan menggunakan CASE WHEN untuk mengecek apakah order tersebut memiliki diskon ( > 0 ).
  • Menghitung jumlah order unik secara keseluruhan dengan menggunakan COUNT(DISTINCT Order_Id).
  • Menghitung organic order ratio, dengan rumus: jumlah order organik/jumlah order keseluruhan.
  • Menggunakan GROUP BY untuk mengelompokkan hasil perhitungan berdasarkan bulan.

9 of 19

Data Understanding

2

Product ID

Sales

Quantity

Discount

Profit

FUR-TA-10000577

957.5775

5

0.45

-383.031

FUR-TA-10000577

1044.63

3

0

240.2649

FUR-TA-10000577

1114.272

4

0.2

41.7852

FUR-TA-10000577

1044.63

5

0.4

-295.9785

FUR-TA-10000577

1218.735

5

0.3

-121.8735

 

10 of 19

Query SQL

2

  • Menghasilkan Matrics Monthly total discount against sales
  • Menggunakan fungsi DATE_TRUNC untuk mengubah tanggal order menjadi bulan
  • Menggunakan subquery untuk menghitung harga asli dari produk dengan menggunakan rumus: (Sales / (1 - Discount)) dan menyimpannya dalam kolom baru bernama Original_Price.
  • Menggunakan SUM untuk menjumlahkan jumlah diskon yang diinvestasikan dan total penjualan pada setiap bulan.
  • Menggunakan GROUP BY untuk mengelompokkan hasil perhitungan berdasarkan bulan.

11 of 19

Query SQL

2

  • Menyatukan Keseluruhan SQL

12 of 19

Analisis Organic and Inorganic Orders

3

Matrics:

  • Monthly organic order
  • Monthly inorganic order
  • Monthly organic order ratio

​

Link: Dashboard

13 of 19

Analisis Organic and Inorganic Orders

3

Analisis Homogeneous Subsets Monthly Organic Order

total_organic_order

Tukey HSDa,b

​

​

​

​

Bulan

N

Subset

 

 

1

2

3

2

4

24.75

 

 

1

4

25.25

 

 

7

4

42.50

42.50

 

4

4

43.00

43.00

 

8

4

43.00

43.00

 

6

4

45.75

45.75

 

3

4

 

47.00

 

5

4

 

49.25

 

10

4

 

52.50

 

9

4

 

 

93.00

12

4

 

 

95.00

11

4

 

 

100.00

Sig.

 

0.058

0.881

0.989

Means for groups in homogeneous subsets are displayed.� Based on observed means.� The error term is Mean Square(Error) = 74.177.

a. Uses Harmonic Mean Sample Size = 4.000.

b. Alpha = .05.

Dari hasil uji Homogeneous Subsets Monthly Organic Order, terlihat bahwa terdapat 3 subset dan kelompok bulan dapat dikelompokan menjadi 4 kelompok.

  • Kelompok pertama adalah kelompok subset kesatu dengan rata-rata terendah terdiri dari bulan dengan rata-rata terendah yaitu bulan: 2 dan 1,
  • Kelompok kedua dengan bulan yang memiliki 2 kelompok subset yaitu bulan: 7, 4, 8, dan 6,
  • Kelompok ketiga adalah kelompok subset kedua dengan rata-rata tertinggi yaitu bulan: 3, 5 dan 10.
  • Dan kelompok keempat adalah subset dengan rata-rata tertinggi yaitu bulan: 9, 12, dan 11.

Berdasarkan visualisasi data dan uji HSD, didapati bahwa terdapat 3 fase yang signifikan dalam periode tahunan. Pada bulan 8 ke 9 mengalami kenaikan yang signifikan, lalu untuk bulan 9 ke 10 mengalami penurunan dan mengalami kenaikan Kembali untuk bulan ke 11. Selain itu, terlihat adanya tren penurunan dari bulan 12 ke bulan 1 kemudian ke bulan 2, dan akan mengalami kenaikan kembali pada bulan ke 3, dan akan stabil hingga bulan ke 8.

14 of 19

Analisis Organic and Inorganic Orders

3

Analisis Homogeneous Subsets Monthly Inorganic Order

Dari hasil uji Homogeneous Subsets Monthly Inorganic Order, terlihat bahwa terdapat 3 subset dan kelompok bulan dapat dikelompokan menjadi 3 kelompok.

  • Kelompok pertama terdiri dari bulan dengan rata-rata terendah yaitu bulan: 2 dan 1,
  • Kelompok kedua dengan subset yang memiliki anggota kelompok terbanyak diantaranya bulan: 4, 3, 8, 7, 5, 6, dan 10,
  • Dan kelompok ketiga adalah subset dengan rata-rata tertinggi yaitu bulan: 9, 12, dan 11.

Berdasarkan visualisasi data dan uji HSD, didapati bahwa terdapat 3 fase yang signifikan dalam periode tahunan. Pada bulan 8 ke 9 mengalami kenaikan yang signifikan, lalu untuk bulan 9 ke 10 mengalami penurunan dan mengalami kenaikan Kembali untuk bulan ke 11. Selain itu, terlihat adanya tren penurunan dari bulan 12 ke bulan 1 kemudian ke bulan 2, dan akan mengalami kenaikan kembali pada bulan ke 3, dan akan stabil hingga bulan ke 8.

total_inorganic_order

Tukey HSDa,b

​

​

​

​

Bulan

N

Subset

 

 

1

2

3

2

4

20.75

 

 

1

4

25.00

 

 

4

4

 

51.25

 

3

4

 

52.00

 

8

4

 

52.00

 

7

4

 

52.75

 

5

4

 

53.25

 

6

4

 

55.25

 

10

4

 

65.00

 

9

4

 

 

98.25

12

4

 

 

102.00

11

4

 

 

111.00

Sig.

 

1.000

0.595

0.696

Means for groups in homogeneous subsets are displayed.� Based on observed means.� The error term is Mean Square(Error) = 82.003.

a. Uses Harmonic Mean Sample Size = 4.000.

b. Alpha = .05.

15 of 19

Analisis Organic and Inorganic Orders

3

Analisis Homogeneous Subsets Monthly Organic Order Ratio

Dari hasil uji Homogeneous Subsets Monthly Organic Order Ratio, terlihat bahwa hanya terdapat 1 subset saja yang berarti tidak terdapat perbedaan signifikan antara order organic tiap bulannya. Dengan hasil tersebut dapat disimpulkan juga tidak bahwa tidak terdapat perbedaan order inorganic tiap bulannya.

  • Dari hasil data tersebut, dapat disimpulkan bahwa tidak ada perbedaan yang signifikan antara rasio pesanan organik yang terjadi pada setiap bulan. Hal ini dapat dilihat dari nilai Sig. 0,244 > 0,05, yang berarti bahwa hipotesis nol (yaitu bahwa tidak ada perbedaan signifikan antara rata-rata dari subset yang homogen) diterima. yang menunjukkan bahwa perbedaan rasio pesanan organik antara setiap bulan tidak signifikan secara statistik.
  • Kemudian dapat diketahui bahwa rata-rata ratio organic order lebih kecil dari inorganic order, dan organic order lebih besar hanya terjadi di bulan 2.

organic_order_ratio

Tukey HSDa,b

​

​

Bulan

N

Subset

 

 

1

7

4

0.44

10

4

0.45

6

4

0.45

8

4

0.46

4

4

0.46

3

4

0.47

11

4

0.47

12

4

0.48

5

4

0.48

9

4

0.49

1

4

0.49

2

4

0.54

Sig.

 

0.244

Means for groups in homogeneous subsets are displayed.� Based on observed means.� The error term is Mean Square(Error) = .002.

a. Uses Harmonic Mean Sample Size = 4.000.

b. Alpha = 0.05.

16 of 19

Analisis Matrics Monthly Total Discount Against Sales

3

Link: Dashboard

17 of 19

Analisis Matrics Monthly Total Discount Against Sales

3

Analisis Regression Matrics Monthly Total Discount Against Sales

Dari output regression yang dihasilkan di atas, terlihat bahwa terdapat korelasi positif yang signifikan antara variabel Total Discount dan variabel Total Sales. Ini berarti bahwa semakin tinggi tingkat Discount yang dilakukan, maka semakin tinggi pula tingkat Sales yang diperoleh.

  • Konstanta (konstanta model) yang ditunjukkan oleh nilai (Constant) dari 15727,167 menunjukkan bahwa jika nilai Total Discount sama dengan 0, maka nilai Total Sales diharapkan sebesar 15727,167.
  • Nilai koefisien unstandardized (B) sebesar 2,721 menunjukkan bahwa setiap peningkatan 1 unit pada nilai Total Discount, diharapkan terjadi peningkatan 2,721 unit pada nilai Total sales.
  • Nilai koefisien standardized (Beta) sebesar 0,849 > 0,05 menunjukkan bahwa variabel Total Discount memiliki kontribusi yang signifikan terhadap variabel Total Sales.

Model regresi yang dihasilkan adalah:

Total Sales = 15727.167 + 2.721 x Total Discount

Coefficientsa

Model

​

Standardized Coefficients

t

Sig.

 

Beta

 

 

1

(Constant)

15727.167

3536.968

 

4.447

0.000

 

Total_Discount

2.721

0.250

0.849

10.877

0.000

a. Dependent Variable: Total_Sales

18 of 19

Analisis Matrics Monthly Total Discount Against Sales

3

Analisis Regression Matrics Monthly Total Discount Against Sales

  • Output di atas menunjukkan bahwa model regresi yang digunakan memiliki korelasi yang kuat (R = .849) antara variabel Total_Discount dengan variabel Total_Sales.
  • Nilai R Square (0.720) menunjukkan bahwa variabel Total_Discount memiliki pengaruh yang kuat terhadap variabel Total_Sales dan menjelaskan sekitar 72% variasi dalam Total_Sales.
  • Adjusted R Square (0.714) menunjukkan bahwa model menjelaskan sekitar 71.4% variasi dalam Total_Sales setelah mempertimbangkan jumlah variabel dalam model.
  • Std. Error of the Estimate (13475.83670) menunjukkan bahwa rata-rata perbedaan antara nilai prediksi dan nilai aktual adalah sekitar 13.475 dollar.

Model Summaryb

Model

R

R Square

Adjusted R Square

Std. Error of the Estimate

1

.849a

0.720

0.714

13475.83670

a. Predictors: (Constant), Total_Discount

b. Dependent Variable: Total_Sales

19 of 19

Conclusion & Recommendation

4

Conclusion :

  • Berdasarkan hasil analisis di atas, dapat disimpulkan bahwa terdapat hubungan yang signifikan antara program promosi marketing tiap bulannya dengan jumlah order. Hal ini dapat dilihat dari analisis homogeneous subset yang menunjukkan bahwa rata-rata organic order dan inorganic order mengalami kenaikan dan penurunan yang signifikan dalam periode bulan.
  • Kemudian dapat diketahui bahwa customer cenderung membeli dengan inorganic order, dimana rata-rata ratio organic order lebih kecil dari inorganic order. Namun dari kedua tipe order tersebut tidak memiliki perbedaan rata-rata yang signifikan secara statistic.
  • Dari analisis regresi, dapat diketahui bahwa tingkat discount memiliki pengaruh positif terhadap sales. Model regresi yang dihasilkan total discount terhadap sales adalah:

Total Sales = 15727.167 + 2.721 x Total Discount.

​

Recommendation :

  • Terdapat beberapa program promosi yang telah memberikan hasil yang baik dalam meningkatkan jumlah order yaitu di bulan: 9, 11, dan 12. Perusahaan dapat terus mengevaluasi kesuksesan program promosi tersebut dan mempertimbangkan untuk mengulang program tersebut juga dapat menerapkannya di bulan-bulan yang memiliki total order yang rendah seperti bulan 1 dan 2.
  • Jika Perusahaan memiliki target total sales sebesar 20.000 dollar maka dengan model regresi di atas dapat diperkiraan investasi promosi yang harus dilakukan sebesar 1570.32 dollar. (Excel > What-if Analysis > Goal seek)