1. Project Introduction and Objective
In today's e-commerce environment, businesses generate vast amounts of product data that can be leveraged to improve pricing strategies, marketing campaigns, and customer engagement. The objective of this project was to transform raw Jumia product data into meaningful business insights using Microsoft Excel.
The project involved data cleaning, feature engineering, statistical analysis, PivotTables, data visualization, and the development of an interactive dashboard that enables users to explore product performance across pricing, discounts, ratings, and customer engagement metrics.
Specifically, the dashboard was designed to answer key business questions such as:
- Which products receive the highest ratings?
- Which products generate the most customer engagement?
- How are products distributed across different price ranges?
- Do discounts influence customer engagement?
- What relationship exists between ratings and reviews?
- Which products may require improvement despite heavy discounting?
2. Dataset and Business Questions
The dataset contains 112 Jumia products and includes the following fields:
- Product Name
- Current Price
- Old Price
- Discount
- Reviews
- Ratings
Additional calculated fields were created during analysis, including:
- Price Category -Absolute Discount
- Discount Percentage
- Discount Category
- Rating Category
Key Business Questions
- Which products have the highest ratings?
- Which products attract the greatest number of reviews?
- How are products distributed across discount levels?
- Are discounts associated with increased customer engagement?
- Do higher ratings lead to more reviews?
- Which products combine high discounts with poor ratings?
Screenshot 1: Raw Dataset
Original Jumia product dataset before cleaning and transformation
3. Initial Data Quality Audit
Before performing the analysis, the dataset was audited to identify data quality issues.
The review focused on:
- Missing values
- Data type inconsistencies
- Duplicate records
- Invalid ratings
- Price formatting issues
Several fields required cleaning because prices contained currency text ("KSh"), ratings appeared as text strings such as "4.5 out of 5", and review counts included negative signs that had to be standardized.
Findings
- Product prices required conversion to numeric values.
- Ratings required extraction from text format.
- Additional classification fields were needed to support segmentation analysis.
- The data contained both raw and transformed fields requiring validation.
Screenshot 2: Data Audit
Data quality review and validation performed before analysis.
4. Cleaning and Preparation Decisions
A structured cleaning process was followed to prepare the dataset for analysis.
Rating Extraction
Ratings such as:
4.5 out of 5
were converted to
4.5
Creation of Analysis Tables
A structured product table (tblProducts) was created to support PivotTables and dashboard visualizations.
Screenshot 3: Cleaned Dataset
Cleaned and enriched dataset used for all dashboard calculations.
5. Excel Formulas and Enrichment Fields
Several calculated fields were added to improve the analytical value of the dataset.
Absolute Discount
=[@[Old Price]]-[@[Current Price]]
This measures the discount amount offered on a product.
Discount Percentage
=([@[Absolute Discount]]/[@[Old Price]])*100
This calculates the percentage reduction from the original price.
Price Category
Products were segmented using quartile thresholds:
Excel
=IF(B2<=Analysis!$B$6,"Low Price",
IF(B2<=Analysis!$B$7,"Medium Price",
"High Price"))
Discount Category
=IF(F2="","Missing",
IF(F2<20,"Low Discount",
IF(F2<=40,"Medium Discount",
"High Discount")))
Rating Category
Excel
=IF(I2="","Missing",
IF(I2<3,"Poor",
IF(I2<=4.5,"Average",
"Excellent")))
Correlation Analysis
Pearson correlation coefficients were calculated using:
=CORREL(DiscountRange,ReviewRange),
=CORREL(RatingRange,ReviewRange)
6. PivotTable and Analysis Workflow
After cleaning the dataset, PivotTables were used to summarize and analyze product performance.
The workflow involved:
- Creating PivotTables from the cleaned product table.
- Grouping products by price, discount, and rating categories.
- Ranking products based on ratings, reviews, and discounts.
- Generating PivotCharts for dashboard visualization.
Major PivotTable Analyses
Rating Distribution
| Rating Category | Product Count |
|---|---|
| Average | 26 |
| Excellent | 19 |
| Poor | Remaining products |
Major PivotTable Analyses
Rating Distribution
| Category | Product Count |
|---|---|
| Average | 26 |
| Excellent | 19 |
| Poor | Remaining products |
Discount Distribution
| Category | Product Count |
|---|---|
| High Discount | 62 |
| Low Discount | 18 |
| Medium Discount | Remaining products |
Engagement by Discount Category
| Category | Total Reviews |
|---|---|
| High Discount | 334 |
| Low Discount | 38 |
7. Dashboard Design and Slicer Connections
The final dashboard was designed to provide an intuitive and interactive user experience.
KPI Cards
The dashboard displays:
- Total Products: 112
- Average Current Price: KSh 1,186.89
- Average Rating: 3.89
- Total Reviews: 723
Visual Components
The dashboard includes:
- Rating Distribution Chart
- Discount Distribution Chart
- Average Rating by Price Category
- Engagement by Discount Category
- Top Products by Rating -Top Products by Reviews
- Top Products by Discount
Interactive Slicers
Slicers were connected to PivotTables and PivotCharts to allow filtering by:
- Price Category
- Discount Category
- Rating Category
This functionality enables users to explore different product segments dynamically without modifying the source data.
Screenshot 4: Final Dashboard
Interactive Excel dashboard displaying KPIs, rankings, and product performance trends.
8. Key Findings
Several important insights emerged from the analysis.
Product Pricing
The average product price was approximately KSh 1,186.89, with prices ranging from KSh 38 to KSh 3,750.
Discounting Is Common
Most products were classified within the High Discount category, indicating that discounting is a widely used pricing strategy among products in the dataset.
Ratings Are Generally Positive
Products recorded an average rating of 3.89 out of 5, suggesting generally favorable customer feedback.
Weak Relationship Between Discounts and Engagement
The correlation between discounts and reviews was approximately -0.174, indicating a very weak negative relationship.
This suggests that larger discounts do not necessarily lead to more customer engagement.
Ratings Do Not Strongly Drive Reviews
The correlation between ratings and reviews was approximately 0.057, indicating virtually no meaningful relationship.
Products with excellent ratings do not automatically receive more customer engagement.
Slight Positive Relationship Between Price and Rating
The correlation between current price and rating was approximately 0.110, suggesting only a weak positive association.
Higher-priced products tend to have slightly better ratings, but the relationship is minimal.
Top Rated Products
Examples of top-rated products include:
- Anti-Skid Absorbent Insulation Coaster For Home Office
- Bedroom Simple Floor Hanging Clothes Rack Single Pole Hat Rack
- 40cm Gold DIY Acrylic Wall Sticker Clock
9. Business Recommendations
Based on the findings, the following recommendations are proposed:
Promote Proven Products
Focus marketing efforts on products that combine:
- High ratings
- High review volumes
- Competitive pricing
Investigate Poorly Rated Products
Products receiving large discounts but still attracting poor ratings should be reviewed for potential quality issues.
Reduce Reliance on Discounts
Since higher discounts were not strongly associated with greater engagement, discounting alone should not be viewed as the primary growth strategy.
Utilize Customer Feedback
Review trends should continuously be monitored to identify recurring customer concerns and improvement opportunities.
Highlight High-Performing Listings
Products with consistently strong ratings should receive premium placement within the online marketplace.
10. Limitations and Lessons Learned
Limitations
- A significant portion of the products had missing (null) ratings and review information. As a result, analyses involving customer engagement and customer satisfaction were performed on a smaller subset of available products.
- The dataset does not include sales revenue.
- Customer demographics were unavailable.
- Product category analysis was limited. -Results reflect only the products included in the dataset.
Lessons Learned
This project demonstrated the value of Excel as a business intelligence tool and strengthened skills in:
- Data cleaning
- Formula development
- PivotTable analysis
- Dashboard design
- Data visualization
- Statistical analysis
- Insight generation
A key lesson was that assumptions should always be validated with data. In this analysis, discounts appeared influential at first glance, but correlation analysis showed only a weak relationship with customer engagement.
11. GitHub Repository
Conclusion
This project successfully transformed raw Jumia product data into an interactive Excel dashboard capable of supporting business decision-making. Through data cleaning, calculated fields, PivotTable analysis, correlation testing, and dashboard development, meaningful insights were generated regarding pricing strategies, discounts, ratings, and customer engagement. The analysis revealed that deeper discounting alone does not guarantee stronger engagement, highlighting the importance of product quality and customer satisfaction in driving marketplace performance.
Top comments (0)