Introduction
Retail businesses generate large amounts of transactional data every day, but raw data alone does not provide much value unless it can be transformed into meaningful insights and actionable information.
For our Retail FMCG Sales Analytics project, we worked with a dataset representing one full year of retail activity during 2024 for a multi-city FMCG retailer in India.
The dataset contains 100,000 transaction records and 21 variables, covering product and brand information, customer characteristics, sales and financial metrics, stores and cities, sales channels, payment methods, loyalty status, and inventory information.
The objective was not simply to perform exploratory analysis. We wanted to build an end-to-end analytics solution that could move from:
Raw Data → Data Preparation → EDA → Business Analytics → Power BI → Machine Learning → Streamlit Deployment
The final solution combines Python-based analysis, interactive Power BI dashboards, regression modeling, and a Streamlit application that allows users to explore the data and generate transaction-level margin predictions.
1. Understanding the Dataset
The dataset contains several analytical dimensions that make it suitable for both business intelligence and predictive analytics.
The main dimensions include:
Product: Category and Brand
Customer: Age, Gender, and Loyalty Flag
Sales: Units, Selling Price, Revenue, Cost, Margin, and Margin %
Store: City and Store Format
Channel: Online, Offline, and Omnichannel
Payment: UPI, Card, Wallet, and Cash
Inventory: Stock on Hand, Reorder Level, and Lead Time
Time: Invoice Date and derived temporal features
The dataset includes eight FMCG categories: Beverages, Snacks, Dairy, Grocery, Personal Care, Home Care, Fruits, and Vegetables.
One of the main data quality challenges was Customer Age, where approximately 40% of the values were missing. The original dataset also did not include information about returns, wastage, or promotional campaigns, which limited some areas of analysis.
2. Data Cleaning and Preprocessing
Before performing analysis or machine learning, we first had to understand and prepare the dataset.
The preprocessing workflow focused on improving consistency while preserving the analytical value of the original data.
The main steps included:
- Identifying missing values and data quality issues.
- Validating customer age values.
- Creating an indicator for missing customer age.
- Handling missing customer age values.
- Handling missing categorical values.
- Extracting temporal features from Invoice_Date.
- Creating additional analytical features.
- Preparing categorical variables for machine learning.
- Separating the target variable from the input features.
The project also considered the limitations of the dataset during interpretation. In particular, the missing customer-age values affect age-based segmentation, while the absence of promotion and wastage data limits more advanced FMCG analysis.
3. Exploratory Data Analysis
After cleaning the data, the next stage was Exploratory Data Analysis.
The purpose of EDA was not only to create visualizations, but to understand how different business dimensions interact.
We explored:
Sales and Profitability
Revenue, cost, margin, margin percentage, and units sold were analyzed to understand overall business performance.
Product and Category Performance
Category and brand-level analysis was used to examine differences between sales volume and profitability.
This distinction is particularly important in FMCG because the category with the highest sales volume is not necessarily the category generating the highest margin.
Sales Channels
The dataset contains online, offline, and omnichannel transactions, allowing us to compare the economic performance of different channels.
Customer Behavior
Customer age, gender, and loyalty status were analyzed to understand differences in customer behavior.
Inventory
Stock on hand, reorder level, and lead time were analyzed as operational indicators.
These variables allowed us to investigate questions around inventory availability and replenishment.
4. Business Intelligence with Power BI
After completing the exploratory analysis, we transformed the results into interactive dashboards using Microsoft Power BI.
The objective was to move beyond static charts and create a business-facing interface where users could interact with the results.
The dashboard focused on areas such as:
Revenue
Profitability
Profit margin
Units sold
Sales channels
Categories
Brands
Cities
Customer loyalty
Inventory performance
Sales & Profitability Dashboard
The first dashboard provides a high-level view of sales and profitability.
It combines KPI cards with charts that allow users to explore revenue and profit across different business dimensions.
The dashboard also provides interactive filtering, allowing users to investigate specific channels and categories.
Customers & Sales Channels
The customer-focused analysis looks at sales channels, customer age, loyalty, revenue, and margin.
This helps connect customer behavior with financial performance rather than treating customer analytics as a separate topic.
Power BI therefore became the business intelligence layer of the project, transforming analytical results into an interactive decision-support interface.
5. Machine Learning
The next stage was to introduce predictive analytics.
We formulated the machine learning problem as a supervised regression task, with transaction-level Margin as the target variable. Ten regression models were evaluated using standard metrics including:
MAE
MSE
RMSE
R²
The purpose was to compare different approaches rather than relying on a single algorithm.
The evaluated models included:
Linear Regression
Ridge Regression
Lasso Regression
Decision Tree
Random Forest
Gradient Boosting
XGBoost
LightGBM
CatBoost
Support Vector Regression
The final evaluation identified Linear Regression as the selected model based on the executed test results, with an R² value of approximately 1.00 and error values approaching zero.
The selected model was then integrated into the deployed application.
Why compare multiple models?
Model comparison gave us a broader understanding of how different regression approaches performed on the dataset.
Instead of assuming that a more complex algorithm would automatically produce the best result, we evaluated the models using the same evaluation framework and selected the final model based on the observed test performance.
6. Streamlit Deployment
One of the main goals of the project was to take the analysis beyond notebooks and dashboards.
For this reason, we developed a Streamlit web application that integrates the main analytical and machine learning components into one interface.
The deployed application contains five main sections:
Overview
Introduces the project, dataset, and analytical context.
Analytics
Provides interactive Python-based analysis across revenue, profit, margin, channels, categories, brands, cities, customers, loyalty, and inventory.
Data Quality
Provides visibility into important data quality characteristics, including missing customer-age values.
Machine Learning
Presents the machine learning workflow, evaluated models, and final model results.
Prediction
Allows users to enter transaction characteristics and generate a predicted transaction-level margin.
The application workflow is essentially:
Load Data → Load Model → Prepare Features → Accept User Inputs → Apply Transformations → Generate Prediction
This deployment creates a user-facing layer on top of the analytical workflow and removes the need for users to directly execute the original notebooks.
7. The Complete Architecture
The project can be viewed as several connected layers rather than separate tools.
Retail FMCG Dataset
│
▼
Data Understanding
│
▼
Cleaning & Preprocessing
│
▼
Feature Engineering
│
▼
Exploratory EDA
/ \
/ \
▼ ▼
Business Analytics ML Modeling
│ │
▼ ▼
Power BI Model Evaluation
│ │
└───────┬───────┘
▼
Streamlit
│
▼
Interactive Solution
This architecture allowed us to keep the project connected from the original dataset all the way to the final deployed application.
The final report describes the same lifecycle as a nine-stage workflow: dataset understanding, cleaning, feature engineering, EDA, business analytics, Power BI, machine learning, model evaluation, and Streamlit deployment.
8. Key Business Questions
The project was built around several business questions rather than analysis for its own sake.
Which categories perform better in terms of margin versus volume?
This allows management to distinguish between high-volume categories and highly profitable categories.
Which sales channels are more economically viable?
The combination of revenue, margin, and channel information allows online, offline, and omnichannel performance to be compared.
Do loyalty customers behave differently?
Customer loyalty can be analyzed alongside revenue, units, and margin.
How can inventory indicators support operational decisions?
Stock on hand, reorder level, and lead time provide indicators that can be used to investigate inventory and replenishment performance.
Which store formats are more efficient for perishable products?
The dataset contains store-format and category information that can support comparisons between Hyper, Super, and Express formats.
9. Challenges and Limitations
No analytical project should be presented without discussing its limitations.
The main limitations of this dataset were:
Missing Customer Age:
Approximately 40% of customer-age values were missing.
No Returns or Wastage:
This limits detailed analysis of shrinkage and perishables.
No Promotion Data:
The dataset does not include promotional campaigns or discounts, limiting promotion-effectiveness and price-elasticity analysis.
Limited Basket Analysis:
Each record represents a single transaction line, which limits the ability to reliably analyze products purchased together.
These limitations are important because they define what the dataset can—and cannot—tell us.
10. What We Built
At the end of the project, we had a complete analytical solution rather than a collection of disconnected notebooks.
The final solution includes:
Python
For data preparation, analysis, visualization, and machine learning.
Power BI
For interactive business intelligence dashboards.
Machine Learning
For comparing ten regression models and building a margin prediction capability.
Streamlit
For exposing the analytical and predictive functionality through an interactive web application.
GitHub
For organizing and sharing the complete project.
The final project therefore combines descriptive analytics, business intelligence, predictive analytics, and deployment into one workflow.
11. Final Takeaways
The main lesson from this project was that a successful data analytics project is not only about building charts or achieving a high machine learning score.
The real value comes from connecting the different stages.
A dataset becomes useful when it is properly understood.
Clean data enables reliable analysis.
EDA reveals patterns.
Business intelligence makes those patterns easier to explore.
Machine learning adds predictive capability.
And deployment makes the final solution accessible to people who may never open a Jupyter Notebook.
For us, the project was an opportunity to work through that entire process.
100,000 records → one analytical workflow → one deployed solution.
Project Resources
GitHub Repository:
https://github.com/Fadydesoky/Retail-FMCG-Analytics
Live Streamlit Application:
https://retail-fmcg-analytics.streamlit.app/
What's next?
There are several directions in which this project could be extended.
Future versions could include:
Demand forecasting
Inventory optimization
Promotion and pricing analysis
Customer segmentation
Basket analysis
Perishable-product analysis
More advanced machine learning approaches
Additional Streamlit functionality
These extensions are also consistent with the limitations identified in the project report.
Let's Connect
If you found this project interesting or would like to discuss data analytics, business intelligence, machine learning, or similar projects, feel free to connect with me.
LinkedIn:
https:www.linkedin.com/in/fadydesokysaeedabdelaziz
GitHub:
Top comments (0)