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

Introduction

Jumia is an E-commerce platform that allows individuals and businesses to buy and sell products online. Think of it as an online marketplace. This project seeks to transform raw product pricing and performance data into an interactive Excel dashboard, that provides a summary of useful information. This varies from examining the correlation between product pricing and discount levels influence purchase, as well as analyzing feedback from customers through product rating and reviews.

The goal of this analysis is to provide an overview of Jumia products’ performance. In this article, I will walk you through my end-to-end process where I clean and organise the raw data, enrich the data through calculations, analyze trends and correlations within the data, and finally, summarize key findings through an interactive dashboard.

Dataset description:

The raw dataset is made up on 6 columns A-F with Row 1 as the header row, followed by 115 data entries, with the data range as A1:F116.

• A: Product (name of the product)
• B: Current price in Kenya shillings
• C: Old price in Kenya shillings
• D: Discount percentage
• E: Reviews (number of reviews per product)
• F: Rating (product rating out of 5)

Data Cleaning and Preparation:

At first glance through the data, there are a number of issues ranging from duplicate entries, missing values, especially in the reviews and rating columns, as well as inconsistencies in alignment, formatting and data types.

In the product and rating columns, the values in those columns are not fully visible at a glance. To fix this:
Click anywhere within your data > Ctrl+A to select your entire range of data > On the Home tab, under the Cells group > Select the dropdown under Format > Select Autofit Column Width

Additionally, in order to have the header row frozen in place as you scroll down the data:
On the View tab, under the Window group > Select the dropdown under Freeze Panes > Select Freeze top row

Standardize data types

Ensure that all columns are in the right data type as follows:

• A: Product (name of the product) - TEXT
• B: Current price in Kenya shillings -CURRENCY(KES)
• C: Old price in Kenya shillings -CURRENCY(KES)
• D: Discount percentage -DECIMAL NUMBER (2dp)
• E: Reviews (number of reviews per product) -NUMBER
• F: Rating (product rating out of 5)-NUMBER

Steps:
Select column ie Product column > On Home tab under Number group > Select the correct data type from the dropdown OR click on the arrow at the bottom right of the Number group > Select correct data type as in TEXT

Select column Current price > On Home tab under Number group > Select the correct data type from the dropdown OR click on the arrow at the bottom right of the Number group > Select correct data type as in CURRENCY > Set to 2 decimal places > Select the currency symbol as KES/Ksh

Follow the same process for all columns to standardize the values in each to the right data type. Note that, text columns are align to the left, while numeric columns are aligned to the right.

Then, looking at the rating column, this is meant to be a value between 0 and 5, with Number as the data type. However, the words “out of 5” are in each cell in that row which makes it difficult to do calculations such as sum, average etc.
Therefore, rename the column to “Rating (out of 5), then have individual values as in 1,2,3.5 etc. This allows us to have the data in the column in Number format.

Find and replace command

Instead of manually going through rows of data to check for typos or change a label, this command allows you to “search and update”. Similarly this can be sued to find and standardize missing values in both text and numeric columns.

Select the column range ie Rating column > Input the shortcut Ctrl + H to access the find and replace dialogue box:

Under Find what: Input what excel is to search for within the selected range, in this case, “out of 5”
Under Replace with: Input what excel should replace the value with, in this case, leave blank

In the Current price and Old price columns, the correct data type should be currency, with the symbol as either KES of Ksh. However, in the raw data the currency prefix is manually typed in.
Use Find and Replace to solve this. Select column, then open dialogue box:
Find ‘Ksh’ and Replace with ‘blank’

Similarly, in the review column, it is impossible to have a negative number of reviews. Ideally, the column should have whole numbers representing the number of reviews given by customers for each product. Ue the find and replace option to remove the negative sign on some of the entries on the column.

Find and replace command can also be used to deal with blanks within the data range.
To do this:
Select entire data range (Ctrl + A) > Go to Home tab under Editing group > Select Sort & Filter dropdown > Select Filter option (has a funnel icon)
Each column header will now have a downward facing arrow that displays the values in that column as well as Sort & Filter options for that column.
In text columns, replace the blanks with either Unknown/ Not provided / N/A, while in numeric columns, replace the blanks with ‘null’.

Remove duplicates

In case of accidental double entries, the Remove duplicates command tells Excel to look for rows where entries in every single column match, then deletes the entire duplicate row.
To remove duplicates > Ctrl + A to select entire range > On Data tab under Data tools group > Select Remove duplicates tool > On dialogue box, ensure to check “My data has headers”

As a result, Excel finds and removes 3 duplicate values, 112 unique values remain.

After cleaning this data, our range is now A1:G113.

Data Enrichment

Here, we need to add the following calculated columns to contribute to our analysis:

1. Absolute discount (Kes)
This is the discount amount in Kenya Shillings, obtained by subtracting the current price from the old price
To obtain values for this column, input the formula:
=Old price – Current price

2. Rating category
This classifies the rating for each product, with reference to the Rating column, as:
Where a rating below 3 is marked as “Poor”, a rating above 4.5 is marked as “Excellent” and a rating between 3 and 4.4 is marked as “Average”.
To obtain values for this column:
=IF(ISBLANK(I2),"Not provided",IF(I2<3,"Poor",IF(I2>4.5,"Excellent","Average")))

In the function above, the Nested IF function is used to test multiple conditions in one formula. The general syntax is:
=IF(logical_test1, value_if_true1, IF(logical_test2, value_if_true2, value_if_false2))

Additionally, our reference column, the Rating column contained blanks which Excel interprets as zero. Using the =ISBLANK() function to check if a cell is blank and return True for blank cells and False for null cells.
Therefore, rows where the rating column is blank, will return “Not provided” in the the Rating category column.

3. Discount category
This classifies the discount percentage for each product, with reference to the Discount percentage column, as:
Where a discount below 20% is marked as ‘Low discount”, a discount between 20% and 40% is marked as “Medium discount”, and a discount above 40% marked as a “High discount”
To obtain values for this column:
=IF(F2<0.2,"Low discount",IF(F2>0.4,"High discount","Medium discount"))

4. Pricing category
This classifies the products based on their Current price as:
Products costing below Kes.1300 marked as “Budget friendly”, those costing between Kes.1300 and Kes.2500 marked as “Value” while those that cost above Kes.2500 marked as “Premium”.
To obtain values for this column:
=IF(B2<1300,"Budget friendly",IF(B2>2500,"Premium","Value"))

Next in the data enrichment process, we need to calculate a summary of the statistics we have. The following values were calculated:

Summation	Reference column	Formula	Result
Average current price	B: Current price	=AVERAGE(B2:B113)	KES 1,186.89
Average old price	C: Old price	=AVERAGE(C2:C113)	KES 1,811.11
Average discount percentage	F: Discount percentage	=AVERAGE(F2:F113)	0.37/37%
Average rating (out of 5)	I: Rating	=AVERAGE(I2:I113)	3.9
Total number of products	A: Products	=COUNT(A2:A113)	112 products
Total number of reviews	H: Reviews	=COUNT(H2:H113)	57

This now gives us our cleaned and enriched data for analysis:

Trend analysis

Now that our data is cleaned, and we have a summary of all key metrics, we will be using pivot charts and tables to check for patterns, trends and correlations between the variables in the data set. This will give us insights into the performance of different products, and guide business recommendations.
This also provides visual representation which is especially helpful in disseminating this information to the relevant decision makers.

From our data, we will be answering the following questions:

• Are higher discounts leading to higher customer engagement?
• Do highly rated products have higher or lower prices?
• Which products are performing best based on reviews and ratings?
• Which products may need improved pricing strategies?
• What recommendations would you give to Jumia sellers?

From the business questions above, pivot tables and charts allow us to investigate relationships and trends within our data.
Pivot tables – Allow us to compare different measures and summarize information.
To inset a pivot table:
Click anywhere within your data range > On Insert tab > Select Pivot table > Select from Data/ Range > Select to place in a new worksheet > OK

On the right side of the worksheet is the Pivot table fields panel. There are 4 fields in a pivot table:

1. Filters – Allows you to include or exclude specific data from the pivot table
2. Columns – Displays data in columns across the top of the pivot table, to create comparison layers.
3. Rows – Displays data in rows on the pivot table, allows for grouping categories.
4. Values – Contain the numerical data to be interpreted or summarized. Performs calculations such as average, count, sum etc

Now, onto the relationships under investigation:

1. Relationship between discounts and reviews.
Insert a pivot table onto a new worksheet > Move Review column in the Values field > Set the summation to Count of reviews > Move Discount Category column to the Rows field

This shows the number of reviews for products in the high, medium and low discount category.
To insert the chart above to give a visual representation of the pivot table:
Click inside the pivot table >Go to Pivot Table Analyze on the menu bar > Select pivot chart > Select Column chart and preview > OK

The products with the highest discounts have the highest number of customer reviews, while the products with the lowest discounts have the lowest number of customer reviews. This implies that higher discounts drive more customer engagement, while lower discounts barely lead to customer interactions.

Recommendation:
Higher discount products should be more highlighted to the customer in marketing efforts to maximise traffic. The business may consider increasing the discount for the products with low discounts, or removing them from the promotions to make room for products with greater chance of generating customer interest

2. Relationship between ratings and reviews
Insert a pivot table onto a new worksheet > Move Reviews column to Values field > Set summation to Count for reviews > Move rating category to the Rows field

This shows the number of reviews for the respective rating categories ie Excellent, Average and Poor rating.
Insert a pie chart, as above, for a visual summary of the table.

Customer feedback is split between the positives and negatives. 19 excellent reviews and 12 poor reviews make up over half the total 57 customer reviews. This is against a total of 112 products.

Recommendation:
Identify the 12 customers who gave a poor review in order to identify the root cause, be it product defects, delays in delivery, poor customer service etc
Additionally, the business could incentivize detailed feedback, such as offering 10% off a customer's next purchase after completing a feedback form. This could help drive active customer feedback.

3. Relationship between pricing and ratings
Insert a Pivot table onto a new worksheet > Move Rating column to Values field > Set summation to average for rating values > Move Price category column to Rows field

Insert a column chart to visually represent this table.

Both the premium and value price categories have a rating above 4, out of the highest rating possible of 5. Customers buying this products are getting high quality products and value for their money.
However, budget friendly products have a lower rating indicating that lower cost items fail to meet customer expectations.

Recommendations:
Identify features, packaging or customer service that comes with premium and value products, and where feasible, see if these elements can be scaled down to the budget friendly products.

Dashboarding

A dashboard serves as the control centre of your analysis. It allows you to monitor key performance indicators (KPIs) at a glance.

A few key things to consider while building your dashboard include:

• Choosing the right visuals: For example, a line chart to track sales trends over time or a bar chart to compare performance between different product categories.

• Keep it focused: The dashboard is meant to be a summary of important information, and it is therefore best to avoid clutter.

• Interactivity: Ensure your dashboard is responsive to filters, by use of slicers allowing you to view information for a specified subset of data.

• Format: Use a consistent colour scheme, layout, and uniform alignment to ease user friendliness and ensure your report has a professional look.

Slicers
Slicers are a visual filter tool that allow filtering of data in pivot tables and charts, without altering the underlying data.
They serve to ease user interactivity, allowing users to filter data by various criteria.
In our dashboard, we will insert slicers for price category, rating category and discount category. To do this:

Click anywhere within the pivot table > Go to PivotTable Analyze on the menu bar > Under the Filter group select Insert slicer > Select slicer/ column to filter by ie Discount category, Rating category and Price category > select Ok

Layout

1. Header text box containing the title of the analysis. In our case: JUMIA PRODUCT PERFORMANCE DASHBOARD
2. Key Performance Indicators: Text boxes under the title that show important metrics such as total number of products, average product price, average discount percentage, average rating and total number of customer reviews.
3. Slicers to filter the data being viewed through the dashboard. In our case, slicers for discount, rating and pricing categories.
4. Key visuals such as pivot charts.

Interactivity
To ensure the interactivity of your dashboard, ensure to report connections on all slicers. What this does, is to link each slicer to multiple pivot tables and charts in the workbooks.
Therefore, all connected visuals show the same filtered data.
To achieve this:
Right click on the slicer > Select Report Connections or Pivot Table Connections > Check the boxes for all the pivot tables or charts you want to connect to that slicer > OK

After incorporating all the above into my dashboard, this was the result:

Project Resources

You can explore the full dataset and the Excel workbook used in this analysis in my GitHub repository.