DEV Community

Jack Kisutsa
Jack Kisutsa

Posted on

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

#data #productivity #showdev

1. Project Introduction and Objective

In this project, I used Microsoft Excel and Power Query to clean and analyze a Jumia product dataset and then built an interactive dashboard to summarize pricing, discounts, ratings and customer engagement.

The main objective was to turn a small raw e-commerce dataset into useful business information. I wanted the final dashboard to answer practical questions such as:

  1. Do products with higher discounts receive more customer engagement?
  2. Do higher priced products have better ratings?
  3. Is there a relationship between product rating and number of reviews?
  4. Which products have the highest review engagement?
  5. Which products may require further investigation because they have high discounts but low ratings?

The project also gave me practical experience in data cleaning, excel formulas, PivotTables, PivotCharts, slicers, correlation analysis and dashboard design.

2. Dataset and Business Questions

The original dataset contained 115 rows and 6 columns:

  1. Product
  2. Current price
  3. Old price
  4. Discount
  5. Review
  6. Rating

The dataset was small but it contained several realistic data quality problems. This made it useful for me to practice the complete analytics process rather than going directly to visualization.

I structured the workbook into the following sheets:

Raw_Data
Cleaned_Data
Analysis
Pivot_Tables
Dashboard
Data_Dictionary

As we have always been taught in class,I kept the Raw_Data sheet unchanged so that I always have a copy of the original source data.

3. Initial Data-Quality Audit

Before cleaning the data, I profiled the dataset in Power Query
using Column Quality, Column Distribution and Column Profile.

The audit identified several issues:

Data-quality check Result
Original rows 115
Original columns 6
Blank Review values 58
Blank Rating values 58
Populated Review values stored as negative numbers 57
Current Price ranges 1
Old Price ranges 1
Exact duplicate rows removed 3
Discount values outside 0 to 100% 0
Rating values outside 0 to 5 after cleaning 0

One unusual issue was the Review field. All populated review counts were negative, even though a review count cannot logically be negative.
I therefore treated the negative sign as a data collection or scraping issue rather than a genuine business value.

The Rating field also required cleaning because values were stored as text such as:

4.5 out of 5
rather than as numeric ratings.

This audit was important because it prevented incorrect calculations later in the project.

4. Cleaning and Preparation Decisions

I performed the main cleaning steps in Power Query.

Product names

I applied Trim and Clean to remove unnecessary spaces and non-printable characters. Repeated product names were not automatically deleted because two rows with the same product name are not necessarily duplicates.

Current and old prices

The price fields contained KSh and commas so I removed the currency text and converted the fields to numeric values.

I notieced that two records contained price ranges rather than single prices:

Current Price: KSh 1,620 - KSh 1,980
Old Price: KSh 2,200 - KSh 3,200

I used the midpoint of each range:
Current Price midpoint = KSh 1,800
Old Price midpoint = KSh 2,700

This decision was documented in the Data Dictionary.

Discount

The Discount field was already interpreted correctly as a percentage.
The cleaned values ranged from 1% to 64%, so I did not divide the field by 100 or remove its percentage data type.

Reviews

The populated Review values were negative. I used Absolute Value in Power Query to convert them to valid positive counts.

The missing reviews remained blank. I deliberately did not convert missing reviews to zero because a blank means that the review information is unavailable, while zero would mean that the product definitely had no reviews.

Ratings

I renamed Ratingd to Rating, removed the text out of 5 and
converted the result to a decimal number.

The final valid ratings ranged from 2.0 to 5.0.

Duplicate records

I checked duplicates using the analytical fields:

  • Product
  • Current Price
  • Old Price
  • Discount
  • Review
  • Rating

Three redundant duplicate rows were removed, reducing the dataset from 115 to 112 products/records.

After cleaning:

  • 112 records remained.
  • 57 had complete Rating and Review information.
  • 55 were missing both Rating and Review.
  • There were 0 Power Query errors.

5. Excel Formulas and Enrichment Fields

After cleaning the source fields in Power Query, I used Excel formulas to create analytical categories and flags. The cleaned Excel table was named tblProducts.

Discount Amount

I calculated the difference between Old Price and Current Price:

=[@[Old Price]]-[@[Current Price]]
Enter fullscreen mode Exit fullscreen mode

Rating Category

I grouped ratings into three categories while keeping missing values separate:

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

The categories were:

  1. Poor: below 3
  2. Average: 3 to 4.5
  3. Excellent: above 4.5
  4. Missing: no rating available

Discount Category

I grouped discounts into Low, Medium, and High:

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

The thresholds were:

  1. Low Discount: below 20%
  2. Medium Discount: 20%--40%
  3. High Discount: above 40%

Price Category

I calculated the first and third quartiles of Current Price:

=QUARTILE.INC(tblProducts[Current Price],1)
Result: **KSh 493**
Enter fullscreen mode Exit fullscreen mode 
=QUARTILE.INC(tblProducts[Current Price],3)
Result: **KSh 1,669.50**
Enter fullscreen mode Exit fullscreen mode

I then categorized each product:

=IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price"))
Enter fullscreen mode Exit fullscreen mode

Engagement Flag

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

=QUARTILE.INC(tblProducts[Review],3)
Enter fullscreen mode Exit fullscreen mode

The result was 14 reviews.

The engagement formula was:

=IF([@Review]="","Missing",IF([@Review]>=Review_P75,"High Engagement","Low Engagement"))
Enter fullscreen mode Exit fullscreen mode

Therefore:

  • High Engagement: 14 reviews or more
  • Low Engagement: fewer than 14 reviews
  • Missing: no Review value

Performance Flags

I created three Yes/No flags:

High Rating + High Engagement

=IF(AND([@[Rating Category]]="Excellent",[@[Engagement Flag]]="High Engagement"),"Yes","No")
Enter fullscreen mode Exit fullscreen mode

High Discount + Low Rating

=IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Poor"),"Yes","No")
Enter fullscreen mode Exit fullscreen mode

High Discount + High Rating

=IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Excellent"),"Yes","No")
Enter fullscreen mode Exit fullscreen mode

These fields helped turn individual rows into business segments that could be summarized using PivotTables.

6. PivotTable and Analysis Workflow

I used both formulas and PivotTables for analysis.
The overall cleaned dataset had:

  1. 112 products
  2. Average Current Price: KSh 1,186.89
  3. Average Discount: 37%
  4. Average Rating: 3.89
  5. Average Reviews: 13
  6. Discount analysis

Discount Analysis

The Discount Category PivotTable produced:

Discount Category Products Average Discount Average Reviews
Low Discount 19 8% 10
Medium Discount 31 31% 15
High Discount 62 48% 11

Medium-discount products recorded the highest average review count.

Price analysis

Price Category Products Avg Current Price Avg Discount Avg Reviews Avg Rating
Low Price 28 KSh 284.75 48% 17 3.64
Medium Price 56 KSh 1,073.84 36% 10 3.88
High Price 28 KSh 2,315.14 27% 13 4.08

Low-priced products had the highest average review engagement while high-priced products had the highest average rating.

Rating and engagement

Among the 57 products with complete Review and Rating data:

Rating Category High Engagement Low Engagement Total
Poor 4 8 12
Average 6 20 26
Total 16 41 57

I also calculated the Pearson correlation between Rating and Review:

=CORREL(tblProducts[Rating],tblProducts[Review])
Enter fullscreen mode Exit fullscreen mode

The result was approximately 0.06, showing almost no linear relationship between rating and review count.

Performance flags

The analysis identified:

6 High Rating + High Engagement products
10 High Discount + Low Rating products
8 High Discount + High Rating products

Because these are independent flags, the counts should not simply be added together and interpreted as unique products.

7. Dashboard Design and Slicer Connections

After completing the analysis, I created a one-page dashboard designed to provide a quick summary of product performance.

The dashboard included five KPI cards:

  1. Total Products: 112
  2. Average Price: KSh 1,186.89
  3. Average Discount: 37%
  4. Average Rating: 3.89
  5. Total Customer Reviews: 723

I created nine charts:

  1. Top 10 Products by Ratings
  2. Top 10 Products by Review Count
  3. Top 10 Products by Discount Percentage
  4. Discount vs Reviews
  5. Rating vs Reviews
  6. Price vs Rating
  7. Rating mix
  8. Discount mix
  9. Engagement by Discount

The first two charts summarize category-level patterns. The scatter plotshows the weak relationship between ratings and reviews while the Top 10 chart identifies products with the strongest observed review
engagement.

Rating vs Review scatter plot

The scatter plot used Rating on the X-axis and Review Count on the Y-axis. I added a linear trendline and displayed the R-squared value.

The trendline is almost flat and the chart shows R² ≈ 0.0033, which supports the correlation result of approximately 0.06.

Top 10 products by review count

A horizontal bar chart was used because product names were long and easier to read in this format.

Review count was treated as an engagement indicator, not as evidence of sales.

Slicers

I added three slicers:

  1. Rating Category
  2. Price Category
  3. Discount Category

The slicers were connected to compatible PivotTables through Report Connections/PivotTable Connections. This allows a user to select a category and dynamically explore different product segments.

The scatter plot was created as a normal Excel XY chart rather than a PivotChart, so it remains an overall view and is not controlled directly by the PivotTable slicers.

Final dashboard

The dashboard was arranged so that the viewer sees the KPIs first, followed by the charts, slicers and business insights.

8. Key Findings

Finding 1: Medium discounts recorded the strongest average review engagement

Evidence: Medium-discount products averaged 15 reviews, compared with 11 for high-discount products and 10 for low-discount products.

Meaning: The deepest discounts were not associated with the highest review engagement in this dataset.

Action: Sellers could test medium and high discount bands and
compare engagement before assuming that deeper discounting produces better product response.

Caveat: Review count is an engagement proxy. The dataset does not include sales or listing age, so this relationship should not be interpreted as causal.

Finding 2: Average rating increased across price categories

Evidence: Average rating increased from 3.64 for low-priced products, to 3.88 for medium-priced products and 4.08 for high-priced products.

Meaning: Higher-priced products in this dataset tended to have stronger average customer ratings.

Action: Sellers could investigate the product quality, features,brands or listing characteristics associated with highly rated high-priced products.

Caveat: This is an association. The analysis does not prove that increasing a product's price would improve its rating.

Finding 3: Rating and review engagement had almost no linear relationship

Evidence: The correlation between Rating and Review was
approximately r = 0.06, with R² ≈ 0.0033.

Meaning: A highly rated product does not necessarily receive a high number of reviews.

Action: Sellers should consider both rating quality and review engagement when evaluating product performance rather than relying on rating alone.

Caveat: Only 57 products had complete Rating and Review
information and review count is not a direct measure of sales.

Finding 4: Performance flags identified both opportunities and risks

Evidence: The analysis found 6 high-rating/high-engagement products, 10 high-discount/low-rating products and 8 high-discount/high-rating products.

Meaning: Some products appear to be strong engagement candidates, while others may require investigation because heavy discounting is occurring alongside weak ratings.

Action: Sellers could investigate the 6 high-rating/high-engagement products for promotional opportunities and review quality or listing issues among the 10 high-discount/low-rating products before increasing promotional support.

Caveat: These flags are screening tools. The dataset does not
contain revenue, units sold, margin, advertising spend, or profitability data, so the flagged products cannot automatically be classified as commercial winners or failures.

9. Business Recommendations

Based on the completed analysis, I would recommend the following
actions:

  1. Test discount bands rather than automatically increasing discounts. Medium-discount products had higher average review engagement than high-discount products.
  2. Investigate highly rated, higher-priced products. Understanding what these products have in common may provide useful lessons for product positioning and listing quality.
  3. Use rating and engagement together. The weak correlation shows that a high rating alone does not guarantee strong review activity.
  4. Review high-discount/low-rating products. These products may require investigation into quality, expectations, listing content, or promotional strategy.
  5. Study high-rating/high-engagement products. These products can be investigated further as possible candidates for targeted visibility or promotional tests.

These recommendations are deliberately limited to what the dataset supports.

10. Limitations and Lessons Learned

Limitations
The dataset had substantial missing feedback data. Out of 112 cleaned records, only 57 had both Rating and Review information. This reduces the amount of data available for analyses involving customer feedback.

The project is also observational. Patterns such as higher ratings among high-priced products or stronger engagement among medium-discount products show association, not causation.

Lessons learned

This project reinforced several lessons for me.

First, data cleaning is part of analysis, not a separate optional step. If I had used the original negative Review values or text-based ratings directly, the final analysis would have been misleading.

Second, I learned the importance of documenting assumptions. Converting price ranges to midpoints, using 14 reviews as the high-engagement threshold and defining category boundaries all affect the results and therefore need to be transparent.

Third, you can use PivotTables to summarize data and also create an interactive dashboard. I also found out that PivotCharts rely on the PivotTables they get their data from. This means you can create separate PivotTables from the same data source for your charts. That way, a chart on your dashboard won't accidentally update a detailed analytical PivotTable.

Fourth, a dashboard should not contain every calculation. The Analysis and Pivot_Tables sheets can hold detailed work while the Dashboard should focus on the KPIs, charts, filters and insights that help a user understand the data quickly.

Finally, I learned to be careful with business language. A correlation does not prove causation, reviews do not equal sales and a flagged product is a starting point for investigation rather than a final business conclusion.

Conclusion

This project took a Jumia product dataset through the complete Excel analytics workflow: audit, cleaning, enrichment, analysis,visualization and business interpretation.

Starting with 115 raw records, I used Power Query to resolve price formatting, rating text, negative review values, missing information and duplicate records. The final cleaned dataset contained 112 records. I then used Excel formulas to create price, discount, rating, engagement and performance categories before summarizing the results through PivotTables and charts.

The final interactive dashboard provides a concise view of product pricing, discounts, ratings and customer engagement while still recognizing the limitations of the available data.

For me, the main lesson was that building a useful dashboard is not only about creating attractive charts. The quality of the final dashboard depends on the decisions made before visualization: how the data is cleaned, how metrics are defined, how assumptions are documented and how carefully the findings are interpreted.

Top comments (0)