Project Introduction
An analysis project turning Jumia product data into useful pricing,promotion and customer-engagement insights.The project will help us understand how price,promotions and customer feedback influence product performance.
Project Objectives
By the end of this project we should be able to understand and explain :
Whether large discounts are associated with more reviews
Whether highly rated products attract stronger engagement
Which products perform best based on ratings and reviews
Whether price and rating move together
Which products may need a different strategy or marketing
Dataset And Data Quality Audit
Our dataset above is made up of 6 columns namely;Product,Current Price,Old Price,Discount,Ratings and Reviews.It consist of 116 different rows.
The data had no blanks for product,current price,discount and old price fields.It instead had a total of 58 blanks for both ratings and reviews.There were 3 exact duplicates reducing blank count to 55 for both ratings and reviews.
They were no ratings above 5 or below 0 neither were they discounts outside the range of 0-100
Cleaning and Preparation Decisions.
Cleaning
Kudos to our data scientists because generally our data was clean except for minor changes.We did trim the product field removing extra spaces before the products.
Current and old prices had their fields formatted as text so we went ahead to remove KSh,removed commas and extra spaces and formatted as currency aligning both fields to the right
For the ratings field we removed 'out of 5' as generally ratings are always from 0 to 5.For the blank rows in ratings and reviews, we decided to totally leave them as blank as opposed to filling them as zeros because ideally zero would have translated to poor rather than "Not Reviewed" or "Not rated"
Generally for the bigger part of cleaning work was actually getting to change the fields data type.
Preparations
Enriching data to me is always the first step to analysis.Data enrichment will provide enhanced analytical depth,higher data accuracy and integrity and improved decision making
For our Jumia dataset, we did add numerous fields to help make our analysis easier.We checked for any abnormalities in our data and had them recorded
Is our current price greater than our old price for any rows ?
Do we have rows that have missing values for discount,do we have rows that have discount outside the range of 0-100 ?
Do we have rows that have missing values for ratings,do we have any ratings outside the range of 0-5 ?
The above questions resulted to three additional fields for checking prices,discount and ratings.We went ahead to categorise prices,ratings and discount such that
For every rate given below 3,excel was to return poor,between 3 and 4 were classified as average and excellent for rates above 4
We had low discount being any value below 20%,medium discount being values ranging between 20% and 40% and high discount as values from 40%
For prices, we did create a threshhold to be able to group the prices.We had first quartile as the maximum for low prices and our third quartile as our minimum for high prices
Excel Formulas
These are some of the excel formulas we used to do cleaning and preparation of our data
=TRIM()
=IF(OR([@Ratings]<0,[@Ratings]>5,ISBLANK[@Ratings]),"Check Rating","Ok")
=IF(OR([@Current Price] > [@Old Price]),"Check Price',"Ok")
=IF(OR([@Discount]<0%,[@Discount]>100%,ISBLANK[@Discount]),"Check Discount","Ok")
For categorising our prices,discount and ratings we used
=IF(ISBLANK([@Ratings]),"Missing Rating",IF([@Ratings]<3,"Poor",IF([@Ratings]<4,"Average","Excellent")))
=IF[@Discount),"Missing Discount",IF([@Discount]<20%,"Low Discount",IF([@Discount]<40%,"Medium Discount","High Discount"))
=IF([@Current Price]<Price_Q1,"Low Prices",IF([@Current Price]<Price_Q3,"Medium Prices","High Prices"
Price_Q1=QUARTILE.INC(tblproducts[Current Price],1)
Price_Q3=QUARTILE.INC(tblproducts[Current Price],3)
Engagement and performance flags
We did define a rule using percentile and created flags for
high discount and low rating
high discount and low engagement
many reviews and average rating; and
strong engagement and excellent rating
For high discount,strong engagement,low engagement and rating we used the following formulas to create our threshold
strong_engage =PERCENTILE.INC(tblproducts[@Review],0.75)
low_engage =PERCENTILE.INC(tblproducts[@Review],0.25)
high_discount =PERCENTILE.INC(tblproducts[@Discount],0.75)
low_discount =PERCENTILE.INC(tblproducts[@Discount],0.25)
average =AVERAGE(tblproducts[@Rating])
Our thresholds helped us find if there were products with high discount and low rating,high discount and low engagement or even many reviews and average rating.
PivotTables And Analysis
A pivot table is simply a data tool used to instantly group,aggregate and transform data.Pivottables can be actually be used to create pivotcharts that are used for visualizations.Visualization is actually a summary of our analysis
For our Jumia dataset we did create different pivottables such as rating mix,discount mix,price vs rating,engagement by discount,discount vs reviews.These pivottables actually helped us to findings and some recommendations
We did analyse correlation between discount vs reviews,ratings vs reviews and current price vs rating.Unfortunately or fortunately there were only weak correlations for all three meaning none of them variables change together.It is important to note that correlation is not causation.
Here is also a picture of our dashboard
You notice that on the far left side of our dashboard we have table like structures that are not necessarily charts.They are called slicers and generally used to filter data.You might actually need more than one slicer for your dashboard and it is also important to note that you need to have them connected in order for one change to affect all charts
How do you connect slicers ?
Right click the slicer
A dropdown appears that you click on report connections
You then proceed to select all pivottables you created
Note
It is very important to note that if when creating your pivottables, you did click on add data to this data model in order to create on the existing worksheet,then your slicers simply won't work.
Findings And Recommendations
Findings
A lot of products had their rates as missing
Most products have been rated excellent,followed closely by average then lastly poor
Most products had high discount of above 40%
Most highly rated products are products with high prices
Products with medium discount received the most reviews of 351,products with high discount received 334 reviews and products with low discount received 38 reviews
Recommendations
Maintain the good quality,good customer services for the highly rated products
We also need to know the kind of reviews(good or bad) our customer gives back to be able to enrich what we can find from our data.
[ (https://github.com/MburuKefoi/Jumia_Product_Performance_Dashboard) ]
In an effort to keep this article fun and short,above is my link to the file so that you can check it out
Top comments (0)