How I turned a raw marketplace export into an interactive dashboard, and what it revealed about discounting.
Introduction
Jumia's marketplace team had a problem that sounds simple but isn't: nobody could say whether discounts actually work.
Sellers set prices and slash them routinely. But there was no evidence that a deeper discount brought more customer engagement, no view on whether well-rated products cost more or less than the rest, and no way to spot which listings were quietly underperforming.
So I was handed a raw export — 115 Home, Kitchen and Tools listings, straight off the site, with no cleaning — and asked to clean it, analyse it, and build an interactive Excel dashboard. This article walks through exactly how I did it, including the judgement calls.
The Data As It Arrived
The export was messy in the specific way that real marketplace exports are messy:
| Column | What it looked like | The problem |
|---|---|---|
| Current price | KSh 1,525 |
Text with a currency prefix and thousands separator |
| Current price | KSh 1,620 - KSh 1,980 |
A range, not a number |
| Old price | KSh 2,200 - KSh 3,200 |
Same issues |
| Discount | 0.38 |
A decimal where a percentage is expected |
| Review | -14 |
Review counts stored as negatives |
| Ratingd | 4.5 out of 5 |
Text, and a misspelled header |
None of that can be analysed as-is. KSh 1,525 is not a number. -14 reviews is not a count. 4.5 out of 5 is not a rating.
Step 1 — Confirming the Duplicates (Carefully)
The obvious first move is to find duplicates. The dangerous first move is to delete them by product name.
Five product names repeat in this dataset. If I had dropped duplicates by name alone, I would have destroyed real data:
| Product | Rows | Verdict |
|---|---|---|
| Balloon Insert, Birthday Party Balloon Set, PU Leather | 3 | Identical in every column → true duplicate |
| 6 Layers Steel Pipe Storage Shoe Cabinet | 2 | Identical in every column → true duplicate |
| Creative Owl Shape Keychain Black | 2 | KSh 199 vs KSh 176 → different products |
| Simple Metal Dog Art Sculpture | 2 | KSh 499 vs KSh 399 → different products |
| Angle Measuring Tool Full Metal | 2 | KSh 799 vs KSh 657 → different products |
So I compared complete rows across all six columns, not just the name. Only rows identical in every field were removed.
115 raw rows → 112 clean rows.
Step 2 — Converting Prices to Numbers
Two different problems here.
The simple case. KSh 1,525 → strip the KSh, strip the comma → 1525.
The range case. One product, a sofa cover, arrived as KSh 1,620 - KSh 1,980 — with a range in both the current and old price columns. I split the column on the space delimiter, which left two numeric columns, then used a conditional column in Power Query:
if [current price.5] = null then [current price.2]
else ([current price.2] + [current price.5]) / 2
Where a second value exists, take the midpoint; where it doesn't, keep the single price.
Judgement call: a midpoint is a choice, not a fact. It's the least-wrong option for a single range, but it should be recorded as an assumption — which is why it appears in the cleaning log.
Step 3 — Reviews, Ratings and Missing Values
Reviews were stored as negatives (-14, -55, -2). I split on the - character as a delimiter, dropped the sign-only column, and cast the remainder to a positive whole number.
Ratings read "4.5 out of 5". I split on the first space, which separated the number from the out of 5 text, then deleted the text column, leaving a clean numeric rating.
Missing values — and this is where the two columns diverge:
- Missing ratings (55 products) were imputed with the column average, done in Power Query's Advanced Editor.
- Missing reviews were left at zero, read as "no reviews". That asymmetry is the most important limitation in the whole project, and I'll come back to it.
Step 4 — Enrichment: Four New Columns
With clean numbers, I added the analytical layer:
| Column | Rule |
|---|---|
discount_amount |
old_price − current_price |
rating_category |
Poor < 3 · Average 3–4.5 · Excellent > 4.5 |
discount_category |
Low < 20% · Medium 20–40% · High > 40% |
price_categories |
Quartile-based: Low ≤ 481 · Medium ≤ 998 · High ≤ 1,676 · Premium > 1,676 |
Two decisions worth explaining.
The rating band gap. The brief specified Poor below 3, Average from 3, and Excellent above 4.5 — leaving 4.0 to 4.5 undefined. I folded that range into Average, making it 3 to 4.5 inclusive. That's caught by nothing and silently drops products otherwise.
The price thresholds. I used quartiles of current price rather than round numbers, so all four bands hold an equal share of the catalogue (28 products each). Equal groups make category comparisons meaningful; arbitrary thresholds don't.
Outlier detection used Tukey's IQR method on old price:
let
Q1 = List.Percentile(#"Changed Type11"[old_price], 0.25),
Q3 = List.Percentile(#"Changed Type11"[old_price], 0.75),
IQR = Q3 - Q1,
LowerBound = Q1 - 1.5 * IQR,
UpperBound = Q3 + 1.5 * IQR
in
if [old_price] < LowerBound or [old_price] > UpperBound then "Outlier" else "Normal"
That flagged exactly one product: the 32PCS Cordless Drill Set at KSh 6,143.
Finally, I checked the discount math. Recomputing (old_price − current_price) / old_price against the stated discount column showed deviations under 1 percentage point everywhere — consistent with source rounding. The deviations are stored in their own column so they stay auditable rather than assumed.
Step 5 — Descriptive Statistics
| Metric | Value |
|---|---|
| Products | 112 |
| Total reviews | 723 |
| Average current price | KSh 1,186.89 |
| Average old price | KSh 1,811.11 |
| Average discount | 36.78% |
| Average discount amount | KSh 624.21 |
| Average rating | 3.89 / 5 |
| Most expensive | 32PCS Cordless Drill Set — KSh 3,750 |
| Least expensive | 3PCS Knitting Crochet Needle Set — KSh 38 |
The catalogue leans hard on discounting: 62 of 112 products are discounted above 40%.
Step 6 — What the Analysis Actually Found
Finding 1: Bigger discounts do NOT bring more reviews
Correlation between discount percentage and review count: +0.012. Essentially zero.
| Discount band | Products | Avg reviews |
|---|---|---|
| Low (<20%) | 18 | 2.11 |
| Medium (20–40%) | 32 | 10.97 |
| High (>40%) | 62 | 5.39 |
Medium discounts more than double the engagement of deep discounts. The 62 products slashing prices above 40% attract roughly half the reviews of the shallower medium band. This directly contradicts the assumption the whole discounting strategy was built on.
Finding 2: Expensive products are rated slightly higher
Correlation: +0.110.
| Price band | Products | Avg rating |
|---|---|---|
| Low (≤481) | 14 | 3.64 |
| Medium (≤998) | 14 | 4.03 |
| High (≤1,676) | 10 | 3.68 |
| Premium (>1,676) | 19 | 4.08 |
Premium products rate highest, though the pattern isn't clean — the High band dips below Medium.
Finding 3: The most-reviewed product is also one of the worst-rated
The 120W Cordless Vacuum Cleaner has 69 reviews — the highest demand in the catalogue — paired with a 2.8 rating at 49% off.
It's not alone. Ten products are discounted above 40% while rated below 3.0:
| Product | Discount | Rating | Reviews |
|---|---|---|---|
| 120W Cordless Vacuum Cleaner | 49% | 2.8 | 69 |
| 5-PCS Stainless Steel Cooking Pot Set | 55% | 2.1 | 13 |
| Intelligent LED Night Light | 52% | 2.7 | 15 |
| Wall-mounted Sticker Plug Fixer | 50% | 2.0 | 1 |
| 380ML USB Portable Blender | 50% | 2.3 | 7 |
Finding 4: 32 high-discount products have zero reviews
More than half of all deeply discounted listings generate no measurable response at all.
Finding 5: Beware the perfect ratings
Seven products hold a 5.0 rating. Six of them have 1–3 reviews.
The genuinely strongest performer is the 40cm Gold Acrylic Wall Clock: 4.8 from 12 reviews — a rating you can actually trust. This is the raters' paradox: small-sample perfect scores are mostly noise.
Step 7 — The Dashboard
The dashboard sheet consolidates everything into a seller-readable board.
Five KPIs and where each number comes from:
| KPI | Value | Source |
|---|---|---|
| Total Products | 112 | COUNTA(Table1[product]) |
| Average Price | 1,186.89 | AVERAGE(Table1[current_price]) |
| Average Discount Amount | 624.21 | AVERAGE(Table1[discount_amount]) |
| Average Rating | 3.89 | AVERAGE(Table1[ratings]) |
| Total Reviews | 723 | ABS(SUM(Table1[review])) |
Three sections: product performance (top-10 tables by discount, reviews and rating), trends (three relationship charts) and categories (rating, discount and price bands), all wired to slicers for rating category, discount category and price category, with conditional formatting on the extremes.
A note on the KPI labels. The tile currently labelled AVERAGE DISCOUNT points at discount_amount (624.21), while the true average discount percentage is 36.78%. It should be relabelled Average Discount Amount — a seller reading "average discount 624" would misread the board.
Limitations (Please Read These)
I want to be straight about what this analysis can and cannot support.
- 55 of 112 products have no rating data. Ratings were imputed with the column average, so half the catalogue carries a synthetic value. Every rating conclusion rests on the 57 products with real ratings.
-
Missing reviews became zeros, not "unknown". So
32 high-discount products with zero engagementmay partly reflect unreported data rather than true apathy. This is the weakest link in the dataset. - Ratings may be fragmented. No product exceeds 70 reviews, and several products appear twice at different prices. It's likely these are price variants, meaning ratings are split per variant rather than consolidated per product.
- Correlations are all weak (below |0.2|). These are patterns in 112 rows, not proof of cause and effect.
5. One price range was resolved to a midpoint — an assumption, not a fact.
Three Recommendations for Jumia Sellers
1. Cap discounts at 40%.
Medium-band products average 10.97 reviews versus 5.39 in the High band — roughly double the engagement at a shallower discount. Moving the 62 high-discount listings into the 20–40% range protects margin without costing attention.
2. Fix quality before cutting price on the vacuum cleaner and cooking pot set.
The 120W Cordless Vacuum Cleaner has the catalogue's highest demand (69 reviews) and a below-average 2.8 rating at 49% off; the Cooking Pot Set sits at 2.1 at 55% off. Deep discounts on poorly-rated products generate volume and negative sentiment — not repeat customers.
3. Stop discounting the 32 zero-review listings and investigate visibility instead.
Over half of all high-discount products have no reviews. Zero engagement at 40%+ off points to a discoverability or listing-quality problem, not a pricing one. Money is being given away with no evidence it's buying traffic.
Conclusion
The headline finding is uncomfortable but useful: discounting is not buying engagement on this marketplace.
The evidence points the other way — moderate discounts outperform deep ones, and the deepest discounts sit on products customers don't rate well. Meanwhile, more than half the heavily discounted catalogue is invisible to customers entirely.
The fix isn't "discount more." It's discount with discipline, fix the products that are letting customers down, and find out why 32 listings with 40%+ off are generating nothing at all.
Tools used: Excel (Power Query, PivotTables, slicers, conditional formatting, dashboard design).
Repo: [https://github.com/Cyrusz55/Jumia_Sale_Analysis] · Workbook: Excel_jumia_analysis.xlsx · README: [https://github.com/Cyrusz55/Jumia_Sale_Analysis/blob/master/README.md]
Top comments (0)