E-commerce data can look simple at first: product name, price, discount, reviews and rating. But turning a raw export into something a seller can actually use requires much more than making a few charts.
In this project, I used Excel 2010 and 2021 to clean, analyse and build a dashboard from a raw Jumia product export. The goal was to understand how discounts, prices and customer feedback relate to product performance.
The business question
The project brief asked four practical questions:
- Are higher discounts leading to higher customer engagement?
- Do highly rated products have higher or lower prices?
- Which products show strong performance or need pricing attention?
- What actions can sellers take from the evidence?
The original export contained 115 listings. After cleaning and removing exact duplicate rows, the final analytical dataset contained 112 records.
Figure 1: Raw export before cleaning.
Step 1: Cleaning the raw data
I kept the original data untouched and created a separate cleaned dataset.
The main cleaning tasks were:
- removing exact duplicate rows;
- converting price text into numeric values;
- handling price ranges by using their midpoint;
- converting negative review counts to positive counts;
- converting ratings such as
4.6 out of 5to numeric ratings; - retaining missing ratings as blank rather than treating them as zero; and
- recording the decisions in a cleaning log.
Three duplicate rows were removed, leaving 112 records.
Figure 2: Cleaned dataset.
Step 2: Enriching the dataset
I added four fields needed for the analysis.
Discount Amount
Discount Amount = Old Price - Current Price
Rating Category
The project brief left a gap between 4.0 and 4.5, so I explicitly added a Good category:
- below 3 → Poor
- 3 to 4 → Average
- above 4 to 4.5 → Good
- above 4.5 → Excellent
- blank → Not Rated
Discount Category
- below 20% → Low
- 20% to 40% → Medium
- above 40% → High
Price Category
I used current-price quartiles:
- Low: up to KSh 493
- Medium: above KSh 493 to KSh 1,669.50
- High: above KSh 1,669.50
Figure 3: Example formulas and enrichment fields.
Step 3: Descriptive statistics
The cleaned dataset contains:
| Metric | Result |
|---|---|
| Products | 112 |
| Rated products | 57 |
| Unrated products | 55 |
| Total reviews | 723 |
| Average current price | KSh 1,186.89 |
| Average old price | KSh 1,811.11 |
| Average discount | about 36.8% |
| Average rating | 3.89 / 5 |
The current price ranged from KSh 38 to KSh 3,750.
Step 4: PivotTables and product rankings
PivotTables were used to compare categories and create ranking tables without changing the cleaned source data.
Figure 4: PivotTables used for category analysis and rankings.
The Top 10 by Reviews were led by the 120W Cordless Vacuum Cleaner with 69 reviews, followed by the 137 Pieces Cake Decorating Tool Set with 55 and the Electronic Digital Display Vernier Caliper with 49.
The Top 10 by Rating contained seven products rated 5.0 and three rated 4.8.
Step 5: Testing the relationships
Three scatter plots were used to test the numeric relationships:
- Discount % vs Reviews
- Rating vs Reviews
- Current Price vs Rating
Figure 5: Relationship analysis.
Discount vs Reviews
The correlation was 0.0122 with R² of 0.00015.
That means there was essentially no linear relationship between discount percentage and review count in this sample.
Rating vs Reviews
The correlation was 0.0572 with R² of 0.0033.
This is a very weak positive relationship.
Current Price vs Rating
The correlation was 0.1101 with R² of 0.0121.
The relationship is weak, although the category-level averages showed that high-price products had a higher average rating than low-price products (4.08 vs 3.64).
Figure 6: Category PivotCharts used in the dashboard.
Step 6: Outliers and exception groups
I also looked at combinations that could be more useful for sellers than a single metric.
High discount + poor rating
There were 10 products in this group.
For example:
- 120W Cordless Vacuum Cleaner — 49% discount, 69 reviews, 2.8 rating
- Intelligent LED Body Sensor Wireless Lighting Night Light USB — 52%, 15 reviews, 2.7
- 5-PCS Stainless Steel Cooking Pot Set — 55%, 13 reviews, 2.1
High discount + little engagement
There were 32 products with a High Discount and zero reviews.
Strong customer demand
Using the upper quartile of reviews (7) as the threshold, 32 products had 7 or more reviews.
Strong demand + average rating
Five products had 7 or more reviews but only an Average rating. This is an important group because engagement was present, but customer satisfaction was not in the top categories.
Step 7: Building the dashboard
The final dashboard was designed as a visual canvas rather than as another analysis sheet.
It contains:
- five KPI cards;
- three Top-10 summary tables;
- three relationship scatter plots;
- three category PivotCharts; and
- slicers for Rating Category, Discount Category and Price Category.
I kept the underlying PivotTables and analysis on separate sheets and used the dashboard for presentation.
Figure 7: Final Jumia Product Performance Dashboard.
What did the data tell us?
Are higher discounts leading to higher engagement?
Not in this sample. The correlation between discount and reviews was only 0.0122. In fact, the Medium discount group had the highest average review count at 10.97, compared with 5.39 for High discounts.
This means a bigger discount should not automatically be assumed to produce more customer engagement.
Do highly rated products have higher or lower prices?
There was a weak positive relationship between price and rating (r = 0.1101). High-price products averaged 4.08 compared with 3.64 for low-price products, but the relationship was weak at the individual-product level.
Which products need the most attention?
The strongest attention signals were the combinations rather than any single ranking:
- 10 products had both High Discount and Poor Rating.
- 32 had High Discount and zero reviews.
- 2 had High Price and Poor Rating.
Three practical recommendations
1. Test discount levels instead of assuming deeper discounts are better
The relationship between discount percentage and reviews was essentially zero in this sample.
Use controlled pricing tests and compare engagement outcomes rather than automatically increasing discounts.
2. Investigate product experience before increasing promotions on poorly rated products
A large discount does not solve a product-quality or customer-experience problem. Products with high discounts and poor ratings should be reviewed for product quality, specification accuracy, images, fulfilment and customer feedback before further promotion.
3. Use price, rating and reviews together
Price alone was only weakly associated with rating. Sellers should evaluate the full picture: price position, review volume, rating, customer comments and the product's value proposition.
A few lessons from the project
The biggest lesson was that dashboard building is not the same as analysis.
The analysis sheets answer the questions. The dashboard should make the answers easy to understand.
Final takeaway
The most important result was not a single product or a single discount percentage. It was the lack of a simple relationship between discount and reviews, combined with the presence of several products where high discounts coexisted with poor ratings or no engagement.
For sellers, that suggests a more targeted approach: understand customer response, monitor product quality and use pricing as one part of the strategy rather than treating discount depth as a universal solution.
Top comments (0)