Introduction
The project introduces a Jumia dataset in csv file.
Jumia is an ecommerce powerhouse operating accross Africa.
The goal of this project is to create an interactive Excel dashboard that provides insights into the
performance of products listed on Jumia
Overview
The dataset contains information about products listed on Jumia with the following columns:
• Product: Name of the product.
• Current Price: The current selling price of the product (in KSh).
• Old Price: The original price before discount (in KSh).
• Discount: The percentage discount offered on the product.
• Review: The number of customer reviews received by the product.
• Rating: The average customer rating of the product (out of 5).
Objectives of the objectives
- Identify errors in the dataset
- Perform data cleaning
- Perform analysis for trends
- Visualize your results using pivot charts
- Use slicers to interact with the indicators
- Create a dashboard with all the KPIs
Data Cleaning
I copied and named the cleaning sheet cleaned.
Autofitted column width to accomodate the long product names by
CTRL + A ->Home -> Format -> Autofit column width -> OK
Applied a filter for all the columns by Select First Header row -> Home -> Sort and Filter -> Filter-> Apply filter -> OK
Changed the header row background color Select First Header row -> Home -> Fill color -> Select the desired color -> OK to differenciate from the main data.
Data Errors Identified
- Duplicates
- The current and old price are in text format
- Rating is mispelled and in text format
- Review column is in negative number
- Price in a row is in range of two numbers
Removing duplicates
Select the entire data -> Go to data tab -> Remove duplicates -> Choose all columns -> OK
Change current price and old price to currency format.
Select the current/old price column -> Use Find and replace -> First Replace Ksh to Blank -> OK -> Select price column -> Home -> Number -> Choose currency on the dropdown menu -> choose Ksh on the currency symbol -> select 1 decimal place -OK
Change Rating to number format
I used Find and replace
Select Rating column -> Home tab -> Find and select -> Replace or CTRL + H -> example 2.0 out 5 with 2, 2.5 out of 5 with 2.5
Changed the name by renaming the Ratingd to Ratings.
Change Review to a positive number
Select the Review column
CTRL + H for find and replace-> Replace - with blank-> OK
Price in a row is in range of two numbers
I calculated the average of the two numbers in range by
=AVERAGE(2200,3200)
Data Enrichment
The project required we create 3 columns
Price category
- High price greater ksh 3000
- Medium price greater or equal ksh 2000
- Low price less than 1000
I used a nested IF statement in the inserted column and renamed it price category.
=IF(B2>3000,"High Price",IF(B2>=2000,"Medium Price","Low Price"))
Discount Category
- High discount greater than 40%
- Medium discount less than or equal 40%
- Low discount less than 20%
I use a nested ID statement and renamed the column discount category
=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount"))
Rating Category
- Excellent greater than 5
- Average less than or equal 4.5
- Poor less than 3
I used a nested IF statement here too and renamed Rating category.
=IF([@Ratings]="","Missing",IF([@Ratings]<3,"Poor",IF([@Ratings]<=4.5,"Average","Excellent")))
Discount Amount
I used subtraction from old price to current price.
Renamed the column Discount amount.
=[@[Old price]]-[@[Current price]]
DATA ANALYSIS USING PIVOT TABLES AND CHARTS.
I created pivot tables to help me with data analysis and data visualization.
From the data analysis
- Click anywhere in the data table
- Go to insert table
- Choose pivot table
- Choose from existing table and a new worksheet
- OK
I created a pie chart and a bar chart showing discount by reviews, ratings category by average reviews, price and rating using the pivot tables created.
Excel Dashboard
I used these slicers to connect all the pivot tables/charts.
Created a pivot table -> Insert -> Slicer -> Choose columns (Rating category, Price category, Discount category) -> OK
From the Slicers and pivot tables/chart I created a Dashboard displaying all the charts, slicers for interaction and Key performance Indicators shown below
CONCLUSION
From the dataset given ratings,reviews,prices and large discounts do not guarantee for huge sales.Jumia should stock product that makes a different in quality this will attract customers.
Top comments (0)