DEV Community: Antonina Wambui

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

Antonina Wambui — Wed, 09 Sep 2026 16:56:45 +0000

Introduction

One thing I have started learning during my journey into data analytics is that data rarely arrives ready for analysis.

After working through Excel as part of the LuxDevHQ Data Science and Analytics, I wanted to put the skills I had learned into practice. For this project, I worked with dataset containing product listing from Jumia and challenged myself to go beyond simply creating charts.

The goal was to take messy e-commerce data, understand what was wrong with it clean and transform it, analyze the patterns, and finally present the result through an interactive Excel dashboard.

The dataset contained 115 product records with information about products, prices, discounts, reviews and ratings. At first glance, it looked fairly simple. However, after inspecting it more closely, I discovered missing values, duplicate records, negative review counts, inconsistent data formats and a product with prices recorded as ranges.

That made the project much more interesting.

Instead of jumping straight into visualization, I followed a complete analytics workflow:
Raw Data → Data Audit → Data Cleaning → Data Transformation → Analysis → Visualization → Dashboard → Insights

This article walks through that process and some of the lessons I learned along the way.

Understanding the Dataset
The original dataset contained six main columns:

Product
Current Price
Old Price
Discount
Review
Ratingd

The Ratingd column was simply a naming error, so I standardized it to Rating.

One One important limitation became clear very early: the dataset did not contain actual sales information.

Because of this, I could not honestly claim that a product with more reviews was necessarily selling more.

Instead, I treated the number of reviews are not the same thing as sales. A product could have more reviews because it has been available for a longer period, for example.

Question I wanted the Data to Answer
With that limitation in mind, I focused my analysis around questions such as:

Do longer discounts appear to attract more customer engagement?
Is there a relationship between product ratings and reviews?
Does product price appear to influence ratings?
Which product price appears to influence ratings?
Are there products that deserve further investigation?
What patterns can be communicated effectively through an Excel dashboard?

Step 1: Auditing the Raw Data
Before cleaning anything, I wanted to understand the condition of the dataset.

The initial audit revealed several interesting problems:

Data Quality Issue	Finding
Total records	115
Original columns	6
Missing Reviews	58
Missing Ratings	58
Exact duplicate rows	3
Repeated product names	6
Negative review values	57
Price ranges	1
Invalid rating range	None
Invalid discount range	None

One of the biggest red flags was the Review column.

There were 57 non-blank review values and all 57 were negative.

That immediately raised a question:
How can a product have a negative number of review?

Obviously, a review count cannot realistically be negative. Since the issue affected every non-blank value rather than only a few records, I treated it as a systematic data-quality problem that needed investigation.

This was a good reminder that data cleaning isn't just about fixing errors; it is about understanding why something looks wrong before changing it.

Step 2: Cleaning the Data
Once I understood the problems, I started cleaning the dataset.
I tried to make each cleaning decision based on the information available rather than simply forcing the data to look perfect.

Correcting Negative Review
For the negative review counts, I created a helper column and used:

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

The ABS () function converts the negative values into positive values while the IF () statement ensures that blank cells remain blank.

After verifying the results, I converted the corrected values to static values.
This gave me realistic positive review counts without turning missing information into fake data.

Handling Missing Values
Both the review and rating columns contained 58 blank cells.
I decided not to replace these blanks with zero.

Why?
Because:

Blank ≠ Zero

A blank review count means the review information was not captured.

A zero means the product actually had zero reviews.

Those two situations carry different meanings.

Replacing all blanks with zero could therefore distort averages, rankings and other calculations.
So, in this case, leaving the missing values untouched was the more honest choice.

Step 3: Dealing With the Price Range
One particularly interesting record belonged to a 1/2/3 Seater Elastic Sofa Cover.

It's prices were recorded as ranges:

Current Price: Ksh 1,620-1980
Old Price: Ksh 2,200-3,200

I considered three possible approaches:

Use the minimum price
Remove the record
Calculate the midpoint

I chose the midpoint approach.

This allowed me to retain the product instead of throwing away an otherwise useful record.

The original price range was retained separately so that the transformation could be traced.

However, this decision also produced an interesting result.

Using the midpoint prices gave me a calculated discount of approximately 33%, while the advertised discount in the dataset was 38%.

Rather than changing one value to make them agree, I kept both.

Step 4: Removing duplicates
The dataset contained three completely identical rows.
Since every field matched, I removed those records as exact duplicates.

However, I also found six products with repeated product names.
I did not automatically remove those.

Why?
Because the repeated product names had differences in things such as price discount or review count. Since Jumia is a marketplace with multiple sellers, these could represent separate listings for similar products.

Removing them simply because the product names matched could therefore introduce another form of data error.

So my rule was:
Exact duplicate → Remove

Same product name but different listing information → Keep

Step 5: Standardizing the Dataset
After dealing with the major data-quality issues, I standardized the remaining fields.

Some of the changes included:

Renaming Ratingd to Rating
Removing "out of 5" from rating values
Converting ratings into numerical values
Converting prices from text into numbers
Standardizing column names
Formatting prices as Kenyan Shillings
Applying consistent number formatting.

The cleaned data was then structured as Excel table so that formulas, PivotTables and charts could work more efficiently.

Creating New Analytical Fields
After cleaning the data, I created additional fields to make the dataset more useful for analysis.

Discount Analysis
I calculated the discount amount by finding the difference between the old and current prices. I also calculated the percentage discount and kept it alongside the original advertised discount.

This allowed me to compare the seller's advertised discount with the discount calculated from the actual prices.

Rating Categories
To make ratings easier to analyze, I grouped them into:

Poor: Below 3
Average: 3-4.5
Excellent: Above 4.5

Ratings between 4.1 and 4.5 were included in the Average category as a documented working assumption.

Discount Categories
Discounts were grouped into:

Low: Below 20%
Medium: 20%- 40%
High: 40%

Price Categories
Instead of choosing price ranges arbitrarily, I used quartiles from the dataset.

Using QUARTILE.INC(), I obtained

Q1: Ksh 493
Q3: Ksh 1,669.50

These thresholds were then used to classify products into Low, Medium and High price categories.

Engagement Strength
Since review count was being used as a proxy foe customer engagement, I used the 75th percentile as the threshold.

The Review Q3 was 13 reviews, so products with 13 or more reviews were classified as having strong engagement.

Data Status and Combination Flags
I also created a Data Status field to identify products as:

Complete
Missing Rating
Missing Review
Missing Both

Finally, I created four combination flags to highlight products requiring further attention:

High discount + Low Rating
High discount + Low Engagement
Many Reviews + Average Rating
Strong Engagement + Excellent Rating

Where the required data was missing, the result was marked "missing" rather than making an assumption.

Analysis Using PivotTables and Excel Functions
With the dataset cleaned and enriched, I moved on to analysis using PivotTables, PivotCharts, formulas, FILTER(), correlation and scatter plots.

I focused on three relationships:

Discount vs. Reviews
Rating vs. Reviews
Current Price vs. Rating

Because Review and Rating contained missing values, I used FILTER () to create helper ranges containing only complete pairs before calculating correlations.

The results were:

Relationship	Pearson r	R²
Discount vs. Reviews	-0.111	0.002
Rating vs. Reviews	0.043	0.002
Current Price vs. Rating	0.110	0.012

All three relationships were very weak.

For example, the -0.111 correlation between discount and reviews is very close to zero, while the R² values show that these variables explained very little of the variation in one another.

This doesn't mean that price, ratings or discounts never influence customer behavior. It simply means that this dataset did not show a strong relationship between them.

Product Ranking
I also created PivotTables to identify:

Top 10 products by Rating
Bottom 10 products by Rating
Top 10 products by Discount

For rating rankings, products without ratings were excluded. Where products has the same rating, review count was used as the tie-breaker.

This gave the rankings a consistent and transparent approach.

Building the Interactive Dashboard
After completing the analysis, I brought the most important findings together into a single-screen interactive dashboard.

The dashboard contains:
KPI Cards

Total Products
Average Price
Average Discount
Average Rating
Total Reviews

Products Performance

Top 10 by Rating
Top 10 by Reviews
Top 10 by Discount

Relationship Analysis

Discount vs. Reviews
Rating vs. Reviews
Price vs. Rating The scatter plots include trendlines and R² values to help interpret the relationship.

Category Analysis
I also included charts showing by the distribution of products by:

Rating Category
Discount Category

Interactive Slicers
To make the dashboard easier to explore, I added slicers for:

Rating Category
Discount Category
Price Category

These allow users to filter the dashboard and explore different groups of products interactively.

Key Findings
1. Bigger Discounts Did Not Mean More Engagement
The correlation between discount and reviews was -0.111, indicating an extremely weak relationship.

In this dataset, products with larger discounts did not necessarily receive more reviews.

2. Price Was Barely Related to Rating
The correlation between current price and rating was 0.110, also a very weak relationship.

This suggests that higher-priced products were not necessarily rated better.

3. Some Highly Discounted Products Still Had Weak Performance
Some products combined high discounts with low ratings or low engagement.

These products may require more than simply another price reduction. Their product description, images, listing quality or customer expectations could be worth investigating.

4. Some Products Had Strong Engagement and Excellent Ratings
I also identified five products with both strong engagement and excellent ratings.

These products could provide useful examples for understanding what appears to work well, although the available data isn't enough to explain exactly why.

Recommendations
Based on the analysis, I would recommend:
Don't rely on discounts alone to drive engagement. Sellers could also improve product images, descriptions and overall listing quality.

Investigate highly discounted products with weak ratings or engagement before offering even larger discounts.

Study high-performing products with strong engagement and excellent ratings to identify patterns worth replicating.

Interpret the results carefully. Correlation shows relationships, but it does not prove that one variable causes another.

Limitations
The analysis had several limitations.

The most important was the lack of sales, revenue, units sold, and listing-age data. Therefore, review count was only used as a proxy for engagement and should not be treated as actual sales performance.

Other limitations included:

One product had a price range that required midpoint estimation.
Many products had missing ratings and reviews.
The rating categories included a working assumption for ratings between 4.1 and 4.5.
The dataset contained only 115 original records.
Correlation does not imply causation.

These limitations are important because they define how far the findings can reasonably be generalized.

What I Learned
The biggest lesson from this project was that data analysis starts before the charts.

What initially looked like a simple dataset turned out to contain several issues that could have affected the analysis if they had gone unnoticed.

I learned to question unusual values, handle missing information carefully, document assumptions, and avoid making changes without understanding the impact.

I also got to experience the complete analytics workflow:

Data Auditing → Cleaning → Transformation → Analysis → Visualization → Dashboard → Insights

Most importantly, this project showed me that Excel can be much more than a spreadsheet. With the right approach, it can be used to turn messy data into meaningful information and communicate insights through an interactive dashboard.

And honestly, seeing a few raw columns turn into a complete analytical dashboard was one of my favorite parts of this project.

I'm really enjoying this data analytics journey.

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

Antonina Wambui — Wed, 09 Sep 2026 09:31:35 +0000

Introduction

When I first started learning data analytics, I thought working with data was mainly about creating charts, finding patterns and presenting insights.
I quickly realised there is an important step that comes before all that: making sure the data is actually ready to be analysed.

Raw datasets can contain missing values, duplicate records, inconsistent text, incorrect data types, and formatting problems. If these issues are ignored, they can affect the results of the analysis and eventually lead to misleading conclusions.

During my Excel training at LuxDevHQ, I was introduced to some of the tools and techniques Excel provides for preparing data for analysis. This included sorting and filtering, formatting, handling missing values, removing duplicates and text functions such as TRIM, PROPER and CONCAT.

For my practice, I worked with a synthetic dataset that allowed me to simulate a real-world data-cleaning task. Rather than jumping into analysis, I focused on understanding the dataset first and gradually transforming it into a cleaner and more consistent version.

In this article, I'll share my process, the Excel tools I used, and some of the lessons I picked up along the way.

1. Starting With the Raw Dataset

The first step in any data analysis project is understanding what you're working with.
I imported my dataset into Excel and took some time to look through the columns and rows before making any changes.

At this stage, I wasn't trying to fix anything yet. I wanted to understand:

What information does each column contain?
Which columns contain numbers?
Which contain text?
Are there dates?
Are there missing values?
Are there values that look inconsistent?
Could any columns have duplicates?

This simple inspection helped me realise that data cleaning shouldn't begin with immediately changing things. You first need to understand what the data represents.

2. Creating a Safe Copy

One thing I learned early was the importance of protecting the original dataset.

Instead of working directly on my original data, I created a duplicate worksheet and used the copy for my cleaning process.
This gave me a backup that I could return to whenever I made a mistake.

It might seem like a small step, but when you're experimenting with Excel for the first time, it's easy to accidentally delete a column, change a value or apply formatting to the wrong range.
Having an untouched copy made the process much safer.

3. Making the Dataset Easier to Work With

Before looking for errors, I first made the spreadsheet easier to read.
I adjusted the column widths using Excel's AutoFit feature so that the values and column headings were visible.

I also converted the dataset into an Excel table.
This was useful because tables make it easier to:

Filter information
Sort records
Navigate through large datasets
Keep formatting consistent
Work with formulas

I then enabled filters on the columns so I could inspect specific categories instead of manually going through every row.

For example, filtering a column allowed me to quickly identify blank cells or check whether the same category had been entered in different ways.

4. Checking Data Types
One of the most interesting things I learned was that how a value looks isn't always the same as how Excel understands it.

A column containing numbers doesn't necessarily mean it should be treated as a numerical field.

For example, an ID such as:

10001

may look like a number, but if it is simply identifying a transaction or customer, there is no reason to calculate an average or total from it.

The same applies to phone numbers.

I therefore checked my columns and made sure they were using appropriate formats.

Some of the common data types I worked with included:

Data	Appropriate Format
Transaction/Customer ID	Text
Names	Text
Phone Numbers	Text
Dates	Date
Quantity	Number
Prices/Revenue	Currency or Number
Categories	Text

This was a good reminder that data types should be based on what the data means, not just what it looks like.

5. Looking for Missing Data
Next, I checked the dataset for missing values.
Missing data is not automatically an error. Sometimes a value is genuinely unavailable.

The important thing is deciding what to do with the missing value based on the context.

For example, if a category such as location was missing, I could use a value such as "Unknown" where appropriate.

However, I wouldn't randomly replace a missing numerical value with zero.

A blank amount and an amount of zero do not necessarily mean the same thing.

This made me realise that data cleaning isn't simply about making every cell look complete. It's about making appropriate decisions about what each value represents.

6. Finding Duplicates
Another important check was identifying duplicate records.

I used Excel's
Data → Remove Duplicates
feature to check whether the dataset contained repeated records.

Even when duplicates aren't found performing the check is still useful.
It confirms that you've considered one of the common problems that can affect analysis.

For example, if the same transaction appeared twice and I calculated total revenue without noticing it the final result could be higher than the actual revenue.

This showed me why data validation should happen before analysis.

7. Cleaning Inconsistent Text
Text data can look simple, but it can create some interesting problems.

For example, Excel may treat these as different values:

Nairobi
nairobi
NAIROBI

To a person, they all appear to mean the same thing. To a computer, however, differences in formatting or extra spaces can affect how values are grouped and analysed.

This is where some of Excel's text functions became useful.

TRIM
I used TRIM to remove unnecessary spaces from text.
=TRIM(A2)

This is particularly useful when data has been copied from another source and contains unwanted space.

PROPER
I also explored PROPER for standardising names.
=PROPER(A2)

For example:

JOHN KAMAU

can become:

John Kamau

CONCAT
Another function I practised was CONCAT,which can be used to join pieces of text together.

=CONCAT(A2,B2)

These functions showed me that Excel isn't only useful for calculations. It can also be used to transform and standardise text.

8. Using Find and Replace
Not every cleaning problem requires a formula.

Excel's Find and Replace feature can be much quicker when dealing with specific inconsitencies.

The shortcut:
Ctrl + H
Opens Find and Replace

For example, if one category had been entered several times incorrectly, I could search for the incorrect version and replace it with the correct one.

This was one of those small Excel features that I initially overlooked but found surprisingly useful during the cleaning process.

9. Helper Columns: A Simple but Useful Technique
Something else I learned was the use of helper columns.

Instead of changing the original values immediately, I could create a temporary column containing a formula such as:
=TRIM(A2)

Then I could fill the formula down the dataset, check the result and, once satisfied, copy the result and use Paste Special → Values.

This allowed me to compare the original values with the cleaned ones before replacing anything.

For a beginner, I found this approach much less intimidating because I could see exactly what the formula was doing.

10. What This Exercise Taught Me
This exercise changed the way I look at spreadsheets.
Before, I thought cleaning data mainly meant removing duplicates and filling blank cells.
Now I understand that it involves several different decisions, including:

Understanding → Checking → Cleaning → Validating → Preparing

I also learned that there isn't always one correct way to clen a dataset.

The right approach depends on:

The type of data
What each column represents
Why values are missing
The type of inconsistency
What the data will eventually be used for

For example, a missing text value might reasonably be labelled "Unknown", while a missing financial value may need further investigation rather than simply being replaced with zero.

11. From Cleaning to Analysis
The biggest lesson for me was that data cleaning is part of data analysis, not something separate from it.

A dashboard may look beautiful, but if the data behind it contains errors, the visualization can still communicate the wrong story.

The process therefore becomes:
Raw Data → Cleaning → Validation → Analysis → Visualization → Insights

Excel can support several of these stages, which makes it a useful starting point for someone learning data analytics.

Conclusion
My first hands-on experience with Excel data cleaning gave me a better understanding of what happens before the charts and dashboards are created.

I learned how to inspect a dataset, protect the original data, work with tables, check data types, identify missing values and duplicates, standardise text and use Excel functions such as TRIM, PROPER, and CONCAT

More importantly, I learned that cleaning data isn't about making a spreadsheet look perfect. It's about making sure the information is consistent, meaningful and reliable enough to support analysis.

I'm still at the beginning of my data analytics journey, but this exercise has given me a stronger foundation in Excel and a better appreciation for the work that happens behind the scenes before we can confidently say:

"Let's analyse the data"

My First GitHub Project

Antonina Wambui — Fri, 21 Aug 2026 12:06:11 +0000

Introduction

When working on a project, you may make many changes to your files. It can become difficult to keep track of what you changed. Git helps you solve this by allowing you to record your changes and keep a history of your work. GitHub provides a place where the project can be stored online.

What is Git?

Git is a tool used to keep track of changes made to files in a project. It helps you have different versions of your work so that you can see how your project has changed over time. This can be useful when working on a project because you can keep track of your progress and go back to an earlier version if needed.

What is GitHub?

GitHub is the online platform where you can store and share that Git project.

What is a Repository?

A (repo) is a place where your project is stored and git keeps track of it's history.

Creating a Local Folder

The first step is to create a folder on my computer where I will keep my project in my files.
This is called a local folder because it is stored in my computer.

For example, I can create a folder called my-first-project. I can then open Git Bash and move into the folderusing the cd command.

mkdir my-first-project
cd my-first-project

The **mkdir** command is used to create a new folder, while the **cd** command is used to move into that folder.

So local folder - is basically my project's home on my computer.
You create folder first, then git comes later when you

bash
git init

so the order is

Create folder
Enter folder
Git init
Git starts tracking the project

Staging with git add

Staging is the process of preparing changes before a commit.

After creating or changing a file, I need to tell git which changes I want to include in my next commit.
This is done by using the git add command.

Example, I can use

git add .

The . means that Git should stage all the changes in the current folder.

Also can stage one specific file by using its name:
Bash
git add README.md

After using git add, the changes are placed in the staging area and are ready to be committed.

**Committing with git commit**
After staging my changes, the next step is to commit them. 
A **commit** is like a saving version or checkpoint of my project. It allows Git to keep a record of the changes I have made.

To create commit, I use the following command

bash
git commit -m "Initial commit"

the -m allows me to add a message describing what I have committed. The message should briefly explain what the changes are
For example
Bash
git commit -m "Add README file"

What is SSH

SSH stands for Secure Shell.
It is a secure way of connecting my computer to git hub. In Git, SSH can be used to connect my local repository to a GitHub repository.
It uses SSH keys to verify the connection between my computer and GitHub.

Once the connection is set up you can use commands such as
Bash
git push
to send your GitHub securely.

Connecting the project to GitHub

After creating a repository on GitHub, I need to connect it to the project on my computer. This allows Git to know where my project should be sent when i want to upload my work.
I can connect my local repository to GitHub by using the git remote add command. Example:


bash
git remote add origin git@github.com:ninadavid4/my-first-project.git
Here, **origin** is the name given to the GitHub repository. The SSH address after origin tells git where the remote repository is located.
I can check if the connection has been added by using 
Bash
git remote -v
This shows the GitHub repository connected to my local project.

Then later, when you use:
Bash 
git push

Git knows where to send your commits.

##Conclusion
In this project, I learned how to take a project from a local folder on my computer and connect it GitHub using Git and SSH. I learned how to create a Git repository, stage changes using `git add`, save them using `git commit`, and connect the project to a remote GitHub repository. I also learned that SSH provides a secure way for my computer to communicate with GitHub.