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

Introduction

• I have now successfully completed my training in Microsoft Excel at LuxDev HQ. Before the training, I viewed Excel mainly as a tool for viewing and organizing spreadsheets. However, I soon discovered that Excel is far more powerful and versatile than I had initially imagined.

• Throughout the training, I gained practical skills in data cleaning and analysis using a variety of tools, including text and arithmetic functions, formatting and sorting techniques, and methods for correcting and managing data types. I also learned how to create pivot charts and interactive dashboards, which provided me with a better understanding of how raw data can be transformed into meaningful and actionable insights.

• Upon completion of the course, it was time to move from theory to practice. The practical assignment was to develop an interactive dashboard to analyze the Jumia product dataset and uncover insights that could be be driving the pricing, promotions and customer-engagement.

Project Objective

• The key questions this project aims to address are:

  • whether larger discounts are associated with more reviews;
  • whether highly rated products attract stronger engagement;
  • whether price and rating move together;
  • which products perform best based on ratings and reviews; and
  • which products may need a different pricing or marketing strategy.

• The provided dataset consists of 116 products listed on the rows while the columns showcasing the product name, current price, old price, the discount, customer reviews and customer ratings.

• The dataset may seem virtually okay but there are some inconsistencies within it. For example, there are duplicated rows, cells are written using wrong data types and some cells are left blank. Let's work through this systematically moving from data cleaning to dashboard development and ultimately to translating the results into clear and actionable business insights.

Data-Quality Audit and Cleaning

• I first downloaded the Jumia dataset file in csv format then I imported it into excel via clicking on Data tab➡️ get data➡️from text/csv. Excel displays a preview of the imported data then the content is loaded in a new worksheet. I then duplicated the query in a new sheet and renamed it as desired, this prevented me from tampering with the original data. I was now ready to begin data cleaning.
  
  The above shows the dataset in its original form prior to any cleaning

• I carried out a comprehensive data-quality audit on the raw dataset. This step allowed me to systematically identify inconsistencies such as missing values, duplicate rows, non-numeric entries, and out of range values. The findings from this audit are clearly shown in my jumia_project_dashboard.xlsx file

• Next step, I clicked on the Cleaned_Data worksheet then activated Power Query in order to use it in cleaning my data since this is simpler than having to write multiple function for cleaning. On the top left there is a Query icon then clicked on Edit and performed the changes shown below:

  • First I check for any duplicates- I highlighted the first three columns then clicked on Remove Duplicates this ensured that only exact-match rows were removed. Three duplicated rows were found and deleted.
  • On the Product column, I used Transform then Trim to remove any unnecessary spaces in the cells while still retaining meaningful punctuation.
  • To finish I used Close & Load and my dataset was loaded.

• On the Current Price column, I used ctrl+h then replaced "KSh" with " ", now changed the data type from General to Currency ie Ksh2,199.00 for computation purposes. I then repeated the same process for the Old Price column.

• The header Ratingd is misspelled, I also use ctrl+h to change "Ratingd➡️Rating".

• For the Discount column, my observation was that once I imported my dataset from Csv format to Excel, the values were automatically converted to decimals. Therefore, the next thing was to change the data type into Percentage i.e 0.38➡️38%.

• On the Review Column, all the values were negatives, I therefore treated the sign as a scraping artifact since review counts cannot be negative. Next I used If function to convert the numbers to absolute values as shown below.
  

• On the Rating Column, the rates were written as "4.5 out of 5" though I wanted to only have the value without the "out of 5". Hence I used the IF function to make the changes as shown
  

• The current price for Seater Elastic Sofa (row 52) is given as a range (Ksh1,620–1,980). I used the midpoint as the expected price, since it is a safer estimate than picking from either extreme. Using the formula:
  =IF(B52="","",AVERAGE(VALUE(TEXTBEFORE(B52,"")),VALUE(TEXTAFTER(B52,"-"))))
  
  The midpoint value is Ksh1,800. Repeating the same process in order to find the midpoint value of the old price which is Ksh2,700.

• Data cleaning is now complete i.e ratings and prices have been standardized, validation checks are in place, and Raw_Data remains untouched.

Data Enrichment

• Data Enrichment is the next step which refers to enhancing the existing dataset by adding extra useful information to it to make it more complete and accurate for analysis. For my dataset I decided to add some columns as explained below.
• Data Status column that would help users understand coverage of whether a product is "Complete" i.e has both review and rating or missing either of the field using the formula:

=IF(E2="","Missing Review/Rating","Complete")

• Discount Amount column so that I could compare the advertised discount with the calculated discount. Formula used to calculate discount was: Further analysis confirmed that the two values were aligned, and therefore no discrepancy was flagged.
• Rating Category column that would group the ratings was either "Poor" if below 3, "Average" that is between 3–4, and Excellent if above 4.5. This left the values between 4.1 and 4.4 as unclassified, as instructor clarification was not available, I chose to apply the working assumption of Poor < 3, Average 3–4.5, Excellent > 4.5 to ensure an exhaustive three-category classification.

=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

• Discount Category column to to group the discounts as either "Low" if its below 20%, "Medium" if its between 20%-40% and "High" if above 40%. Formula used was:

• Price Category, deriving this required the identification of the first(Q1) and third(Q3) quartile prices first. This means finding the price below which the lowest 25% of products fall and the price below which 75% of products fall i.e. the top 25% lie above this value. Formulas used were:
  =QUARTILE.INC(tb1Products[Current Price],1) - for Q1 price
  =QUARTILE.INC(tb1Products[Current Price],3) - for Q3 price

  • I then renamed the Q1 price cell as Product_Q1 which was "Ksh 493" while the cell containing Q3 price as Product_Q3 which was "Ksh 1669.50". Next was to classify the current prices as either "Low", "High" or "Medium". All the prices below the Price_Q1 value were classified as "Low" while those prices between Price_Q1 and Price_Q3 value as "Medium" while the prices above the Price_Q3 value as "High". This was a better approach of classification other than choosing random arbitrary values.
  • I used the formula below to perform the task: =IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")))
  • The results are shown below:

Engagement and Performance Flags

• That section defined a set of yes/no labels known as "flags" that got attached to each product in the dataset based on its discount level, customer rating, and review volume relative to the overall product catalog. Rather than relying on subjective judgment, each flag was defined using percentile-based thresholds calculated directly from the dataset's distribution.

• The base metrics used were:
  |Field Name|Formula|Value|Meaning
  |---|---|---|---|
  |Engagement_Q3|=QUARTILE.INC(tb1Products[Review],3)|14|Products with 14 or more reviews are considered to have strong customer engagement/High reviews|
  |Engagement_Q1|=QUARTILE.INC(tb1Products[Review],1)|5|Products with 5 or less reviews are considered to have low reviews|
  |Discount_Q3|=QUARTILE.INC(tb1Products[Discount],3)|49%|Products discounted at 49% or more are considered to have high discount|
  |Rating_Q1|=QUARTILE.INC(tb1Products[Rating],1)|3|Products rated 3 or below are considered to have low rating|
  |Rating_Q3|=QUARTILE.INC(tb1Products[Rating],3)|4.6|Products rated 4.6 and above are considered to have high rating|
  

• I also created flags for 4 different categories as shown:
  

Data Analysis

• At this stage, it was time to begin analyzing the data in order to use this later in answering the key questions and derive meaningful insights.
• I calculated the KPI's(Key Performance Indicator) needed using various formulas and the findings were displayed in the Analysis worksheet. as shown below.

Relationship Analysis

• I copied the tb1Products data into a new worksheet named Helper_Data and filtered it to retain only rows with complete data across all four fields: Current Price, Discount, Rating, and Review. Using this cleaned dataset, I created three scatter charts: Discount vs Reviews, Rating vs Reviews, and Current Price vs Rating. Each with a linear trendline displaying the equation and R-squared value(R^2). The resulting charts are presented in the Analysis worksheet
• Afterwards I calculated the Pearson correlations(r), the R-squared value(R^2) shown on your trendline is simply the Pearson correlation coefficient(r) squared i.e R^2=r^2 and the results achieved are shown below.

Products Rating

• I then created tables to rank the products according to different categories required for the analysis, I used a PivotTable placing the "Product" name on the Rows field and "relevant measure" on the Value field, Sorted from Largest to Smallest and applied the Value Filter. I was required to make ranked tables for the following categories and the results posted in the Analysis sheet. From the ranked tables, the business question to be answered were:
  • Which products have review counts above the engagement threshold? The engagement threshold was identified as 14 using the third quartile hence only 16 products were above this.
  • Which products combine weak engagement with high discount? There were 7 products in this category. This means that even though these products have very high discounts the reviews are not many showing negative correlation between the two.
  • Which products have high engagement but fall in the Average rating category? There were 6 products for this meaning they have a lot of customer attention though the ratings sit in the "average" band rather than being top performers as expected.

PivotTables and Pivot-Charts

• After I ensured that everything was okay on the dataset, it was time to create several pivot tables and pivot charts that were required for analysis to compare different product measures. They are displayed in the Pivot_Tables worksheet.
• Next I Inserted Slicers for Rating Category, Discount Category and Price Category. I then Reported Connection for the slicers this is a very important step because it enables the user to filter all charts simultaneously when interacting with the dashboard.

Final Dashboard

• Now came the fun part creating an interactive Excel dashboard where all the KPI's, relevant charts, slicers, key insights and recommendations would be displayed. The dashboard should be limited to information that is essential to client and to the insights the analyst intends to convey.
• Orange was selected as the dashboard's primary color to reflect the established brand identity of Jumia, the client for this project.

Key Findings

• Higher Discounts do not necessarily attribute to high customer engagement of products. Therefore, sellers should not rely solely on offering high discounts on products with the expectation of getting more reviews resulting from more sales.
• The total number of Reviews a product has does not attribute to it's Rating. From the dataset it was clear that popular products are not always the best rated and high sales volume does not mean high satisfaction
• Products with higher prices tend to have slightly higher ratings than the rest implying that price may signal quality, or high end buyers may simply be easily satisfied by their purchases.

Recommendations for Jumia Sellers

• Investigate the quality on highly discounted, low rated items. This includes reviewing the products for any defects, checking listings accuracy and and identifying possible delivery challenges before offering deeper discounts.
• Run controlled tests across different discount levels to see which level actually drives better customer engagement, instead of assuming bigger discounts always results to more sales.
• Improve the titles, photos, descriptions, and specifications of products with high engagement but average ratings, since these products already reach a large audience volume.
• Encourage customers to submit ratings and reviews after purchase to improve the quality and completeness of future analysis.
• Promote products with both strong ratings and strong engagement as trusted picks because they have demonstrated customer interest and satisfaction.
• Collect sales, revenue, returns, delivery, and customer complaint data in future analysis to create a more complete view of product performance.

Limitations

• Small sample sizes where some groups had few data points making averages less reliable.
• Missing data point since for the Reviews and Rating, half of the cells were blank resulting to inefficient data for analysis.
• Due to the absence of sales or revenue figures, review counts stood in as an engagement measure though not confirming the actual sales.
• Correlation does not establish causation. The relationships identified in this project describe patterns rather than proving cause and effect.
• The dataset does not include important business variables such as units sold, revenue, profit margin, returns, delivery time, or customer complaints.

Key Lessons

• Set thresholds before analyzing by defining "high," "low," and "average" upfront which keeps the analysis objective.
• Treat reviews, ratings, discounts, and prices as separate measures because they represent different aspects of performance.
• Use Power Query for repeatable data-cleaning steps.
• Brand alignment builds trust hence using Jumia's orange made the report feel client specific and not generic.
• Keep the dashboard focused on the information required for decision making.
• These insights are a starting point, not a final verdict. As more data becomes available, revisiting this analysis will sharpen the recommendations and surface new patterns worth acting on.
• The complete project can be accessed on GitHub using the repository link below: https://github.com/Tracy-Kihara/Jumia-Product-Dashboard