G Coffee Shop Transaction Analysis — Capstone Project

End-to-end data pipeline analyzing transaction data from the G Coffee chain in Malaysia (Kuala Lumpur, Selangor, Putrajaya) from July 2023 to June 2025. The pipeline encompasses data ingestion, validation, cleaning, integration, feature engineering, exploratory analysis, and forecasting — producing datasets ready for RFM segmentation, market basket analysis (Apriori), supervised learning (XGBoost), and hybrid time-series forecasting.

---

---

📁 Struktur Proyek

Capstone-Tempa/
├───dashboard/
│   ├───app.py                      # Dashboard Streamlit interaktif
│   └───Groq_analyst.py            # AI Analyst berbasis Groq API
├───data/
│   ├───*.parquet                   # Data hasil pipeline (transactions, users, dll)
│   ├───*.csv                       # Association rules
│   ├───*.json                      # Cluster metadata
│   ├───*.joblib                    # Model ML (K-Means, scaler)
│   └───*.txt                       # Groq API key
├───function.py                     # Utility functions
├───01-LoadData.ipynb               # Notebook pipeline utama
├───... (notebook lainnya)
├───README.md
├───requirements.txt                # Dependencies Python
├───url.txt                         # URL dashboard
├───ARCHITECTURE.md
└───SourceOfTruth.md

---

1. Project Overview

Aspect	Description
Objective	Clean, integrate, and engineer features from 7 raw CSV tables into master datasets for customer segmentation, basket analysis, and transaction forecasting.
Domain	Retail coffee shop chain — 10 outlets, 8 beverage items, >14.6M transactions.
Data Source	Kaggle — G Coffee Shop Transaction 202307 to 202506
Time Span	1 July 2023 – 30 June 2025 (2 years)
Volume	~14.6M transactions, ~29.2M line items, ~2.2M registered users
Currency	Ringgit Malaysia (RM) — converted in-memory to IDR (1 RM = Rp 3,500) during EDA only. All parquet files store RM.

---

2. Pipeline Architecture

2.1 01-LoadData.ipynb — Data Ingestion and Memory Optimisation

• Downloads dataset from Kaggle via kagglehub.dataset_download().
• Reads partitioned CSV files with glob + pd.concat():
  • transactions/*.csv (14,623,691 rows)
  • transaction_items/*.csv (29,246,323 rows)
  • users/*.csv (2,196,257 rows)
  • menu_items.csv, stores.csv, vouchers.csv, payment_methods.csv (single files).
• Memory Optimisation via function.py:
  • optimize_numeric_data() — downcasts float64 -> float32, int64 -> int8/Int32.
  • optimize_object_data() — converts low-cardinality (<5%) object columns to category.
• Result: Memory footprint reduced significantly, e.g., Transactions from 2,915 MB to 669 MB.
• Output: Intermediate .parquet files per table.

2.2 02-DataValidation.ipynb — Type Standardisation and Validation

• Data type standardisation:
  • transaction_id -> str
  • user_id, voucher_id -> Int64 (nullable)
  • created_at, birthdate, registered_at -> datetime64
  • valid_from, valid_to -> datetime64
• Deep validation (run_deep_validation()):
  • PK duplicates: 0 duplicates found.
  • FK integrity: 0 orphan transactions by store_id.
  • Future dates: 0 transactions with created_at after today.
  • Age bounds: 0 users with age < 12 or > 100 at registration.
• Output: Validated .parquet files per table.

2.3 03-DataCleaning.ipynb — Data Cleaning

a. Invalid Value Inspection

• Orphan transactions (user_id not in Users table): 0 rows.
• Negative monetary values: 0 rows.
• Negative item quantity/price: 0 rows.
• Orphan items (transaction_id not in header): 0 rows.
• Unregistered menu items: 0 rows.
• Price consistency: 0 items with differing unit_price.

b. Duplicate Handling

• Full duplicates in df_TransItem: 802,939 rows.
• Composite duplicates (transaction_id, item_id, created_at): 4,645,360 rows.
• Policy: Remove all rows within a (transaction_id, item_id) group with time difference <= 30 seconds — 2,360,635 rows removed.
• Deterministic dedup (Rev): Row with maximum subtotal retained.
• Final: 0 duplicate rows remaining.

c. Header-Detail Reconciliation

• 2,284,725 transactions had original_amount inconsistent with SUM(subtotal) of items.
• Correction: original_amount overwritten with SUM(subtotal).
• Final: final_amount = original_amount - discount_applied.
• Rev: original_amount_header backed up as audit trail before overwrite.

d. Discount Validation

• Negative discount check: 784 transactions where discount > original_amount (caused by SALES50 voucher).
• Capping: discount_applied = min(discount_applied, original_amount). Result: 0 negative transactions.
• Cross-check against voucher rules: 0 mismatches.

e. Outlier Treatment

Three methods compared for final_amount (initial skewness ~0.53):

Method	Data Loss	Final Skewness
IQR (1.5xIQR)	30,633 (0.21%)	0.50
Capping (P99)	0 (0%)	0.49
Z-Score (|z|>3)	30,619 (0.21%)	0.50

Decision: Capping at percentile 99 selected — retains 100% of data with best skewness reduction.

f. Missing Values

• available_from and available_to columns in menu_items dropped (100% null).

2.4 04-JoinData.ipynb — Table Joining

Join Strategy

Step 1: df_MasterTrans = df_Trans
    LEFT JOIN df_Users   -> ON user_id
    LEFT JOIN df_stores  -> ON store_id

Step 2: df_Master = df_TransItem
    LEFT JOIN df_menu          -> ON item_id
    LEFT JOIN df_MasterTrans   -> ON transaction_id

Step 3: df_Master
    LEFT JOIN df_payment  -> ON payment_method_id = method_id

Validation

• Row count: 26,885,688 (matches input).
• Orphan checks: 0 for menu, store, header.
• Financial audit: 14,623,691 transactions balanced (original_amount = SUM(subtotal)).
• Column cleanup: Redundant _x/_y suffix columns removed.

2.5 05-FeatureEngineering.ipynb — Feature Engineering

a. Temporal Features

Feature	Derivation
hour	dt.hour (0-23)
month	dt.month (1-12)
day_name	dt.day_name()
month_name	dt.month_name()
is_weekend	'Weekend' if Saturday/Sunday, else 'Weekday'
transaction_period	Morning (5-10), Afternoon (11-15), Evening (16-19), Night (20-23), Late Night (0-4)

b. Categorical Features

Feature	Derivation
member_status	'Member' if user_id NOT NULL, else 'Guest'
is_voucher_used	'Voucher' if voucher_id NOT NULL, else 'No Voucher'
discount_ratio	discount_applied / (original_amount + 1e-6)

c. Transaction Aggregation -> df_transaction_features

Group by transaction_id:

Column	Aggregation	New Name
quantity	SUM	basket_size
final_amount	MAX (deterministic)	--
discount_applied	MAX	--
Other fields	MAX	--
item_id	COUNT DISTINCT	item_count (Rev)

d. RFM Analysis -> df_rfm

• Snapshot date: MAX(created_at) + 1 day (2025-07-01).
• Scope: Members only (user_id NOT NULL).
• Components: Recency (days since last transaction), Frequency (transaction count), Monetary (total spend).
• Scaled RFM (Rev): StandardScaler -> RFM_Scaled_Recency, RFM_Scaled_Frequency, RFM_Scaled_Monetary.

e. Apriori Basket Preparation (Rev)

• Binary matrix transaction_id x item_name (1 = purchased, 0 = not).
• Filters: remove items with support < 0.1% (none removed); remove single-item baskets (5.6M, 38%); remove baskets > 30 items (none).
• Result: 9,064,669 transactions x 8 items, sparsity 70.6%.
• Output: df_basket_apriori.parquet.

f. Temporal Train/Test Split (Rev)

• 80/20 temporal split:
  • Training: 11,698,952 transactions (80%) -- 2023-07-01 to 2025-02-04.
  • Test: 2,924,739 transactions (20%) -- 2025-02-04 to 2025-06-30.
• Output: df_train.parquet, df_test.parquet.

2.6 06-EDA.ipynb — Exploratory Data Analysis

Business Overview

Metric	Value (RM)	Value (IDR x Rp3,500)
Total Revenue	~RM 444.0 million	~Rp 1.55 trillion
Total Transactions	14,623,691	--
Average Transaction Value	~RM 30.36	~Rp 106,260
Member Revenue Share	~50%	--
Guest Revenue Share	~50%	--

Analysis Sections

• Time-Based: Daily revenue trend, hourly traffic (peak vs off-peak), monthly revenue, period-based patterns.
• Customer: Revenue split (Member vs Guest), ATV comparison, repeat vs one-time behaviour, preference heatmap.
• Product: Top 10 menu items by quantity, coffee vs non-coffee revenue, preference by time period and city.
• Spatial: Revenue and ATV per city outlet.
• Payment: Revenue distribution across cash, card, ewallet methods.
• Promotion: Voucher impact on basket size, sensitivity analysis by Member vs Guest.
• RFM Segmentation (Rev): Heuristic quartile-based segments (Champions, Loyal, At Risk, Regular).

2.7 06_Modeling_and_Evaluation.ipynb — Forecasting Pipeline (Consolidated)

This notebook supersedes the legacy notebooks 07.ipynb, 08-Benchmark-Models.ipynb, 09_Hybrid_Forecast_HW_XGB.ipynb, and 09-testingXgb.ipynb. It consolidates all forecasting steps into a single pipeline with four sections:

A. Baseline Models (ARIMA, SARIMA, Prophet)

• Univariate time-series models fitted per branch.
• ARIMA/SARIMA with automatic order selection via auto_arima.
• Prophet (Facebook) with weekly seasonality.
• Metrics: MAE, RMSE, MAPE per branch, aggregated across all 10 branches.

B. XGBoost Supervised Learning

• Pooled multivariate model using autoregressive features (lag_1, lag_7, rolling_avg_7), calendar features (day_of_week, month), voucher_rate, and one-hot encoded city.
• Temporal train/test split at 2025-03-25 (80/20).
• Feature importance analysis (gain-based).
• Residual diagnostics.

C. Hybrid Forecasting (Detrending Architectures)

Three architectures compared:

• HWR-XGB: Holt-Winters (additive trend, seasonal_periods=365) + XGBoost on residuals.
• SARIMA-XGB: SARIMA + XGBoost on residuals.
• Prophet-XGB: Prophet + XGBoost on residuals.

XGBoost on residuals uses only non-autoregressive features (day_of_week, month, voucher_rate) to avoid the low-pass filter problem in recursive forecasting.

D. Comparative Evaluation and Output

• Cross-architecture comparison on held-out test set.
• Best model selection: lowest MAE on test set.
• 90-day ahead forecast saved as df_forecast_90days.parquet for the Voucher Engine.
• Forecast variability check (min, max, std, range per branch).

---

3. Custom Module (function.py)

3.1 Class CleaningData

Method	Parameters	Returns	Description
Duplicate(column_name)	Column name	int	Counts duplicate rows in a column.
BoxPlot(column_name, Target)	Column, optional data	Visual	Boxplot for outlier detection.
HistPlot(column_name, Target)	Column, optional data	Visual	Histogram for distribution analysis.
iqr(column_name)	Column name	(outlier_count, original_count, cleaned_data)	IQR method: values outside 1.5xIQR considered outliers.
capping(column_name)	Column name	(skewness, affected_count, capped_data)	Winsorization at percentile 99.
log_transform(column_name)	Column name	(skewness, transformed_data)	Log1p transformation.
z_score_method(column_name, threshold=3)	Column, threshold	(outlier_count, original_count, cleaned_data)	Z-Score method: values with |z| > threshold are outliers.

3.2 Memory Optimisation Functions

Function	Parameters	Description
optimize_numeric_data(df)	DataFrame	Downcasts float64->float32, int64->int8/Int32.
optimize_object_data(df, threshold=0.05)	DataFrame, threshold	Converts low-cardinality (<5%) object columns to category.

3.3 Notebooks Using function.py

Notebook	Functions Called
01-LoadData	optimize_numeric_data(), optimize_object_data()
03-DataCleaning	CleaningData (.Duplicate(), .BoxPlot(), .HistPlot(), .iqr(), .capping(), .z_score_method())

---

4. Output Files Summary

File	Rows	Description
transactions.parquet	14,623,691	Transaction headers -- after load and validation.
transaction_items.parquet	29,246,323	Items per transaction -- after load and validation.
transactions_capping.parquet	14,623,691	Headers with outliers capped at P99.
transaction_items_cleaned.parquet	26,885,688	Items after duplicate removal.
users_cleaned.parquet	2,196,257	Users after cleaning.
menu_cleaned.parquet	8	Menu without available_from/to columns.
stores_cleaned.parquet	10	Store data -- intact.
df_Master_Final.parquet	26,885,688	Item-level master (all tables joined).
df_Master_FE.parquet	26,885,688	Master with engineered features.
df_transaction_features.parquet	14,623,691	Transaction-level features (1 row per transaction).
df_rfm.parquet	2,196,257	RFM per user + scaled features.
df_basket_apriori.parquet	9,064,669	Binary matrix for Apriori.
df_train.parquet	11,698,952	Training set (80% temporal split).
df_test.parquet	2,924,739	Test set (20% temporal split).
df_forecast_90days.parquet	900 (90 days x 10 branches)	90-day forecast output (best model) for Voucher Engine.

---

5. Dependencies

• Python 3.12+
• pandas, numpy, matplotlib, seaborn, scipy
• kagglehub (dataset download)
• scikit-learn (StandardScaler, K-Means)
• mlxtend (frequent_patterns Apriori)
• pyarrow (Parquet format)
• statsmodels (ExponentialSmoothing, ARIMA/SARIMA via pmdarima)
• prophet (Facebook Prophet)
• xgboost (gradient-boosted trees)
• pmdarima (auto_arima)

---

6. Menjalankan Dashboard

# Dari root folder Capstone-Tempa/
cd dashboard
streamlit run app.py

Atau langsung:

streamlit run dashboard/app.py

Dashboard juga sudah dideploy dan bisa diakses di:

https://g-coffee-dashboard.streamlit.app/

---

7. Execution Order

Run notebooks in sequence:

01-LoadData.ipynb
02-DataValidation.ipynb
03-DataCleaning.ipynb (or 03-DataCleaning-Rev.ipynb)
04-JoinData.ipynb (or 04-JoinData-Rev.ipynb)
05-FeatureEngineering.ipynb (or 05-FeatureEngineering-Rev.ipynb)
06-EDA.ipynb (or 06-EDA-Rev.ipynb)
06_Modeling_and_Evaluation.ipynb  (consolidated forecasting pipeline)

Secondary notebooks (run after 05-FeatureEngineering):

K-MeansMember.ipynb + aprioriMember.ipynb
K-MeansNonMember.ipynb + aprioriNonMember.ipynb

Legacy notebooks 07.ipynb, 08-Benchmark-Models.ipynb, 09_Hybrid_Forecast_HW_XGB.ipynb, and 09-testingXgb.ipynb are superseded by 06_Modeling_and_Evaluation.ipynb and retained for reference only.

Ensure function.py is in the same directory. Internet connectivity and Kaggle API credentials are required for 01-LoadData.ipynb.