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 Are larger discounts correlated with a higher volume of reviews?
Do highly-rated products naturally attract stronger customer engagement?
What is the relationship between product pricing and customer ratings?
Which products represent the top performers based on ratings and review volume?
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 and 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.
For the headings, we had to change to correct spellings for Rating from Ratingd For the price columns, we had to convert headers using the function to convert them to proper case.
That is, the first letter of each word is capitalized.
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.
Removed all trailing whitespace and extra spaces using the function.
Steps Insert a new column to the right of the Product column In Cell B2, type the function below: 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 Then, select cell A2, and right- click on it Then from the dialog box choose, Paste as Values instead of Paste.
Use the 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 Copy the values in B2:B116, paste special in in cell A2.
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.
Negative Reviews For the negative values in the Reviews column, convert them to absolute values using the 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: 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.
Price Ranges Used the midpoint of the price range to get the value for the particular products.
We found the midpoint using the formula: 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.
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: Optionally, compare the advertised discount with the calculated discount: 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 4.1-4.5 are not classified; if the original boundaries must be followed literally, label 4.1-4.5 as .
3.
Discount Category Classify your Discount into the following categories: Low Discount < 20% Medium Discount 20%-40% High Discount > 40%
4.
Price Category On the worksheet, define thresholds rather than arbitrary values.
A preferred approach uses the first and third quartiles Name those sales as and respectively.
Use the following classifications Remember to record the final KSH thresholds in the 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 Average current price Average old price Average discount Average rating Total reviews Most expensive price Least expensive price Then find and return the product names with the least and most expensive prices: Report all ties using , not only the first match.
Scatter Plots To establish relationships between variables, create scatter plots, using one product per point: Discount (x) versus reviews (y); Rating (x) versus reviews (y); and Current price (x) versus rating (y).
Add a linear trendline, display the equation and R-squared value, and calculate the Pearson Correlation: 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