Tahapan Awal

Koneksi MySQL ke R

con <- DBI::dbConnect(odbc::odbc(),
Driver = "MySQL ODBC 8.0 ANSI Driver",
Server = "127.0.0.1",
Database = "cofveve",
UID = "root",
PWD = "Akujeosyura7",
Port = 3306)

Load Library

library(DBI)

## Warning: package 'DBI' was built under R version 4.5.2

library(ggplot2)
library(scales)
library(knitr)

## Warning: package 'knitr' was built under R version 4.5.2

library(dplyr)

## Warning: package 'dplyr' was built under R version 4.5.2

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

Pendahuluan

Latar Belakang

Dalam industri ‘Food and Beverage’ dengan persaingan pasar yang kompetitif, terlebih pada sektor penjualan kopi di Amerika Serikat, pengambilan keputusan berbasis data menjadi mantra jitu untuk memenangkan pangsa pasar. Dengan adanya data, perusahaan kedai kopi memiliki arah yang lebih jelas untuk menentukan strategi ekspansi, pemasaran, maupun manajemen sumber daya. Evaluasi kinerja yang sesuai seperti keuntungan jenis produk, efisiensi biaya, performa penjuakan di berbagai wilayah sangat diperlukan agar perusahaan dapat berkembang ke arah yang benar.

Deskripsi Dataset

Dataset merupakan data sekunder berupa penjualan dan kkinerja keuangan dari sebuah jaringan kedai kopi di Amerika Serikat dari periode tahun 2012 hingga 2013. Dataset ini trdiri dari 4248 baris dengan 20 variabel. Dataset berisi informasi mengenai waktu dan letak penjualan produk, detail produk,catatan keuangan, dan proyeksi penganggaran. Berikut tabelnya:

# Mengambil semua list tabel
all_table_names <- dbListTables(con)

# Membaca tabel di R
all_tables_data <- lapply(all_table_names, function(tb_name) {
  dbReadTable(con, tb_name)
})
names(all_tables_data) <- all_table_names

# Print Tabel
for (i in seq_along(all_table_names)) {
  t_name <- all_table_names[i]
  cat(paste0("### Tabel ", i, ". ", t_name))
  print(kable(head(all_tables_data[[t_name]], 10)))
  cat("\n\n---\n\n") 
}

## ### Tabel 1. facttable
## 
## | Profit| Margin| Sales| COGS| Total.Expenses| Marketing| Inventory| Budget.Profit| Budget.COGS| Budget.Margin| Budget.Sales| Area.Code| ProductId|Date                |
## |------:|------:|-----:|----:|--------------:|---------:|---------:|-------------:|-----------:|-------------:|------------:|---------:|---------:|:-------------------|
## |     94|    130|   219|   89|             36|        24|       777|           100|          90|           130|          220|       719|         1|2012-01-01 00:00:00 |
## |     68|    107|   190|   83|             39|        27|       623|            80|          80|           110|          190|       970|         2|2012-01-01 00:00:00 |
## |    101|    139|   234|   95|             38|        26|       821|           110|         100|           140|          240|       970|         3|2012-01-01 00:00:00 |
## |     30|     56|   100|   44|             26|        14|       623|            30|          30|            50|           80|       303|        13|2012-01-01 00:00:00 |
## |     54|     80|   134|   54|             26|        15|       456|            70|          60|            90|          150|       303|         5|2012-01-01 00:00:00 |
## |     53|    108|   180|   72|             55|        23|       558|            80|          80|           130|          210|       720|         6|2012-01-01 00:00:00 |
## |     99|    171|   341|  170|             72|        47|      1091|           110|         140|           160|          300|       970|         8|2012-01-01 00:00:00 |
## |      0|     87|   150|   63|             87|        57|       435|            20|          50|            80|          130|       719|         9|2012-01-01 00:00:00 |
## |     33|     80|   140|   60|             47|        19|       336|            40|          50|            70|          120|       970|        10|2012-01-01 00:00:00 |
## |     17|     72|   130|   58|             55|        22|       338|            20|          40|            70|          110|       719|        11|2012-01-01 00:00:00 |
## 
## 
## ---
## 
## ### Tabel 2. location
## 
## | Area.Code|State       |Market  |Market.Size  |
## |---------:|:-----------|:-------|:------------|
## |       203|Connecticut |East    |Small Market |
## |       206|Washington  |West    |Small Market |
## |       209|California  |West    |Major Market |
## |       210|Texas       |South   |Major Market |
## |       212|New York    |East    |Major Market |
## |       213|California  |West    |Major Market |
## |       214|Texas       |South   |Major Market |
## |       216|Ohio        |Central |Major Market |
## |       217|Illinois    |Central |Major Market |
## |       224|Illinois    |Central |Major Market |
## 
## 
## ---
## 
## ### Tabel 3. product
## 
## |Product.Line |Product.Type |Product           | ProductId|Type    |
## |:------------|:------------|:-----------------|---------:|:-------|
## |Beans        |Coffee       |Amaretto          |         1|Regular |
## |Beans        |Coffee       |Colombian         |         2|Regular |
## |Beans        |Coffee       |Decaf Irish Cream |         3|Decaf   |
## |Beans        |Espresso     |Caffe Latte       |         4|Regular |
## |Beans        |Espresso     |Caffe Mocha       |         5|Regular |
## |Beans        |Espresso     |Decaf Espresso    |         6|Decaf   |
## |Beans        |Espresso     |Regular Espresso  |         7|Regular |
## |Leaves       |Herbal Tea   |Chamomile         |         8|Decaf   |
## |Leaves       |Herbal Tea   |Lemon             |         9|Decaf   |
## |Leaves       |Herbal Tea   |Mint              |        10|Decaf   |
## 
## 
## ---

Tujuan Analisis

Analisis terhadap dataset ini bertujuan untuk menggali wawasan yang berharga sebagai bentuk evaluasi keefektifan strategi perusahaan kopi dalam menjalankan bisnisnya. Analisis difokuskan ke berbagai hal seperti, mengidentifikasi produk dan kategori minuman mana yang menghasilkan margin keuntungan terbesar, mengetahui negara bagian dan regional pasar mana yang memiliki performa penjualan terbaik, dan mengukur seberapa baik perusahaan dalam mencapai target penjualan dan keuntungan. yang telah dianggarkan sebelumnya.

Pembahasan

1. Keuntungan berdasarkan Kategori Produk (Coffee vs. Tea vs. Espresso)

# --- 1. Query SQL untuk Tabel Keuntungan Produk ---
querycof2<-"
SELECT 
    p.`Product Type`,
    p.`Product Line`,
    SUM(f.Sales) AS Total_Sales,
    SUM(f.Profit) AS Total_Profit,
    SUM(f.COGS) AS Total_COGS,
    SUM(f.`Total Expenses`) AS Total_Expenses
FROM 
    factTable f
JOIN 
    Product p ON f.ProductId = p.ProductId
GROUP BY 
    p.`Product Type`, p.`Product Line`
ORDER BY 
    Total_Profit DESC;"

datatug2 <- dbGetQuery(con, querycof2)
head(datatug2)

##   Product Type Product Line Total_Sales Total_Profit Total_COGS Total_Expenses
## 1       Coffee        Beans      216828        74683      90696          60025
## 2     Espresso        Beans      222996        68620      97000          64603
## 3   Herbal Tea       Leaves      207214        63254      92810          57814
## 4          Tea       Leaves      172773        52986      78166          47220

# --- 2. Plot untuk Keuntungan Produk ---

##Kostumisasi Warna
custom_colors <- c(
  "Coffee" = "chocolate", 
  "Espresso" = "black", 
  "Herbal Tea" = "pink", 
  "Tea" = "darkgreen"
)

## Plot
ggplot(datatug2, aes(x = reorder(`Product Type`, -Total_Profit), y = Total_Profit, fill = `Product Type`)) +
  geom_bar(stat = "identity", width = 0.6, show.legend = FALSE) +
  geom_text(aes(label = paste0("$", Total_Profit)), vjust = -0.5, size = 4.5, fontface = "bold") +
  theme_minimal() +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) + 
  scale_fill_manual(values = custom_colors) + 
  labs(
    title = "Profitability by Product Category",
    subtitle = "Comparing Total Profit across Categories",
    x = "Product Type",
    y = "Total Profit"
  ) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    axis.text.x = element_text(size = 12, face = "bold"),
    panel.grid.major.x = element_blank()
  )

Berdasarkan plot tersebut, kategori produk yang paling menguntungkan dari perusahaan kopi tersebut adalah kopi/Coffee ($74683) disusul denga Espresso ($68620), Herbal Tea ($63254), dan terakhir teh/Tea ($52986). Kategori tersebut mewakili beberapa jenis produk yang dijual oleh perusahaan.

2. 10 Produk Paling Menguntungkan

# --- 1. Query SQL untuk Tabel Top 10 Produk ---
querycof <- "
SELECT 
    p.Product,
    p.`Product Type`,
    SUM(f.Sales) AS Total_Sales,
    SUM(f.Profit) AS Total_Profit,
    ROUND((SUM(f.Profit) / SUM(f.Sales)) * 100, 2) AS Profit_Margin_Percent
FROM 
    factTable f
JOIN 
    Product p ON f.ProductId = p.ProductId
GROUP BY 
    p.Product, p.`Product Type`
ORDER BY 
    Total_Profit DESC
LIMIT 10;
"
datatug1 <- dbGetQuery(con, querycof)
head(datatug1)

##          Product Product Type Total_Sales Total_Profit Profit_Margin_Percent
## 1      Colombian       Coffee      128311        55804                 43.49
## 2          Lemon   Herbal Tea       95926        29869                 31.14
## 3 Decaf Espresso     Espresso       78162        29502                 37.74
## 4     Darjeeling          Tea       73151        29053                 39.72
## 5      Chamomile   Herbal Tea       75578        27231                 36.03
## 6      Earl Grey          Tea       66772        24164                 36.19

# # --- 2. Plot untuk Top 10 Produk ---
ggplot(datatug1, aes(x = reorder(Product, Total_Profit), y = Total_Profit, fill = `Product Type`)) +
  geom_bar(stat = "identity") +
  coord_flip() +
  geom_text(aes(label = paste0("$", Total_Profit)), hjust = -0.2, size = 3.5) +
  theme_minimal() +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) +
  labs(
    title = "Top 10 Most Profitable Products",
    subtitle = "Ranked by Total Actual Profit",
    x = "Product",
    y = "Total Profit",
    fill = "Category"
  ) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    axis.text = element_text(size = 10)
  )

Dari masing-masing kategori, terdapat produk-produk yang dijual oleh perusahaan. Produk yang paling laku adalah kopi jenis “Colombian” dengan selisih yang cukup tinggi dengan urutan kedua, Herbal Tea jenis Lemon, dan disusul dengan Espresso jenis Decaf Espresso. Dari plot tersebut terlihat 10 produk paling menguntungkan yang dijual oleh perusahaan kopi.

3. 5 States (Negara Bagian) dengan Penjualan Terbaik

# --- 1. Query SQL untuk Tabel Top 5 States---
querycof3<-"
SELECT 
    l.State,
    l.`Market Size`,
    SUM(f.Sales) AS Total_Sales,
    SUM(f.Profit) AS Total_Profit
FROM 
    factTable f
JOIN 
    Location l ON f.`Area Code` = l.`Area Code`
GROUP BY 
    l.State, l.`Market Size`
ORDER BY 
    Total_Profit DESC
LIMIT 5;"
datatug3 <- dbGetQuery(con, querycof3)
head(datatug3)

##        State  Market Size Total_Sales Total_Profit
## 1 California Major Market       96892        31785
## 2   Illinois Major Market       69883        30821
## 3       Iowa Small Market       54750        22212
## 4   New York Major Market       70852        20096
## 5   Colorado Major Market       48179        17743

# --- 2. Plot untuk Top States ---
library(ggplot2)

ggplot(datatug3, aes(x = reorder(State, -Total_Profit), y = Total_Profit, fill = `Market Size`)) +
  geom_bar(stat = "identity", width = 0.6) +
  geom_text(aes(label = paste0("$", Total_Profit)), vjust = -0.5, size = 4.5, fontface = "bold") +
  theme_minimal() +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) + # Adds breathing room for the text at the top
  scale_fill_brewer(palette = "Set1") + # Uses a bold color palette to differentiate market sizes
  labs(
    title = "Top 5 Best Performing States",
    subtitle = "Ranked by Total Profit (Color-coded by Market Size)",
    x = "State",
    y = "Total Profit",
    fill = "Market Size"
  ) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    axis.text.x = element_text(size = 11, face = "bold"),
    panel.grid.major.x = element_blank(), # Cleans up the background
    legend.position = "top" # Moves the legend to the top so it doesn't squish the chart
  )

Amerika Serikat memiliki 50 negara bagian yang dimana setiap negara bagian memiliki dinamika yang berbeda-beda. Pemilik perusahaan memutuskan untuk membagi negara bagian menjadi 2 kategori, “Major Market” dan “Small Market”. Negara bagian yang memiliki kepadatan penduduk yang tinggi cenderung masuk ke kategori “Major Market” begitu juga sebaliknya. Hal ini digunakan agar evaluasi kinerja menjadi lebih adil.

Berdasarkan plot, dapat dilihat 5 negara bagian dengan keuntungan tertinggi, dengan California diurutan pertama ($31785), disusul Illinois ($30821), dan Iowa sebagai negara bagian “Small Market” ($22212)

4. Analisis Variansi Penjualan (Sales Variance) per State.

# --- 1. Query SQL untuk Variansi per State ---
query_state_var <- "
SELECT 
    l.State,
    l.`Market Size`,
    SUM(f.Sales) AS Actual_Sales,
    SUM(f.`Budget Sales`) AS Budget_Sales,
    SUM(f.Sales) - SUM(f.`Budget Sales`) AS Sales_Variance
FROM 
    factTable f
JOIN 
    Location l ON f.`Area Code` = l.`Area Code`
GROUP BY 
    l.State, l.`Market Size`
ORDER BY 
    Sales_Variance DESC;
"

data_state_var <- dbGetQuery(con, query_state_var)


# --- 2. Visualisasi Diverging Bar Chart ---
library(ggplot2)
library(dplyr)
data_state_var <- data_state_var %>%
  mutate(Status = ifelse(Sales_Variance >= 0, "Melampaui Target", "Di Bawah Target"))

ggplot(data_state_var, aes(x = reorder(State, Sales_Variance), y = Sales_Variance, fill = Status)) +
  geom_bar(stat = "identity", width = 0.7) +
  geom_text(aes(label = paste0("$", Sales_Variance),
                hjust = ifelse(Sales_Variance >= 0, -0.2, 1.2)), 
            size = 3, fontface = "bold") +
  coord_flip() + # Wajib: Memutar grafik agar nama State mudah dibaca
  theme_minimal() +
  scale_fill_manual(values = c("Melampaui Target" = "forestgreen", "Di Bawah Target" = "firebrick")) +
  # Memberi ruang agar teks tidak terpotong
  scale_y_continuous(expand = expansion(mult = c(0.2, 0.2))) + 
  labs(
    title = "Kinerja Sebenarnya: Variansi Penjualan per Negara Bagian (State)",
    subtitle = "Siapa yang paling efisien melampaui target anggaran?",
    x = "Negara Bagian (State)",
    y = "Variansi Penjualan Aktual vs Budget ($)",
    fill = "Status Kinerja"
  ) +
  theme(
    plot.title = element_text(face = "bold", size = 14),
    axis.text.y = element_text(size = 10, face = "bold"),
    legend.position = "top",
    panel.grid.major.y = element_blank()
  )

Variansi Penjualan merupakan selisih antara penjualan aktual (Sales) dengan target penjualan yang direncanakan oleh perusahaan (Budget Sales). Dimana jika variansi positif, angka penjualan aktual lebih besar dari target anggarannya begitu juga sebaliknya.

Berdasarkan Plot, dapat diketahui Variansi Penjualan masing-masing negara bagian dimana perusahaan ini membuka kedainya. Nevada menjadi negara bagian dengan surplus terbesar ($13619), disusul dengan Iowa ($7750), dan New York ($7272). Meskipun masuk dalam salah satu negara bagian dengan keuntungan terbanyak, Illinois menjadi satu-satunya negara bagian yang bekerja dibawah target (-$757) menandakan bahwa profit yang tinggi diantara yang lain bukan berarti baik secara penjualan.

Kesimpulan

Berdasarkan analisis mengenai data penjualan perusahaan kopi pada periode 2012–2013, Dapat disimpulkan menjadi 3 poin fundamental:

Dominasi Kopi “Colombian” sebagai Ujung Tombak Dengan banyaknya ragam inovasi minuman, Produk kopi, khususnya pada produk “Colombian”, menjadi penyumbang besar finansial perusahaan. Varian ini mencetak margin keuntungan yang jauh melampaui produk lainnya (mencapai lebih dari $55.000). Segingga perlu adanya penjagaan kualitas dan ketersediaan produk ini, karena di sinilah loyalitas konsumen paling kuat terbentuk.
Small Market, dengan Pangsa Pasar Kecil, Memberi Impact Besar Analisis ini mematahkan statement bahwa perusahaan harus selalu fokus pada kota-kota metropolitan padat penduduk (Major Market). Iowa dan Nevada, yang dikategorikan sebagai “Small Market”, secara mengejutkan tampil sebagai bintang utama pada surplus anggaran.
Illinois menjadi Satu-satunya Negara Bagian yang Tidak Mencapai Target Salah satu temuan menarik dari analisis ini adalah anomali pada negara bagian Illinois. Jika hanya melihat pada sisi keuntungan, Illinois terlihat sangat sukses karena berada di peringkat kedua penyumbang profit terbesar. Namun, setelah dianalisis, Illinois adalah satu-satunya wilayah operasional yang gagal mencapai target anggarannya (budget sales), dengan defisit sebesar -$757. Sehingga perlu adanya peningkatan performa pada negara bagian ini.

Tugas Coffee Chain Dataset

Jeosyura Abiyyi Priyanto

2026-04-19