DEV Community: Janet Kangogo

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

Janet Kangogo — Fri, 11 Sep 2026 00:38:22 +0000

Introduction

For this project, I used Microsoft Excel to analyze a Jumia product dataset and build an interactive dashboard.

The aim was to understand how product prices, discounts, ratings, and customer reviews relate to product performance.

I focused on questions such as:

Do higher discounts lead to more customer reviews?
Do highly rated products receive more engagement?
Are more expensive products rated better?
Which products are performing best?
Which products may need a different pricing or marketing approach?

Since the dataset did not include sales or revenue, I used the number of reviews as a way of measuring customer engagement.

Step 1: Understanding the Dataset

The original dataset had six columns:

Product
Current price
old price
Discount
Review
Ratingd

Before doing any analysis, I first checked the data to see what needed cleaning.

Some of the issues I found were:

115 rows and 6 columns
58 blank Review values
58 blank Rating values
57 negative Review values
3 duplicate rows
1 product with a price range
a misspelled Ratingd column
prices containing KSh and commas
ratings written like 4.5 out of 5

I kept the original data unchanged so that I could always compare it with the cleaned version.

Step 2: Creating a Data Quality Audit

Before cleaning the data, I recorded the issues I found in a Data_Dictionary sheet.

I also kept a cleaning log showing what I changed and why.

Step 3: Cleaning the Data

I created a separate cleaned version of the dataset and kept the raw data unchanged.

Cleaning Product Names

I used the TRIM function to remove extra spaces from product names.

=TRIM(A2:A113)

Fixing the Headers

I renamed the columns to:

Removing Duplicates

I selected all six original columns so that only exact duplicates were removed.

After this step, I had: 112 product records

Cleaning the Prices

Some prices were written like: KSh 2,199

I used Ctrl + H (Find and Replace) to remove KSh and commas, turning values like KSh 2,199 into 2199 so Excel could treat them as numbers.

Handling the Price Range

One product had a current price of:

1620 - 1980

and an old price of:

2200 - 3200

Instead of deleting the row, I used the midpoint.

Current Price:

(1620 + 1980) / 2 = 1800

Old Price:

(2200 + 3200) / 2 = 2700

Cleaning Reviews

The review values appeared as negative numbers.

Since a review count cannot be negative, I treated the negative sign as a formatting issue and converted the values to positive numbers.

=IF(E2="","",ABS(VALUE(E2)))

I left missing review values blank.

Cleaning Ratings

Ratings were written like: 4.5 out of 5

I converted them into normal numbers using: =IF(F2="","",VALUE(SUBSTITUTE(F2," out of 5","")))

Missing ratings were also left blank.

Step 4: Validating the Cleaned Data

Before moving on to analysis, I checked that the cleaned values made sense.

For Rating: =IF(OR(F2<0,F2>5),"Check rating","OK")

For Discount: =IF(OR(D2<0,D2>1),"Check discount","OK")

For Price: =IF(B2>C2,"Check prices","OK")

After that, I converted the cleaned dataset into an Excel Table using Ctrl + T and named it:

tblProducts

Step 5: Adding New Columns

I added a few extra columns to make the analysis easier.

Discount Amount

I calculated how much money had actually been reduced from the old price.

=[@[Old Price]]-[@[Current Price]]

Rating Category

I grouped the ratings into:

Poor - below 3
Average - 3 to 4.5
Excellent - above 4.5
Missing - blank rating

Formula:

=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

Discount Category

I grouped the discounts into:

Low Discount - below 20%
Medium Discount - 20% to 40%
High Discount - above 40%

Formula:

=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

Price Category

Instead of choosing random price ranges, I used quartiles.

First quartile:

=QUARTILE.INC(tblProducts[Current Price],1)

Result: KSh 493

Third quartile:

=QUARTILE.INC(tblProducts[Current Price],3)

Result: KSh 1,669.50

I then grouped products into:

Low Price
Medium Price
High Price

Formula:

=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=[@[Price_Q1]],"Low Price",IF([@[Current Price]]<=[@[Price_Q3]],"Medium Price","High Price")))

Step 6: Measuring Customer Engagement

Because the dataset did not include sales, I used reviews as an engagement measure.

I used the 75th percentile of Review to define strong engagement.

=QUARTILE.INC(tblProducts[Review],3)

The result was: 14 reviews

So I treated products with 14 or more reviews as having strong engagement buy using the formula below

=IF([@Review]="","Missing",IF([@Review]>=Engagement_Threshold,"Strong Engagement","Lower Engagement"))

Step 7: Descriptive Analysis

I created an Analysis sheet and calculated the main KPIs.

Total Products

=ROWS(tblProducts[Product])

Result: 112

Average Current Price

=AVERAGE(tblProducts[Current Price])

Result: KSh 1,186.89

Average Old Price

=AVERAGE(tblProducts[Old Price])

Result: KSh 1,811.11

Average Discount

=AVERAGE(tblProducts[Discount])

Result: 36.78% when converted to percentage

Average Rating

=AVERAGE(tblProducts[Rating])

Result: 3.889473684

Total Reviews

=SUM(tblProducts[Review])

Result: 723

The most expensive product price

=MAX(tblProducts[Current Price])

with its name found as

=XLOOKUP(MAX(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Result: KSh 3,750

The least expensive product price
=MIN(tblProducts[Current Price]) with its name =XLOOKUP(MIN(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Result: KSh 38

Step 8: Looking at Relationships

I created three scatter plots to see whether the main variables were related.

Discount vs Reviews

The correlation was approximately: -0.136822724

This showed a very weak negative relationship.

In simple terms, larger discounts were not clearly linked to more reviews.

Rating vs Reviews

The correlation was approximately: 0.057209035

This was very close to zero, meaning highly rated products did not necessarily receive more reviews.

Price vs Rating

The correlation was approximately: 0.110090213

This showed only a very weak positive relationship between price and rating.

For each chart, I added a trendline, equation, and R-squared value.

Note that correlation does not mean causation.

Step 9: Ranking Products

I used PivotTables to compare and rank products.

I created:

Top 5 products by rating
Bottom 5 products by rating
Top 10 products by rating
Top 10 products by reviews
Top 10 products by discount
High-discount products with low ratings
High-discount products with low engagement
Products with many reviews but average ratings

Sample shown below

Step 10: Creating PivotCharts

I created PivotTables for:

Rating Category
Discount Category
Price Category
Average Rating by Price Category
Average Reviews by Discount Category
Top Products by Rating
Top Products by Reviews
Top Products by Discount

I then created PivotCharts from these tables.

For the product rankings, I used horizontal bar charts because the product names were long and easier to read that way.

Step 11: Adding Slicers

To make the dashboard interactive, I added slicers for:

Rating Category
Discount Category
Price Category

I connected the slicers to the relevant PivotTables using Report Connections.

This made it possible to filter several charts at once.

Step 12: Building the Dashboard

I brought the most important information together on one dashboard.

The top section included:

Total Products
Average Price
Average Discount
Average Rating
Total Reviews

The dashboard also included:

Top 10 Products by Rating
Top 10 Products by Reviews
Top 10 Products by Discount
Discount vs Reviews
Rating vs Reviews
Price vs Rating
Rating Category breakdown
Discount Category breakdown

I used consistent formatting for prices, percentages, ratings, and review counts so that the dashboard was easy to read.

Key Findings

Higher discounts did not automatically mean more engagement

The correlation between Discount and Review was about: -0.14

This was a very weak relationship.

Medium-discount products actually had a higher average number of reviews than high-discount products.

More reviews did not always mean better ratings

The relationship between Rating and Review was almost zero.

The 120W cordless vacuum cleaner had:

69 reviews
2.8 rating

This showed that a product can attract attention but still have weak customer satisfaction.

Expensive products were not much better rated

The relationship between Current Price and Rating was only about: 0.11

This means price alone was not a strong sign of customer satisfaction.

Large discounts did not guarantee strong performance

Some products had large discounts but still had low ratings or low engagement.

This suggests that sellers should not depend on discounting alone.

Recommendations

Based on the analysis:

Sellers should not assume that bigger discounts automatically lead to more customer engagement.
Products with many reviews but low or average ratings should be investigated for possible quality or customer-experience issues.
Products with strong ratings but low review counts may benefit from more promotion.
High-discount products with low engagement may need a different marketing or pricing strategy.
Product performance should be judged using several measures together instead of looking at only price, discount, rating, or reviews.

Limitations

The dataset did not include:

sales
revenue
listing age
seller information
product visibility

Because of this, reviews were only used as an engagement proxy.

The results show patterns in the data, but they do not prove that one variable directly caused another.

Conclusion

This project gave me practical experience turning raw Jumia product data into an interactive Excel dashboard.

I started by checking and cleaning the data, then added new fields, calculated KPIs, analyzed relationships, created PivotTables and PivotCharts, and finally brought everything together in a dashboard.

It showed me how Excel can be used not just for calculations, but also for understanding data and turning it into useful business insights.

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

Janet Kangogo — Wed, 02 Sep 2026 19:13:35 +0000

Introduction

Excel is a useful tool for working with data, but before any analysis can begin, the data needs to be in a usable state. A dataset may have blank cells, duplicate records, inconsistent entries, or values that do not follow the expected format.

Cleaning the data helps correct these issues and makes the dataset more consistent. Once the data is clean, it becomes easier to use Excel tools such as formulas, Pivot Tables, filters, and charts to analyze and understand the information.

1. Start With a Copy of the Data

Before making any changes, keep the original dataset untouched and work from a duplicate sheet.

Keeping the original data provides a reference point in case values are accidentally changed or removed.

2. Make the Data Easier to Read

Formatting makes a large dataset easier to inspect without changing the actual data.

Wrap Text

Wrap Text displays long content on multiple lines within the same cell.

AutoFit

AutoFit automatically adjusts the width of a column or height of a row according to its contents.

3. Cleaning the Data

Before analysing the data, it is important to check for issues that could affect the results. This includes missing values, inconsistent entries, duplicates, unnecessary columns, and values that do not make sense.

Missing and Inconsistent Values

Missing information may appear as an empty cell or as values such as: None, N/A, Unkown

Some of these values are not technically blank because they contain text, but they may still represent missing information.

The meaning of a value should be checked before changing it. For example, None may represent missing information in one column but be a valid response in another.

Where different entries have the same meaning, they can be standardized. For example, None, N/A can be changed to Unkown

This keeps the data consistent and makes it easier to analyse.

Handling Blank Values

How a blank value is handled depends on the type of information in the column.

A blank may be:

Left blank if the correct value is not known.
Replaced with Unknown for categorical data.
Filled with an appropriate value where its meaning is known.
Investigated further before making any changes.

For numerical columns, adding text such as Unknown should be avoided because it can interfere with calculations.

Checking for Duplicates

Duplicate records can affect totals, counts, averages, and other calculations.

It is safer to select the entire dataset when removing duplicates, as similar records may contain typos or inconsistencies.

Checking for Unusual Values

The dataset should also be checked for values that do not make sense when compared with other information in the same record.

For example:

Hiring Year: 1920
Age: 50

Both are valid numbers, but they are unlikely to make sense together.

Unusual values should be investigated rather than automatically deleted because some may be genuine.

After these checks, the dataset is more consistent and ready for validation and further analysis.

4. Filters

Once the dataset has been cleaned, filters can be used to view specific records without deleting or changing the rest of the data.

For example, the HR dataset can be filtered by:
Work Experience = 7 Years
Education Level = Bachelors
Employment Type = Permanent

Filters are useful when checking particular categories, identifying unusual records, or focusing on a smaller part of a large dataset.

5. Multilevel Sorting

Sorting arranges data in a particular order. Excel also allows sorting by more than one column.

For example, the dataset can be sorted by:

Work Experience
Education Level
Employment Type

Excel first sorts by Work Experience, then by Education Level within each department, and finally by Employment Type

This is known as multilevel sorting.

6. Text Functions

Excel provides several functions for working with text. These can be useful when cleaning names, codes, email addresses, and other text fields.

Functions always start with the equal sign i.e =

LEN() – Counts the number of characters in a cell.
TRIM() – Removes extra spaces from text.
LEFT() – Extracts characters from the left side of text.
RIGHT() – Extracts characters from the right side of text.
MID() – Extracts characters from the middle of text.
FIND() – Finds the position of a character or text within a cell.
CONCAT() – Joins text from multiple cells together.

These functions can be used individually or combined depending on how the text needs to be cleaned or changed.

7. Conditional Formatting

Conditional Formatting changes the appearance of cells when they meet certain conditions. This makes it easier to notice important or unusual values.

Common conditions include:

Greater than
Less than
Between
Top 10
Bottom 10
Above average
Below average

For example, employees who have worked for less than 18 years can be highlighted automatically.

Data Bars provide another way of comparing numerical values. Excel displays a bar inside each cell based on the size of the value.

Larger values have longer bars, making differences easier to see without creating a separate chart.

8. Statistical and Mathematical Functions

These are for carrying out basic calculations and understanding numerical data.

POWER() – Raises a number to a specified power.
SQRT() – Returns the square root of a number.
PRODUCT() – Multiplies numbers together.
MIN() – Returns the smallest value.
MAX() – Returns the largest value.
MODE() – Returns the most frequently occurring value.
MEDIAN() – Returns the middle value.
COUNT() – Counts cells containing numbers.
COUNTA() – Counts non-empty cells.
COUNTBLANK() – Counts empty cells.

These functions provide a quick way of understanding the values contained in a dataset.

9. COUNTIF() & SUMIF()

COUNTIF() and SUMIF() are useful for counting or adding values based on specific conditions in a dataset.

COUNTIF() – Counts records that meet one condition.
COUNTIFS() – Counts records that meet multiple conditions.
SUMIF() – Adds values that meet one condition.
SUMIFS() – Adds values that meet multiple conditions.

10. Working With Dates and Time

Dates are useful in data analysis because they can be used to calculate periods and identify trends.

Some common Excel date functions include:

TODAY() – Returns the current date.
NOW() – Returns the current date and time.
YEAR() – Extracts the year from a date.
MONTH() – Extracts the month from a date.
DAY() – Extracts the day from a date.
DATEDIF() – Calculates the difference between two dates.
NETWORKDAYS() – Calculates working days between two dates, excluding weekends.

11. Mean, Median, and Outliers

The mean is the average of a group of numbers, while the median is the middle value when the numbers are arranged in order.

Consider the below example:

The value 500,000 is much higher than the other values. This affects the mean because it pulls the average upwards.

The median is less affected by extremely high or low values.

Comparing the mean and median can therefore help identify data that may be skewed or contain possible outliers.

An outlier should not automatically be deleted. It may be an incorrect entry, but it could also be a genuine value that is simply different from the rest.

IMPORTANT: Data Validation

Data Validation helps control what can be entered into a cell. This reduces inconsistent entries and helps keep the dataset clean when inputted at the data collection step.

For example, if the Department column should contain only HR, IT, Finance or Unknown

a dropdown list can be created to restrict entries to these values.

Without data validation, the same department could be entered in different ways. Excel would treat these entries as different values, creating inconsistencies and adding more data cleaning tasks.

Conclusion

The main takeaway is that good analysis starts with clean data. Preparing and cleaning the data makes it easier to work with and analyse.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

Janet Kangogo — Mon, 24 Aug 2026 17:50:30 +0000

Git is used to track changes made to files in a project. The basic Git workflow has four main parts:

Working Directory → Staging Area → Commit → Push

A simple project can contain different types of files, for example:

hello.py
practice.sql
README.md

The same Git workflow can be used for all these files.

Working Directory

The working directory is where the project files are created and edited.

For example, hello.py can contain:

A SQL file can contain:

And README.md can contain:

When a file is changed, Git can detect the change.

The current state of the project can be checked using:

git status

It can show modified and untracked files, staged and unstaged changes, as well as the current branch.

The output can show something like:

Which means the file has been changed, but the change has not been staged yet.

Staging Area

The staging area is used to select the changes that should go into the next commit.

To stage hello.py:

git add hello.py

On checking git status, the output can show something like:

More than one file can also be staged:

git add hello.py practice.sql

To stage all the changes:

git add .

Note that: Not every change has to be included in the same commit.

Commit

After staging the required changes, a commit can be created:

git commit -m "Your Commit Message"

A commit records the staged changes in the local Git repository.

The message after -m describes the change.

The commit history can be checked using:

git log

This shows the commits that have already been made.

Create a repository on Github

A repository should be created on GitHub where the project would be pushed.
The GitHub repository acts as the remote repository for the project.

On GitHub, New repository or the “+” icon was selected, then a name and description were added before creating the repository.

Push

A commit is stored in the local repository. It does not automatically go to GitHub.

Push: Sends committed changes from the local repository to the remote repository, such as GitHub, using git push or git push origin main to specify the remote (origin) and branch (main).

End Result

When you refresh git hub, the repository is updated

The Full Workflow

The Git workflow can be shown as:

Working Directory > git add > Staging Area > git commit> Local Repository > git push > Remote Repository (GitHub)

Conclusion

The Git workflow shows how changes move from the working directory to GitHub. The changes are checked using git status, added using git add, committed using git commit, and then pushed using git push.