DEV Community: Mburu Kibandi

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

Mburu Kibandi — Mon, 07 Sep 2026 11:52:57 +0000

Project Introduction

An analysis project turning Jumia product data into useful pricing,promotion and customer-engagement insights.The project will help us understand how price,promotions and customer feedback influence product performance.

Project Objectives

By the end of this project we should be able to understand and explain :

Whether large discounts are associated with more reviews
Whether highly rated products attract stronger engagement
Which products perform best based on ratings and reviews
Whether price and rating move together
Which products may need a different strategy or marketing

Dataset And Data Quality Audit

Our dataset above is made up of 6 columns namely;Product,Current Price,Old Price,Discount,Ratings and Reviews.It consist of 116 different rows.

The data had no blanks for product,current price,discount and old price fields.It instead had a total of 58 blanks for both ratings and reviews.There were 3 exact duplicates reducing blank count to 55 for both ratings and reviews.

They were no ratings above 5 or below 0 neither were they discounts outside the range of 0-100

Cleaning and Preparation Decisions.

Cleaning

Kudos to our data scientists because generally our data was clean except for minor changes.We did trim the product field removing extra spaces before the products.

Current and old prices had their fields formatted as text so we went ahead to remove KSh,removed commas and extra spaces and formatted as currency aligning both fields to the right

For the ratings field we removed 'out of 5' as generally ratings are always from 0 to 5.For the blank rows in ratings and reviews, we decided to totally leave them as blank as opposed to filling them as zeros because ideally zero would have translated to poor rather than "Not Reviewed" or "Not rated"

Generally for the bigger part of cleaning work was actually getting to change the fields data type.

Preparations

Enriching data to me is always the first step to analysis.Data enrichment will provide enhanced analytical depth,higher data accuracy and integrity and improved decision making

For our Jumia dataset, we did add numerous fields to help make our analysis easier.We checked for any abnormalities in our data and had them recorded

Is our current price greater than our old price for any rows ?
Do we have rows that have missing values for discount,do we have rows that have discount outside the range of 0-100 ?
Do we have rows that have missing values for ratings,do we have any ratings outside the range of 0-5 ?

The above questions resulted to three additional fields for checking prices,discount and ratings.We went ahead to categorise prices,ratings and discount such that

For every rate given below 3,excel was to return poor,between 3 and 4 were classified as average and excellent for rates above 4
We had low discount being any value below 20%,medium discount being values ranging between 20% and 40% and high discount as values from 40%
For prices, we did create a threshhold to be able to group the prices.We had first quartile as the maximum for low prices and our third quartile as our minimum for high prices

Excel Formulas

These are some of the excel formulas we used to do cleaning and preparation of our data

=TRIM()
=IF(OR([@Ratings]<0,[@Ratings]>5,ISBLANK[@Ratings]),"Check Rating","Ok")
=IF(OR([@Current Price] > [@Old Price]),"Check Price',"Ok")
=IF(OR([@Discount]<0%,[@Discount]>100%,ISBLANK[@Discount]),"Check Discount","Ok")

For categorising our prices,discount and ratings we used

=IF(ISBLANK([@Ratings]),"Missing Rating",IF([@Ratings]<3,"Poor",IF([@Ratings]<4,"Average","Excellent")))
=IF[@Discount),"Missing Discount",IF([@Discount]<20%,"Low Discount",IF([@Discount]<40%,"Medium Discount","High Discount"))
=IF([@Current Price]<Price_Q1,"Low Prices",IF([@Current Price]<Price_Q3,"Medium Prices","High Prices"
Price_Q1=QUARTILE.INC(tblproducts[Current Price],1)
Price_Q3=QUARTILE.INC(tblproducts[Current Price],3)

Engagement and performance flags

We did define a rule using percentile and created flags for

high discount and low rating
high discount and low engagement
many reviews and average rating; and
strong engagement and excellent rating

For high discount,strong engagement,low engagement and rating we used the following formulas to create our threshold

strong_engage =PERCENTILE.INC(tblproducts[@Review],0.75)
low_engage    =PERCENTILE.INC(tblproducts[@Review],0.25)
high_discount =PERCENTILE.INC(tblproducts[@Discount],0.75)
low_discount  =PERCENTILE.INC(tblproducts[@Discount],0.25)
average       =AVERAGE(tblproducts[@Rating])

Our thresholds helped us find if there were products with high discount and low rating,high discount and low engagement or even many reviews and average rating.

PivotTables And Analysis

A pivot table is simply a data tool used to instantly group,aggregate and transform data.Pivottables can be actually be used to create pivotcharts that are used for visualizations.Visualization is actually a summary of our analysis

For our Jumia dataset we did create different pivottables such as rating mix,discount mix,price vs rating,engagement by discount,discount vs reviews.These pivottables actually helped us to findings and some recommendations

We did analyse correlation between discount vs reviews,ratings vs reviews and current price vs rating.Unfortunately or fortunately there were only weak correlations for all three meaning none of them variables change together.It is important to note that correlation is not causation.

Here is also a picture of our dashboard

You notice that on the far left side of our dashboard we have table like structures that are not necessarily charts.They are called slicers and generally used to filter data.You might actually need more than one slicer for your dashboard and it is also important to note that you need to have them connected in order for one change to affect all charts

How do you connect slicers ?

Right click the slicer
A dropdown appears that you click on report connections
You then proceed to select all pivottables you created
Note
It is very important to note that if when creating your pivottables, you did click on add data to this data model in order to create on the existing worksheet,then your slicers simply won't work.

Findings And Recommendations

Findings

A lot of products had their rates as missing
Most products have been rated excellent,followed closely by average then lastly poor
Most products had high discount of above 40%
Most highly rated products are products with high prices
Products with medium discount received the most reviews of 351,products with high discount received 334 reviews and products with low discount received 38 reviews

Recommendations

Maintain the good quality,good customer services for the highly rated products
We also need to know the kind of reviews(good or bad) our customer gives back to be able to enrich what we can find from our data.

[ (https://github.com/MburuKefoi/Jumia_Product_Performance_Dashboard) ]

In an effort to keep this article fun and short,above is my link to the file so that you can check it out

Excel For Data Analytics

Mburu Kibandi — Sun, 30 Aug 2026 07:05:43 +0000

Introduction

Excel is a spreadsheet operator developed by Microsoft and used by different operating systems.It features a wide variety of tools from computational tools to graphical tools,infact Excel 2016 has up to 484 functions used to perform different tasks.It is undoubtedly one of the most powerful applications used in Data Analytics

Features of Excel

Data in Excel is stored in a spreadsheet.A spreadsheet is a table made up of rows and columns.A collection of many spreadsheets form a workbook.

A row in a spreadsheet is labelled with numbers i.e 1,2,3 whereas a column is letter labelled from A,B,C...

The intersection of rows and columns form cells where information is stored,a cell in excel is therefore identified by the row and column header e.g A1,C5. This cell name is usually displayed in the name box,shown at the top left of your screen

When you open your workbook,you are met with a command bar interface at the top of your window that we call ribbons.Ribbons categorises tools into logical operations for easy access of formatting tools,data management tools and graphical tools.

Ribbon Structure

Tabs: Top level headers on top of your screen (File,Home,Insert,Draw...)
Groups:Tabs' sub-sections that appears when you click on tabs,they usually display various tools i.e Font,Alignment,Data Types,Sort&Filter
Command Buttons:A variety of buttons that execute commands when clicked upon.Some buttons will reveal lists of selectable choices one can choose from e.g Font Size,Number Format,Conditional Formatting

Data Types

What are Data Types ?

Data Types refer to simply the type of text one can input in a cell.Excel typically allows different types of data types as discussed below

Text(String) : Alphanumeric characters or words.Excel aligns text data type to the left by default
Number : Salaries,Age,Quantities or Counts.Excel aligns numeric data types to the right.We have different number formats in excel that changes how values look in your screen i.e Currencies,General,Date&Time
Date & Time : Another data type used to record dates.Dates in excel are stored sequentially from 1st January 1900 which is recorded as one,the reason behind being to make mathematical operations fast
Formulas : Different type of functions used to perform computational and logical operations.It is easier to use SUM formulae to find totals than manually having to add them together.To write a formulae ,you start by an equal sign
Boolean : Logical operations that return True or False

Data Cleaning

Data imported as a csv file generally requires some cleaning.Once or twice you may find some clean data that requires no cleaning.Data cleaning involves you checking for the correct data type,expanding your fields or filling in some blanks.Cleaning your data prepares it for different formatting.

Above is an image showing how raw data often looks like.

The best way of cleaning your data is by first checking up for errors. Looking at our data we can see that current and old price fields both have the numbers aligned to the left as text as opposed to the right.It is also evident that review and ratings fields do have blanks.Apart from those minor errors our data is majorly clean

We can start by highlighting the whole workbook then under home tab,look for format,usually a dropdown will appear if you click on it and then you can choose Autofit Column Width to expand our fields.Ideally we can do this manually by having our "cross-like" pointer between fields but it will take a lot of time

Just after formatting your column width,highlight your workbook and under home tab,apply filter
Your excel should primarily look like this

You can change the headings to be bold and add some background colour to make it more visible.

First things first,we need to remove duplicates from our dataset,we will highlight our workbook and under data tab choose remove duplicates from data tools.Excel will pop up a message of how many unique values are left.

NOTE : The easiest way of highlighting the whole workbook is by clicking on the triangle on top of your first row

Cleaning your Data

Here are some quick steps on how to clean your current and old price fields

Highlight column "Current Price" by clicking on your column header
Ctrl + H will pop up Find and Replace dialog box,where you can ask your PC to find KSh and for now do not replace with anything.Leave the replace input box empty.Click on Replace All to Replace
Highlight column "Current Price"
Under Home tab,Find the Group Number and click on the arrow,change the type of your number to currency then choose 'KES' as Kenyan symbol

Your column should have your data aligned to the left.Redo the same for Old Price field

For reviews and ratings,here is how to best clean your data

Click on the filter symbol
Sort your data from largest to smallest,sorting your data prompts your PC to have the cells with errors to come first,this often is the easiest method for finding errors because some datasets can be very large
For our data sorting doesn't have the blanks coming first ,so
Highlight the column "Reviews"
Ctrl + H for the Find and Replace dialog box,leave the find input box as blank and type Unknown in the replace input box
Do the same for ratings and discount fields

Above is our cleaned data

This data has been generally clean and did not need too much changes.For some dataset with numerous fields,cleaning will surely take some time.For instance,some data will have different words to mean the same thing e.g NYC and New York City in different cells.We can even encounter misspellings.It is important we correct to have one word to have one meaning all through

For such cases we;

Ctrl + H for the Find and Replace dialog box and type New York City in the find input box and replace it with NYC
Click on Replace All to Replace.

Some datasets will have names of people in different cases.You may find some surnames are written in uppercase where as the first names are written in lowercase.We need to change the names to proper cases and here we will invoke text functions

Text Functions
We have different text functions that transform data differently

Upper : Upper function will change the data to uppercase.Usually written as "=UPPER(cell address)"
Lower : Lower function will change data to lowercase.Usually written "=LOWER(cell address)"
Proper : Proper function changes your data to proper case,where the first letter of your word is capital and the rest small e.g John.To invoke this function "=PROPER(cell address)"

Incase you find yourself where you have to format names then follow these steps

Highlight the column,right click and insert two new columns
Type out the respective formula you want in one column
Copy the result of column1 and paste special to column2
We choose the option of pasting values inorder to be able to delete the column the formula we are referencing otherwise when we don't paste values and delete the column we are referencing,our new column will return an error.

Data Validation

Data validation helps to restrict what data can be entered.It is usually useful when data entry has not been done.Data validation tool is under the Data tab
To validate data,

Highlight the column
Under data tab,select data validation,a dropdown of options usually appears and you can choose data validation
A dialog box pops up that has two input boxes,Allow and Data.You can choose list from the list that appears when you click on the arrow at the end of the allow input box and type the entries you want allowed in the source input box.
Click Ok

Conclusion

In a nutshell,data cleaning is just finding errors,removing duplicates and missing values in a dataset to make it accurate and ready for analysis.Excel does this perfectly

A GitHub Project Using Git

Mburu Kibandi — Sun, 23 Aug 2026 06:48:06 +0000

Introduction

GitHub is a developer platform that allows developers to create,store and manage their code.It provides access control,bug tracking and task management for every project. Git is a system used to handle everything from small to large projects. GitHub uses Git as its distributed version control.

Step by step guide

Creating a folder

In your git version,type

pwd(print working directory)
pwd will basically tell you the absolute path for your directory.It is usually essential to know in order to be able to navigate to the right directory

From the image above,my pc path is from C to Users to HP

In order to navigate to the right directory we are going to run the cd command.Cd which stands for change directory will change your directory to your preferred directory.In this case we will cd to desktop where we store our files
In the Desktop directory we can now create a folder that we intend to push to GitHub.Git uses the command mkdir(make directory) to create a folder.Lets name the folder "My GitHub Project"
(When you check your File Explorer you will find a new folder named My GitHub Project)
We wish to create another folder 'data',this folder should be a subfolder inside My GitHub Project.To do this,we cd to the parent folder and then run the mkdir command
For our GitHub project, we need a README markdown file that normally gives a brief explanation of what the project is about.It is usually the face of the project.To create a README file we run the command touch README.md
We want to add a brief description of what the project is about.
To add the description we are going to use the echo command,below is a screenshoot of how to write the command

NOTE:
We use a single greater sign to overwrite what we have in the README file and use double greater sign to add to what is in the file
Below is an image of what we've done till now

The cat(concatenate) command read files and print them directly as they appear

To copy data that should be analysed we can run

cp ~/Downloads/MOCK_DATA.csvdata/
where
"/Downloads/MOCK_DATA.csv" is the source and data/ is the destination

Pushing to GitHub

We now have our folder and we can now push it to GitHub

Run the command git init that creates an empty repository in git,it allows git to start watching file changes,tracking history and managing branches
When you run the git init command the PC returns "Initialized empty repository..."
We then run git status command,a command that shows what git sees
The PC returns "nothing added to commit but untracked files present"
We now run
git add .
git add prepares changes for the next commit
Now when you run
git commit -m "Add commit message"
Git now saves changes to the git repository
The next step is to check our branch and ensure its under main branch.If your run git branch and the PC returns master,then you have to rename it to main by running
git branch -m main

Log in your GitHub and create a repository and copy your SSH key,then navigate back to your git version and the following commands

You can finally run git push -u origin main and enter your GitHub passphrase to push to GitHub.