Spots

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case…

title: "Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products" published: false description: "Turning a messy export of 115 Jumia listings into a clean dataset and an interactive Excel dashboard on pricing, discounts and customer reviews." tags: excel, dataanalysis, ecommerce, beginners Jumia and its sellers set prices and discounts every day, but nobody could say whether those discounts actually move customer engagement. In this project, part of the DSEAfrica Data Lab, I played the role of a data analyst on Jumia's marketplace team. The task: take a raw export of home, kitchen and tools listings and turn it into an interactive Excel dashboard that sellers can read without any Excel skills. Here is how I did it, from the raw file to the final recommendations.

The business questions Before opening the data, I

The business questions Before opening the data, I wrote down what the dashboard had to answer: Do higher discounts bring more customer reviews? Do highly rated products cost more or less than the rest? Which listings perform best, and which need a different pricing or marketing strategy? The raw data The export contains 115 listings and six columns: product name, current price, old price, discount, reviews and rating. It is deliberately messy: prices are text with a KSh prefix and thousands separators (KSh 1,525) one listing shows a price range (KSh 1,620 - KSh 1,980) review counts are stored as negative numbers ratings are text (4.5 out of 5) and the header is misspelt (Ratingd) there are blanks and duplicate rows � Step 1: Cleaning the data I kept the raw sheet untouched and built a cleaned copy, logging every decision in a Cleaning_Log sheet.

Duplicates: 3 rows were exact duplicates across all

Duplicates: 3 rows were exact duplicates across all six columns, so 115 rows became 112. Listings with the same title but different prices were kept as separate variants. Prices: I stripped KSh and the commas to get real numbers. For the price range I used the midpoint (1,800 and 2,700) and flagged the row. Discount: 38% became the number 38. Reviews: I took the absolute value of the negative counts. Blank reviews became 0, because a blank means no review. Ratings: I removed "out of 5" and left blank ratings empty rather than 0, so they don't drag averages down. A quick check confirmed that my recalculated discount matches the reported discount within 0.5 percentage points on every row, except the price-range listing.

� Step 2: Enriching the data I added

� Step 2: Enriching the data I added calculated columns using formulas: Discount Amount = Old Price - Current Price Category Rule Rating Poor < 3, Average 3 to < 4, Good 4 to < 4.5, Excellent >= 4.5 Discount Low < 20%, Medium 20-40%, High > 40% Price Budget < KSh 500, Mid-range 500-1,499, Premium >= 1,500 The brief's rating bands left a gap between 4 and 4.5, so I added a fourth band, "Good". The price thresholds are round numbers close to the first and third quartiles of the current price. All thresholds live in an Assumptions sheet, so changing one cell updates every category.

� Step 3: Descriptive statistics Measure Value Products

� Step 3: Descriptive statistics Measure Value Products 112 Average current price KSh 1,186.89 Average old price KSh 1,811.11 Average discount 36.78% Average rating 3.89 out of 5 (57 rated products) Total reviews 723 The most expensive product is a 32-piece cordless drill set at KSh 3,750, and the cheapest is a crochet needle set at KSh 38. Only 57 of the 112 products have a rating, which matters for everything that follows. Step 4: What the data says I used Pearson correlations and category averages. Relationship r Discount vs reviews 0.01 Rating vs reviews 0.06 Price vs rating 0.11 Price vs discount -0.55 Discount category Products Avg reviews Avg rating Low (< 20%) 18 2.1 3.73 Medium (20-40%) 32 11.0 4.28 High (> 40%) 62 5.4 3.61 Higher discounts do not lead to higher engagement. The best results come from moderate discounts.

Of the 62 high-discount listings, 32 have no

Of the 62 high-discount listings, 32 have no review at all. I also flagged segments: 10 listings combine a discount above 40% with a rating below 3, and 9 products show strong demand (at least 10 reviews and a rating of 4.5 or more). Outliers, found with the 1.5 x IQR rule, include 3 price outliers and 10 review-count outliers.

Step 5: The interactive dashboard The Dashboard sheet

Step 5: The interactive dashboard The Dashboard sheet has four sections: Overview: five KPIs (total products, average price, average discount, average rating, total reviews) Product performance: top-10 tables by discount, reviews and rating, plus the five lowest-rated products Trends: charts comparing reviews and ratings across categories, plus three scatter plots Categories: how products split into rating and discount bands Filters for rating, discount and price category update every KPI, table and chart. Conditional formatting highlights the extremes: ratings below 3 in red, 4.5 and above in green, and discounts above 40% in amber. �

� Recommendations for Jumia sellers Prefer moderate discounts

� Recommendations for Jumia sellers Prefer moderate discounts of 20-40%. They average 11.0 reviews and a 4.28 rating, against 5.4 and 3.61 for discounts above 40%. Fix quality before discounting. Ten listings pair a deep discount with a rating below 3. One vacuum cleaner has the most reviews of all (69) but only 2.8 stars, so more visibility is hurting it. Promote proven products and collect reviews for the rest. Nine products already show strong demand, while 55 of the 112 listings have no review at all. Limitations The sample is small (112 products, 57 rated), correlation is not causation, and review count is only a proxy for engagement. Thresholds such as "many reviews = 10 or more" are my own choices and can be edited in the workbook.

What I learned Cleaning takes most of the

What I learned Cleaning takes most of the effort, and small decisions (blank rating as empty instead of 0, midpoint for a price range) change the results. Logging every decision made the analysis easy to defend. The workbook and README are available in the GitHub repository:https://github.com/aymarganina-sys/jumia-product-performance-dashboard . For further actions, you may consider blocking this person and/or reporting abuse

News

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

title: "Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products" published: false description: "Turning a messy export of 115 Jumia listings into a…

@spots #dev
Source: Dev.to
See more like this