DEV Community: MERCY MUMBI WAHOME

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

MERCY MUMBI WAHOME — Mon, 07 Sep 2026 15:38:33 +0000

Introduction: Turning Jumia Product Data into Business Insights

E-commerce platforms generate a lot of product data, but raw numbers become useful only when they can support better decisions. For Jumia sellers, prices, discounts, ratings and customer reviews can provide clues about product performance, customer engagement and possible pricing strategies.
For this project, I worked with a dataset of 112 Jumia products to explore these relationships using Microsoft Excel. I wanted to find out whether higher discounts are associated with more customer reviews, whether highly rated products receive stronger engagement, and whether product price is related to rating. I also wanted to identify the products performing best and those that may require a different pricing or marketing approach. I followed a complete data-analysis workflow:

Raw Data → Cleaning → Transformation → Analysis → Visualization → Insights → Recommendations

The project uses Excel Tables, Power Query, formulas and functions, PivotTables, PivotCharts, slicers and dashboard techniques. This article documents that process and shows how the raw Jumia data was transformed into an interactive dashboard and, ultimately, evidence-based business recommendations.

Understanding the Dataset and Its Initial Problems

Before cleaning the data, I first needed to understand what I was working with. The dataset contains 112 Jumia products and six main fields: Product, Current Price, Old Price, Discount, Review and Rating. Current Price and Old Price represent product pricing, Discount captures the promotional percentage, Review represents the number of customer reviews, while Rating records the average customer rating out of 5. I treated this stage as a data-quality audit rather than immediately changing anything. The purpose was to identify issues that could affect calculations and visualizations later.
The raw dataset contained formatting and consistency issues that needed attention, particularly around numerical fields. Prices could contain currency symbols or unnecessary characters, while ratings and review counts needed to be standardized into usable numerical values. I also needed to check systematically for missing values, duplicates and other inconsistencies before beginning the analysis.
This preliminary inspection was important because reliable analysis depends on reliable input data. Instead of correcting problems as I encountered them later, I first established what needed to be fixed and why.

Data Cleaning and Preparation

The initial dataset contained 115 products, but some records were duplicates. The Balloon Insert product appeared three times, while the 6 Layers Steel Pipe Shoe Cabinet appeared twice. I removed the duplicate records so that each product was represented only once, leaving the required 112 products for analysis.
The Review column also contained negative values such as -2, -14 and -55. Since review counts cannot be negative, I removed the minus signs and converted the values into positive numbers. At the same time, 58 products had missing review counts and ratings, so I kept these as missing rather than incorrectly treating them as zero.
The rating field was stored as text, for example 4.5 out of 5, and the column itself was labelled Ratingd. I renamed the column to Ratings and extracted the numerical rating. Price values also contained KSh and commas, so I removed these characters and converted the prices into numerical values. One product used a price range rather than a single price, which required special handling before numerical analysis.

Data Enrichment

I added calculated columns to support deeper analysis. These additional fields transformed the original variables into useful measures and categories that could later be used in PivotTables, charts, slicers and the dashboard. I created a Discount Amount column to measure the absolute difference between the Old Price and Current Price. I used the Excel formula*=C2-B2*
I also created a Rating Category column to classify products as Poor, Average or Excellent according to the project criteria. I used the logical IF and AND functions with the formula =IF(G2="","Not Rated",IF(G2<3,"Poor",IF(G2<=4,"Good","Excellent"))) .
A Discount Category column was added to group products into Low Discount, Medium Discount and High Discount based on the required percentage thresholds. The formula used was =IF(E2<20%,"Low Discount",IF(E2<=40%,"Medium Discount","High Discount")).
Finally, I added a Price Category column to segment products into Low, Medium and High price groups using the formula =IF(B2<=1275,"Low",IF(B2<=2512,"Medium","High")) for the price thresholds established for the analysis. These enriched variables provided a stronger foundation for comparing product behaviour and developing the later seller-performance analysis.

Excel Techniques and Descriptive Analysis

After completing the cleaning and enrichment stages, I created a separate Analysis sheet to calculate the key descriptive statistics for the cleaned dataset. I started by determining the total number of products using the formula =COUNTA('Cleaned Data'!A2:A113), which confirmed that the cleaned dataset contained 112 products.
I then calculated the total number of reviews using the formula =SUM('Cleaned Data'!H2:H113), which added the available review counts while leaving missing values as blanks rather than treating them as zero.
To understand the overall pricing level, I calculated the** average current price** using =AVERAGE('Cleaned Data'!B2:B113) and the average old price using =AVERAGE('Cleaned Data'!C2:C113).
I also calculated the average discount using =AVERAGE('Cleaned Data'!E2:E113) and the average rating using =AVERAGE('Cleaned Data'!G2:G113).
To identify the most expensive product, I first used =MAX('Cleaned Data'!B2:B113) to find the highest Current Price, then used** =INDEX('Cleaned Data'!A2:A113,MATCH(MAX('Cleaned Data'!B2:B113),'Cleaned Data'!B2:B113,0))** to return the product associated with that price.
I followed the same approach for the least expensive product, using =MIN('Cleaned Data'!B2:B113) to identify the lowest Current Price and =INDEX('Cleaned Data'!A2:A113,MATCH(MIN('Cleaned Data'!B2:B113),'Cleaned Data'!B2:B113,0)) to return the corresponding product name.
These calculations gave me the initial numerical summary of the dataset and established a foundation for investigating relationships between pricing, discounts, customer reviews and ratings.

Correlation Analysis

To investigate whether pricing, discounts and customer engagement were related, I used correlation analysis. Correlation measures the direction and strength of a linear relationship between two variables, with values ranging from -1 to +1. A value close to +1 indicates a strong positive relationship, a value close to -1 indicates a strong negative relationship, while a value close to 0 indicates little or no linear relationship. I conducted three correlation tests using the Excel **CORREL **function.

Discount vs. Reviews
The first test examined whether products offering higher discounts received more customer reviews. I used the Excel formula =CORREL('Cleaned Data'!E2:E113,'Cleaned Data'!H2:H113). The resulting correlation coefficient was -0.14, indicating a very weak negative relationship. In the Jumia dataset, this means that products with higher discounts tended to have slightly fewer reviews, but the relationship was too weak to indicate a meaningful pattern. Therefore, the data does not support the assumption that increasing a product's discount automatically leads to greater customer engagement through reviews.

Rating vs. Reviews
The second test investigated whether products with higher customer ratings also attracted more reviews. I used the formula =CORREL('Cleaned Data'!I2:I113,'Cleaned Data'!H2:H113). The result was 0.06, which is very close to zero and indicates an almost nonexistent linear relationship. Within this dataset, highly rated products were therefore not necessarily the products with the highest number of reviews. This suggests that customer satisfaction, represented by rating, and customer engagement, represented by review volume, were largely independent of each other among the products analysed.

Current Price vs. Rating
The final test examined whether the price of a product was associated with its customer rating. I used the formula =CORREL('Cleaned Data'!B2:B113,'Cleaned Data'!I2:I113). The resulting coefficient was 0.11, showing a very weak positive relationship. This indicates that more expensive products tended to have slightly higher ratings, but the relationship was not strong enough to suggest that price was an important predictor of customer satisfaction. In this dataset, a higher-priced product was therefore not necessarily better rated than a lower-priced product.

Top 5 Highest and Lowest Rated Products

To identify the products with the strongest and weakest customer ratings, I used Excel's LARGE and SMALL functions. These functions were appropriate because they allowed me to rank the numerical ratings and identify the highest and lowest values directly from the cleaned dataset.
For the top 5 highest-rated products, I first used =LARGE('Cleaned Data'!G2:G113,1) to identify the highest rating, which was 5. I then used the FILTER function to retrieve the products associated with this rating. Five products achieved the maximum rating of 5, making them the highest-rated products in the dataset and potential indicators of strong customer satisfaction.
For the top 5 lowest-rated products, I used the SMALL function to rank the ratings from lowest upward. The five lowest ratings was 2.0 . I then used FILTER to identify the products corresponding to these ratings. These products represent the weakest-rated products in the dataset and may require further investigation into product quality, customer expectations or seller performance.

Top 10 Products with the Highest Discounts

To identify the products receiving the highest discounts, I ranked the discount percentages from highest to lowest using Excel's LARGE function. I used =LARGE('Cleaned Data'!D2:D113,1) to identify the highest discount and adjusted the ranking number to retrieve the top 10 values. I then used FILTER to identify the products corresponding to these discounts. This showed which products were being promoted most heavily and provided a basis for comparing high discounts with reviews and ratings.

Top 10 Products by Customer Reviews

To identify the products with the strongest customer engagement, I ranked the review counts from highest to lowest using Excel's LARGE function. I used =LARGE('Cleaned Data'!H2:H113,1) to find the highest review count and then ranked the remaining values from 2 to 10. I used XLOOKUP to match each review count to its corresponding product with =XLOOKUP(F61,'Cleaned Data'!H2:H113,'Cleaned Data'!A2:A113).
The highest review count was 69, and the Top 10 results identified the products receiving the most customer reviews. These products represent the strongest customer engagement in the dataset and can later be compared with their ratings, prices and discount levels to understand what may be associated with stronger customer interaction.

High Discounts with Low Ratings

I also investigated products that were receiving high discounts but poor customer ratings. I used the FILTER function with two conditions: a discount above 40% and a rating below 3. The formula was** =FILTER('Cleaned Data'!A2:A113,('Cleaned Data'!F2:F113>40%)('Cleaned Data'!I2:I113<3),"No matching products")*. This was useful because it highlighted products where heavy price reductions were not translating into strong customer satisfaction, providing potential areas for sellers to review product quality, pricing or marketing strategies.

Products Requiring Better Pricing or Marketing Strategies

I then identified products that may require a different pricing or marketing strategy by combining three indicators: poor ratings, low review activity and discounts above 20%. I used the FILTER function with the formula =FILTER('Cleaned Data'!A2:A113,('Cleaned Data'!I2:I113<3)*('Cleaned Data'!H2:H113<5)*('Cleaned Data'!F2:F113>20%),"No matching products"). I chose these conditions because they highlight products that have low customer satisfaction, limited engagement and are already being discounted. The analysis returned several products, suggesting that discounting alone may not be sufficient for this group and that sellers may need to review their pricing, product quality, positioning or marketing approach.

Key Insights

The Jumia dataset reveals that customer response is more complicated than simply offering a cheaper price. Despite discounts reaching as high as 64%, the relationship between discounts and reviews was almost nonexistent (-0.14). This suggests that aggressive promotions may attract attention, but they do not automatically create lasting customer engagement.
A particularly interesting pattern is the separation between popularity and satisfaction. With a rating-review correlation of only 0.06, products generating reviews are not necessarily the products customers rate highly. This means a seller could have a product that attracts considerable attention while still failing to deliver a satisfying experience.
The data also shows that price is a poor substitute for perceived value. The price-rating correlation of 0.11 indicates that paying more does not guarantee a better customer experience. At the same time, the presence of products rated 5.0 alongside products rated as low as 2.0 suggests that product-level factors can create much larger differences in customer satisfaction than price alone.

Business Recommendations

Turn discounts into a targeted tool, not a rescue strategy. Sellers should use large discounts strategically on products that already demonstrate good customer response, rather than repeatedly discounting poorly rated products. A product with a 64% discount and weak customer feedback should trigger investigation before another promotion.
Build around the products customers already love. The five products achieving 5.0 ratings provide potential benchmarks. Sellers should examine what makes these products successful such as product quality, presentation, value or reliability and replicate those strengths across weaker products.
Treat poor ratings as a product problem first. Products falling around 2.0–2.2 should be reviewed for quality issues, misleading descriptions, poor fulfilment or unmet customer expectations. Reducing their price further may increase sales temporarily but could worsen customer dissatisfaction if the underlying problem remains.
Use different strategies for different products. The absence of products with more than 40 reviews and average ratings of 3–4 shows that highly engaged products in this dataset tend to fall outside that specific middle-rating segment. Sellers should therefore segment products by both engagement and satisfaction instead of applying one marketing strategy to the entire catalogue.

Conclusion

Working on the Jumia dataset was more interesting than I expected. At the beginning, I thought I would simply clean the data, apply a few Excel formulas, create some charts and be done. But as I worked through the dataset, I started noticing things that made me question my own assumptions. I expected bigger discounts to attract more customers and expensive products to have better ratings, but the data did not always support that. That was probably the biggest surprise for me.
I also realised how much the quality of the data matters. Removing duplicates, organising the information and creating useful categories may seem like small steps, but they made a big difference in how confidently I could interpret the results. I started seeing Excel differently too. It was no longer just about knowing which formula to use; it was about knowing what question I was trying to answer and what the result was actually telling me.
The part I found most interesting was watching ordinary rows of data turn into something that could tell a business story. A rating represents a customer's experience, a review shows engagement, a discount reflects a seller's strategy, and the price gives another piece of the picture. Putting all these pieces together made the dataset feel much more real.

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

MERCY MUMBI WAHOME — Mon, 31 Aug 2026 08:57:23 +0000

Introduction

When I began my journey into data science and analytics, Excel was one of the first tools I encountered. Week 1 changed my perspective by showing me that Excel is much more than a platform for entering and storing information. It can be used to organize, validate, transform, and prepare data for meaningful analysis. In this article, I explore the fundamental Excel concepts I learned and apply them to an HR dataset containing employee information. I demonstrate how to work with an Excel workbook, understand cells and data types, navigate the Ribbon and Home tab, use functions and operators, and identify common data-quality problems.
The main focus is data cleaning, since reliable analysis begins with reliable data. Using the HR dataset, I demonstrate how seemingly small inconsistencies—such as incorrect formatting, duplicate records, missing values, inconsistent text, and invalid dates; can affect the quality of information produced from a dataset. The process therefore moves from understanding Excel → entering and examining data → identifying problems → cleaning the data → preparing it for analysis.

What is Excel

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, and present data. It provides a structured environment in which information can be stored in rows and columns, with each intersection forming a cell. For data analytics, Excel is particularly useful because it combines data storage with tools for calculations, filtering, sorting, visualization, and data cleaning. It also provides functions that can transform raw information into useful insights. My first important realization was that good analysis does not begin with immediately calculating results. It begins with understanding the data and ensuring that the data is accurate, consistent, and suitable for analysis.
To begin working with Excel, I installed and opened Microsoft Excel on my computer. When Excel opens, it provides options for creating a new workbook or opening an existing one. An Excel workbook is the main file that contains one or more worksheets. A worksheet is the individual spreadsheet where data is entered and manipulated. Each worksheet consists of rows, columns, and cells. A cell can contain different types of information, including text, numbers, dates, percentages, and currency values. Recognizing these data types is important because Excel handles each type differently. For example, a date should be stored as a date rather than ordinary text if I want to sort dates chronologically or perform calculations involving time.

Exploring the Excel Interface

Excel organizes its tools through the Ribbon, which appears at the top of the workbook. The Ribbon contains tabs such as Home, Insert, Page Layout, Formulas, Data, Review, and View, with each tab grouping related commands. The Home tab is especially useful when working with data. It contains options for formatting text and numbers, changing alignment, inserting or deleting cells, sorting and filtering data, and finding or replacing information. These features make it possible to improve both the appearance and quality of a dataset without leaving the worksheet. An Excel workbook also has several useful features, including multiple worksheets, formulas, functions, formatting tools, tables, sorting, filtering, and conditional formatting. Together, these features allow Excel to support the entire journey from raw data to analysis.

Data Entry and Understanding the Dataset

Data entry involves recording information into the appropriate cells while maintaining consistency in how the information is represented. For example, numbers should remain numerical, dates should use a consistent date format, and text should be entered consistently. For my practical exercise, I used the HR_Dataset_Dirty.xlsx dataset. It contains employee information such as Employee ID, names, department, salary, hire date, age, gender, performance score, work experience, employee type, location, and training hours. Before making any changes, I examined the dataset to understand its structure and identify possible data-quality issues.

Data Cleaning

Before cleaning the data, I made a copy of the original dataset. This is an important data-management practice because the original data should always be preserved in case I need to compare changes or recover information. I created a separate worksheet for the cleaned data and worked on the copy rather than altering the original dataset. This allowed me to distinguish between the raw data and the cleaned data.
Data cleaning is the process of identifying and correcting inaccurate, incomplete, inconsistent, or incorrectly formatted data. It is an essential stage of data analytics because poor-quality data can produce misleading results. While examining my dataset, I found several examples of these problems. For instance, employee names appeared with inconsistent capitalization, departments were represented in different ways, and some dates were invalid or inconsistently formatted.

Text Cleaning and Standardization

Text cleaning involves correcting inconsistencies in text so that similar information is represented in a uniform way. Common examples include inconsistent capitalization, extra spaces, spelling variations, and different labels referring to the same category. Excel provides several useful text functions for this. PROPER capitalizes the first letter of each word, UPPER converts text to uppercase, and LOWER converts text to lowercase. TRIM removes unnecessary spaces, while Find and Replace can be used to replace inconsistent words or labels throughout a dataset.
For example, the names JOHN, john, and JoHn represent the same person but have different formatting. Using PROPER standardizes them to John. Similarly, department labels such as HR, H.R, and Hr may need to be standardized to one consistent value. In my HR dataset, I identified inconsistent capitalization in the First Name column. Some names appeared entirely in uppercase while others were lowercase or mixed. I therefore applied the PROPER function to standardize the names.

Removing Duplicates

Duplicate data occurs when the same record, or an identifier that should be unique, appears more than once. Duplicates can distort analysis by causing certain employees or observations to be counted multiple times. Excel provides Remove Duplicates **under the **Data tab. Duplicates should be reviewed before deletion because two records may share one value while still representing different records. For my HR dataset, Employee ID is particularly useful for checking duplicates because it should uniquely identify an employee. We found repeated Employee IDs, so this gives us a real example of why duplicate checking is necessary.

Formatting and Data Types

Data formatting ensures that information is displayed consistently and that Excel recognizes each value according to its correct data type. Common types include text, numbers, dates, percentages, and currency. Correct data types are important because Excel performs calculations and sorting differently depending on how a value is stored. For example, a salary should be numerical so that it can be calculated, while a hire date should be recognized as a date so that employees can be sorted chronologically.
Common Excel tools for this include Number Format, Currency, Date, and Format Cells. In my HR dataset, I found inconsistencies in the Salary and Hire Date columns. Some salary values used different currency representations, while some dates were entered in different formats. I standardized these values so that the dataset would use consistent formats.

For valid dates, I standardized the display format so that all dates follow the same pattern.

Missing Values and Blank Cells

Missing data occurs when information expected in a dataset has not been provided. A blank cell and a zero should not automatically be treated as the same because a blank may indicate unavailable information, while zero can be a valid value depending on the field. Excel tools such as Filter, Find & Select, and functions such as COUNTBLANK can help identify missing values. For example, a missing salary or department requires investigation, while a project count of zero may be a legitimate record. In my HR dataset, I checked for blank values and considered the meaning of each field before deciding how the missing information should be handled. This approach helped ensure that cleaning my data did not introduce new errors by replacing meaningful values unnecessarily.

Find and Replace

This is used to locate a particular value or text and replace it with a consistent or corrected value. It is especially useful when the same inconsistency appears many times in a dataset. For example, HR, Hr, and H.R may all refer to the same department, but Excel treats them as different text values. In our HR dataset, we can standardize such department names using Find and Replace so that the same department has one consistent label. This improves filtering, grouping, counting, and later analysis.

Data Validity and Logical Checking

Data validity means checking whether values are reasonable and make sense within the context of the dataset. It involves checking whether values fall within an expected and reasonable range. In an HR dataset, age is a good example because employees would generally be expected to fall within the defined working-age range. For this exercise, I used 19 to 65 years as the acceptable range. Excel's logical functions, particularly IF, together with comparison operators such as >= and <=, can be used to identify values outside this range. This allows unusual records to be flagged for investigation rather than automatically deleted. Applying this check helps improve the reliability of the dataset before analysis.

Conditional Formatting

It is an Excel feature that automatically changes the appearance of cells when they meet a specified condition. It helps identify unusual values quickly without manually checking every record. For example, it can highlight high salaries, low performance scores, missing values, or unusually long work experience. In our HR dataset, I used conditional formatting to highlight employees with more than 25 years of work experience. This makes potentially unusual records easier to identify and investigate before analysis.

Operators and Functions
Excel uses operators and functions to transform and analyze data. Operators are symbols that tell Excel how to perform an operation or comparison. Arithmetic operators include addition, subtraction, multiplication, and division, while comparison operators include equal to, greater than, less than, greater than or equal to, less than or equal to, and not equal to. In my dataset, I applied PROPER for name standardization and used IF and AND to validate employee ages against the 19–65 range. I also used comparison operators to identify work experience exceeding 25 years. These examples demonstrated how functions and operators can turn simple spreadsheet values into useful data-quality checks.

Conclusion

My first week with Excel showed me that effective data analytics begins with clean, reliable data. By working with the HR dataset, I learned how to identify and correct inconsistencies, duplicates, missing values, formatting issues, and invalid information using Excel's tools and functions. Most importantly, I learned to look beyond the numbers and question the quality and meaning of the data before analysing it. This experience has given me a stronger foundation and greater confidence as I continue my journey in data science and analytics.

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

MERCY MUMBI WAHOME — Mon, 24 Aug 2026 18:03:00 +0000

Beginner friendly explanation of Git and GitHub: Kenya Hospital Health Records

When I started learning Git and GitHub, I was completely new to them and the terms used like repository. I slowly began to understand how these tools work together. Git helps track and manage changes to a project while GitHub allows us to store the project online.
In this project, I applied what I learned by creating Kenya Hospital Health Records and moved it from my local computer to GitHub. So this article explains my journey from the start to successfully uploading my project to GitHub, explaining each step in a simple way.

1.Understanding the Git Workflow

I learnt the basics of a G it workflow: working directory where I create and edit files, staging where I prepare selected changes, commit to save them as a checkpoint and pushing to send the work from my computer to GitHub.

2.Connecting Git to GitHub Using SSH

I first confirmed that Git was installed on my computer, then checked for an existing SSH key and found mine

3.Creating the project locally

I created a folder named Kenya Hospital Health Records and opened it in GitHub, then created the project files that would make my hospital records project. I then checked the folder to confirm the files have been created successfully. This gave me the basic project structure that I would later track using Git.

4.Initializing Git and Checking the Project

Now that my files were ready, I needed to introduce Git to the project by creating a local repository that would allow me to track changes to my files. Then I checked the Git status to see how it viewed the files. They appeared as untracked.

Next, I staged the files which tells Git which changes I want to include in my next commit. I then created my first commit, the initial commit. A commit is like a checkpoint that saves the current state of my project in Git's history.

5.Creating the GitHub Repository

After creating and committing my project locally, I needed an online place to store it, so I created a new repository on GitHub and gave it a name similar to my local project. I kept the repository empty because the project and its initial commit were already on my computer which will allow me to connect to the existing local project to the new GitHub repository

6.Connecting the Local Project to GitHub

I then connected my local Git repository to the GitHub repository using the SSH address I had copied from GitHub. I gave this remote connection the name** origin**, the standard name for the main remote repository. I checked the remote connection to confirm that the GitHub SSH address had been saved correctly for both fetching and pushing.

7.Setting the main branch

The next step was to make sure my project used the main branch. A branch is a separate line of development where changes to a project can be managed.

8.Pushing the project to GitHub

I used the push command to send my committed files from the local repository to the GitHub repository. The command also connected my local main branch with the remote main branch so that Git would know where to send future updates. Git confirmed the main branch was successfully set up to track origin. This was an important moment in my learning because I could see the work I had created and saved locally could finally appear online.

After the push was done, I opened my GitHub repository and I could see my project files online confirming that the local project had been successfully uploaded.

Conclusion

This project helped me understand Git and GitHub by actually using them. I learned how to create a local project, track it with Git, connect it securely to GitHub using SSH and upload online.
The workflow:** Working Directory > Staging > Commit > Push > GitHub** may seem complicated at first but once each stage is understood, it becomes easier to follow.