Introduction
In one of my previous articles: Getting Started with Excel for Data Analytics: From Basics to Data Cleaning I gave the basics to getting started with excel. This article walks through an end-to-end project: building an interactive Excel dashboard to analyze product performance on Jumia. By analyzing product data, we can understand how discounts, customer reviews, and ratings influence online shopping trends.
The final deliverable is a professional dashboard that turns raw data into actionable pricing, promotion, and customer-engagement insights, all within Microsoft Excel.
1.Project Objective
The primary goal is to build a professional, interactive Excel dashboard that transforms raw Jumia product data into useful business intelligence. By the end of this project, stakeholders should be able to answer five core questions:
Does a larger discount drive more customer reviews (engagement)?
Are highly rated products associated with stronger engagement?
How do price and rating move together?
Which products are the best performers based on ratings and reviews?
Which products may need a different pricing or marketing strategy?
This is not merely a chart-building exercise; it is an analysis project. Every cleaning decision must be traceable, calculations validated, and recommendations grounded in evidence from the workbook.
2. Dataset and Business Questions
Dataset Overview
The dataset (Excel_jumia_dataset.xlsx) contains 115 product rows with the following columns:
| Column | Description |
|---|---|
Product |
Product name (text) |
Current price |
Selling price in KSh; may include ranges (e.g., "KSh 1,620 - KSh 1,980") |
old price |
Original price; may also include ranges |
Discount |
Numeric discount fraction (e.g., 0.38) |
Review |
Number of reviews (stored as negative integers; blanks possible) |
Ratingd |
Star rating as text (e.g., "4.5 out of 5"); blanks possible |
Business Questions
The dashboard is designed to answer the five questions above, each addressing a different aspect of product performance:
- Discount vs. Reviews: Are price cuts effective at driving customer engagement?
- Rating vs. Reviews: Do better products naturally attract more attention?
- Price vs. Rating: Is there a relationship between what customers pay and how they perceive quality?
- Top/Bottom Performers: Which products deserve promotion, and which need improvement?
- Strategy Recommendations: Which products require pricing or marketing adjustments?
3. Initial Data-Quality Audit
Before any analysis, a thorough data-quality audit is essential. As one guide notes, "Most failures start before design: messy source data, unclear metrics, and too many visuals on one sheet".
Identified Issues
| Issue | Details |
|---|---|
| Price formats | Current price and old price contain text ("KSh 950") and sometimes ranges ("KSh 1,620 - KSh 1,980") |
| Review column | All non-blank entries are negative integers (e.g., -2, -55), which is unconventional |
| Rating column | Stored as text ("4.5 out of 5") rather than numeric |
| Missing values | Several rows lack reviews or ratings entirely |
| Duplicate rows | Some products appear multiple times (e.g., "Balloon Insert" appears at least twice) |
Summary Statistics (Pre-Cleaning)
- Total products: 115
- Products with missing reviews: ~55 rows
- Products with missing ratings: ~55 rows
- Price ranges: KSh 38 to KSh 3,750+
- Discount range: 0.01 to 0.64
The dataset is messy but analyzable. A perfect sample for demonstrating Excel's data-cleaning capabilities.
4. Cleaning and Preparation Decisions
Tools Used
- Power Query (Get & Transform) for reproducible, traceable data cleaning
- Excel formulas for calculated columns and validation
Cleaning Steps
Before everything else open a new worksheet and name it cleaned_data. Copy the top raw data (headers + 116 rows of fields) and paste it into cell A1 of the new sheet created.
4.1 Price Columns
Issue: Text with currency symbols and commas; sometimes ranges.
Solution: Extract the minimum price for Current price and the maximum price for old price.
// Power Query: Extract first number from text
= Text.BeforeDelimiter([Current price], " - ")
// Then remove "KSh " and commas, convert to number
4.2 Review Column
Issue: Negative integers or blanks.
Solution: Convert to positive integers using =**ABS**(Review). Treat blanks as 0 (no reviews).
4.3 Rating Column
Issue: Text format *4.5 out of 5*.
Solution: Extract the numeric part.
m // Power Query: Extract rating = Number.From(Text.BeforeDelimiter([Ratingd], * *))
4.4 Missing Values
- Reviews: Fill blanks with
0 - Ratings: Leave as
null(exclude from rating-based analyses)
4.5 Duplicates
Remove duplicate rows using Power Query's Remove Duplicates function.
Cleaning Summary
| Before | After |
|---|---|
| Text prices with KSh | Numeric prices (KSh) |
| Negative reviews | Positive integers |
*4.5 out of 5* |
4.5 (numeric) |
| Blank reviews | 0 |
| Duplicate rows | Removed |
5. Excel Formulas and Enrichment Fields
Key Formulas Used
Extract Numeric Price from Text
excel =**VALUE**(**SUBSTITUTE**(**LEFT**(A2, **FIND**(* *, A2)-1), *,*, ""))
Extract Rating
excel =**VALUE**(**LEFT**(Ratingd, **FIND**(* *, Ratingd)-1))
Convert Negative Reviews
excel =**ABS**(Review)
Validate Discount
excel =(old_price - current_price) / old_price
Enrichment Fields Added
To enable deeper analysis, I added several calculated columns:
| Enrichment Field | Formula / Logic | Purpose |
|---|---|---|
Price_Group |
IF(current < 500, *Low*, IF(current < 1500, *Medium*, *High*)) |
Segment by price tier |
Discount_Group |
IF(Discount < 0.25, *Low*, IF(Discount < 0.45, *Medium*, *High*)) |
Segment by discount level |
Rating_Category |
IF(Rating < 3, *Poor*, IF(Rating < 4, *Average*, *Excellent*)) |
Segment by rating quality |
Performance_Score |
(Rating * 10) + (Reviews / MAX(Reviews) * 10) |
Composite score for ranking |
Absolute_Discount |
old_price - current_price |
Absolute discount amount |
6. PivotTable and Analysis Workflow
PivotTable Setup
PivotTables are the backbone of Excel dashboards as they help you slice, dice, and visualize data with no formulas needed. I created PivotTables on a separate support sheet, following the best practice of keeping them isolated from the dashboard itself.
Analysis Workflow
- Load Cleaned Data: Power Query loads transformed data into a worksheet table.
-
Create PivotTables: Build separate PivotTables for each analysis:
- Average price, discount, rating, and total reviews (KPIs)
- Discount vs. Reviews (scatter data)
- Rating vs. Reviews (scatter data)
- Price vs. Rating (scatter data)
- Top/Bottom performers (sorted tables)
- Build Charts: Connect PivotCharts to each PivotTable.
- Assemble Dashboard: Place charts, KPIs, and slicers on a single dashboard sheet.
Scatter Plot Setup
For correlation analysis (Discount vs. Reviews, Rating vs. Reviews, Price vs. Rating), I used:
- X-axis: Independent variable (Discount, Rating, or Price)
- Y-axis: Dependent variable (Reviews)
- Trendline: To visualise correlation direction
7. Dashboard Design and Slicer Connections
Design Principles
Following best practices for dashboard design:
- Simplicity: Avoid pie charts and 3D charts; prioritise bar, line, and scatter charts
- Information hierarchy: Primary KPIs at the top, charts in the middle, filters on the left
- Colour discipline: Keep colours to three or fewer
- Clarity: Every chart has a clear title and purpose
Dashboard Layout
+----------------------------------------------------------+
| Jumia Product Performance Dashboard |
| Total: 112 | Avg Discount: 36.8% | Avg Rating: 4.1 |
| Total Reviews: 723 |
+----------------------------------------------------------+
| [Slicers: Discount Group | Price Group | Rating Category] |
+----------------------------------------------------------+
| Discount vs Reviews | Rating vs Reviews | Price vs Rating |
| (Scatter + Trend) | (Scatter + Trend) | (Scatter + Trend) |
+----------------------------------------------------------+
| Top 10 by Rating | Top 10 by Reviews | Bottom 10 by Perf. |
| (Bar Chart) | (Bar Chart) | (Bar Chart) |
+----------------------------------------------------------+
| Key Insights: |
| • No strong correlation between discount and reviews |
| • High rating doesn't guarantee many reviews |
| • Top performer: 137-Piece Cake Decorating (4.6, 55) |
+----------------------------------------------------------+
Slicer Connections
Slicers provide interactivity, allowing users to filter the entire dashboard dynamically:
- Discount Group slicer (Low / Medium / High)
- Price Group slicer (Low / Medium / High)
- Rating Category slicer (Poor / Average / Excellent)
All PivotTables and PivotCharts are connected to the same slicers, ensuring consistent filtering across the dashboard.
8. Key Findings
8.1 Discount vs. Reviews
Finding: There is no strong positive correlation between discount size and review count. Products with very high discounts (>50%) often have low to moderate review counts.
| Product | Discount | Reviews | Rating |
|---|---|---|---|
| 137-Piece Cake Decorating | 24% | 55 | 4.6 |
| Electronic Digital Caliper | 35% | 49 | 4.6 |
| 3D Waterproof Shower Curtain | 49% | 44 | 4.6 |
| 6-in-1 Bottle Opener | 64% | 0 | N/A |
Insight: Price cuts alone don't guarantee engagement. Medium discounts (20–40%) tend to attract more reviews than very high discounts.
8.2 Rating vs. Reviews
Finding: High rating does not guarantee many reviews. Some 5-star products have only 1–2 reviews (e.g., Anti-Skid Coaster, Peacock Pillow). Conversely, lower-rated products sometimes have more reviews.
| Product | Rating | Reviews |
|---|---|---|
| Anti-Skid Coaster | 5.0 | 2 |
| Peacock Pillow | 5.0 | 2 |
| 5-PCS Cooking Pot Set | 2.1 | 13 |
Insight: Engagement is driven by factors beyond rating, product category, visibility, and promotion matter more.
8.3 Price vs. Rating
Finding: No clear relationship between price and rating. Products at all price points can be highly or poorly rated.
| Product | Price (KSh) | Rating |
|---|---|---|
| Classic Black Cat Pillow | 171 | 5.0 |
| LED Wall Digital Clock | 2,999 | 4.6 |
| VIC Wireless Vacuum | 1,220 | 2.9 |
Insight: Price does not consistently predict quality perception.
8.4 Top Performers
Based on composite scoring (rating + reviews), the top performers are:
- 137 Piece Cake Decorating Tool (4.6, 55 reviews)
- Electronic Digital Display Caliper (4.6, 49 reviews)
- 3D Waterproof Shower Curtain (4.6, 44 reviews)
- 100 Pcs Crochet Hook Set (4.7, 39 reviews)
- LASA Folding Table (4.8, 5 reviews)
8.5 Underperformers
Products needing attention:
| Product | Rating | Reviews | Discount |
|---|---|---|---|
| Wall-mounted Plug Fixer | 2.0 | 1 | 50% |
| 5-PCS Cooking Pot Set | 2.1 | 13 | 55% |
| Artificial Potted Flowers | 2.2 | 6 | 45% |
| 380ML USB Blender | 2.3 | 7 | 50% |
Observation: Many underperformers have high discounts but poor ratings, suggesting product quality issues rather than pricing problems.
9. Business Recommendations
For Underperforming Products (Low Rating + Low Reviews)
- Investigate product quality: Poor ratings despite high discounts suggest quality issues
- Improve product listings: Better images, descriptions, and specifications
- Consider discontinuation: If quality cannot be improved, remove the product
For High-Rated, Low-Review Products
- Increase visibility: Use Jumia's advertising or promotional campaigns
- Encourage reviews: Offer voucher rewards, loyalty points, or discount coupons for verified feedback
- Feature in curated collections: Highlight high-rated products in category pages
For Pricing Strategy
- Avoid excessive discounts: Products with >50% discounts don't show higher engagement
- Optimise discount ranges: Medium discounts (20–40%) appear most effective
- Shift from price-driven to value-driven strategy: Focus on product quality and customer experience rather than competing solely on price
For Overall Platform Growth
- Incentivise reviews systematically: Build a review culture through rewards
- Spotlight high-rated categories: Use top performers as case studies
- Build trust through reviews: Transparent customer feedback drives long-term loyalty
10. Limitations and Lessons Learned
Limitations
| Limitation | Impact |
|---|---|
| Missing data | Several rows lack reviews or ratings, reducing sample size |
| No product categories | Manual classification would enable deeper segmentation |
| Price extraction assumption | For ranges, we used lower bound for current and upper bound for old price |
| Review count ≠ engagement quality | Some products may be new; review counts don't capture sentiment |
| Observational analysis | Correlations do not imply causation |
Lessons Learned
- Data cleaning is the most critical step: It is important to clean your data because it produces a structured dataset ready for analysis.
- Power Query saves time: Automating cleaning steps makes the process reproducible and traceable
- Slicers transform usability: Interactive filters make dashboards far more useful than static charts
- Simplicity wins: A dashboard is only useful if someone can open it, read it in under a minute, and trust the numbers
- Excel remains relevant: Despite modern BI tools, Excel's flexibility and ubiquity make it an essential skill
11. Links to Resources
GitHub Repository
The complete project, including the Excel workbook, cleaning scripts, and documentation, is available on GitHub:
Dashboard File
Direct download link to the Excel dashboard:
Download Jumia_Product_Dashboard.xlsx
Conclusion
This project demonstrates how Microsoft Excel combined with Power Query, PivotTables, and slicers can transform raw e-commerce product data into a professional, interactive dashboard. The analysis reveals that discounts alone do not drive engagement and that high ratings do not guarantee many reviews. Instead, product quality, visibility, and strategic pricing matter more.
For Jumia and other e-commerce platforms, the path to growth lies not in price wars but in value-driven strategies: improving product quality, spotlighting top performers, optimising discount ranges, and building trust through authentic customer reviews.
Top comments (0)