Introduction.
Data is an important part of modern e-commerce because it helps businesses understand their products, customers, pricing strategies, and overall performance.
For this project, I analyzed a dataset of Jumia products using Microsoft Excel. The goal was to transform raw product data into useful information and present the results through an interactive Excel dashboard.
The project involved several stages, including data cleaning, data transformation, analysis, PivotTables, charts, and dashboard development.
The final dashboard was designed to provide an overview of product pricing, discounts, customer reviews, and ratings while helping identify products that performed well and products that may require further attention.
Objective.
The objective of this Project was:
- Clean and prepare the raw Jumia product dataset.
- Convert incorrectly formatted values into usable numerical data.
- Analyze product prices, discounts, reviews, and ratings.
- Identify relationships between discounts, reviews, prices, and ratings.
- Categorize products based on their ratings and prices.
- Identify top-performing and poorly performing products.
- Build an interactive dashboard to communicate the findings.
- Provide practical recommendations for Jumia sellers.
Dataset Description.
The Dataset used contained products listed on Jumia.
It contained Columns which include, Products, Current Price, Old Price, Discount, Reviews and Rating.
The original dataset contained several data-quality issues that needed to be addressed before performing the analysis. Below is the Raw Data worksheet, which contained several data-quality issues.
Below is the image of cleaned data.
To clean the Data i foloowed:
Cleaning Prices.
One of the issues i encountered was that prices were not always stored as numbers.
Example;
- KSh 1,525
- KSh 950
These values contain text characters, which can prevent Excel from performing mathematical calculations correctly.
The Formula used to remove the currency text and Commas was:
=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh ",""),",",""))
This Converts a value such as:
KSh 1525 to 1525.
The same Process was applied to the old Price.
This was important because numerical prices were required for calculations such as averages, comparisons, and price categorization.
Handling Price Ranges.
Some products contained price ranges rather than a single price.
Example.
Instead of simply deleting these records, I used a documented approach for analysis.
The midpoint of the range can be calculated as:
=AVERAGE(lower_price,upper_price)
The end results was: KSh 1800
Cleaning Rating.
Some Rating were Presented as Text rather than Numbers.
For analysis, ratings needed to be numerical so that I could calculate average ratings and compare products.
After cleaning, ratings were represented on a scale from:
0 to 5.
Handling Reviews.
The Review column also required checking for invalid values.
I specifically checked for negative review counts because a product cannot logically have a negative number of customer reviews.
The fomula used to identify negative values was:
=COUNTIF(E2:E1000,"<0")
After this, the formula below was used to remove the negatives.
=ABS(E2)
After this the Results were:
Creating Rating Categories.
To make the Analysis easier to understand, i created a new column called:
Rating Category:
Categories were,
- Poor
- Average
Excellent
I used the following classification:Below 3 → Poor
3 to 4.5 → Average
Above 4.5 → Excellent
The formula used to find this was,
=IF(F2<3,"Poor",IF(F2<=4.5,"Average","Excellent"))
Creating Price Category.
I also created a Price Category column.
Since the dataset contained both current and old prices, I used the Current Price for categorization because it represents the price customers are currently paying.
The categories were:
- Low Price
- Medium Price
High Price
For example, I classified:KSh 500 or below → Low Price
KSh 501–1,500 → Medium Price
Above KSh 1,500 → High Price.
The formula used was:
=IF(B2<=500,"Low Price",IF(B2<=1500,"Medium Price","High Price"))
Creating Discount Categories.
I also created categories for discounts:
- Low Discount
- Medium Discount
- High Discount
For example:
- 0–20% → Low Discount
- 21–40% → Medium Discount
- Above 40% → High Discount The formula used was:
=IF(D2<=20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))
Excel Analysis.
After cleaning the data, I used Excel formulas, sorting, filtering, PivotTables, and charts to analyze the dataset.
Some of the key analyses included:
- Total Number of Products.
- Average product price
- Average rating
- Average discount
- Total customer reviews
Rating Distribution.
Discount Distripution.
Price Category Distribution.
Top Rated Products.
PivotTables
PivotTables were an important part of the analysis because they allowed me to summarize large amounts of data quickly.
For example, to analyze rating categories, I used:
Rows: Rating Category
Values: Count of Product
This showed how many products belonged to each rating category.
I used PivotTables to Analyze:
Average Reviews by Rating category.
Average Rating by Price category.
Products with highest number of reviews.
Highest Rated Products.
Trend Analysis.
Discount and Customer Reviews
One of the questions I wanted to answer was:
Does a higher discount result in more customer reviews?
I compared discount percentages with customer review counts.
A PivotTable was used to summarize review activity at different discount levels, followed by a visualization to make the relationship easier to interpret.
There was no direct relation between Discount and Reviews.
Rating and Customer Reviews.
No relation.
Price and Rating.
No relation.
Dashboard Development.
After completing the analysis, I created an interactive Excel dashboard to present the findings.
The dashboard was designed to provide a quick overview of the dataset without requiring the user to examine every row of the original data.
The dashboard included KPI cards, charts, category breakdowns, and business insights.
Dashboard Visualizations
The dashboard included visualizations for the major relationships identified during the analysis.
Discount vs Customer Reviews
This visualization helps determine whether products with larger discounts receive more customer engagement.
Rating vs Customer Reviews
This visualization compares customer review activity across rating categories.
Price vs Rating
This visualization compares average ratings across low-, medium-, and high-priced products.
Rating Category Distribution
This chart shows the proportion or number of products classified as:
- Poor
- Average
- Excellent
Discount Category Distribution
This chart shows the distribution of products across:
- Low Discount
- Medium Discount
- High Discount
Recomendations for Jumia Sellers.
Based on the analysis, I would recommend the following strategies to sellers.
1. Use discounts strategically
Sellers should not assume that larger discounts will automatically produce higher customer engagement. Discount effectiveness should be monitored using actual review and sales-related indicators.
2. Investigate highly reviewed but poorly rated products
Products with many reviews but average or low ratings may have strong visibility but potential customer satisfaction problems.
Sellers should investigate product quality and customer feedback.
3. Review expensive products with low engagement
Products with high prices and few reviews may require more competitive pricing, stronger marketing, or better product positioning.
4. Promote strong performers
Products with both high ratings and high review counts should be given greater visibility because they demonstrate strong customer engagement and satisfaction.
5. Monitor pricing and customer response
Sellers should regularly compare prices, discounts, ratings, and reviews rather than relying on a single metric.
Conclusion.
This project showed how Excel can transform raw Jumia e-commerce data into useful business insights. I cleaned and analyzed the data, created PivotTables and charts, and built an interactive dashboard to communicate the findings and support better business decisions.
My github link, https://github.com/kipronobeniel-sketch/Excel_Clean_Up
Top comments (0)