Excel에서 Jumia 제품 성과 대시보드 구축.

Introduction

Building a professional dashboard is rarely a simple task; it requires careful planning and a series of strategic decisions. A clean, well-structured build ensures that complex business contexts are easy to understand, providing stakeholders with an immediate overview of the problem at hand, key findings, and actionable solutions. Clarity is the hallmark of an effective dashboard.

In this post, we will analyze Jumia’s product performance to translate raw data into meaningful insights that drive business growth and improve service delivery. By examining customer engagement, pricing models, and promotional effectiveness, we can better understand the specific factors influencing product success.

Business Questions

1. Are larger discounts correlated with a higher volume of reviews?
2. Do highly-rated products naturally attract stronger customer engagement?
3. What is the relationship between product pricing and customer ratings?
4. Which products represent the top performers based on ratings and review volume?
5. Which products require a pivot in their current pricing or marketing strategy?

Data Methodology

Data Quality Audit

After receiving the dataset, find any errors that may exist within the dataset. We checked whether the dataset had headers, checked for formatting, duplicates, missing values, data types, and any inconsistencies. We used filters and conditional formatting to find out where these errors were. This was very helpful in identifying the errors during the data cleaning process.

Note: Copy your dataset and retain the original. Work on the copied dataset.

We found the following errors and inconsistencies in the dataset:

• Mispelt headings Ratingd instead of Rating
• Mismatching Header cases old prices and Current prices
• Trailing white spaces and extra spaces in column 1
• Duplicates: we found 3 duplicates with an exact match in all fields.
• Negative review values in the reviews column
• One price range in both the Current Price and Old Prices columns instead of a price value.
• Improper data types in the Rating and Prices columns.
• Missing values in the Rating and Review columns.

So, after copying our dataset into the new sheet to retain the original dataset, create a data table. Select all the data, go to Insert > Table from the Tables group section.

Fig 1: An image of Raw Data

Data Cleaning

Now that we have understood the structure and where the underlying issues are in our data, it is time to get rid of the messy details from our dataset in readiness for analysis.

The following are the steps and decisions that we made through the cleaning process.

1. For the headings, we had to change to correct spellings for Rating from Ratingd

2. For the price columns, we had to convert headers using the PROPER() function to convert them to proper case. That is, the first letter of each word is capitalized.
   
=PROPER(B1)
=PROPER(C1)

   B1 and C1: is the address to the Current Price and Old Price headers in the sheet. The function returns the new text formatted to proper case.
   Then we used copy and paste special(Paste as Values), where we copied and pasted the new text to the header, replacing the old one.

3. Removed all trailing whitespace and extra spaces using the TRIM() function.

Steps

• Insert a new column to the right of the Product column

• In Cell B2, type the function below:

  =TRIM(A2)

• Press Enter to return the new text value

• The column is auto-filled.

_Copy and Paste Special _

• Select all the values in the B column, i.e., from B2:B116
• Copy the values using the Shortcut CTRL + C
• Then, select cell A2, and right- click on it
• Then from the dialog box choose, Paste as Values instead of Paste.

Use the CLEAN() function to also remove non-printable characters from the Product column.

Steps

• Clear the column we used for trimming
• In B2 enter the following function =CLEAN(A2)
• Copy the values in B2:B116, paste special in in cell A2.

1. Duplicates

Three duplicates were found; they were an exact match in every field. Remove them to avoid redundancy and incorrect results of analysis due to redundancy.

Steps

• Select all the data
• On Data Tab click on Remove Duplicates in the Data Tools group section
• Select all the fields, and then click OK

A confirmation pop-up message will show you the number of duplicates removed.

1. Negative Reviews

For the negative values in the Reviews column, convert them to absolute values using the ABS() function.

Steps

• Insert a new column to the right of the review column
• On the new column, F, enter the following function in cell F2: =ABS(E2)

E2 is the address of the first review negative value

• Press Enter
• Copy and Paste Special the values to replace those in the review column.

1. Price Ranges

Used the midpoint of the price range to get the value for the particular products. We found the midpoint using the formula:

`Price = (Lower Range Limit + Upper Range Limit) / 2`

Enter fullscreen mode Exit fullscreen mode

1. Data Types

Current Price and Old Price Columns

• Used Find and Replace to replace KSh with Blanks
• Converted the remaining values to Number type, then to Currency type to 2 decimal places with the symbol KSh, and used the , 1000 separator

Rating

Converted Rating to Number type, formatted to one decimal place.

Review

Converted Review column to Number type, a whole number.

Steps to Change to Number Type

• Select the Column with the data
• Go to the Home tab; in the Number group section, from the drop-down, select More Number formats
• On the left side of the pop-up window, select Number or Currency and specify the other options.
• Then click OK to apply the changes.

1. Missing Values

For missing values and blanks, never assume anything. Leave them blank as they are. Note that a Blank does not mean zero.

Exclude blanks from metrics that require the missing field.

Add a data status field (for example, Complete, Missing Rating) if it can help users understand better.

Enriching Fields and Excel Formulas

We added the following columns to our cleaned data.

1. Discount Amount

Use the following formula to get the values for the column:

=@[Old Price]-@[Current Price]

Optionally, compare the advertised discount with the calculated discount:

=IFERROR(([@[Old Price]]-[@[Current Price]])/[@[Old Price]],"")

Flag any difference instead of overwriting the advertised value.

2. Rating Category

Rating values are classified as follows:

Poor < 3

Average Between 3 and 4

Excellent > 4.5

=IF([@Discount]="", "Missing", IF([@Discount]<20%, "Low Discount", IF([@Discount]<=40%, "Medium Discount", "High Discount"))).

4.1-4.5 are not classified; if the original boundaries must be followed literally, label 4.1-4.5 as Unclassified.

3. Discount Category

Classify your Discount into the following categories:

Low Discount < 20%

Medium Discount 20%-40%

High Discount > 40%

=IF([@Discount]="", "Missing", IF([@Discount]<20%, "Low Discount", IF([@Discount]<=40%, "Medium Discount", "High Discount")))

4. Price Category

• On the Analysis worksheet, define thresholds rather than arbitrary values. A preferred approach uses the first and third quartiles

  
=QUARTILE.INC(tblProducts[Current Price],1)
=QUARTILE.INC(tblProducts[Current Price],3)


• Name those sales as Price_Q1 and Price_Q3, respectively.

• Use the following classifications

=IF([@[Current Price]]="", "Missing", IF([@[Current Price]]<=Price_Q1, "Low Price", IF([@[Current Price]]<=Price_Q3, "Medium Price", "High Price")))

• Remember to record the final KSH thresholds in the Data Dictionary for ease of interpretation by other users.

5. Engagement and Performance Flag

• For strong ‘Customer Engagement,’ define a measurable rule such as review count at or above the 75th Percentile. You may also create flags for:

• high discount and low rating;

• high discount and low engagement;

• many reviews and average rating; and

• strong engagement and excellent rating

• State the exact thresholds.

Note: Do not select the products subjectively.

Pivot Tables and Analysis

Calculate:

Metric Example Excel formula Total products =ROWS(tblProducts[Product]) Average current price =AVERAGE(tblProducts[Current Price]) Average old price =AVERAGE(tblProducts[Old Price]) Average discount =AVERAGE(tblProducts[Discount]) Average rating =AVERAGE(tblProducts[Rating]) Total reviews =SUM(tblProducts[Review]) Most expensive price =MAX(tblProducts[Current Price]) Least expensive price =MIN(tblProducts[Current Price])

Then find and return the product names with the least and most expensive prices:

=XLOOKUP(MAX(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])
=XLOOKUP(MIN(tblProducts[Current Price]),tblProducts[Current Price],tblProducts[Product])

Enter fullscreen mode Exit fullscreen mode

Report all ties using FILTER,, not only the first match.

Scatter Plots

To establish relationships between variables, create scatter plots, using one product per point:

1. Discount (x) versus reviews (y);
2. Rating (x) versus reviews (y); and
3. Current price (x) versus rating (y).

Add a linear trendline, display the equation and R-squared value, and calculate the Pearson Correlation:

=CORREL(tblProducts[Discount],tblProducts[Review])
=CORREL(tblProducts[Rating],tblProducts[Review])
=CORREL(tblProducts[Current Price],tblProducts[Rating])

Enter fullscreen mode Exit fullscreen mode

• A PivotTable or Filter helper range containing only complete pairs may be required because blanks can cause errors in some versions of Excel.
• Interpret direction carefully.
• Note that correlation does not establish causation.

Ranked Tables

Create ranked tables for:

• top 5 and bottom 5 products by rating;
• top 10 products by discount;
• top 10 products by reviews;
• top 10 products by rating;
• products with high discounts but low ratings;
• products with high discounts but low engagement; and
• products with many reviews but average ratings.

Use a PivotTable(Rows: Product; Values: relevant measure; sort in ascending/descending order, and apply Value Filters > Top 10.

Use review count as a tiebreaker for rating ranks and rating as a tiebreaker for review ranks. Exclude ratings from rating lists and show how you handle ties.

Seller Performance Questions

Translate ranked tables into evidence-based answers:

1. Strong Demand

Which products have review counts above the engagement threshold?

1. Pricing/Marketing Attention

Which products combine weak engagement or weak ratings with high price/high discount?

1. Many Reviews and Average Experience

Which products have high engagement but fall in the Average rating category?

1. Promotion Inefficiency

Which high-discount products remain below the engagement threshold?

Fig 2: Cleaned Data

Pivot Tables

These are the suggested PivotTables:

PivotTable Rows Values Recommended visual Rating mix Rating Category Count of Product Doughnut or column chart Discount mix Discount Category Count of Product Column chart Price vs rating Price Category Average of Rating Column chart Engagement by discount Discount Category Average/Sum of Review Column chart Top products by rating Product Average of Rating Horizontal bar chart Top products by reviews Product Sum of Review Horizontal bar chart Top products by discount Product Average of Discount Horizontal bar chart

• And use Insert > Slicer for Rating Category, Discount Category, and Price Category. Connect each slicer to every compatible PivotTable using Report Connections (or Pivot Table Connections).
• Remember to test each slicer independently.

Fig 3: Pivot Tables and Pivot Charts

Dashboard Design and Slicer Connections

• Here is the structure of the Jumia Product Performance dashboard:

+------------------------------------------------------------------+
| Title, refresh date, and slicers                                 |
+------------------------------------------------------------------+
| Total Products | Avg Price | Avg Discount | Avg Rating | Reviews |
+------------------------------------------------------------------+
| Top 10 by Rating | Top 10 by Reviews | Top 10 by Discount        |
+------------------------------------------------------------------+
| Discount vs Reviews | Rating vs Reviews | Price vs Rating         |
+------------------------------------------------------------------+
| Rating Mix | Discount Mix | Key Insights / Recommendations       |
+------------------------------------------------------------------+

Enter fullscreen mode Exit fullscreen mode

Dashboard Standards

Ensure a clean and tidy dashboard is created. Use consistent color, formatting, font, and relevant symbols for the different columns.

Fig 4: Image showing Jumia Product Performance Dashboard

Key Findings

• High discounts do not mean a high number of reviews or better engagement. From the analysis, there is no relationship at all between discount and reviews. The correlation coefficient between discount and reviews is -0.14.
• There is a weak relationship that establishes no pattern between rating and reviews. This indicates that more reviews do not translate to higher ratings in the data; hence, no causation. The correlation coefficient stands at 0.06.
• The analysis establishes a weak positive correlation between prices and ratings of 0.11. Prices are not influenced by the rating of the product.

Business Recommendations

• Investigate quality issues on highly discounted products but have low ratings.
• Find out why highly rated products have higher prices than the average and lower-rated products. Does this influence come from sales made?
• Combine promotions of products with high ratings and higher prices to increase engagement and check if they can translate to sales.

Limitations and Lessons

We’ve learnt valuable lessons throughout the project. We were able to use and apply functions, build PivotTables and Pivot Charts, build a clean and interactive dashboard, access price, discount, rating, and engagement relationships, clean data for real data analysis, and communicate findings, limitations, and business recommendations. This will go along way in helping sellers to make informed business decisions for their businesses.

Conclusion

None of the performance metrics- discount, rating, and reviews translate to better engagement. They do not influence either metric or engagement.

Links to GitHub

To review and see the completed project, visit Jumia Product Performance Dashboard on GitHub.