1. Introduction
E-commerce sites collect huge amounts of listing data, but it's rarely ready to analyze right out of the box. Prices show up as messy text instead of numbers, review counts sometimes don't make logical sense, and ratings are missing more often than you'd expect. This article walks through the full process of turning raw Jumia product listings into a working, interactive Excel dashboard including the data problems I ran into, how I cleaned them, the formulas used to build new analytical fields, and what the data ultimately showed. The goal isn't just to show the final result, but to explain why each decision was made, so the same approach can be reused on similar marketplace data.
The main question behind this project was easy to ask but harder to answer well: does discounting actually help sellers on Jumia, and if so, how? More specifically: do bigger discounts lead to more customer engagement (reviews), or better perceived quality (ratings)? To answer that, the analysis moved through four stages: getting the raw data in, cleaning and validating it, engineering useful features, and building a dashboard that non-technical people could actually use.
2. Dataset
The raw dataset consists of 115 rows and six columns as per the Raw_Data worksheet in the Jumia_product_dashboard.xlsl file. Below are screenshots of the Raw_Data worksheet and field descriptions.
This dataset is just a single snapshot in time;it doesn't include sales numbers, how old each listing is, or who the seller is. That limits what we can actually claim. The analysis can show that things are related, not that one causes the other.
3. Data Quality Assessment
Before any transformation, the raw data was profiled systematically. This produced a data quality audit with the following findings:
- 345 price cells were text, not numbers. Prices had things like currency symbols, commas, or extra spaces so Excel couldn't do math on them until we cleaned them up.
- 58 blank cells in both Review and Ratings. These weren't zero they just meant no review or rating existed yet. If they were treated as zero, it would have made every average look much worse than it really was.
- 57 review counts were negative. A review count can't be negative in real life, so this was likely an error from scraping or exporting the data.
- *57 discount values didn't make sense *. Discounts should always be between 0% and 100%, so these were probably also export errors.
- 3 rows were exact duplicates. If left in, they would have been counted twice in every total and average.
- 2 prices were written as a range (like "100–200") instead of one number, which formulas can't use directly.
- Column headers weren't consistent with spelling mistakes. This seems minor, but it causes real problems once formulas depend on typing the header name exactly right.
4. Data Cleaning
Before cleaning, I copied the data from Raw_Data sheet to Cleaned_Data sheet and converted the data range to an Excel Table with Ctrl+T and gave it the name tblProducts.
- Removed the 3 duplicate rows. They added no new information and would have thrown off every total. 112 Products remained.
-
Renamed the column headers. so they were consistent. This also made them safe to use as table references (like
tblproducts[Current Price]) instead of plain cell ranges. -
Formatted prices column.Removed KSh, commas, and extra spaces, then convert to a number using this formula.
=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh",""),",",""))Format cleaned values as KSh #,##0.00 -
Turned the price ranges into a midpoint number. This keeps the row usable without guessing at an exact figure. I used the average function to do this.
-
Changed disount column data type. Remove %, convert to a number, divide by 100 if necessary, and format as Percentage using the formula below.
=VALUE(SUBSTITUTE(D2,"%",""))/100 -
Converted negative review counts to whole numbers. Since a negative review count isn't real, it was treated as an error and corrected it rather than deleting the row using the formula below.
=IF(E2="","",ABS(VALUE(E2))) -
Formatted Ratings column. Removed out of 5, converted to a decimal, and leave genuinely missing ratings blank using this formula.
=IF(F2="","",VALUE(SUBSTITUTE(F2," out of 5",""))) - Kept missing Review and Ratings cells as "Missing," not zero. This was the most important cleaning decision in the whole project.
- Added checks for Price, Ratings and discounts column using the formulas shown.
5. Excel formulas and enrichment fields.
Once the numbers were clean, new columns were added to turn raw numbers into useful, easy to read categories.
Discount Amount:
=[@[Old Price]]-[@[Current Price]]
Rating Category (fixed thresholds, since a star rating means the same thing everywhere):
=IF([@Ratings]="","Missing",IF([@Ratings]<3,"Poor",IF([@Ratings]<=4.5,"Average","Excellent")))
Discount Category Chosen to match how discount tiers are usually talked about in retail.
=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))
Price Category used the dataset's own quartiles that were calculated in the analysis sheet instead of fixed numbers, since "expensive" or "cheap" depends on what's actually in this dataset:
Price_Q1 = QUARTILE.INC(tblproducts[Current Price], 1)
Price_Q3 = QUARTILE.INC(tblproducts[Current Price], 3)
=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")))
Customer Engagement used the same quartile approach, splitting products into Low, Steady, and Strong engagement.
=IF([@Review]="","Missing",IF([@Review]<=Review_Q1,"Low Engagement",IF([@Review]<=Review_Q3,"Steady Engagement","Strong Engagement")))
Review Category
=IF([@Review]="","Missing",IF([@Review]<=Review_Q1,"Low Reviews",IF([@Review]<=Review_Q3,"Steady Reviews","Many Reviews")))
Finally, three simple QC check columns were added so problems would show up immediately if new data was ever added. Price, Ratings and Disounts.
6. PivotTable and analysis workflow;
After cleaning, 112 products remained. Summary numbers were calculated directly from the cleaned table.
These were the formulas used:
Total products =ROWS(tblProducts[Product])
Average current price =AVERAGE(tblProducts[Current Price])
Average old price =AVERAGE(tblProducts[Old Price])
Average discount =AVERAGE(tblProducts[Discount])
Average rating =AVERAGE(tblProducts[Rating])
Total reviews =SUM(tblProducts[Review])
Most expensive price =MAX(tblProducts[Current Price])
Least expensive price =MIN(tblProducts[Current Price])
Below is a screenshot of the summary.
The Xlookup function was used to find the excat most and least expensive product.
=XLOOKUP(MAX(tblproducts[Current Price]),tblproducts[Current Price],tblproducts[Product])
Correlation was also tested between the following fields using this formulas:
*Discount (x) versus review *(y); -0.136822724
As disount goes up the number of reviews tend to slightly go down but because the value is very close to zero the linear relationship is non-existent. That's worth pausing on, because it goes against the common assumption that bigger discounts automatically mean more customer engagement.
Ratings (x) versus review (y); 0.057209035
As the product rating increases, the number of reviews tends to increase very slightly.Because the strength of this relationship is so close to zero there is no meaningful linear relationship between the two variables, hence a product's rating does not predict how many reviews it will receive and vice versa.
Current price (x) versus rating (y); 0.110090213
As the current price of a product increases, its rating tends to increase slightly.The relationship is highly minimal. Because the value is so close to zero, price explains only a tiny fraction of the variation in product ratings. You cannot reliably use a product's price to predict its consumer satisfaction rating.
There are also three line charts for each correlation analysis.
From there, PivotTables were built on top of the cleaned data. PivotTables act as a middle layer. They make the charts faster to load and let the slicers filter several charts at once without slowing the whole workbook down.
The following are the tables created:
top 5 and bottom 5 products by rating;
top 10 products by discount;
top 10 products by reviews;
top 10 products by rating;
products with high discounts but low ratings;
products with high discounts but low engagement; and
products with many reviews but average ratings.
7. Dashboard Design and Slicer Connections
The dashboard was built so a non-technical person like a category manager could explore the data without ever touching a formula.
Below are screenshots of the Dashboard.
- The top part of the dashboard is the dashboard title: Product Performance and Pricing
- The furthest left side of the board are the slicers; Price category, Rating Category and Discount Category. All are connected to every PivotTable behind the charts, so clicking one option filters all the visuals on the sheet at once. Connecting both slicers to all five PivotTables (instead of just one chart each) is what makes the dashboard feel interactive rather than static: a single click updates the whole picture at once. You do this by right clicking on each slicer then reporting connections and choose all tables.
Eight charts:
- Top 10 products by ratings
- Top 10 products by review
- Top 10 products by Discounts
- Discount vs Reviews: does a bigger discount mean more reviews?
- Rating vs Review: how does review count change across different rating scores?
- Price vs Ratings: do more expensive products get rated higher?
- Rating Mix: what share of products fall into each rating category, including "Missing"?
- Discount Mix: how many products sit in each discount tier?
Insights
The insight text box contains findings and recommendations.
8. Key Findings
- Bigger discounts bring more reviews, but not better ratings. High and Medium Discount products get far more total reviews than Low Discount ones, but the correlation check shows this isn't a strong, reliable pattern at the individual product level.
- Most products sit in the High Discount group (about 63 products, versus 24 in Low Discount)The catalog leans heavily toward deep discounting.
- Higher priced products get slightly better ratings (about 4.1 vs. 3.75 for the cheapest tier), a small but consistent gap.
- A clear "Missing" ratings group exists, separate from "Poor": these are simply unrated products, not confirmed bad ones.
- Discount size barely predicts review count (correlation ≈ −0.14) something else is driving engagement more than the discount itself.
9. Business Recommendations
- Test smaller discounts on Low Discount products to see if reviews still grow without hurting ratings.
- Check a sample of High Discount listings to see if they're real deals or just aging stock being cleared out.
- Improve photos and descriptions on Low Price listings, where ratings tend to be a bit lower.
- Focus review-request campaigns on "Missing" rated products before worrying about the smaller "Poor" group.
- Don't rely on discount depth alone to grow reviews because the data shows it's a weak lever on its own.
10. Limitations and Lessons Learned
This dataset has no sales numbers, no listing age, and no seller identity hence "more reviews" can't be read as "more sales," and older listings can't be told apart from genuinely more popular ones. With only 112 cleaned rows, smaller groups (like Low Discount, at about 24 products) carry more uncertainty than larger ones. And because this is a single snapshot, none of these patterns should be treated as fixed facts but starting points for real tests, like controlled discount experiments, not final answers.
The biggest lesson from this project wasn't about charts or formatting. It was the decision to keep missing ratings labeled as "Missing" instead of quietly treating them as zero or dropping them. That one choice is what let the dashboard tell the difference between a product nobody has rated yet and one people genuinely dislike, a distinction that changes what a seller should actually do next.
Top comments (0)