Introduction
Big E-commerce organisation like Jumia handle large datasets that are vital in the day-to-day operations. The data needs to be evaluated to make good and decisive business decisions that will improve overal business perfomance.
For this article I will take you through the process of building a comprehensive product analysis and dashboards that will guide smarter business decisions.
Objective
The objective of this project is to:
To produce excel dashboards that provide insights into the perfomance of products.
To analyze product pricing, discounts, customer reviews and ratings to understand product perfomance and identity trends.
Dataset Overview
Product - Name of the product
Current price - The current seeling price of products in (Ksh)
Old price - The original price before discount in (Ksh)
Discount - The percentage discounts offer on the products
Review - The number of customer reviews received on the products
Ratings - The average customer rating of the product out of 5
Data Cleaning
Before any analysis could be done on the dataset, I had to clean the data for the mistakes that were done during data entry. This are the steps that i did towards effective data cleaning
1) The first step i did was to copy the data to a new worksheet then renamed it "Cleaned" and renamed the other worksheet as "original"
2) The next step I added freezing panes to assist with easier navigation.
3) Used the proper function to capitalize the first letters of the titles
4) To change the numeric columns (Current price) and (Old price) to their correct data types of currencies I pressed ctrl and selected both columns then changed the data field to currencies then used the find and replace to change (ksh) into blank which then sets the data to the correct field type.
5) Removed duplicates on the dataset which will lead to an accurate analysis
Data Enhancement
1) Discounted Amount
To find the discounted amount I created a new column right after the Old price column then perfomed a calculation on the new column Discounted amount = Old price - Current price
2) Rating category
For the rating category Iwas expected to display the rating either excellent, average or poor i used the below formula
=IF(NOT(ISNUMBER(H3)),"Not Provided",IF(H3<3,"poor",IF(H3<=4,"average",IF(H3>4.5,"excellent","good"))))
Since in the rating column there was blank cells i replaced the blanks with "Not Provided". The IF(NOT(ISNUMBER function was used to check if the highlighted cell has a number or not and returns cells that is not numerical with "Not provided".
I added rating of "good rating" since there was some figures that were left out and they were above the average rating and slightly below the excellent rating.
3) Discount Category
The Dicount category was categorized into three high, medium and low. I inserted a new column next to the discount pecentage and used the following formula for groupings. =IF(E4<20%, "Low Discount", IF(E4<40%, "Medium Discount", IF(E4>41%, "High Discount")))
4) Price Category
I was required for this project to display if prices were high, medium or low, used the following formula to determine the out of the prices
=IF(B3<500, "Low Price", IF(B3<1500, "Medium Price", IF(B3>1501, "High Price")))
Data Analysis
For data analysis I created an new worksheet and the worksheet created a table that best describe the various questions raised for the project as show below
Then used the filter and sort to find out the top rated products and lowest rated products to further give insights of how the products are performing on the market
To find if there was any relationship between products and customers reviews then also find if there was any relationship between high discount and more reviews on the products I used the CORREL() function where a positive relationship results with a +1, with a -1 symbolizing a strong negative relationship. Close to 0 represents a no relationship of two products
Dashboard
For the dashboard I first started by creating pivot tables on then data then from the pivot i then created slicers that are instrumental on the creating of the dashboards used for visualization.
Business Insights And Recommendations
1) High Discounts do not translate to high customer engagements since most of the products that have high discounts have poor ratings from customers.
2) Highly rated products from the data most of the products tend to have low prices on them but that is not on all products since other products have a medium or high price making it difficult to ascertain if products that have high ratings are cheap in the market.
3) High quality assurance on low rated products to make sure the products do not have defects making it to have low rating after which it affects the prices from low prices to medium.
4) Improving on the review method of the products so that most of the products are reviewed by customer after purchase since from the data a lot of customer fail to have reviews of products making it hard to know the customer satisfaction.
Conclusion
This project have changed how i interact with data and has given me more insights and how to approach projects to answers business questions and to provide solutions that make a huge difference on the overall performance products and organization.
The link below to github for the workbook that contains Original raw data and cleaned data, data enrichment, analysis and dashboard
Github repository https://github.com/alfieotuko/Jumia-product-analysis
Top comments (0)