DEV Community

Yop Gony Dak
Yop Gony Dak

Posted on

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

#datascience #excell #beginners

Introduction

E-commerce platforms generate huge volumes of product-level data every day prices, discounts, reviews, and ratings , but raw data on its own doesn't tell a story. It has to be cleaned, structured, and visualized before it can support real decisions.

For this project, I set out to analyze a dataset of products scraped from Jumia, one of Africa's largest e-commerce platforms, and turn it into an interactive Excel dashboard. My objective was to answer three practical questions that any Jumia seller or category manager would care about:

  1. Do higher discounts actually drive more customer engagement (reviews)?
  2. Do highly rated products command higher or lower prices?
  3. Which products are performing best, and which need a different pricing or marketing strategy?

This article walks through the full workflow — from raw data to a finished dashboard — including the data cleaning steps, the Excel formulas and techniques I used, the pivot tables and charts I built, and the business recommendations that came out of the analysis.

📸 [Insert screenshot: project overview / cover image of the final dashboard]

Dataset Description

The raw dataset contains 115 products pulled from Jumia, with the following original fields:

Column Description
Product Product name/title
Current price Listed selling price (KSh)
Old price Original price before discount (KSh)
Discount Discount percentage
Review Number of customer reviews
Rating Star rating (e.g. "4.5 out of 5")

The data came in a fairly "raw" state — prices were stored as text strings with currency symbols and thousands separators (e.g. "KSh 1,525"), ratings were embedded in descriptive text ("4.5 out of 5"), and a large portion of the review and rating fields were blank.

📸 [Insert screenshot: raw dataset — first rows of the "Excel_jumia_dataset" sheet]

Data Cleaning and Preparation Process

Before any analysis could happen, the dataset needed a proper cleanup. Here's what that involved:

  • Converting price fields to numeric values — stripping the "KSh" prefix and comma separators from Current price and Old price so they could be used in calculations.
  • Extracting numeric ratings — pulling the numeric score out of text like "4.5 out of 5" into a clean Rating column.
  • Handling missing values — out of 115 products, 58 were missing a review count and 58 were missing a rating, meaning roughly half the catalog has no visible engagement data. Rather than dropping these rows (which would bias the analysis toward only the most popular products), I kept them and flagged this as a data-quality issue in the final insights — this gap directly affects how confidently Jumia can price and merchandise nearly half its listings.
  • Creating derived columns to make the data analysis-ready:
    • Discount Amount = Old price − Current price
    • Price category (Low / Medium / High)
    • Discount category (Low / Medium / High)
    • Rating category (Poor / Average / Excellent)

📸 [Insert screenshot: cleaned dataset — "cleaned products" sheet showing the new columns]

Excel Techniques, Formulas, and Analysis Performed

The core of the cleaning and categorization work was done with nested IF() formulas. A few examples from the cleaned dataset:

Price categorization:

=IF(B2<500,"Low Price",IF(B2<=2000,"Medium Price","High Price"))
Enter fullscreen mode Exit fullscreen mode

Discount amount:

=D2-B2
Enter fullscreen mode Exit fullscreen mode

Discount categorization (based on discount %):

=IF(G2<20%,"Low Discount",IF(G2<=40%,"Medium Discount","High Discount"))
Enter fullscreen mode Exit fullscreen mode

Rating categorization:

=IF(J2<3,"Poor",IF(J2<4,"Average","Excellent"))
Enter fullscreen mode Exit fullscreen mode

📸 [Insert screenshot: formula bar showing the nested IF formulas above, applied to the cleaned products sheet]

Beyond the IF() logic, I used array formulas to automatically pull ranked "Top 10" lists directly from the dataset — for example, surfacing the top 10 products by discount percentage, by review count, and by rating, without manually sorting the data. This made the "analysis" sheet dynamic: if the underlying data changes, the top-10 lists update automatically.

📸 [Insert screenshot: "analysis" sheet — Top 10 Highest Discounts / Top 10 Highest Reviews / Top 10 Highest Rated tables]

Dashboard Creation Process

With the data cleaned and categorized, I built PivotTables to summarize the dataset across multiple dimensions at once:

  • Average current price, average discount, average rating, and total reviews (overview KPIs)
  • Count of products by discount category (Low / Medium / High Discount)
  • Count of products by price category (Low / Medium / High Price)
  • Count of products by rating category (Poor / Average / Excellent)
  • Average review count broken down by discount category and by rating category
  • Average rating broken down by price category

📸 [Insert screenshot: pivot tables sheet showing the summary tables]

These pivot tables fed directly into a set of charts, which I then arranged into a single-page interactive Dashboard with KPI cards for:

  • Total products analyzed
  • Average price
  • Average discount
  • Average rating
  • Total reviews

📸 [Insert screenshot: chart(s) built from the pivot tables — e.g. bar chart of product count by price category, discount category distribution]

📸 [Insert screenshot: final Dashboard sheet, showing KPI cards, charts, and layout together]

Key Insights and Business Recommendations

After building the analysis and dashboard, here's what the data actually showed:

1. Do higher discounts lead to higher customer engagement?
No — the data shows no meaningful correlation between discount size and review volume. Deep discounts are not the lever driving customer engagement on Jumia.

2. Do highly rated products have higher or lower prices?
Neither, meaningfully. Price and rating are only weakly related in this dataset — being expensive doesn't guarantee a better rating, and being cheap doesn't guarantee a worse one.

3. Which products are performing best?
Crochet/knitting kits, cake-decorating sets, and cleaning gadgets stand out — they combine solid ratings (4.1–4.7) with genuine review volume, rather than just a high rating with little supporting evidence.

4. Which products need a different pricing/marketing strategy?
Two distinct groups emerged:

  • 13 "high discount / low rating" products — heavy discounting isn't fixing an underlying quality or fit problem.
  • A cluster of 5-star products with almost no reviews — these are under-marketed rather than under-priced; they deserve more visibility, not a lower price.

📸 [Insert screenshot: key insights summary / business insights sheet]

Recommendations for Jumia Sellers

  1. Reallocate discount budget away from blanket deep discounts and toward better listing content (photos, descriptions) and promotion for categories with proven demand.
  2. Audit and fix or delist the 13 high-discount/low-rating products — discounting alone won't resolve a quality or listing problem.
  3. Actively solicit reviews on high-rating, low-review products to unlock the visible social proof they already deserve.
  4. Close the review/rating data gap platform-wide — with roughly half the catalog missing review or rating data, sound pricing and merchandising decisions are currently being made without full information.

Conclusion

This project reinforced something I think gets underrated in data work: most of the value isn't in the fancy chart at the end, it's in the cleaning and categorization steps that come before it. Nested IF() formulas, careful handling of missing data, and well-structured PivotTables turned a messy scraped dataset into a dashboard that can actually inform a pricing and marketing conversation.

Top comments (0)