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

Introduction

After completing Excel's coursework this week courtesy of the LuxDevHQ Data Science, Analytics and AI Programme, I decided to put what I had learnt into practice by analyzing Jumia product data. I challenged myself to turn the raw data into something useful for analysis and decision making.

The dataset contained 115 product listings with information such as prices, discounts, reviews and ratings. However, it was far from ready for data analysis. There were missing values, duplicate records, negative review counts, inconsistent formatting and even one product whose price was given as a range.

So, this project ended up being about much more than creating a dashboard. I first had to understand the data, identify the issues, make reasonable cleaning decisions, document those decisions and then use the cleaned data to build an interactive Excel dashboard.

In this article, I'll walk through that process, from the initial data-quality audit to the final dashboard and the insights I found from the analysis.

The Dataset and Business Questions

The dataset, Excel_jumia_dataset.csv, contained six fields originally:

• Product
• Current price
• old price
• Discount
• Review
• Ratingd

The last column, Ratingd, was misspelled in the original dataset and was later corrected to Rating.

One of the first things I noticed was that the dataset didn't contain actual sales, revenue or units sold data. Because of this, I couldn't use sales as a measure of product performance.

Instead, I used review count as a proxy for customer engagement throughout the project.

This was important to take note of because having more reviews doesn't necessarily mean that a product has higher sales. A product that has been listed for a longer period, for example, may simply have had more time to accumulate reviews.

With that in mind, I focused on the following questions:

• Are bigger discounts associated with more reviews?
• Do highly rated products receive more engagement?
• Does product price have any relationship with rating?
• Which products stand out based on ratings, reviews or discounts?
• Which products might need a different pricing, marketing or listing strategy?

Data-Quality Audit

Before changing anything, I wanted to understand exactly what I was working with.

The initial audit showed:

• 115 rows and 6 columns
• 58 blank Review values
• 58 blank Rating values
• 3 exact duplicate rows
• 6 repeated product names with different prices, discounts, or review counts
• 57 negative Review values
• 1 row where both Current price and Old price were given as ranges
• Ratings were within the expected 0–5 range
• Discounts were within 0–100%
• No Current Price was higher than the corresponding Old Price

One thing that I immediately realised: all 57 non-blank Review values were negative.

That was unusual. Since a review count cannot be negative realistically, I treated this as a likely systematic scraping or formatting issue rather than assuming the values represented something meaningful.

If only a few values had been negative, I would have been more cautious about changing them. But because every non-blank review value had the same issue, the pattern strongly suggested a consistent data-quality problem.

The original dataset as imported into the Raw_data sheet. The negative Review values and misspelled Ratingd header are visible.

Cleaning the Data and Why I Made These Decisions

Cleaning the dataset was one of the most important parts of the project. I didn't want to change values simply because they looked inconvenient. For each major cleaning step, I considered what the data was actually telling me and how the decision would affect the analysis.

1. Fixing the Negative Review Counts

There were 57 negative Review values and all non-blank review values had this issue.

I created a helper column and used the following formula:

=IF(E2="","",ABS(VALUE(E2)))

This converted the negative values into positive review counts while keeping genuinely blank cells blank.

After checking the results, I pasted the corrected values as values so that the cleaned numbers were no longer dependent on the formula.

2. Handling Missing Reviews and Ratings

Both Review and Rating contained 58 blank values.

I intentionally left these cells blank.

A blank Review means that the review count wasn't captured. A zero means that the product had zero reviews. These are two completely different situations.

Replacing the blanks with zero would therefore have affected averages, rankings and other calculations in ways that weren't supported by the original data.

3. Handling the Price Range

One product, a 1/2/3 Seater Elastic Sofa Cover, had its prices listed as ranges:

Current price: KSh 1,620–1,980
Old price: KSh 2,200–3,200

I had three options to choose from:

Use the lower value
Remove the row
Use the midpoint

I chose the midpoint.

Using the lower bound would ignore part of the information provided, while removing the row would mean losing a useful product just because its price was formatted differently.

The original range was retained elsewhere so that the decision could still be traced back to the source data.

There was also an interesting consequence of this decision. When I calculated the discount using the midpoint prices, I got 33%, while the seller's advertised discount was 38%.

Rather than replacing the advertised value, I kept both values. The difference is documented as a result of using an estimated midpoint rather than an exact price.

4. Removing Duplicates

The dataset contained three rows that were completely identical across all six original fields. I removed these as they were exact duplicates.

However, there were also six cases where the same product name appeared more than once but the prices, discounts or review counts were different.I kept these records.

Because Jumia is a marketplace with multiple sellers, the repeated product names could represent different seller listings rather than duplicate records. Removing them without evidence would have meant making an assumption that the data didn't support.

5. Fixing Text and Formatting Issues

I also made other formatting corrections:

• Removed "out of 5" from the Rating values
• Converted Rating into a numeric decimal
• Changed Ratingd to Rating
• Standardized old price to Old Price
• Converted prices from text into numeric currency values
• Formatted prices as KSh #,##0.00

The cleaned tblProducts table showing corrected data types, standardized fields and additional analytical columns.

All of these cleaning decisions were documented in the Data Dictionary sheet so that the process could be reviewed and traced back to the original data.

Adding Enrichment Fields

Once the basic cleaning was complete, I created additional fields to make the dataset more useful for analysis.

Discount Amount and Calculated Discount

I calculated the difference between the Old Price and Current Price to get the Discount Amount.I also calculated the corresponding percentage discount and kept it alongside the original advertised Discount.
This made it possible to compare the advertised discount with the discount calculated from the prices.

Rating Category

I grouped products into three rating categories:

Poor: < 3
Average: 3–4.5
Excellent: > 4.5

The original brief left ratings from 4.1 to 4.5 without a category. I therefore followed the suggested option of including this range under Average and documented this as a working assumption.

Discount Category

Discounts were grouped into:

Low: < 20%
Medium: 20–40%
High: > 40%

Price Category

Rather than guessing price cutoffs, I used the data itself to determine the categories.

I calculated the first and third quartiles of Current Price using

QUARTILE.INC()

The resulting thresholds were:

Price Q1: KSh 493
Price Q3: KSh 1,669.50

I then used these thresholds to classify products into Low, Medium and High price categories.

Engagement Strength

I also wanted a data-driven way to identify products with relatively high engagement.

I used the 75th percentile of Review count as the threshold:

Review Q3: 13 reviews

Products with 13 or more reviews were therefore classified as having Strong Engagement.

Data Status

Because the dataset contained many missing ratings and reviews, I added a Data Status field showing whether each product was:

• Complete
• Missing rating
• Missing review
• Missing both

This made it easier to understand the level of data coverage behind the analysis.

Combination Flags

Finally, I created four flags to identify products with interesting combinations:

• High discount + low rating
• High discount + low engagement
• Many reviews + average rating
• Strong engagement + excellent rating

Whenever one of the required values was missing, the formula returned "Missing" rather than making an assumption.

Example of an Excel formula used to classify products into Rating Categories.

PivotTables and Analysis Workflow

With the data cleaned and enriched, I moved on to the analysis.
One of the main things I wanted to investigate was whether price, discount, rating and review count were related.

I focused on three relationships:

• Discount vs. Reviews
• Rating vs. Reviews
• Current Price vs. Rating

Because Review and Rating contained missing values, I didn't want blank cells to interfere with the correlation analysis.

I therefore created helper ranges using FILTER() to return only rows where both variables in each pair were available. I then used these complete pairs for the correlation calculations and scatter plots.

The results were:

All three relationships were very weak.

For example, the correlation between Discount and Reviews was -0.111, which is very close to zero.

The R² values were also extremely small, meaning that each variable explained very little of the variation in the other variable.

Scatter plots for Discount vs. Reviews, Rating vs. Reviews, and Current Price vs. Rating, each with a linear trendline, equation and R² value.

Product Rankings

I also created PivotTables to identify:

• Top 10 products by Rating
• Bottom 10 products by Rating
• Top 10 products by Reviews
• Top 10 products by Discount

I also used the combination flags to identify products that met the four additional conditions.

For rating-based rankings, products with missing ratings were excluded.
Where products had the same rating, I used review count as the tie-breaker. For review-count ties, rating was used as the tie-breaker.

This made the ranking rules explicit rather than leaving the ordering to chance.

_PivotTables used to identify the highest and lowest-performing products across ratings and discounts.

Building the Interactive Dashboard

After completing the analysis, I brought the most useful pieces together into a single-screen dashboard.

I wanted the dashboard to answer the main questions quickly without requiring someone to scroll through multiple sheets.

The dashboard includes:

• KPI Cards
  • Total Products
  • Average Price
  • Average Discount
  • Average Rating
  • Total Reviews
• Product Ranking Charts
  • Top 10 by Rating
  • Top 10 by Reviews
  • Top 10 by Discount
• Relationship Analysis
  • Discount vs. Reviews
  • Rating vs. Reviews
  • Price vs. Rating

Each scatter plot includes a trendline, equation and R² value.

• Category Analysis
  • Rating Mix
  • Discount Mix
• Interactive Slicers The dashboard has slicers for:
  • Rating Category
  • Discount Category
  • Price Category

These slicers are connected to the compatible PivotTables and charts, allowing the dashboard to update when a user filters the data.

The completed single-screen dashboard combining KPIs, product rankings, relationship analysis, category charts and interactive slicers.

Key Findings

After cleaning and analyzing the data, a few findings stood out.

1. Bigger Discounts Didn't Necessarily Mean More Engagement

The correlation between Discount and Review count was -0.111.

That's really close to zero.

So in this dataset, products with larger discounts did not necessarily have more reviews.

2. Price and Rating Were Barely Related

The correlation between Current Price and Rating was 0.110.
This is also a very weak relationship.

In this dataset, highly rated products were not always more expensive or cheaper than products with lower ratings.

3. Some Heavily Discounted Products Still Had Weak Performance

Some products combined a high discount with either a low rating or low engagement.

These products caught my attention because offering a bigger discount may not solve the underlying problem.

They could instead be worth investigating from a listing-quality perspective, such as product descriptions, images, presentation or customer expectations.

4. Some Products Had Both Strong Engagement and Excellent Ratings

There were also 5 products that stood out for having both strong engagement and excellent ratings.They could provide useful examples of what appears to be working well.

Even though the dataset doesn't contain enough information to explain why these products perform well, they would be good choices for further investigation.

Business Recommendations

Based on the patterns in the data, I would recommend:

• Not to rely on discounts alone to drive engagement. Since discount and review count were almost not related, sellers could test other approaches such as improving product descriptions, images and overall listing quality.

• Investigating heavily discounted products with low ratings or engagement. Instead of immediately offering an even bigger discount, it may be better to first understand why the product isn't attracting positive customer feedback.

• Studying products with strong engagement and excellent ratings. These products could provide useful examples for understanding what works well across listings.

• Using data carefully when making decisions. The results show patterns in this dataset, but they don't prove that changing one variable will cause another to change.

Limitations

This project also has limitations like any other.

1. The biggest one is that the dataset doesn't contain sales, revenue, units sold or listing-age data. Because of this, review count is only a proxy for engagement and cannot be treated as a measure of sales performance.

2. There is also one product whose price had to be estimated using the midpoint of a range rather than an exact value.

3. The Rating Category assumption for ratings between 4.1 and 4.5. I grouped these under "Average" based on the suggested option in the project brief, but this was just a working assumption .

4. The correlations in this analysis describe relationships between variables. Correlation does not imply causation.

What I Learned

One of my biggest lessons from this project was that data analysis starts before the charts.

Initially, the dataset looked small and straightforward. But once I started auditing it, I found several issues that could have easily affected the final results if I had jumped straight into analysis.

The negative review counts were a good example. If I hadn't investigated that pattern, I could have ended up presenting misleading engagement figures.

I also learned the importance of documenting assumptions. For example, rather than silently changing the price-range row or filling missing values with zeros, I recorded what I did and why.

Most importantly, this project helped me see Excel as more than just a spreadsheet tool. I was able to use it for the full analytics process that is data auditing, cleaning, transformation, analysis, visualization and communication , all within one workbook.

And probably the biggest lesson was that a good dashboard isn't just about making something look nice. The real value comes from making sure that the numbers behind it are trustworthy and that the story you're telling is actually supported by the data.

I am honestly still in awe of how such a seemingly small dataset could yield so many other columns and useful insights after proper cleaning and analysis.
From just a few original columns, I was able to create additional fields, uncover patterns, build meaningful visualizations and turn the data into a dashboard that tells a story.

How I love being a data analyst!

Project Files

The complete project is available here:

GitHub Repository: Jumia_Product_Performance_Analysis
Dashboard Worksheet: Available in the repository under dashboard
Raw Dataset: Available under data