Building an Interactive Excel Dashboard For E-Commerce Product Analysis:A Case Study of Jumia Products

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]5,ISBLANK[@Ratings]),"Check Rating","Ok") =IF(OR([@Current Price] > [@Old Price]),"Check Price',"Ok") =IF(OR([@Discount]100%,ISBLANK[@Discount]),"Check Discount","Ok") For categorising our prices,discount and ratings we used =IF(ISBLANK([@Ratings]),"Missing Rating",IF([@Ratings]