DEV Community: Alex Majale

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

Alex Majale — Sun, 06 Sep 2026 12:02:19 +0000

If you've ever wondered what actually happens between "I have a spreadsheet full of messy data" and "I have a dashboard I'd be proud to show someone," this article walks through that whole journey, step by step, using a real example.

1. Project Introduction and Objective

Online stores discount things constantly. But here's a question that's easy to assume the answer to and actually get wrong: Does a bigger discount get you more customer interest?
The goal wasn't just "make some charts." It was to build something a manager could actually open and trust because every number on it either comes from a live formula or is clearly an assumption.

2. Dataset and Business Questions

The raw data is : 116 rows, 6 columns.

Column	What it holds
Product	The product
Current price	The price you'd actually pay
old price	The original price before the discount
Discount	The advertised discount
Review	Number of customer reviews
Ratingd	rating

Business questions:

whether larger discounts are associated with more reviews
whether highly rated products attract stronger engagement
whether price and rating move together
which products perform best based on ratings and reviews
which products may need a different pricing or marketing strategy.

3. Initial Data-Quality Audit

I audited the raw data for problems. Here's what I actually found:

58 rows had no value in Review or Ratingd at all.
58 rows had negative numbers in the Review column. Reviews are a count of people, so a negative count is impossible.
3 rows were exact duplicates.
6 different product names appeared more than once, but with different prices.
1 row had a price range instead of a single value.
Prices were stored as text, with a currency prefix.
Ratings were stored as text.

4. Cleaning and Preparation Decisions

Every cleaning decision went into a documented "data dictionary"

Issue	Rows affected	Decision	Reason
Negative review counts	58 rows	Converted to positive	A review count can't be negative
Exact duplicate rows	3	Removed	They were duplicates
Duplicate product names, different prices	6	Kept as separate rows	interpreted as different types of the same product
One price stored as a range	1	Replaced with the midpoint	A single number was required for any calculation to work
Prices stored as text with currency symbols	113	Converted to numbers	Can't calculate on text
Ratings stored as `"4.5 out of 5"`	58	Extracted the numeric `4.5`	Text can't be calculated

5. Excel Formulas and Enrichment Fields

This is the part that turns "a cleaned spreadsheet" into "a dataset you can actually analyze."
Breaking down what these do:

Rating Status =IF(OR(F2<0,F2>5,ISBLANK(F2)),"Check rating","OK")
Rating must be between 0 and 5. If it isn't (or it's blank), this flags it instead of silently trusting the data.
Calculated Discount =IFERROR((C2-B2)/C2,"")
Instead of trusting the advertised discount, I recalculated it independently from the old and new prices.
Discount Check =IF(OR(D2="",K2=""),"Missing",IF(ABS(D2-K2)>2%,"Check Discount","OK")) compares the advertised discount to the calculated one, and flags anything more than 2 percentage points apart.
Rating Category =IF(F2="","Missing",IF(F2<3,"Poor",IF(F2<=4.5,"Average","Excellent"))) Rating into "Poor" (below 3), "Average" (3–4.5), or "Excellent" (above 4.5), so I can group and count products instead of comparing 113 individual decimals.

I used the same pattern for Discount Category (Low/Medium/High) =IF(D2="","Missing",IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))) and Price Category (Low/Medium/High) =IF(B2="","Missing",IF(B2<=Price_Q1,"Low Price",IF(B2<=Price_Q3,"Medium Price","High Price"))) , the price Categories are based on the dataset's own quartiles, calculated with:

Price_Q1 = QUARTILE.INC(tblProducts[Current Price], 1)   → KSh 493
Price_Q3 = QUARTILE.INC(tblProducts[Current Price], 3)   → KSh 1,669.50
Review_Q3 = QUARTILE.INC(tblProducts[Review], 3)         → 14 reviews

I stored these as named ranges so every formula in the workbook could reference Price_Q1 instead of a hardcoded number.
Engagement flags =IF(E2="","Missing",IF(E2>=Review_Q3,"Strong Engagement","Below Threshold")) so that i can group them with the dataset review threshold.

Finally, I layered on a few compound flags that combine two conditions at once:

High Discount + Low Rating =IF(OR(D2="",E2=""),"Missing",IF(AND(D2>40%,F2<3),"Flag","")) discount over 40% and rating under 3
High Discount + Low Engagement =IF(OR(D2="",E2=""),"Missing",IF(AND(D2>40%,E2<Review_Q3),"Flag","")) discount over 40% and reviews below the 75th percentile
Many Reviews + Average Rating =IF(AND(E2>=Review_Q3,F2>=3,F2<=4.5),"Flag","") heavily reviewed but only "Average," not "Excellent"

6. PivotTable and Analysis Workflow

With every product tagged and categorized, PivotTables became genuinely simple.

Here's one of the PivotTables:

PivotTable feeding a chart:

I built seven of these PivotTable/PivotChart pairs in total — rating mix, discount mix, price vs. rating, engagement vs. discount, and three "Top 10" rankings (by rating, by reviews, by discount).

Alongside the PivotTables, I ran descriptive statistics and correlation checks directly on the Cleaned Data table using AVERAGE, SUM, MAX/MIN, and CORREL.

7. Dashboard Design and Slicer Connections

Everything comes together on a single dashboard sheet: a title bar, five KPI cards across the top, and nine charts.

8. Key Findings

Here's the "Business Insights" sheet I built to summarize what the data actually showed

In plain language:

The catalog is aggressively discounted. 55.4% of products (62 of 112) are discounted more than 40%.
But bigger discounts don't buy more attention. Products discounted 20–40% average 15.3 reviews each more than the heavily-discounted group's 11.1 average, and clearly more than the lightly-discounted group's 9.5. The correlation between discount size and review count across the whole catalog is essentially flat (r ≈ -0.14).
Rating and popularity are basically unrelated. A product's star rating barely correlates with how many people reviewed it (r ≈ 0.06).
Price has only a weak relationship with rating. Higher-priced products rate slightly better on average (4.08 vs. 3.64 for the cheapest band), but the relationship is too weak to call a real trend (r ≈ 0.11).
One product is a real warning sign: a cordless vacuum cleaner has the most reviews in the entire dataset (69) but only a 2.8-star rating.

9. Business Recommendations

Based on what the data actually supports:

Don't assume "discount deeper" means "sell more." Test the 20–40% discount band deliberately it's where engagement was strongest in this dataset.
Audit high-visibility, low-rating listings first. They're seen by the most people, so any quality or accuracy issue there does the most damage.
Invest in listing quality over price cuts. Since price alone barely predicts rating, better photos, descriptions, and accurate specs may do more for perception than another 5% off.
Chase more reviews on unrated listings, not fewer discounts. Half the catalog (55 of 112 products) has no rating or review at all.

10. Limitations and Lessons Learned

Being upfront about what this analysis can't tell you is just as important as the findings themselves:

A review count isn't a sales figure. Engagement ≠ conversion, and nothing here should be read as a profit or revenue claim.
One number was a judgment call, not a fact: the single price-range row that I replaced with a midpoint.

Cleaning and documentation take longer than the "real" analysis, and that's normal. Most of the time on this workbook went into deciding what a negative review count means, what to do with a price range, and how to label the 55 products with no rating.

11. Links

[https://github.com/majalealex-ux/Jumia-Product-Performance-Dashboard]

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

Alex Majale — Sat, 29 Aug 2026 05:06:33 +0000

Introduction

We interact with data almost every day. But having data is one thing, and having clean, usable data is another. This is where Excel comes in. It can be a good starting point for cleaning, organising, and analysing data.
Excel is an electronic spreadsheet program that allows users to organize, calculate, format, and analyze data.
Data cleaning is the process of identifying and correcting errors, missing values, duplicates, and inconsistent formats in raw data sets.

Basics of Excel

The first thing you'll see once you open Excel is rows, columns, and cells.
A row runs horizontally and is labeled with numbers (1, 2, 3), while a column runs vertically and is labeled with letters (A, B, C)
A cell is the individual rectangular box formed by the intersection of a vertical column and a horizontal row.
These rows, columns, and cells are contained within a worksheet.
One or more worksheets form a workbook.
The image below contains an example of an Excel worksheet with its features;

Inside the worksheet, there is basic information I need to know, such as;

Sorting which is the process of rearranging your rows of data into a specific order based on the values in one or more columns. This includes arranging text from A to Z or Z to A, arranging numbers from smallest to largest or largest to smallest, and arranging dates and times from oldest to newest or newest to oldest.
Filtering a tool that hides rows you do not want to see so you can focus only on the data that matches your rules.
Basic formulas, where I learned that every formula has to begin with an equals sign (=). These formulas include addition, such as =1+1 or =A1+B1, and subtraction, such as =2-1 or =A1-B1.
Formatting which is the process of changing the visual appearance of data in a spreadsheet to make it easier to read and understand without changing the actual data values.

Data Cleaning

I learnt some basic data cleaning techniques such as;
Removing duplicates
Handling missing values
Removing extra spaces with TRIM()
Standardizing text with UPPER(), LOWER(),PROPER()
Fixing inconsistent dates

To understand how data cleaning works in Excel, I used a small example containing employee records. At first, the data looked normal, but a closer look revealed some errors.

Employee Name	Department	Location	Hire Date
john kamau	Finance	Nairobi	12/01/2024
Jane Wanjiku	HR	nairobi	15/01/2024
John Kamau	Finance	Nairobi	12/13/2024
brian Otieno	IT	NAIROBI	18/01/2024
Mary Akinyi	HR	Kisumu	20/01/2024

1. Removing duplicates

The first issue I noticed was that John Kamau appeared twice with the same information. Duplicate records can affect analysis by making some results appear higher than they actually are.

I used Excel's Conditional formatting feature to identify and remove the repeated record.

2. Standardising text

The Location column also contained different versions of the same location:

Nairobi
nairobi
NAIROBI

Although they refer to the same place, Excel can treat them as different values when analysing the data.

I standardised the entries so that they followed the same format.

3. Checking dates

The Hire Date column also needs attention. Dates may appear in different formats depending on how the data was entered. I checked that the values were recognised as actual dates and then applied a consistent date format. Such as correcting the 12/13/2024 to 12/01/2024.

4. Fixing the Word format

The employee name format is in improper format with words such as john kamau in lower cases instead of proper cases. So i used the formula =PROPER(A2) to change the format to John Kamau

Conclusion

I initially thought cleaning data meant simply removing blanks and duplicates. I learned that consistency is equally important, especially with dates, categories, and text.

My First GitHub Project: From a Local Folder to Github Using Git and SSH

Alex Majale — Thu, 20 Aug 2026 10:09:58 +0000

Introduction

When I first heard the words Git and GitHub, I actually thought that git was the short form of GitHub, only to realise that they are two different things.
Git is free, open-source version control software used by programmers to track code changes and collaborate. Git is a version control system that runs on my computer. GitHub, on the other hand, is a cloud-based web platform where computer programmers store, share, and work together on software code.
This article explains how I created a local repository and pushed the project to GitHub using Git and SSH.

What Requirements I Needed

A GitHub account
Git installed on the Computer
An SSH Key configured with GitHub
Visual Studio Code installed on the Computer

How I Did It

I started by opening GitBash on my desktop, then typed ls to see what my desktop contained.
The ls command means list, and it is used to list the contents of a directory.
Next, I created a folder called My First Project.
using mkdir "My First Project"
mkdirmeans make directory and is used to create a new folder.
I then used cd "My First Project" to enter the folder.
cd means change directory and is used to open folders.
While in the My First Project folder, I created another folder called Data.
Using mkdir Data
Then I created a README file using touch README.md
touch is used to create an empty file
So to add information to the README file I used
echo "# My First Project"> README.md
echo means to output or display text, data, or variable values directly to a screen, terminal, or log file.
The # is used to show that it is a heading.
I then generated practice data and copied it into the Data folder
I then used echo "This is practice data on Hardware sales">> README.md
This time I used >> so that I could not overwrite the first line in the README file.
I then used cat README.md to see the contents of the README file.
cat is a standard command used to read, print, combine, and create text files directly from the terminal.
I then confirmed the contents of the Data folder using ls Data
I keyed in git init
git init stands for "Git Initialize," and it is the command used to create a brand-new, empty Git repository.
Then git status
git status shows the current state of your repository's working directory and staging area. It tells you which files have been changed, which ones are prepared for your next save point (commit), and which ones Git is completely ignoring.
Then ran git add .
git add is the command used to save your code changes into a temporary preparation zone.
Then I ran git commit -m "Add My First Project"
git commit is a save point, or snapshot, of your project's files at a specific moment in time.
I then ran git log
git log is a command-line tool used to view the history of changes (commits) made to a code repository.
Then git remote add origin[SSH link]
The SSH link is found on GitHub, where you copy it from the repository you created.
Then I ran git remote -v
Finally, I ran git push -u origin main
git push uploads your local repository commits to a remote repository.
After this, the project was uploaded to my GitHub.

Conclusion

I was able to learn about the difference between git and GitHub, and along the way I managed to understand the process of pushing a project from a local repository to an online repository using Git, GitHub, and SSH.