Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

If you've ever wondered what actually happens between "I have a spreadsheet full of messy data" and "I have a dashboard I'd be proud to show someone," this article walks through that whole journey, step by step, using a real example.

1. Project Introduction and Objective

Online stores discount things constantly. But here's a question that's easy to assume the answer to and actually get wrong: Does a bigger discount get you more customer interest?
The goal wasn't just "make some charts." It was to build something a manager could actually open and trust because every number on it either comes from a live formula or is clearly an assumption.

2. Dataset and Business Questions

The raw data is : 116 rows, 6 columns.

Column	What it holds
Product	The product
Current price	The price you'd actually pay
old price	The original price before the discount
Discount	The advertised discount
Review	Number of customer reviews
Ratingd	rating

Business questions:

• whether larger discounts are associated with more reviews
• whether highly rated products attract stronger engagement
• whether price and rating move together
• which products perform best based on ratings and reviews
• which products may need a different pricing or marketing strategy.

3. Initial Data-Quality Audit

I audited the raw data for problems. Here's what I actually found:

• 58 rows had no value in Review or Ratingd at all.
• 58 rows had negative numbers in the Review column. Reviews are a count of people, so a negative count is impossible.
• 3 rows were exact duplicates.
• 6 different product names appeared more than once, but with different prices.
• 1 row had a price range instead of a single value.
• Prices were stored as text, with a currency prefix.
• Ratings were stored as text.

4. Cleaning and Preparation Decisions

Every cleaning decision went into a documented "data dictionary"

Issue	Rows affected	Decision	Reason
Negative review counts	58 rows	Converted to positive	A review count can't be negative
Exact duplicate rows	3	Removed	They were duplicates
Duplicate product names, different prices	6	Kept as separate rows	interpreted as different types of the same product
One price stored as a range	1	Replaced with the midpoint	A single number was required for any calculation to work
Prices stored as text with currency symbols	113	Converted to numbers	Can't calculate on text
Ratings stored as "4.5 out of 5"	58	Extracted the numeric 4.5	Text can't be calculated

5. Excel Formulas and Enrichment Fields

This is the part that turns "a cleaned spreadsheet" into "a dataset you can actually analyze."
Breaking down what these do:

• Rating Status =IF(OR(F2<0,F2>5,ISBLANK(F2)),"Check rating","OK")
  Rating must be between 0 and 5. If it isn't (or it's blank), this flags it instead of silently trusting the data.

• Calculated Discount =IFERROR((C2-B2)/C2,"")
  Instead of trusting the advertised discount, I recalculated it independently from the old and new prices.

• Discount Check =IF(OR(D2="",K2=""),"Missing",IF(ABS(D2-K2)>2%,"Check Discount","OK")) compares the advertised discount to the calculated one, and flags anything more than 2 percentage points apart.

• Rating Category =IF(F2="","Missing",IF(F2<3,"Poor",IF(F2<=4.5,"Average","Excellent"))) Rating into "Poor" (below 3), "Average" (3–4.5), or "Excellent" (above 4.5), so I can group and count products instead of comparing 113 individual decimals.

I used the same pattern for Discount Category (Low/Medium/High) =IF(D2="","Missing",IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))) and Price Category (Low/Medium/High) =IF(B2="","Missing",IF(B2<=Price_Q1,"Low Price",IF(B2<=Price_Q3,"Medium Price","High Price"))) , the price Categories are based on the dataset's own quartiles, calculated with:

Price_Q1 = QUARTILE.INC(tblProducts[Current Price], 1)   → KSh 493
Price_Q3 = QUARTILE.INC(tblProducts[Current Price], 3)   → KSh 1,669.50
Review_Q3 = QUARTILE.INC(tblProducts[Review], 3)         → 14 reviews

I stored these as named ranges so every formula in the workbook could reference Price_Q1 instead of a hardcoded number.
Engagement flags =IF(E2="","Missing",IF(E2>=Review_Q3,"Strong Engagement","Below Threshold")) so that i can group them with the dataset review threshold.

Finally, I layered on a few compound flags that combine two conditions at once:

• High Discount + Low Rating =IF(OR(D2="",E2=""),"Missing",IF(AND(D2>40%,F2<3),"Flag","")) discount over 40% and rating under 3
• High Discount + Low Engagement =IF(OR(D2="",E2=""),"Missing",IF(AND(D2>40%,E2<Review_Q3),"Flag","")) discount over 40% and reviews below the 75th percentile
• Many Reviews + Average Rating =IF(AND(E2>=Review_Q3,F2>=3,F2<=4.5),"Flag","") heavily reviewed but only "Average," not "Excellent"

6. PivotTable and Analysis Workflow

With every product tagged and categorized, PivotTables became genuinely simple.

Here's one of the PivotTables:

PivotTable feeding a chart:

I built seven of these PivotTable/PivotChart pairs in total — rating mix, discount mix, price vs. rating, engagement vs. discount, and three "Top 10" rankings (by rating, by reviews, by discount).

Alongside the PivotTables, I ran descriptive statistics and correlation checks directly on the Cleaned Data table using AVERAGE, SUM, MAX/MIN, and CORREL.

7. Dashboard Design and Slicer Connections

Everything comes together on a single dashboard sheet: a title bar, five KPI cards across the top, and nine charts.

8. Key Findings

Here's the "Business Insights" sheet I built to summarize what the data actually showed

In plain language:

• The catalog is aggressively discounted. 55.4% of products (62 of 112) are discounted more than 40%.
• But bigger discounts don't buy more attention. Products discounted 20–40% average 15.3 reviews each more than the heavily-discounted group's 11.1 average, and clearly more than the lightly-discounted group's 9.5. The correlation between discount size and review count across the whole catalog is essentially flat (r ≈ -0.14).
• Rating and popularity are basically unrelated. A product's star rating barely correlates with how many people reviewed it (r ≈ 0.06).
• Price has only a weak relationship with rating. Higher-priced products rate slightly better on average (4.08 vs. 3.64 for the cheapest band), but the relationship is too weak to call a real trend (r ≈ 0.11).
• One product is a real warning sign: a cordless vacuum cleaner has the most reviews in the entire dataset (69) but only a 2.8-star rating.

9. Business Recommendations

Based on what the data actually supports:

1. Don't assume "discount deeper" means "sell more." Test the 20–40% discount band deliberately it's where engagement was strongest in this dataset.
2. Audit high-visibility, low-rating listings first. They're seen by the most people, so any quality or accuracy issue there does the most damage.
3. Invest in listing quality over price cuts. Since price alone barely predicts rating, better photos, descriptions, and accurate specs may do more for perception than another 5% off.
4. Chase more reviews on unrated listings, not fewer discounts. Half the catalog (55 of 112 products) has no rating or review at all.

10. Limitations and Lessons Learned

Being upfront about what this analysis can't tell you is just as important as the findings themselves:

• A review count isn't a sales figure. Engagement ≠ conversion, and nothing here should be read as a profit or revenue claim.
• One number was a judgment call, not a fact: the single price-range row that I replaced with a midpoint.

Cleaning and documentation take longer than the "real" analysis, and that's normal. Most of the time on this workbook went into deciding what a negative review count means, what to do with a price range, and how to label the 55 products with no rating.

11. Links

[https://github.com/majalealex-ux/Jumia-Product-Performance-Dashboard]