💊 Kimia Farma Sales Dashboard

Kimia Farma, Indonesia's first pharmaceutical company, was founded by the Dutch East Indies government in 1817 as NV Chemicalien Handle Rathkamp & Co. In 1958, the Indonesian government merged several pharmaceutical companies into PNF Bhinneka Kimia Farma as part of nationalization efforts. On August 16, 1971, PNF was transformed into a Limited Liability Company, known as PT Kimia Farma (Persero).

Source: Kimia Farma

⭐ Introduction

As a Big Data Analyst Intern at Kimia Farma, facilitated by Rakamin Academy, the primary objective of this project was to analyze and generate a comprehensive sales dashboard for the company utilizing the provided data. Throughout this project, an in-depth understanding of data warehouse, data lake, and datamart, allowing effective analysis.

Tools

• Query: MySQL Workbench ➡️ Script
  
• Visualization: Looker Studio ➡️ Dashboard
  
• Dataset: Kimia Farma x Rakamin ➡️ Link

Objectives

• Investigate and analyze Kimia Farma's sales performance over a six-month period
• Identifying trends and potential areas for improvement
• Creating a datamart (base table and aggregate table)
• Create a dashboard for company sales report

Dataset

• Penjualan
  Sample Dataset:
  

id_distributor	id_cabang	id_invoice	tanggal	id_customer	id_barang	jumlah_barang	unit	harga	mata_uang	brand_id	lini
TD	CAB01	IN5997	20/01/2022	CUST55380	BRG0001	1	DUS	1170	IDR	BRND001	OGB & PH
TD	CAB01	IN6297	20/01/2022	CUST55381	BRG0002	5	DUS	2338	IDR	BRND002	ETIKAL
TA	CAB02	IN6155	21/01/2022	CUST55382	BRG0003	9	DUS	10691	IDR	BRND003	MARCKS

• Barang
  Sample Dataset:
  

kode_barang	sektor	nama_barang	tipe	nama_tipe	kode_lini	lini	kemasan
BRG0001	P	ACYCLOVIR DUS	ZPJ1	Produk jadi	206	OGB & PH	DUS
BRG0002	P	ALERGINE TABLET SALUT	ZPJ1	Produk jadi	203	ETIKAL	DUS
BRG0003	P	AMPICILLIN	ZPJ1	Produk jadi	210	MARCKS	BOTOL

• Pelanggan
  Sample Dataset:
  

id_customer	level	nama	id_cabang_sales	cabang_sales	id_group	group
CUST55380	Company	APOTEK TAPAK	CAB01	Aceh	Z32	Apotek
CUST55381	Company	APOTEK MAJA	CAB02	Kuningan	Z32	Apotek
CUST55382	Company	KLINIK GM	CAB03	Jakarta	Z31	Klinik

ERD

Click to view ERD

⭐ Create Datamart

Base table

Base table is a table that contains raw or original data collected from its source and contains the information needed to answer questions or solve specific problems. The base table in this project is created from a combination of sales, customer, and product tables with the primary key on invoice_id.

Click to view Query

-- Import dataset
-- Create base table
-- Create base table datamart
CREATE TABLE base_table (
SELECT
    j.id_invoice,
    j.tanggal,
    j.id_customer,
    c.nama,
    j.id_distributor,
    j.id_cabang,
    c.cabang_sales,
    c.id_group,
    c.group,
    j.id_barang,
    b.nama_barang,
    j.brand_id,
    b.kode_lini,
    j.lini,
    b.kemasan,
    j.jumlah,
    j.harga,
    j.mata_uang
FROM penjualan j
	LEFT JOIN pelanggan c
		ON c.id_customer = j.id_customer
	LEFT JOIN barang b
		ON b.kode_barang = j.id_barang
ORDER BY j.tanggal
);

-- Add primary key
ALTER TABLE base_table ADD PRIMARY KEY(id_invoice);

Aggregate table

Aggregate table is a table created by collecting and calculating data from base table. This aggregate table contains more concise information and is used to analyze data more quickly and efficiently. The results from this table will be used as a source for creating sales dashboard reports.
Result: Click here

Click to view Query

-- Create aggregate table datamart 
CREATE TABLE aggregate_table (
SELECT
    tanggal AS date,
    MONTHNAME(tanggal) AS month,
    id_invoice,
    cabang_sales AS branch_location,
    nama AS customer,
    nama_barang AS product,
    lini AS brand,
    jumlah AS total_product_sold,
    harga AS price_per_unit,
    (jumlah * harga) AS total_revenue
FROM base_table
ORDER BY 1, 4, 5, 6, 7, 8, 9, 10
);

⭐ Visualization

Export the aggregate table to CSV from the data source in Looker Studio. Then, build a dashboard using the data from the aggregate table in Google Data Studio.
Result: Click here