Introduction
Data analysis is crucial for informed decision-making in the e-commerce sector. Jumia is one of the leading e-commerce platforms in Africa. The multi-national corporation operates an online marketplace that links sellers with buyers. Jumia features an enormous range of items with diverse prices and discounts. Usually, customers browse Jumia’s website and make online purchases by adding items of choice to a virtual card and then making payments through a safe digital checkout. Additionally, the website allows buyers to give quantitative scores (ratings) and qualitative feedback (reviews) about products purchased. In this project, the goal was to use Microsoft Excel to analyze a dataset comprising items listed on Jumia with the aim of understanding how pricing, discounts, and customer engagement affect product performance. Ultimately, insights from the analysis would help answer critical business questions such as:
Are higher discounts leading to higher customer engagement?
Do highly rated products have higher or lower prices?
Which products are performing best based on reviews and ratings?
Which products may need improved pricing strategies?
-
What recommendations would you give to Jumia sellers?
The entire process was step-wise and involved data cleaning and preparation, data enrichment, analysis, and presentation.Data Cleaning
The original Jumia dataset contained a host of errors, including duplicates, unnecessary characters, and erroneous data types. As such it was important to first clean the data before proceeding to the analysis stage.
Removal of Duplicates
Removal of duplicates entails deletion of entries that are an exact match in all columns of the table. To do so, I highlighted the entire table, navigated to the data section of the ribbon and then selected “Remove duplicates”. A total of 3 duplicate values was deleted leaving behind 112 unique values.
Removal of Unnecessary Characters
A quick glance at the data showed that it contained unnecessary characters such as the letter “d” in rating, which was misspelt as “Ratingd”, the (-) sign in the review column, “out of 5” in the rating column, and “KSh” prefix in the current price and old price columns. To begin with, I corrected the misspelt column D header by simply deleting the extra “d”. Next, I replaced the “out of 5” phrase with blank using the Find and Replace function.
In total, 57 replacements were made.
The same approach was used to clean the old price and current price columns whereby “KSh” prefixes were replaced with blanks.
The next phase of data cleaning entailed selecting the correct data types for various columns. Notably, all column data types were set to “General”. To rectify this error, I highlighted individual columns and set the values to the right data types. For the rating column, I set the data type to “number” and selected “One Decimal Place” for uniformity of figures. I also set the review column to “number” with zero decimal places. Because the discount was a percentage figure, I set the data type for that column to “Percentage” with zero decimal places. As for the current price and old price columns, I set the data type to currency and selected “Zero Decimal Places”. While cleaning the price columns, I also noticed the prices of one item in row 40 was set as a range instead of a single figure. To correct this error, I calculated the average for the range provided in the ‘old price’ column and set it as the ‘old price’. I also calculated the average for the range provided in the ‘new price’ column and set it as the ‘new price’. I also noticed that several rows in the rating and review columns were left blank. Because replacing the blanks with median or average figures would lead to inaccurate computation of average product rating and total reviews, I opted to leave the blanks unfilled.
Data Enrichment
To improve my analysis, it was crucial to create additional columns using formulas listed below:
Discount Amount Column
Discount Amount = Old Price – Current Price
Discount Category Column
Discount Category = IF(E2<20%,"Low Discount",IF(E2<=40%,"Medium Discount","High Discount"))
Rating Category Column
Rating Category = IF(ISBLANK(I38),"Not Provided",IF(I2<3,"Poor",IF(I2<=4.5,"Average","Excellent")))
Price Category Column
Prior to inserting the ‘Price Category’ column I computed the first quartile as 493 and third quartile as 1670. I then used these values as my conditions for the IF function below:
Price Category = IF(B2<=493,"Low Price",IF(B2<=1670,"Medium Price","High Price"))
The results of data cleaning, enrichment, and preparation was the data below:
Data Analysis
Descriptive Analysis
This stage of data analysis entailed calculating averages, sums, maximum, and minimum values. Here, I used pivot tables. Creating the pivot tables was done by clicking any cell>>insert>>pivot tables>>New page. The pivot tables helped me perform analysis and obtain the results below:
Average current price of products = KSh. 1186
Average old price of products = Ksh. 1811
Average discount percentage = 37%
Average product rating = 3.9
Total number of products = 112
Total number of reviews = 723
Most Expensive Product = 32PCS Portable Cordless Drill Set with Cyclic Battery Drive -26 Variable Speed costing KSh. 3750
Least Expensive Product = 3PCS Single Head Knitting Crochet Sweater Needle Set costing KSh. 38
Trend Analysis
I used scatter plots to find relationships between different variables and answer the questions below:
Does a higher discount percentage result in more customer reviews?
Do highly rated products receive more customer reviews?
Are expensive products rated higher than cheaper products?
Product Performance Analysis
To perform product performance analysis, I used pivot tables with filters. The tables helped me determine:
Top 5 products with the highest ratings
Top 5 products with the lowest ratings
Top 10 products with the highest discounts
Top 10 products with the highest number of reviews.
Top 10 products with the highest number of reviews.
Top 10 highest-rated products.
Products with high discounts but low ratings.
Products with strong customer engagement.
To determine products with strong customer engagement, I used both reviews and ratings.
Seller Performance Analysis
Products that appear to have strong customer demand
Products with high rating and a high number of reviews qualify to be classed as having strong customer demand. The products include:
Products that may require better pricing or marketing strategies
Products with high discount and high rating could be underpriced. The products include:
The pricing flaw can be potentially resolved by reviewing pertinent pricing strategy.
Products receiving many reviews but having average ratings
Products with many reviews but average ratings include:
Products with high discounts but low customer engagement
Products with high discount but low rating and low number of reviews qualify to be classed as having high discount but low customer engagement. These include:
Dashboard Design
The dashboard contained:
- KPIs
- Product Performance information
- Trend Analysis
Business Insights
The analysis showed that:
- There was no clear relationship between discounts and customer reviews.
- There was no clear relationship between product rating and pricing.
- Best performing best based on reviews and ratings are:
- Products that may need improved pricing strategies
There exist products with very high discounts but very low ratings. These include:
- Wall-mounted Sticker Punch-free Plug Fixer
- 5-PCS Stainless Steel Cooking Pot Set with Steamed Slices
- Electric LED UV Mosquito Killer Lamp, Outdoor/Indoor Fly Killer Trap Light -USB For such products, lowering their prices further will not impact their appeal to customers. Here, the issue seems to be unsatisfactory quality.
- On the other hand, we have products with high ratings but very low reviews. These include:
- Anti-Skid Absorbent Insulation Coaster for Home Office
- Bedroom Simple Floor Hanging Clothes Rack Single Pole Hat Rack – White
- Classic Black Cat Cotton Hemp Pillow Case for Home Car Such products could benefit from enhanced marketing.
Recommendations
Jumia sellers should:
Replace products with high discounts but low ratings with alternatives of superior quality.
Enhance marketing of products with high ratings but low reviews.
Price confidently because there is no clear relationship between pricing and rating/reviews. The sellers should not offer exaggerated discounts with the aim of increasing customer engagement.
Top comments (0)