DEV Community: Mohammed Swaleh

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

Mohammed Swaleh — Mon, 07 Sep 2026 08:35:23 +0000

Project Introduction and Objective

E-commerce platforms thrive on data. Sellers and marketplaces like Jumia need to understand how pricing, discounts, and customer feedback influence product performance. This project set out to build an interactive Excel dashboard that transforms raw Jumia product data into actionable insights.

The goal was not just to create charts, but to audit, clean, analyze, and visualize the data in a way that supports evidence-based recommendations for sellers.

Dataset and Business Questions

The dataset contained product names, current and old prices, advertised discounts, customer reviews, and ratings. Reviews were used as a proxy for engagement, since sales volumes were not available.

Key business questions:

Do larger discounts attract more reviews?
Do highly rated products show stronger engagement?
Is there a relationship between price and rating?
Which products perform best, and which need pricing or marketing adjustments?

Initial Data-Quality Audit

Before cleaning, the dataset revealed several issues:

Misspelled headers (e.g., Ratingd instead of Rating)
Negative review counts
Blank values in reviews and ratings
Prices expressed as ranges
Duplicate product rows

These anomalies were documented in a Data Dictionary worksheet to ensure transparency.

Cleaning and Preparation Decisions

Data cleaning followed clear, traceable rules:

Prices: stripped of “KSh” and commas, converted to currency (KES).
Discounts: converted to decimals.
Reviews: negative signs treated as scraping artifacts, converted to absolute values.
Ratings: standardized to decimals, blanks left as missing.
Price ranges: replaced with midpoints, while preserving original text for auditability.
Duplicates: removed only when all fields matched.

PivotTable and Analysis Workflow

PivotTables powered the analysis:

Rating Mix → distribution of Poor, Average, Excellent products
Discount Mix → proportion of Low, Medium, High discounts
Price vs Rating → average rating by price category
Engagement by Discount → reviews by discount category
Top Products → ranked by rating, reviews, and discount

Scatter plots with trendlines and correlation coefficients tested relationships between:

Discounts vs Reviews
Ratings vs Reviews
Price vs Rating

Dashboard Design and Slicer Connections

The dashboard was designed for single-screen readability:

KPIs row: total products, average price, average discount, average rating, total reviews
Ranked tables: top products by rating, reviews, and discount
Scatter plots: discount vs reviews, rating vs reviews, price vs rating
Mix charts: rating and discount distributions
Slicers: interactive filters for rating, discount, and price categories

Consistent formatting ensured clarity:

KES for prices
Decimals for discounts and ratings
Thousands separators for reviews

Key Findings

1. Price vs Rating

Products priced between KSh 1600–1999 show the highest average rating (0.87).
Lower-priced items (KSh 400–799) also perform well (0.83).
Mid-range prices dip slightly, showing weaker ratings. ➡️ Both affordable and moderately premium products attract better ratings.

2. Engagement by Discount

Engagement peaks in the 0.2–0.3 discount band (~18 reviews).
Very high discounts (>0.5) show lower engagement (~7 reviews). ➡️ Moderate discounts are the sweet spot for driving customer interaction.

3. Top Products by Rating

Near-perfect ratings (0.96):
- LASA Folding Table Serving Stand
- 40cm Gold DIY Acrylic Wall Sticker Clock
- Portable Home Small Air Humidifier

4. Top Products by Reviews

120W Cordless Vacuum Cleaner leads with 69 reviews but has a low rating (0.56).
Other high-engagement products:
- 137 Pieces Cake Decorating Tool Set (55 reviews)
- Electronic Digital Vernier Caliper (49 reviews)
- 3D Waterproof EVA Shower Curtain (44 reviews) ➡️ High engagement does not always align with high ratings.

5. Top Products by Discount

6-in-1 Bottle Can Opener tops with a 64% discount.
Heavy discounting concentrated in low-cost household goods. ➡️ Sellers use price cuts to stimulate demand in this category.

Correlation Analysis

Price vs Rating: Weak positive correlation (0.191426). Higher prices show a slight tendency toward better ratings, but the link is weak.

Ratings vs Reviews: Weak positive correlation (0.14671). More reviews loosely align with higher ratings, but the relationship is minimal.

Discount vs Reviews: Weak negative correlation (-0.13682). Larger discounts are slightly associated with fewer reviews.

➡️ Overall, none of the variables strongly predict the others.

Business Recommendations

Test discount bands to optimize engagement without eroding margins.
Improve listing content for products with visibility but average ratings.
Investigate quality issues for highly discounted but poorly rated items.
Promote products that combine excellent ratings with strong engagement.

Limitations and Lessons Learned

Reviews are a proxy for engagement, not sales.
Missing values were left blank rather than imputed.
Price ranges required assumptions (midpoints).
Correlation does not imply causation — discounts may coincide with other factors like product age or visibility.

Dashboard Screenshot

Jumia Product Dashboard Screenshot

Conclusion

This project demonstrates how Excel can be used not just for reporting, but for data-driven decision-making in e-commerce. By combining careful cleaning, structured analysis, and interactive dashboards, sellers gain practical insights into how pricing and customer feedback shape product performance.

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

Mohammed Swaleh — Sat, 29 Aug 2026 21:14:53 +0000

Excel Data Cleaning & Analysis Guide

1. Introduction

Excel is more than a spreadsheet—it’s a powerful data-analysis tool. Analysts use it to inspect, validate, filter, summarize, and prepare raw data before deeper analysis.

The quality of insights depends on the quality of data. That’s why data cleaning is the foundation of effective analysis.

This guide demonstrates Week 1 Excel concepts using an employee dataset with fields like employee IDs, names, departments, gender, hire dates, salaries, education level, and performance scores. The raw file contains common issues: inconsistent capitalization, blanks, duplicates, varying department names, and misformatted dates/currency.

By applying Excel’s formatting tools, text functions, filters, conditional formatting, numerical functions, and date functions, we transform messy data into an analysis-ready dataset.

2. Why Data Cleaning Matters

Garbage in, garbage out: dashboards or predictions are misleading if data is flawed.
Standardization makes datasets transparent, usable, and valuable.
Examples:
- “IT”, “I.T.”, and “Information Tech” → treated as different departments unless standardized.
- Duplicate employee IDs → inflate totals.
- Blank performance scores → missing information.
- Dates saved as text → unreliable for tenure calculations.

Best practice: keep raw data separate, create a cleaned worksheet, and document your cleaning logic.

3. Dataset & Initial Inspection

Dataset size: 877 employee rows (with duplicates).
Issues: inconsistent formats in IDs, names, departments, salaries, and dates.
First steps:
- Autofit Column Width for readability.
- Freeze top row for easier navigation.
- Apply a structured cleaning process.

3.1 Standardizing Employee IDs

Remove emp- prefix for uniform IDs.
Use Conditional Formatting to highlight duplicates.
Action:
- Delete exact duplicates.
- Retain one ID if records differ.
Result: reduced dataset to 870+ entries.

3.2 Cleaning Names

Apply =PROPER(text) → converts names to proper case.
Example: john → John.
Replace raw columns with corrected values.

3.3 Standardizing Departments

Filter and rename department names for consistency.
Eliminates spelling variations.

3.4 Formatting Salaries & Bonuses

Convert to KES currency format.
Sort values for comparison.
Replace other currencies with standardized KES.

3.5 Dates & Employment Details

Reformat Hire Dates consistently.
Sort newest → oldest for recruitment timeline.
Standardize categorical fields: Gender, Performance Score, Marital Status, Education Level, Employee Type, Office Location, Remote Work Status.
Standardize numerical fields: Experience, Project Count, Promotion Year, Training Hours, Feedback Score.

3.6 Filtering Data

Filters allow quick inspection:

Employees in IT department.
Female employees only.
Salaries > 80,000.
Blank performance scores.

Use Number Filters for thresholds (e.g., top 10 salaries).

4. Identifying Data-Quality Issues

4.1 Conditional Formatting

Highlights problems without deleting data:

Duplicate values.
Blank cells.
Dates before today.
Values above/below thresholds.

Example: Employee ID 10407 appears twice → verify before deleting.

4.2 Find & Replace (Ctrl + H)

Quick fixes for repeated issues:

Replace “Finanace” → Finance.
Replace “N/A” → blank.
Replace unwanted characters in IDs.

Use carefully—preview matches before replacing.

5. Cleaning Text with Functions

Function	Purpose	Example
`=PROPER(text)`	Capitalizes first letter	`aNNa mWANGI` → Anna Mwangi
`=LOWER(text)`	Converts to lowercase	`JAMES` → james
`=UPPER(text)`	Converts to uppercase	`it` → IT

5.1 Removing Extra Spaces

=TRIM(A2) → removes leading, trailing, and repeated spaces.

5.2 Measuring & Extracting Text

=LEN(text) → counts characters.
=LEFT(text,n) → extracts from left.
=RIGHT(text,n) → extracts from right.
=MID(text,start,n) → extracts middle.
=CONCAT(text1,text2,...) → joins text.

6. Numerical & Statistical Functions

6.1 Arithmetic Operators

+ Addition
- Subtraction
* Multiplication
/ Division
^ Exponent

Example:

5% salary increase → =G2*0.05
New salary → =G2+(G2*0.05)

6.2 Aggregate Functions

=SUM(range) → total
=AVERAGE(range) → mean
=MEDIAN(range) → middle value
=MODE(range) → most frequent
=MIN(range) → lowest
=MAX(range) → highest

7. Counting & Conditional Analysis

7.1 Count Functions

COUNT → numeric cells only.
COUNTA → nonblank cells.
COUNTBLANK → empty cells.

7.2 Conditional Counting

COUNTIF(range,"IT") → IT employees.
COUNTIFS(range1,"IT",range2,"Female") → Female IT employees.

7.3 Conditional Aggregation

SUMIF → total salaries by condition.
SUMIFS → multiple conditions.
AVERAGEIF → average by condition.
AVERAGEIFS → average with multiple conditions.

8. Dates & Time-Based Cleaning

=TODAY() → current date.
=NOW() → current date & time.
=YEAR(F2) → extract year.
=MONTH(F2) → extract month.
=DAY(F2) → extract day.

8.1 DATEDIF

Tenure in days → =DATEDIF(F2,TODAY(),"d")
Months → "m"
Years → "y"

8.2 NETWORKDAYS

Working days between dates → =NETWORKDAYS(F2,TODAY())

9. Recommended Cleaning Workflow

Preserve raw data.
Copy into cleaned worksheet.
Inspect with filters & conditional formatting.
Identify duplicates visually.
Standardize text with TRIM, PROPER, LOWER, UPPER.
Validate text with LEN, LEFT, RIGHT, MID, CONCAT.
Check completeness with COUNT functions.
Summarize with COUNTIFS, SUMIFS, AVERAGEIFS.
Use date functions for tenure/workdays.
Review, document assumptions, then analyze.

Conclusion

Data cleaning must happen before interpretation.

Conditional formatting → detect issues.
Filters → inspect subsets.
Find & Replace → correct repeated values.
Text functions → standardize labels.
Aggregate functions → summarize numbers.
Conditional functions → reveal group patterns.
Date functions → calculate time-based insights.

By following a structured workflow, analysts ensure datasets are consistent, reliable, and ready for advanced analysis like PivotTables, dashboards, and statistical modeling.

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

Mohammed Swaleh — Sat, 22 Aug 2026 17:52:45 +0000

Introduction

I have always used the term Git, Github and Git bash interchangeably, thinking they refer to the same thing, but I have come to learn and understand that GitHub is an online platform on the internet where developers store and share their projects, or repositories. A repository, normally called a repo, is a project being tracked by Git. Git is a version control system that runs on the local computer and can be used to track checkpoints created in a project. Therefore, Git is the tool used to track changes in a project, while SSH, which stands for Secure Shell, provides a secure way to connect your git running on your computer to GitHub on the internet. _ Git Bash _ is a tool or an application that can be used to run Git commands

This guide explains how to upload a project from a local folder on your computer to GitHub.

1. Create a GitHub Account

Go to (https://github.com/) and sign up for an account if you do not already have one.

(https://github.com/signup)

You will normally provide your email address, password and username for the github account. After creating the account, verify your email address.

After signing in, create a new repository. A repository is a storage space for your project.

When creating the repository, provide a name for the repo, then click Create repository.

2. Install Git on your windows PC

First, install Git on your computer from the official Git website:

[Download Git] (https://git-scm.com/downloads)

After downloading the installer:

Open your Downloads folder.
Find the downloaded Git installer.
Double-click it.
Allow Windows to run the installer.
Go through the installation screens.
For a normal beginner installation, you can generally keep the recommended/default options.
Complete the installation.

One important tool installed with Git for Windows is Git Bash. After installing it, open git bash and run on the command prompt to check that Git works:

git --version

If Git is installed correctly, you will see its version number.

git version 2.55.0.windows.4

3. Configure Git Username and Email Address

Next, you need to tell git your username and email address associated with your GitHub account. You type on the command prompt

git config --global user.name "Your Full Name" to configure your name and
git config --global user.email "Your Email address" to configure your email

Example:
git config --global user.name "Mohammed Swaleh"

git config --global user.email mswaleh@gmail.com

4. Configure main as the Default Branch

Next, you need to Set main as the Default Branch
Run:
git config --global init.defaultBranch main

This tells Git to use main, as the default initial branch for new repositories.

5. Verify Your Git Configuration

To check your name:

Type in Git Bash
git config --global user.name

To check your email:
type
git config --global user.email

To check all global configuration:
git config --global --list

You should see something similar to:

user.name=Mohammed Swaleh
user.email=mswaleh@gmail.com
init.defaultbranch=main

6. Set Up Secure Shell (SSH)

SSH allows Git to connect securely to your GitHub account without asking for your password every time.

Confirm SSH key exists

Open your terminal and check whether SSH key exists
Type
ls -al ~/.ssh

You can see

id_ed25519
id_ed25519.pub

or older files such as:
id_rsa id_rsa.pub

if you encounter this error message:
No such file or directory

This means that the SSH key does not exist and you need to create an SSH key

Create an SSH key

Type in the terminal:
ssh-keygen -t ed25519 -C "your_email@example.com"

Replace:
your_email@example.com, with the email associated with your GitHub account.

Example:
ssh-keygen -t ed25519 -C "mswaleh@gmail.com"

Press Enter to accept the suggested file location. You can also create a passphrase for extra security.

Confirm That the Key Was Created

type:
ls -al ~/.ssh

You should now see:

id_ed25519
id_ed25519.pub

7. ADD THE private SSH KEY TO THE SSH AGENT

The SSH agent helps manage your private SSH key and can remember its passphrase during your session.

To Start the SSH Agent for windows,
open:
PowerShell, as Administrator.

On the powershell terminal,

Run:
Get-Service -Name ssh-agent | Set-Service -StartupType Manual

Then:

Start-Service ssh-agent

Exit powershell as an administrator, open powershell as a normal user,

Type:
ssh-add c:/Users/YOUR_USERNAME/.ssh/id_ed25519

Replace:
YOUR_USERNAME with your actual Windows username.

Example:
ssh-add c:/Users/mswaleh/.ssh/id_ed25519

If you do not know your Windows username, you can run:

whoami

You may receive something like:
DESKTOP-12345\mswaleh

Your username is therefore, in this example:
mswaleh

8. Windows — Copy the Public SSH Key

Open Git Bash.

Run
cat ~/.ssh/id_ed25519.pub | clip
The public key should now be in your clipboard.

If that does not work, display it:
cat ~/.ssh/id_ed25519.pub

You will see one long line similar to:

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAA... mswaleh@gmail.com

Copy the entire line.

9. ADD THE SSH KEY TO GITHUB

On GitHub:

Click your profile picture.
Select Settings.
Open SSH and GPG keys.
Click New SSH key.
Give the key a name.
Paste your public key.
Click Add SSH key.

GitHub may ask you to confirm your email account.
Complete the confirmation.
Your SSH public key should then appear in your GitHub account.

10. TEST THE SSH CONNECTION

Test GitHub SSH Authentication
Return to:
Git Bash on Windows,

Run:
ssh -T git@github.com
If everything is correctly configured, you should get a message similar to:

Hi YOUR_USERNAME! You've successfully authenticated, but GitHub does not provide shell access. This is a success message.
It means:
Computer
↓
Private SSH Key
↓
GitHub verifies the public key
↓
Authentication Successful

11. Open Your Local Project

Use the terminal to move into your project folder.

For example:

cd path/to/your-project

You can check the files in the folder with:

ls

12. Turn the Folder Into a Git Repository

Inside the project folder, run:

git init

This creates a hidden .git folder. Git uses this folder to track changes in your project.

Now check the status of your files:

git status

13. Add a README File

A README file explains what your project is about. Create a file called README.md and write a short description.

Example:

# My First GitHub Project

This is my first project uploaded to GitHub using Git and SSH.

14. Save Your Files in Git

Add all project files:

git add .

The period means "add all files in this folder".

Create your first commit:

git commit -m "Initial commit"

A commit is a saved version of your project. The message explains what was changed.

15. Connect the Local Folder to GitHub

On your GitHub repository page, copy the SSH address. It will look similar to this:

git@github.com:your-username/your-repository.git

Connect your local project to the GitHub repository:

git remote add origin git@github.com:your-username/your-repository.git

You can check the connection with:

git remote -v

16. Upload the Project

Rename your main branch to main:

git branch -M main

Then upload the project to GitHub:

git push -u origin main

The push command sends your committed files from your computer to GitHub.

Refresh your GitHub repository page. Your project files should now be visible online.

17. Updating the Project Later

Whenever you make changes, use these three commands:

git add .
git commit -m "Describe your changes"
git push

For example:

git add .
git commit -m "Add homepage design"
git push

This saves the new changes locally and uploads them to GitHub.

18. Common Problems

GitHub rejects the push

Make sure the repository address is correct:

git remote -v

You can replace the incorrect address with:

git remote set-url origin git@github.com:your-username/your-repository.git

SSH permission denied

Check that your SSH key has been added to GitHub and test the connection again:

ssh -T git@github.com

Files are not uploaded

Check the repository status:

git status

Then add, commit, and push the files:

git add .
git commit -m "Add project files"
git push

Final Git Workflow

The basic workflow is:

git init
git add .
git commit -m "Initial commit"
git remote add origin git@github.com:your-username/your-repository.git
git branch -M main
git push -u origin main

After the first upload, you usually only need:

git add .
git commit -m "Describe your changes"
git push

Conclusion

Moving a project from a local folder to GitHub may seem difficult at first, but the process becomes simple with practice. Git tracks your work, GitHub stores it online, and SSH provides a secure connection between your computer and your GitHub account.