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.
Top comments (0)