Introduction
Data is essential in making business decisions and analysing product performance. For this project, I will document how I performed product performance analysis using Excel and created visual representations of the findings. The analysis focuses on product prices, discounts, ratings, and reviews, with the final output presented through an interactive Excel dashboard.
Data Cleaning and Preparation
The first step was to inspect and clean the dataset before performing the analysis. I checked for missing values, duplicates, inconsistent formats, and values that could affect the accuracy of the results. Every step that involved removing duplicates, using average values for range values e.t.c was documented in the data dictionary
The main cleaning activities included standardizing product prices, extracting numeric ratings, correcting negative review values, handling missing values, and removing duplicate records.
Price Cleaning
Some prices contained the KSh currency label, commas, and spaces. I removed these characters and converted the values into numbers using:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"KSh",""),",","")," ",""))
This allowed the price columns to be used in calculations such as averages, discounts, and price categorization.
Data Cleaning and Preparation
The first step was to inspect and clean the dataset before performing the analysis. I checked for missing values, duplicates, inconsistent formats, and values that could affect the accuracy of the results.
The main cleaning activities included standardizing product prices, extracting numeric ratings, correcting negative review values, handling missing values, and removing duplicate records.
Price Cleaning
Some prices contained the KSh currency label, commas, and spaces. I removed these characters and converted the values into numbers using:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2),"KSh",""),",","")," ",""))
This allowed the price columns to be used in calculations such as averages, discounts, and price categorisation.
Rating Cleaning
Ratings were originally stored as text, for example 4.5 out of 5. I extracted the numeric rating using:
=VALUE(LEFT(F2,FIND(" ",F2)-1))
Missing ratings were retained as missing instead of being represented as zero.
Review Cleaning
Negative review values were corrected using:
=ABS(E2)
Missing reviews were kept separate from zero values because a missing value represents unavailable information rather than a product with no reviews.
After cleaning and removing duplicates, the final dataset contained 111 products.
Data Transformation
After cleaning the data, I created additional columns to support the analysis (Helper Columns)
Discount Amount
The difference between the old price and current price was calculated using:
=[@[Old Price]]-[@[Current Price]]
The discount percentage was calculated using:
=IFERROR(([@[Old Price]]-[@[Current Price]])/[@[Old Price]],"")
Product Categories
To make comparisons easier, products were grouped based on ratings, discounts, prices, and engagement.
Ratings were classified as Poor, Average, or Excellent, while discounts were classified as Low, Medium, or High.
For price categories, I used the first and third quartiles (Q1 and Q3) to classify products as Low, Medium, or High Price:
=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=[@[Price_Q1]],"Low Price",IF([@[Current Price]]<=[@[Price_Q3]],"Medium Price","High Price")))
Using quartiles allowed the categories to be based on the distribution of the dataset rather than manually selected price ranges.
Product Performance Analysis
I created additional performance flags to identify products that may require further investigation.
For example, products with a high discount but low rating were identified using:
=IF([@Rating]="Missing Rating","Missing Rating",IF(AND([@Discount]>=[@[Discount Q3]],[@Rating]<3),"High Discount - Low Rating","Other"))
I also identified products with high discounts and low engagement, as well as products with many reviews but average ratings.
These combinations helped analyse product performance using more than one measure at a time.
PivotTable Analysis
PivotTables were used to summarize the cleaned dataset and compare different product measures.
The analysis included:
- Rating distribution
- Discount distribution
- Average rating by price category
- Engagement by discount category
- Top 10 products by rating
- Top 10 products by reviews
- Top 10 products by discount
For product rankings, ties were considered using reviews and ratings as additional measures where applicable.
Relationship Analysis
Scatter plots and trendlines were used to examine relationships between discounts, reviews, prices, and ratings.
Discount vs Reviews
The relationship between discount and reviews had an R² value of 0.021, indicating a very weak relationship. Larger discounts were therefore not strongly associated with higher review engagement.
Rating vs Reviews
The relationship between ratings and reviews had an R² value of 0.107. This was also a weak relationship, meaning products with more reviews were not necessarily the highest-rated products.
Price vs Rating
Price and rating had an R² value of 0.012, showing almost no relationship between the two variables. Higher-priced products were therefore not necessarily rated better.
These results show associations within the dataset and should not be interpreted as causation.
Dashboard Development
The final analysis was presented through an interactive Excel dashboard.
The dashboard contains five main KPIs:
| KPI | Result |
| --- | ---: |
| Total Products | 111 |
| Average Current Price | KSh 1,192.77 |
| Average Discount | 37% |
| Average Rating | 3.89 |
| Total Reviews | 723 |
The dashboard also contains product rankings, category analysis, relationship charts, and product distribution charts.
Slicers were added for Rating Category, Discount Category, and Price Category, allowing users to filter the dashboard and analyse different groups of products interactively.
Key Findings and Recommendations
The analysis showed that larger discounts were not strongly associated with higher review engagement. Sellers can therefore consider testing different discount levels rather than relying on larger discounts alone.
Products with many reviews were also not necessarily the highest rated. Products with high engagement but average ratings may provide an opportunity to investigate product quality and customer experience.
Finally, higher-priced products were not necessarily better rated. This suggests that factors beyond price may have a stronger influence on customer ratings.
Limitations
The dataset does not contain units sold or revenue. Reviews were therefore used as an engagement proxy and do not represent confirmed sales.
Listing age was also unavailable, which may affect review counts because older products may have had more time to accumulate reviews.
Other factors such as product category, brand, and customer expectations may also influence product performance but were not included in this analysis.
Key Learnings
Through this project, I gained practical experience in cleaning and preparing business data in Excel, applying formulas for data transformation, building PivotTables and Pivot Charts, and creating an interactive dashboard using slicers.
The project demonstrated how Excel can be used to transform raw product data into structured analysis and business insights.
Project Repository
The complete project, including the dataset, Excel workbook, dashboard, and documentation, is available on GitHub
Top comments (1)
Wonderful🥰