A Data Science and Business Intelligence Framework for Large-Scale E-Commerce Analytics Using Embedded Databases

Abstract

The exponential growth of e-commerce transaction data demands scalable, efficient, and reproducible analytical frameworks capable of transforming raw transactional records into actionable business intelligence. This study proposes a novel five-layer Data Science and Business Intelligence (DS-BI) framework integrating embedded in-process analytical database technology with advanced machine learning pipelines for large-scale e-commerce analytics. The framework was validated on the Online Retail II dataset from the UCI Machine Learning Repository, comprising 1,067,371 real-world transactions spanning December 2009 to December 2011 from a UK-based non-store gift-ware retailer. The embedded DuckDB analytical engine processed 805,549 clean transaction records with an average query execution time of 11.46 milliseconds and a storage footprint of only 29.1 MB. RFM-based customer segmentation identified five behavioural segments, with Champions (22.2% of customers) generating approximately 68% of total revenue (£12M of £17.7M total). K-Means clustering achieved an exceptional Silhouette Score of 0.9164 at optimal K=2. Gradient Boosting Regression yielded R²=0.6982 for revenue prediction, while Random Forest churn classification attained AUC-ROC=1.0000. Apriori-based association rule mining identified 18 high-quality cross-selling rules with maximum lift of 13.95. Cohort retention analysis revealed a consistent 15–22% loyal customer core across all acquisition cohorts. The proposed framework demonstrates that embedded databases, when systematically integrated with machine learning and business intelligence pipelines, provide a computationally efficient, reproducible, and practically deployable architecture for e-commerce analytics without requiring enterprise-grade infrastructure.