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

Introduction

This project involved building an interactive Excel dashboard to analyse Jumia products. The dataset contains information such as product, prices, customer ratings, reviews and discounts. Here in are the steps taken to ensure that the raw data translates into meaningful insights to help identify product performance and customer satisfaction.

Data Cleaning and Preparation

Initially, the dataset was cleaned to remove any inconsistencies, blanks, and corrected to ensure that the data was ready for analysis. Below is the raw dataset

1. Remove Duplicates Created a new worksheet from the raw dataset and named it as cleaned. Duplicates were removed from the cleaned worksheet to ensure all repeated records are expunged and only the unique records remain.
2. Format the numerical values to the appropriate category

• The prices column are formatted and converted to currency
• The review column is revised from the negative value and made to whole numbers.
• On the ratings column, the out of 5 is expunged and only the whole numbers retained.
• The blanks in each column are replaced by Not provided

Excel techniques, formulas and analysis performed

Discount Amount
Created a discount column and calculated the difference between the old and current price. Old price - Current price

Discount Category
Grouped products into categories of High discount, Medium discount, and Low discount, based on the discount percentage column.
=IF(D2>=40%, "High Discount", IF(D2>=20%, "Medium Discount", "Low Discount"))

• High discount - more than or equal to 40%
• Medium discount - more than or equal to 20%
• Low discount - less than 20%

Rating Category
Classified products based on their ratings into Excellent, average and poor based on the ratings column using the following function:

=IF(ISBLANK(F2), "Not Provided", IF(F2>=4.5, "Excellent", IF(F2>=3, "Average", "Poor")))

• Blank spaces - Not provided
• Excellent - more than or equal to 4.5
• Average - more than or equal to 3
• Poor - less than 3

Price Category
Classified products into high price, medium price, and low price, using the below function based on the price column:

=IF(B2>=2000, "High Price", IF(B2>=1000, "Medium Price", "Low Price"))

• High price - more than or equal to 2000
• Medium price - more than or equal to 1000
• Low price - less than 1000

Pivot Tables and Charts

Pivot table and charts helps to summarize the relationship between different categories.

• Go to the cleaned dataset and select Ctrl+A
• Go to insert and then select pivot tables

Several pivot tables were created to analyze the following:
Top 10 products by discount: This shows the relationship between the top products that had the most discount.
Slicers of the product and discount were added to the pivot tables

Top 10 products by ratings: This entails top products by ratings.

Discount mix: This shows the relationship between the discount category and the product

Dashboard

The cleaned and analyzed data was used to develop an interactive dashboard presenting e-commerce product performance and comparisons. The dashboard helps to examine product pricing, discounts, ratings, reviews, and customer engagement.

Finding

The average product rating is 3.89/5 which indicates moderate customer satisfaction.
Higher discounts do not necessarily result in higher customer engagement. High-discount products actually have lower average reviews than medium-discount products.

Recommendation

Adopt a medium discount category that is around the 20%–40% range, instead of automatically pushing products into 40% discount territory. This will create more engagement from the customers.