E-commerce sites like Jumia gather enormous volumes of data every day, including product ratings, prices, discounts, and customer reviews. However, this data only takes on significance when it is sufficiently processed and scrutinized to reveal trends that decision-makers may take action upon, such as improving pricing strategy, honing promotions, or enhancing user interaction with the platform.
In order to investigate the relationship between pricing, discounts, and customer ratings and reviews, I looked at a dataset that included 115 raw Jumia product listings. My goal was to turn a raw dataset into a polished, interactive Excel display that leadership and management could use to inform actual business choices.
This article walks through the process used to turn the dataset into a clean, analysis-ready Excel Table, using structured references, data-driven thresholds, and error-aware formulas throughout.
1. Starting Point: Cleaning and Converting a Range into a Table
The first part was data cleaning by deleting all the duplicate values in the excel sheet then converted the cleaned raw data range into a proper Excel Table called tblProducts rather than working with plain cell ranges. I could reference columns by name instead of by letter e.g tblProducts [Current Price] or tblProducts![B2:B113].
Categorization Using IF Formulae
I wrote three IF-based formulas to flag rows worth a second look, each evaluated per row using the [@ColumnName] syntax:
- Discount check: I used this to rank the discounts:
=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount"))) - Price check: I added this because I wanted to catch cases where the current price was higher than the old price, which usually meant a data-entry reversal:
=IF(B2>D2,"Check prices","OK") - Missing rating check, combined with a rating-quality tier:
=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))
Top Rated and Least Rated Products
In order to get the least rated or the top rated products I used the conditional formatting for top or bottom 5 products:
Summarizing the Key KPIs
In order to get a summary of my data I used the formulas below to get: MAX, AVERAGE and COUNTS of different metrics.
=ROWS(tblProducts[Product]) ' total product count
=AVERAGE(tblProducts[Current Price]) ' average price
=AVERAGE(tblProducts[Discount]) ' average discount
=AVERAGE(tblProducts[Rating]) ' average rating
=SUM(tblProducts[Review]) ' total review volume
=MAX(tblProducts[Current Price]) ' most expensive item
=MIN(tblProducts[Current Price]) ' least expensive item
the results were as follows:
Graphs and Correlation
I used the graphs to show the relationship between different table parameters and their relationships e.g:
For the relationship questions, I calculated Pearson correlations directly against the Table and the results were as shown below:
=CORREL(tblproducts!G2:G113,tblproducts!H2:H113)
=CORREL(tblproducts!I2:I113,tblproducts!H2:H113)
=CORREL(tblproducts!B2:B113,tblproducts!I2:I113)
Pivot Tables
PivotTables did the heavier lifting for the rating mix, discount mix, price-vs-rating, and top-10 lists by rating, discount, and reviews each connected to slicers on Rating Category, Discount Category, and Price Category so the dashboard stays interactive rather than static.
Example:
Dashboard
I kept the dashboard to a single screen: title and slicers at top, a KPI strip (total products, average price, average discount, average rating, total reviews), three ranked top-10 panels, three scatter plots (discount-vs-reviews, rating-vs-reviews, price-vs-rating), and a mix/insights row at the bottom.
Conclusion
The discount-vs-review correlation of roughly -0.15 is close enough to zero that I can't call it a real pattern hence sellers shouldn't expect a bigger markdown to automatically generate more reviews. Moreover, Highly rated products receive slightly more customer reviews hence slight positive correlation. Finally, expensive products are rated higher than cheaper products hence slight positive correlation.
Top comments (0)