DEV Community: Expert Writer

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

Expert Writer — Tue, 08 Sep 2026 15:04:20 +0000

Introduction

Data analytics play a vital role in decision-making processes for e-commerce. Online market platforms generate massive amounts of data and information about products, their prices, discounts, customer reviews, offers available and, ratings. When properly analysed, this information can be of great use to the sellers, and institutions in understanding customer behaviour, identify products that are performing highly, improve pricing strategies, and come up with better promotional decisions.
In this particular project, I will focus on the analysis of Jumia product dataset using Microsoft Excel, which is an important tool that can be used for data analysis, to get some essential indications. The major objective of the analysis is to transform raw data into cleaned, structured, analysed, and interactive dashboard that provides meaningful business insights.

Project Objective

The primary objective of this project was to create an interactive Excel dashboard for analysing the performance of products listed on Jumia.
The final dashboard provides an overview of product performance using key performance indicators (KPIs), charts, product rankings, and category breakdowns.
The dashboard is intended to help Jumia sellers and decision-makers understand how pricing, discounts, ratings and customer engagement interact.

The Dataset

The dataset contains information and data about Jumia products.
The original dataset was made of 115 product records and 6 main columns

Product : Name of the product
Current Price : Current selling price (In Ksh)
Old Price : Original price before discount
Discount : Percentage discount offered
Review : Number of customer reviews
Rating : Average customer rating out of 5

Data Cleaning

The original dataset had several quality issues that I had to address before analysis such as:

Duplicate records.
Prices were stored as text containing the KSh currency symbol
Ratings were stored in text such as 4.5 out of 5
Review values appeared as negative numbers.
Missing values in the Review and Rating fields

Action Taken

I started by checking for duplicates in the Original dataset and deleted them.
I did this by selecting the whole data set, and under the Data tab in Excel, I clicked on Remove Duplicates.

This step reduced the number of products to 111

Secondly, I changed the values in Original Price field,which were in Text form, into Numbers.
Similarly, the rating field contained values such as 4.5 out of 5 which is in Text format, and need to be in Numerical values

Checking for Missing Values

Missing values can interfere with calculations and visualizations. The original dataset had missing values in the Review and Rating Columns.

Cleaning the Review Column

The Review column had its values written as negative such as -2, -4, -14, -7.
Logically, the number of customer reviews cannot be negative. I treated the negative signs as erroneous and removed the negatives.

Creating the Discount Amount Column

One of the calculated fields required by the project was the absolute discount amount.
The formula is:
=Old Price - Current Price

Creating the Rating Category

I created the Rating Category to group the products according to their customer ratings as follows:

Poor: Rating below 3
Average: Rating between 3 and 4
Excellent: Rating above 4.5 The Excel formula is: =IF(F2<3,"Poor",IF(F2<=4.4,"Average","Excellent"))

Creating the Discount Category

I created the discount category in three groups:

Low Discount: Below 20%
Medium Discount: 20%–40%
High Discount: Above 40% The Excel formula is: =IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))

Descriptive Statistics

After the data cleaning and transformation, I calculated the descriptive statistics as follows, with the excel functions:

Total Products (=COUNTA(A2:A113)):111
Avg Current Price (=AVERAGE(B2:B113)): 1181.37
Avg Old Price(=AVERAGE(C2:C113)):1803.10
Avg Discount(=AVERAGE(D2:D113)): 37%
Avg Rating (=AVERAGE(F2:F113)): 3.88
Total Reviews(=SUM(E2:E113)): 721

Correlation Analysis

I investigated three major relationships:

Discount and reviews
Rating and reviews
Price and rating

1. Discount Vs Customer Reviews

The first relationship examined was whether products with higher discounts receive more customer reviews.
The correlation between discount percentage and number of reviews, of the final 111 products was -0.139.
This indicates a very weak negative relationship in the dataset.
The results, therefore, suggests that higher discounts are not associated with substantially higher customer engagement in this dataset.
Consequently, it is important, for the sellers not to assume that increasing the discount will automatically generate more customer reviews.

Rating Vs Customer Reviews

The correlation for this relationship was 0.066.
This indicates a weak positive relationship.
Therfore, highly rated products do not necessarily receive significantly more reviews.
It is, therefore, important to note that Customer satisfaction and customer engagement are different dimensions of product performance.

Price Vs Rating

The correlation between price and rating is 0.104.
This represents a very weak positive relationship.
Therefore, more expensive products are not necessarily rated substantially higher than cheaper products.
The result suggests that price alone does not appear to determine customer satisfaction in this dataset.

Identifying Top 10 Products by Customer Reviews

The strongest observation is the first product, 120W Cordless Vacuum Cleaner. It has the highest number of reviews but only a 2.8/5 rating.
This translates into high customer engagement but low customer satisfaction.

Identifying Top 10 Products by Rating

The products showed to genarally have relatively small review counts.
This is why a high rating should not automatically be interpreted as strong market demand.

Identifying Top 10 Products by Discount

Identifying High Discounts with Low Ratings

One of the most useful analyses was to identify products that combine:
High discount + Low rating
These products are a bit troublesome since they may be heavily promoted but still receive poor customer feedback.
One clear example is the 5-PCS Stainless Steel Cooking Pot Set with:

Discount: 55%
Rating: 2.1/5
Reviews: 13
This data suggests that the problem is not in the price. Reducing the price further, may increase sales temporarily, but may not solve the root cause of underlying customer dissatisfaction.
Potential causes may include:
Product quality
Product description
Product expectations
Packaging
Delivery experience
Product durability

Creating KPI cards for the Dashboard

The Key Performance Indicators for my dashboard included:

`- TOTAL PRODUCTS

AVERAGE PRICE
AVERAGE DISCOUNT
AVERAGE RATING
TOTAL REVIEWS`

Dashboard Layout

I organised the dashboard into several sections, keeping the most important information visible, while also providing detailed physical views.

Pivot Tables

Rating Category

This pivot table was to provide a breakdown of products into:

Poor
Average
Excellent

Discount Category

Key Business Findings

The analysis produced several important findings as follows:

1.High Discounts Do Not Guarantee High Engagement

The correlation between discount percentage and reviews was approximately -0.139, indicating a weak negative relationship.
Therefore, increasing discounts does not automatically result in more customer reviews.

2.High Ratings Do Not Guarantee High Demand

The relationship between ratings and review gave a correlation of 0.066, indicating almost no linear relationship. Some highly rated products have very few reviews.
Sellers should, therefore, consider both Rating and Rating Volume to evaluate performance.

3.Price Does Not Strongly Determine Rating

The correlation between price and rating of 0.104 indicates a very weak positive relationship.
Therefore, premium pricing does not automatically result in higher customer ratings.

4.Some Products Have Strong Engagement but Poor Satisfaction

A clear example in this category is the 120W Cordless Vacuum Cleaner. with
29 Reviews 2.8 Rating
This product attracts considerable customer engagement but has poor satisfaction. It needs to be analysed what could be the cause for this trend.

Some Products Are Heavily Discounted Despite Poor Ratings

A good example in this category is the 5-PCS Stainless Steel Cooking Pot Set that has:
55% Discount, 2.1 Rating, 13 Reviews
This indicates that high discounting does not necessarily solve customer satisfaction problems.

Recommendations

1. Avoid excessive reliance on discounts

The sellers should not assume that increasing discounts will automatically increase customer engagement.
Promotional startegies should involve improvements of product quality and better customer experienvces

2. Investigate products with high reviews, and low ratings

Products with large numbers of reviews but low ratings should receive urgent attention. A clear example is the 120W Cordless Vacuum Cleaner

3. Analyse customer feedback

The sellers should examine the content of negative reviews to identify recurring problems that may include:

Quality
Durability
Size
Functionality

4. Use ratings and the number of reviews together

A 5.0 rating based on one review should not be treated in the same way as a 4.7 rating based on dozens of reviews.
A better performance framework should consider:

Customer Rating + Review Volume + Discount + Price

5. Reconsider high discounts on poorly rated products

When a product has high discount and poor rating, the selllers need to examine the underlying problem before enhancing the discount.

Limitations

Missing data in ratings and review counts of some products
The database does not contain a dedicated product-category field. Adding categories would make it possible to compare performance across product groups
The sales data contains reviews but does not have actual sales quantities
The revenue and profit-margin data are not available

Conclusion

This project demonstrated how Microsoft Excel can be used to transform raw e-commerce data into a practical business intelligence dashboard.
The final cleaned dataset contains 111 products after removing three duplicate records and the problematic sofa-cover record.
The analysis demonstrates the importance of cleaning and validating data before creating a dashboard.
The final findings show that:

Higher discounts do not necessarily generate higher customer engagement.
Highly rated products do not necessarily have more reviews.
Expensive products are not necessarily rated higher.
Some products have high customer engagement but poor ratings.
Some products have substantial discounts but poor customer satisfaction.

The most important lesson from this project is that a dashboard is only as reliable as the data behind it. Careful data cleaning, appropriate Excel formulas, meaningful visualizations and thoughtful interpretation are all necessary to turn raw e-commerce data into actionable business intelligence.

Github Link

Below is the link for Github Repository.
https://github.com/Expertwriter006/Jumia-Product-Performance-Dashboard

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Expert Writer — Tue, 01 Sep 2026 07:45:13 +0000

Introduction

Microsoft Excel remains one of the most widely used tools for data analytics, not because it is the most powerful platform available, but because it combines accessibility with genuine analytical depth. It has data entry, data inspection, cleaning, calculation, filtering, visualisation, and reporting in one familiar environment. Through the Excel, we always get to have our first real encounter with dataset for analysis. This is exactly where my Week 1 learning began: understanding the building blocks of a spreadsheet, then immediately putting those building blocks to work on a dataset that badly needed attention.

This article explains my practical exercise, where I used the HR_Dataset_Dirty.xlsx dataset. The dataset contains 876 employee records and 21 variables, including Employee ID, department, salary, hire date, age, gender, performance score, employment status, bonus, education level, work experience, office location, project count, remote-work status, training hours and manager feedback score. The purpose of the exercise was not simply to calculate statistics, but to demonstrate an important principle of data analytics that reliable analysis begins with reliable data.

1. Understanding the Excel Environment

Before touching the dataset, it is worth revisiting the fundamental concepts that make everything else in Excel possible. These are not advanced features, they are the vocabulary of the spreadsheet, and every cleaning technique used later in this article is really just a combination of these basics.

Workbooks, Worksheets, and Cells

An Excel workbook is a file that can contain multiple worksheets (tabs), each organised as a grid of rows and columns. Columns are normally used for variables or fields, while rows represent individual observations. In the HR dataset, for example, Department is a variable and each row represents an employee record. The intersection of a row and a column is a cell, identified by a unique address such as A1 or D9. Every worksheet in the workbook built for this project - from the raw data to the final cleaned table, is simply a grid of cells connected to one another through references and formulas.

The image above illustrates a worksheet, it having both Original Dirty Hr Dataset, and Cleaned Dataset.

The above image demonstrates an Active Cell, bordered in green colour, and positioned in D5, with D being the column, and 5 being the row. The active cell therefore, is at the intersection of D column, and row 5.

Formulas and Functions

A formula is any expression that begins with an equals sign and calculates a result, a function is a predefined formula such as TRIM, PROPER, or IF. In this, I learnt text functions (TRIM, PROPER, SUBSTITUTE, UPPER), logical functions (IF), and counting functions (COUNTIF, COUNTBLANK). As shown throughout this article, these five or six functions are, in practice, almost the entire toolkit needed to take a messy dataset and make it ready for analysis.

2. Inspecting the HR Dataset

The first step in a data-cleaning workflow is data profiling. I checked the number of rows and columns, missing values, duplicate records, data types and inconsistent categories.

The original HR dataset contained 876 rows and 21 columns. It also contained 277 blank cells and seven exact duplicate rows. More importantly, several fields contained inconsistent representations of the same concept.
For example, the Department field contained values such as HR, H.R, Hr, Human Resource, Human Resources, Humman Res. and Humna Resources. These values may refer to the same department, but Excel would treat them as different categories when counting. See in the picture below.

Similar problems occurred in other fields. Gender included Male, MALE, male, M, Female, female, F and Femle. Employee Type included Permanent, Perm, permanent, Contract, Contrct and contractor. Office Location contained variations such as London and Londn, Nairobi and Nairob, and Tokyo and Tokio.

The dataset also contained invalid or suspicious numeric values. Examples included a performance score of 11 even though the intended scale is 1–10, an age recorded as thirty, and a project count recorded as ten. There were also values such as -1 in work experience and 1900 or 2030 in Last Promotion Year that require validation rather than blind acceptance.

Additionally, it had:
Inconsistent Employee IDs - some stored as plain numbers (10764), others prefixed with text (EMP-10540), and several IDs duplicated across two different employees.
Invalid or inconsistent dates - hire dates such as "2019-02-30" (30 February does not exist), "2020/13/05" (month 13 does not exist), "31/04/2021" (April has 30 days), and plain text such as "not available".
Missing values - blank first names, blank office locations, and blank salaries scattered throughout the sheet.

Data Cleaning

Rather than cleaning the data by hand, I built the entire workflow using formulas, so that every 'cleaned' value is calculated automatically and stays connected to its raw source. If the raw data changes, the cleaned output updates with it - this is the real advantage of doing data cleaning in Excel using formulas rather than typing corrections over the original values.

Step 1: Standardising Text - Names, Departments, Locations, and Status

The first pass targets text inconsistency. Full names are rebuilt from First Name and Last Name using TRIM (to remove stray spaces), SUBSTITUTE (to collapse accidental double spaces), and PROPER (to apply consistent capitalisation). Where either name was blank, an IF/OR check flags the record as "MISSING - REVIEW" instead of silently producing an incomplete name.
The result was a worksheet where every text field - name, department, office location, and employment status - now uses one consistent spelling throughout, which is essential before any analysis of the data started.

Step 2: Detecting Duplicate and Missing Records

With text standardised, my next step was finding rows that should not exist (duplicates) and rows that are missing critical information. I first stripped Employee ID of its "EMP-" prefix and converted to a genuine number with SUBSTITUTE and VALUE, so that "EMP-10540" and "10540" are recognised as the same identifier. Then I used COUNTIF against that cleaned ID column to flag any employee ID that occurs more than once, and COUNTBLANK to check each entire row for missing fields.

Step 3: Correcting Numeric Fields - Salary, Age, and Work Experience

Several numeric columns were stored as text, which silently breaks any SUM, AVERAGE, and other calculations on them. Salary values such as "$65109" were converted to true numbers, and the currency used as KES(Kenyan Shillings).

Step 4: Validating and Standardising Hire Dates

Dates were the least consistent field in the dataset, mixing genuine date values with plain text ("not available"), and several strings that look like dates but describe days that do not exist on any calendar.

Step 5: Consolidating into an Analysis-Ready Table

The final worksheet pulls the cleaned output of every previous step into a single, consolidated table, with one row per employee, with standardised names, departments, locations, employment status, salary, age, work experience, and hire date. A final Record Status column combines every flag raised earlier (duplicate ID, incomplete record, invalid date, or missing name/salary/age) into one clear verdict: "Ready for analysis". I added an AutoFilter so that a colleague could instantly isolate the records that still need attention before running any further analysis.

Step 6: Validating the Cleaned Dataset

After cleaning, I performed another inspection rather than assuming the data was correct.
The cleaned working dataset contained 869 rows after removal of the seven exact duplicate rows. Standardised categories were used for fields such as Department, Gender, Education Level, Employee Type, Office Location and Remote Work Status.
Numeric fields were converted to usable numeric values where possible, and invalid values were flagged or converted to blanks when they fell outside defined logical ranges.

Step 7: Basic Excel Analytics After Cleaning

Once the data was clean enough to analyse, I used Excel formulas to provide immediate descriptive statistics.
For example, I used AVERAGE to calculate mean salary, age or performance score. COUNTIF to count employees belonging to a particular department, and SUMIF to total salaries or bonuses for a selected category.
Using the cleaned working data, the average salary was approximately $74,114.16, the average age was approximately 41.98 years, and the average performance score was approximately 4.98.
There were 155 Finance employees and 263 employees classified as Fully Remote in the cleaned working dataset.

Conclusion

Excel is a powerful tool, and the skills provide the foundation for the wider data-analytics workflow. Understanding workbooks, worksheets, cells, ranges, formulas, data types, tables, sorting and filtering may appear basic, but these skills directly support professional data preparation and analysis.
The HR dataset demonstrates that real-world data is rarely perfectly structured. The original file contained 876 records, 21 variables, 277 missing cells, seven exact duplicate rows, inconsistent categorical labels, mixed data types, invalid dates and suspicious numeric values.
Through inspection, standardisation, type conversion, validation and duplicate handling, the working dataset was reduced to 869 records and made substantially more analysis-ready.
The most important lesson is that data cleaning is not merely about making a spreadsheet look neat. It is about making the meaning of the data consistent, transparent, and defensible.

Understanding Git Workflow from Working Directory, Staging, Commit, and Push.

Expert Writer — Sun, 23 Aug 2026 20:34:12 +0000

Git

Git is used to track changes in files and allow for managing ythe developmnet of projects, especially those that entail data. I used git to enable me record the different versions of my data from the original raw data, compare changes, without interfering with the initial data.

Git Workflow

Git has a series of commands that are important for committing changes and tracking the data. The commands are described as a Git Workflow as it involves checking the status of the current project, making changes to the projects by adding files, and committing the changes. The Git Workflow is quite vital as it allows for a systematic and organised way of managing changes, keeping a record of every progress, and making it easier to identify the areas that changes were made.

My project was Kenya Health Records Analysis and it contained and excel data, thus was in this structure:

`Kenya Health Records Analysis/ 
- Kenya Health Records Analysis.xlsx 
- README.md`

Checking the Project Directory

I used cd "Kenya Health Records Analysis" to access the project directory then ran the command ls -la to list the contents that were in the directory

Checking README File

cat README.md
This displayed the contents of the file such as the Title, tools used, and the challenges faced.

Initialize Git

git init
I ran this command to instruct Git that I needed the project to become a git repository.
To confirm whether it was successful, I ran the command ls -laand git status

Changing a file

I was able to edit and modify my project in this stage by opening the README.md file and adding more information in the Challenges Faced section.
I then ran git status but got this error:

`Changes not staged for commit: 
      modified: README.md

I noted that changing a file does not directly create a commit. I therefore, ran git add README.md which communicates to git to adjust the changes.

Commit

git commit -m
This was for creating a checkpoint in the project analysis by recording the changes I have done in the project. It also allowed for me to continue working without having to push the changes in Github immediately.

git commit -m "Kenya Health Records Analysis"

Checking Commit History

git log --oneline
This command allowed me to inspect every of my commit history

This is followed by git status to understand the current state of the project

Push

git push
After making the challenges locally, I ran git push origin main to send them to the remote repository.

Functions of Git

Track changes
Create commits
Mmaintain project history
Manage different versions of my project locally

Conclusion

During this project, I learnt that Git workflow is a sequence and not just a list of random commands. It starts from inspecting the project files with ls -la and then reading the documentation through running cat README.md. I can then run git status to understand the latest status of the repository. After this, I commit using git commit -m, and can review the history using git log --oneline.To finally share the local commits I made, I ran the command git push origin main.
Understanding this workflow gave me essential foundation for working with data analytics project as the one in Kenya Health Records Analysis in terms of documenting my work, and building a portfolio as a Data Scientist.