DEV Community

Janet Kangogo
Janet Kangogo

Posted on

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

#analysis #data #productivity

Introduction

For this project, I used Microsoft Excel to analyze a Jumia product dataset and build an interactive dashboard.

The aim was to understand how product prices, discounts, ratings, and customer reviews relate to product performance.

I focused on questions such as:

  • Do higher discounts lead to more customer reviews?
  • Do highly rated products receive more engagement?
  • Are more expensive products rated better?
  • Which products are performing best?
  • Which products may need a different pricing or marketing approach?

Since the dataset did not include sales or revenue, I used the number of reviews as a way of measuring customer engagement.

Step 1: Understanding the Dataset

The original dataset had six columns:

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

Before doing any analysis, I first checked the data to see what needed cleaning.

Some of the issues I found were:

  • 115 rows and 6 columns
  • 58 blank Review values
  • 58 blank Rating values
  • 57 negative Review values
  • 3 duplicate rows
  • 1 product with a price range
  • a misspelled Ratingd column
  • prices containing KSh and commas
  • ratings written like 4.5 out of 5

I kept the original data unchanged so that I could always compare it with the cleaned version.

Step 2: Creating a Data Quality Audit

Before cleaning the data, I recorded the issues I found in a Data_Dictionary sheet.

I also kept a cleaning log showing what I changed and why.

Step 3: Cleaning the Data

I created a separate cleaned version of the dataset and kept the raw data unchanged.

Cleaning Product Names

I used the TRIM function to remove extra spaces from product names.

=TRIM(A2:A113)

Fixing the Headers

I renamed the columns to:

Product | Current Price | Old Price | Discount | Review | Rating

Removing Duplicates

I selected all six original columns so that only exact duplicates were removed.

After this step, I had: 112 product records

Cleaning the Prices

Some prices were written like: KSh 2,199

I used Ctrl + H (Find and Replace) to remove KSh and commas, turning values like KSh 2,199 into 2199 so Excel could treat them as numbers.

Handling the Price Range

One product had a current price of:

1620 - 1980

and an old price of:

2200 - 3200

Instead of deleting the row, I used the midpoint.

Current Price:

(1620 + 1980) / 2 = 1800

Old Price:

(2200 + 3200) / 2 = 2700

Cleaning Reviews

The review values appeared as negative numbers.

Since a review count cannot be negative, I treated the negative sign as a formatting issue and converted the values to positive numbers.

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

I left missing review values blank.

Cleaning Ratings

Ratings were written like: 4.5 out of 5

I converted them into normal numbers using: =IF(F2="","",VALUE(SUBSTITUTE(F2," out of 5","")))

Missing ratings were also left blank.

Step 4: Validating the Cleaned Data

Before moving on to analysis, I checked that the cleaned values made sense.

For Rating: =IF(OR(F2<0,F2>5),"Check rating","OK")

For Discount: =IF(OR(D2<0,D2>1),"Check discount","OK")

For Price: =IF(B2>C2,"Check prices","OK")

After that, I converted the cleaned dataset into an Excel Table using Ctrl + T and named it:

tblProducts

Step 5: Adding New Columns

I added a few extra columns to make the analysis easier.

Discount Amount

I calculated how much money had actually been reduced from the old price.

=[@[Old Price]]-[@[Current Price]]

Rating Category

I grouped the ratings into:

  • Poor - below 3
  • Average - 3 to 4.5
  • Excellent - above 4.5
  • Missing - blank rating

Formula:

=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

Discount Category

I grouped the discounts into:

  • Low Discount - below 20%
  • Medium Discount - 20% to 40%
  • High Discount - above 40%

Formula:

=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

Price Category

Instead of choosing random price ranges, I used quartiles.

First quartile:

=QUARTILE.INC(tblProducts[Current Price],1)

Result: KSh 493

Third quartile:

=QUARTILE.INC(tblProducts[Current Price],3)

Result: KSh 1,669.50

I then grouped products into:

  • Low Price
  • Medium Price
  • High Price

Formula:

=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=[@[Price_Q1]],"Low Price",IF([@[Current Price]]<=[@[Price_Q3]],"Medium Price","High Price")))

Step 6: Measuring Customer Engagement

Because the dataset did not include sales, I used reviews as an engagement measure.

I used the 75th percentile of Review to define strong engagement.

=QUARTILE.INC(tblProducts[Review],3)

The result was: 14 reviews

So I treated products with 14 or more reviews as having strong engagement buy using the formula below

=IF([@Review]="","Missing",IF([@Review]>=Engagement_Threshold,"Strong Engagement","Lower Engagement"))

Step 7: Descriptive Analysis

I created an Analysis sheet and calculated the main KPIs.

Total Products

=ROWS(tblProducts[Product])

Result: 112

Average Current Price

=AVERAGE(tblProducts[Current Price])

Result: KSh 1,186.89

Average Old Price

=AVERAGE(tblProducts[Old Price])

Result: KSh 1,811.11

Average Discount

=AVERAGE(tblProducts[Discount])

Result: 36.78% when converted to percentage

Average Rating

=AVERAGE(tblProducts[Rating])

Result: 3.889473684

Total Reviews

=SUM(tblProducts[Review])

Result: 723

The most expensive product price

=MAX(tblProducts[Current Price])

with its name found as

=XLOOKUP(MAX(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Result: KSh 3,750

The least expensive product price
=MIN(tblProducts[Current Price]) with its name =XLOOKUP(MIN(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Result: KSh 38

Step 8: Looking at Relationships

I created three scatter plots to see whether the main variables were related.

Discount vs Reviews

The correlation was approximately: -0.136822724

This showed a very weak negative relationship.

In simple terms, larger discounts were not clearly linked to more reviews.

Rating vs Reviews

The correlation was approximately: 0.057209035

This was very close to zero, meaning highly rated products did not necessarily receive more reviews.

Price vs Rating

The correlation was approximately: 0.110090213

This showed only a very weak positive relationship between price and rating.

For each chart, I added a trendline, equation, and R-squared value.

Note that correlation does not mean causation.

Step 9: Ranking Products

I used PivotTables to compare and rank products.

I created:

  • Top 5 products by rating
  • Bottom 5 products by rating
  • Top 10 products by rating
  • Top 10 products by reviews
  • Top 10 products by discount
  • High-discount products with low ratings
  • High-discount products with low engagement
  • Products with many reviews but average ratings

Sample shown below

Step 10: Creating PivotCharts

I created PivotTables for:

  • Rating Category
  • Discount Category
  • Price Category
  • Average Rating by Price Category
  • Average Reviews by Discount Category
  • Top Products by Rating
  • Top Products by Reviews
  • Top Products by Discount

I then created PivotCharts from these tables.

For the product rankings, I used horizontal bar charts because the product names were long and easier to read that way.

Step 11: Adding Slicers

To make the dashboard interactive, I added slicers for:

  • Rating Category
  • Discount Category
  • Price Category

I connected the slicers to the relevant PivotTables using Report Connections.

This made it possible to filter several charts at once.

Step 12: Building the Dashboard

I brought the most important information together on one dashboard.

The top section included:

  • Total Products
  • Average Price
  • Average Discount
  • Average Rating
  • Total Reviews

The dashboard also included:

  • Top 10 Products by Rating
  • Top 10 Products by Reviews
  • Top 10 Products by Discount
  • Discount vs Reviews
  • Rating vs Reviews
  • Price vs Rating
  • Rating Category breakdown
  • Discount Category breakdown

I used consistent formatting for prices, percentages, ratings, and review counts so that the dashboard was easy to read.

Key Findings

Higher discounts did not automatically mean more engagement

The correlation between Discount and Review was about: -0.14

This was a very weak relationship.

Medium-discount products actually had a higher average number of reviews than high-discount products.

More reviews did not always mean better ratings

The relationship between Rating and Review was almost zero.

The 120W cordless vacuum cleaner had:

  • 69 reviews
  • 2.8 rating

This showed that a product can attract attention but still have weak customer satisfaction.

Expensive products were not much better rated

The relationship between Current Price and Rating was only about: 0.11

This means price alone was not a strong sign of customer satisfaction.

Large discounts did not guarantee strong performance

Some products had large discounts but still had low ratings or low engagement.

This suggests that sellers should not depend on discounting alone.

Recommendations

Based on the analysis:

  • Sellers should not assume that bigger discounts automatically lead to more customer engagement.
  • Products with many reviews but low or average ratings should be investigated for possible quality or customer-experience issues.
  • Products with strong ratings but low review counts may benefit from more promotion.
  • High-discount products with low engagement may need a different marketing or pricing strategy.
  • Product performance should be judged using several measures together instead of looking at only price, discount, rating, or reviews.

Limitations

The dataset did not include:

  • sales
  • revenue
  • listing age
  • seller information
  • product visibility

Because of this, reviews were only used as an engagement proxy.

The results show patterns in the data, but they do not prove that one variable directly caused another.

Conclusion

This project gave me practical experience turning raw Jumia product data into an interactive Excel dashboard.

I started by checking and cleaning the data, then added new fields, calculated KPIs, analyzed relationships, created PivotTables and PivotCharts, and finally brought everything together in a dashboard.

It showed me how Excel can be used not just for calculations, but also for understanding data and turning it into useful business insights.

Top comments (0)