Introduction
When shopping onine, I usually find myself looking at two things before making a purchase: Product ratings and reviews since I cannot examine the product physically.Products with high ratings and reviews tend to make the product trustworthy.This motivated me to explore how these factors using a sample dataset from Jumia.
I analyzed a dataset of products listed to investigate whether pricing, discounts, ratings and review counts directly influence one another. By transforming this raw e-commerce data into an interactive Excel dashboard this project uncovers how strategic pricing directly impacts customer engagement.
Data Inspection
Before data cleaning, I inspected all the data to find missing values, duplicates, inconsistent formats, and values that could affect the accuracy of the analysis.
Data Cleaning
Before beginning the analysis, I cleaned and standardized the dataset to ensure that the values were accurate, consistent, and suitable for analysis in Excel. The main cleaning steps involved correcting data formats, handling missing values, and identifying duplicate records.
Correcting Data Formats
I first reviewed each column and converted the values into appropriate data types.
-
Prices- The price columns were initially stored as text because they included currency symbols (KSh), commas, and in some cases, price ranges such as
KSh 1,620 - KSh 1,980. I used Find and Replace (Ctrl + H) to remove the KSh text and other unnecessary characters then converted the values to numerical format. For products with price ranges, I calculated the average of the minimum and maximum prices and used this value for further analysis. I then recalculated the discount percentages based on the standardized prices. -
Ratings- Ratings were stored as text in formats such as 4 out of 5. I used Find and Replace (Ctrl + H) to remove out of 5 and converted the remaining values into numerical ratings.These were also formated as texts i.e
4 out of 5. - Review- All the reviews counts were recorded as negative values. Since review counts represent the number of customer reviews and should be non-negative integers, I converted these values to their absolute values to ensure consistency.
- Discounts- Discount values were standardized and formatted as percentages, allowing them to be compared consistently across products.
Handling Missing Values- The missing values were mostly from the Ratings and Reviews Columns which I replaced using
CTRL+HwithNulls.Duplicates- To handle potential duplicates, I applied Conditional Formatting on the product column to highlight duplicate product names. I then manually cross-examined all columns across the flagged records to verify true duplicates:
-Three True Duplicates: Confirmed as identical records sharing exact matching product names, old prices, current prices, and discount percentages. These were permanently removed to prevent dataset skew.
-Three Distinct Items: Identified varied product configurations, as they exhibited different pricing structures and discount levels despite sharing similar names. These were retained in the dataset.
Data Enrichment
To enhance data analysis, I created four key derived columns in the dataset.
Rating Category.
- </=3 – Poor
- >3 – 4.5 – Average
- >4.5 – 5 – Excellent
- Null – Not Provided
Discount Category
- </=20% – Low Discount
- >20% – 40% Medium Discount
- >40% – High Discount
Review Category.
- 1–23 – Low
- 24–46 – Medium
- >46 – High
- Null – Not Provided
Pricing Category.
- </= KSh 1,000 – Low Price
- >KSh 1,000 – KSh 2,000 Medium Price
- >KSh 2,000 – High Price
Absolute Discount
=D3-B3 = Old Price -Currect Price
I used the following IF functions to come up with the threshholds:
-Reviews
=IF(H2="null","Null",IF(H2>46,"Excellent",IF(H2>23,"Average","Poor")))
-Ratings
==IF(K2="null","Null",IF(K2>4.5,"Excellent",IF(K2>3,"Average","Poor")))
-Price Category
=IF(B2>2000,"High Price",IF(B2>1000,"Medium Price","Low Price"))
-Discount
=IF(F2>40%,"High Discount",IF(F2>20%,"Medium Discount","Low Discount"))
Data Analysis
Using Pearson's Correlation Coefficient (r), I tested three core hypotheses regarding how pricing, discounts, ratings and reviews influence one another:
Key Insights
-As shown above,Pearson correlation analysis of the discount and reviews (r = -0.131), confirming that offering deeper price cuts does not lead to higher customer engagement.
-The correlation between rating and reviews is very weak (r = +0.057) because product rating has almost no impact on how many reviews a listing receives.This happens because Poor products and Moderate products generate almost the exact same average engagement.
-The correlation between price and product ratings is very weak positive (r = +0.110) because price increases do not reliably drive higher customer ratings.
Dashboard Analysis
I did the final analysis using a dashboard that had the following KPIs:
Total Products -112 products
Average Current Price -KSh 1,187
Average Discount-37%
Average Rating -4/5
Total Reviews -723 reviews
The Final Dashboard :
Key Recommendations
Rethink the use of discounts to drive customer engagement up. Since the relationship between discounts and reviews was weak (r = -0.131), sellers should test different discount levels rather than assuming that larger discounts will generate more reviews.
Capitalize on "Medium Price" products. Price and rating show a weak positive correlation (r = +0.110), with ratings rising in the Medium Price tier (4.1 rating) while High (3.88) and Low (3.78) lag behind.
Improve Products with High Engagement but Poor Ratings
The analysis identified products that received a high number of reviews despite having poor ratings. For example, the 120W Cordless Vacuum Cleaner received 69 reviews but had a rating of 2.8. This suggests strong customer engagement but potentially low customer satisfaction.
Conclusion
This analysis provided insights into how ratings, reviews, prices, and discounts relate to product performance on Jumia. I initially assumed that larger discounts would lead to more reviews and stronger customer engagement. However, the weak relationship between discounts and reviews challenged this assumption. The analysis also showed that higher-priced products were not necessarily rated more highly, while products with many reviews were not always highly rated. These findings highlight the importance of considering product quality, customer satisfaction, and overall value, rather than relying on price, discounts or reviews alone.
Top comments (1)
wow nice one