1. Project Introduction and Objective In this project, I used Microsoft Excel and Power Query to clean and analyze a Jumia product dataset and then built an interactive dashboard to summarize pricing, discounts, ratings and customer engagement. The main objective was to turn a small raw e-commerce dataset into useful business information. I wanted the final dashboard to answer practical questions such as: Do products with higher discounts receive more customer engagement? Do higher priced products have better ratings? Is there a relationship between product rating and number of reviews? Which products have the highest review engagement? Which products may require further investigation because they have high discounts but low ratings? The project also gave me practical experience in data cleaning, excel formulas, PivotTables, PivotCharts, slicers, correlation analysis and dashboard design. 2. Dataset and Business Questions The original dataset contained 115 rows and 6 columns: Product Current price Old price Discount Review Rating The dataset was small but it contained several realistic data quality problems. This made it useful for me to practice the complete analytics process rather than going directly to visualization. I structured the workbook into the following sheets: Raw_Data Cleaned_Data Analysis Pivot_Tables Dashboard Data_Dictionary As we have always been taught in class,I kept the Raw_Data sheet unchanged so that I always have a copy of the original source data. 3. Initial Data-Quality Audit Before cleaning the data, I profiled the dataset in Power Query using Column Quality, Column Distribution and Column Profile. The audit identified several issues: Data-quality check Result Original rows 115 Original columns 6 Blank Review values 58 Blank Rating values 58 Populated Review values stored as negative numbers 57 Current Price ranges 1 Old Price ranges 1 Exact duplicate rows removed 3 Discount values outside 0 to 100% 0 Rating values outside 0 to 5 after cleaning 0 One unusual issue was the Review field. All populated review counts were negative, even though a review count cannot logically be negative. I therefore treated the negative sign as a data collection or scraping issue rather than a genuine business value. The Rating field also required cleaning because values were stored as text such as: 4.5 out of 5 rather than as numeric ratings. This audit was important because it prevented incorrect calculations later in the project. 4. Cleaning and Preparation Decisions I performed the main cleaning steps in Power Query. Product names I applied Trim and Clean to remove unnecessary spaces and non-printable characters. Repeated product names were not automatically deleted because two rows with the same product name are not necessarily duplicates. Current and old prices The price fields contained KSh and commas so I removed the currency text and converted the fields to numeric values. I notieced that two records contained price ranges rather than single prices: Current Price: KSh 1,620 - KSh 1,980 Old Price: KSh 2,200 - KSh 3,200 I used the midpoint of each range: Current Price midpoint = KSh 1,800 Old Price midpoint = KSh 2,700 This decision was documented in the Data Dictionary. Discount The Discount field was already interpreted correctly as a percentage. The cleaned values ranged from 1% to 64%, so I did not divide the field by 100 or remove its percentage data type. Reviews The populated Review values were negative. I used Absolute Value in Power Query to convert them to valid positive counts. The missing reviews remained blank. I deliberately did not convert missing reviews to zero because a blank means that the review information is unavailable, while zero would mean that the product definitely had no reviews. Ratings I renamed Ratingd to Rating, removed the text out of 5 and converted the result to a decimal number. The final valid ratings ranged from 2.0 to 5.0. Duplicate records I checked duplicates using the analytical fields: Product Current Price Old Price Discount Review Rating Three redundant duplicate rows were removed, reducing the dataset from 115 to 112 products/records. After cleaning: 112 records remained. 57 had complete Rating and Review information. 55 were missing both Rating and Review. There were 0 Power Query errors. 5. Excel Formulas and Enrichment Fields After cleaning the source fields in Power Query, I used Excel formulas to create analytical categories and flags. The cleaned Excel table was named tblProducts. Discount Amount I calculated the difference between Old Price and Current Price: =[@[Old Price]]-[@[Current Price]] Rating Category I grouped ratings into three categories while keeping missing values separate: =IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent"))) The categories were: Poor: below 3 Average: 3 to 4.5 Excellent: above 4.5 Missing: no rating available Discount Category I grouped discounts into Low, Medium, and High: =IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount"))) The thresholds were: Low Discount: below 20% Medium Discount: 20%--40% High Discount: above 40% Price Category I calculated the first and third quartiles of Current Price: =QUARTILE.INC(tblProducts[Current Price],1) Result: **KSh 493** =QUARTILE.INC(tblProducts[Current Price],3) Result: **KSh 1,669.50** I then categorized each product: =IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")) Engagement Flag I used the 75th percentile of Review count as the threshold for high engagement: =QUARTILE.INC(tblProducts[Review],3) The result was 14 reviews. The engagement formula was: =IF([@Review]="","Missing",IF([@Review]>=Review_P75,"High Engagement","Low Engagement")) Therefore: High Engagement: 14 reviews or more Low Engagement: fewer than 14 reviews Missing: no Review value Performance Flags I created three Yes/No flags: High Rating + High Engagement =IF(AND([@[Rating Category]]="Excellent",[@[Engagement Flag]]="High Engagement"),"Yes","No") High Discount + Low Rating =IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Poor"),"Yes","No") High Discount + High Rating =IF(AND([@[Discount Category]]="High Discount",[@[Rating Category]]="Excellent"),"Yes","No") These fields helped turn individual rows into business segments that could be summarized using PivotTables. 6. PivotTable and Analysis Workflow I used both formulas and PivotTables for analysis. The overall cleaned dataset had: 112 products Average Current Price: KSh 1,186.89 Average Discount: 37% Average Rating: 3.89 Average Reviews: 13 Discount analysis Discount Analysis The Discount Category PivotTable produced: Discount Category Products Average Discount Average Reviews Low Discount 19 8% 10 Medium Discount 31 31% 15 High Discount 62 48% 11 Medium-discount products recorded the highest average review count. Price analysis Price Category Products Avg Current Price Avg Discount Avg Reviews Avg Rating Low Price 28 KSh 284.75 48% 17 3.64 Medium Price 56 KSh 1,073.84 36% 10 3.88 High Price 28 KSh 2,315.14 27% 13 4.08 Low-priced products had the highest average review engagement while high-priced products had the highest average rating. Rating and engagement Among the 57 products with complete Review and Rating data: Rating Category High Engagement Low Engagement Total Poor 4 8 12 Average 6 20 26 Total 16 41 57 I also calculated the Pearson correlation between Rating and Review: =CORREL(tblProducts[Rating],tblProducts[Review]) The result was approximately 0.06, showing almost no linear relationship between rating and review count. Performance flags The analysis identified: 6 High Rating + High Engagement products 10 High Discount + Low Rating products 8 High Discount + High Rating products Because these are independent flags, the counts should not simply be added together and interpreted as unique products. 7. Dashboard Design and Slicer Connections After completing the analysis, I created a one-page dashboard designed to provide a quick summary of product performance. The dashboard included five KPI cards: Total Products: 112 Average Price: KSh 1,186.89 Average Discount: 37% Average Rating: 3.89 Total Customer Reviews: 723 I created nine charts: Top 10 Products by Ratings Top 10 Products by Review Count Top 10 Products by Discount Percentage Discount vs Reviews Rating vs Reviews Price vs Rating Rating mix Discount mix Engagement by Discount The first two charts summarize category-level patterns. The scatter plotshows the weak relationship between ratings and reviews while the Top 10 chart identifies products with the strongest observed review engagement. Rating vs Review scatter plot The scatter plot used Rating on the X-axis and Review Count on the Y-axis. I added a linear trendline and displayed the R-squared value. The trendline is almost flat and the chart shows R² ≈ 0.0033, which supports the correlation result of approximately 0.06. Top 10 products by review count A horizontal bar chart was used because product names were long and easier to read in this format. Review count was treated as an engagement indicator, not as evidence of sales. Slicers I added three slicers: Rating Category Price Category Discount Category The slicers were connected to compatible PivotTables through Report Connections/PivotTable Connections. This allows a user to select a category and dynamically explore different product segments. The scatter plot was created as a normal Excel XY chart rather than a PivotChart, so it remains an overall view and is not controlled directly by the PivotTable slicers. Final dashboard The dashboard was arranged so that the viewer sees the KPIs first, followed by the charts, slicers and business insights. 8. Key Findings Finding 1: Medium discounts recorded the strongest average review engagement Evidence: Medium-discount products averaged 15 reviews, compared with 11 for high-discount products and 10 for low-discount products. Meaning: The deepest discounts were not associated with the highest review engagement in this dataset. Action: Sellers could test medium and high discount bands and compare engagement before assuming that deeper discounting produces better product response. Caveat: Review count is an engagement proxy. The dataset does not include sales or listing age, so this relationship should not be interpreted as causal. Finding 2: Average rating increased across price categories Evidence: Average rating increased from 3.64 for low-priced products, to 3.88 for medium-priced products and 4.08 for high-priced products. Meaning: Higher-priced products in this dataset tended to have stronger average customer ratings. Action: Sellers could investigate the product quality, features,brands or listing characteristics associated with highly rated high-priced products. Caveat: This is an association. The analysis does not prove that increasing a product's price would improve its rating. Finding 3: Rating and review engagement had almost no linear relationship Evidence: The correlation between Rating and Review was approximately r = 0.06, with R² ≈ 0.0033. Meaning: A highly rated product does not necessarily receive a high number of reviews. Action: Sellers should consider both rating quality and review engagement when evaluating product performance rather than relying on rating alone. Caveat: Only 57 products had complete Rating and Review information and review count is not a direct measure of sales. Finding 4: Performance flags identified both opportunities and risks Evidence: The analysis found 6 high-rating/high-engagement products, 10 high-discount/low-rating products and 8 high-discount/high-rating products. Meaning: Some products appear to be strong engagement candidates, while others may require investigation because heavy discounting is occurring alongside weak ratings. Action: Sellers could investigate the 6 high-rating/high-engagement products for promotional opportunities and review quality or listing issues among the 10 high-discount/low-rating products before increasing promotional support. Caveat: These flags are screening tools. The dataset does not contain revenue, units sold, margin, advertising spend, or profitability data, so the flagged products cannot automatically be classified as commercial winners or failures. 9. Business Recommendations Based on the completed analysis, I would recommend the following actions: Test discount bands rather than automatically increasing discounts. Medium-discount products had higher average review engagement than high-discount products. Investigate highly rated, higher-priced products. Understanding what these products have in common may provide useful lessons for product positioning and listing quality. Use rating and engagement together. The weak correlation shows that a high rating alone does not guarantee strong review activity. Review high-discount/low-rating products. These products may require investigation into quality, expectations, listing content, or promotional strategy. Study high-rating/high-engagement products. These products can be investigated further as possible candidates for targeted visibility or promotional tests. These recommendations are deliberately limited to what the dataset supports. 10. Limitations and Lessons Learned Limitations The dataset had substantial missing feedback data. Out of 112 cleaned records, only 57 had both Rating and Review information. This reduces the amount of data available for analyses involving customer feedback. The project is also observational. Patterns such as higher ratings among high-priced products or stronger engagement among medium-discount products show association, not causation. Lessons learned This project reinforced several lessons for me. First, data cleaning is part of analysis, not a separate optional step. If I had used the original negative Review values or text-based ratings directly, the final analysis would have been misleading. Second, I learned the importance of documenting assumptions. Converting price ranges to midpoints, using 14 reviews as the high-engagement threshold and defining category boundaries all affect the results and therefore need to be transparent. Third, you can use PivotTables to summarize data and also create an interactive dashboard. I also found out that PivotCharts rely on the PivotTables they get their data from. This means you can create separate PivotTables from the same data source for your charts. That way, a chart on your dashboard won't accidentally update a detailed analytical PivotTable. Fourth, a dashboard should not contain every calculation. The Analysis and Pivot_Tables sheets can hold detailed work while the Dashboard should focus on the KPIs, charts, filters and insights that help a user understand the data quickly. Finally, I learned to be careful with business language. A correlation does not prove causation, reviews do not equal sales and a flagged product is a starting point for investigation rather than a final business conclusion. Conclusion This project took a Jumia product dataset through the complete Excel analytics workflow: audit, cleaning, enrichment, analysis,visualization and business interpretation. Starting with 115 raw records, I used Power Query to resolve price formatting, rating text, negative review values, missing information and duplicate records. The final cleaned dataset contained 112 records. I then used Excel formulas to create price, discount, rating, engagement and performance categories before summarizing the results through PivotTables and charts. The final interactive dashboard provides a concise view of product pricing, discounts, ratings and customer engagement while still recognizing the limitations of the available data. For me, the main lesson was that building a useful dashboard is not only about creating attractive charts. The quality of the final dashboard depends on the decisions made before visualization: how the data is cleaned, how metrics are defined, how assumptions are documented and how carefully the findings are interpreted.