Introduction
As part of my Data Science course at LuxDevHQ we covered Excel as our first tool. It seemed unnecessary to me at first but the more we covered the more I realized I didn't know as much as is required of me as a data professional. Moreover, I was impressed by the capabilities of Excel, beyond what I normally used it for as a Finance professional who never got structured Excel training.
I used Jumia Discount Dataset for my analysis. Practised everything we covered for the last two weeks from data cleaning, analysis, pivot tables, charts to dashboarding.
Importing
Below is the process I used to import the data after downloading it on my machine.
- Open a blank workbook.
- Select Data > Get Data > From Text/CSV.
- Choose Excel_jumia_dataset.csv and verify that the comma delimiter is detected.
- Load the source into Raw_Data.
- Duplicate the query or sheet before cleaning and load the result into Cleaned_Data.
Data Cleaning
I first of all had to scan through the entire data to see what it contains and maybe spot mistakes that could be easily corrected. Started off with obvious errors that I tabulated for tracking as shown below:
To avoid being overwhelmed with scattered data, I created these worksheets in my workbook for different purposes during the project;
- Raw_Data — an original copy of the CSV;
- Cleaned_Data — cleaned fields, derived columns, and an Excel Table;
- Analysis — descriptive statistics, correlation, and ranked product tables;
- Pivot_Tables — all PivotTables that support the visuals;
- Dashboard — KPIs, charts, slicers
- Data_Dictionary — field definitions, a cleaning log.
Among others that I realized I needed along the way for further analysis and insights that bore the names of the specific metrics I analyzed.
Analysis
Analyzing data seems a bit easy when you are being taught or when someone else is doing it, its only when you do it that you appreciate the logic that goes into this process. Found out that I need to add some columns to enable analysis on my part, which was a bummer who knew that trick, I didn't, anyway we live we learn.
I realized I wasn't going anywhere without additional columns in my data since I had only a few columns to work with and by themselves they weren't going to be of much help. I created the Price Category, Discount Status, Review Status, and Ratings Status columns, that came in handy especially when I created pivot tables to summarize data for insights and recommendations. Maintained my options between 2 to 4 categories in each newly created column.
Applied the Nested IFs and ISBLANK formula to create my categories as indicated here;
Price Category =IF([@[Current price]]<500,"Economical",IF([@[Current price]]<=1000,"Affordable","High Cost"))
Discount Status =IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount"))
Review Status =IF(ISBLANK([@Review]),"No Review", IF([@Review]<10,"Poor Review",IF([@Review]<=20,"Good Review","Excellent Review")))
Ratings Status = =IF(ISBLANK([@Ratings]),"Not Rated",IF([@Ratings]<3,"Poor Review",IF([@Ratings]<=4.5,"Average","Excellent")))
The thing with the analysis part there is no right way or wrong way to do it there is only how you choose to do it, I found this method to be the most straight forward and fast since I am also training to ensure I use the least amount time possible to create a report start to finish.
Pivot Tables and Charts
After creating necessary columns and coming to the conclusion that my analyzed data is sufficient for drawing inference I proceeded to create Pivot tables based on some objectives:
which products may need a different pricing or marketing strategy
On the pivot table for this category, I placed the product and price on the rows field but made sure to group by 500, then added Average of Review to the values field and finally, Rating status to the columns as well as a slicer for the rating status.
As for the other Pivot tables, I was a bit experimental, tried different variables in different field until I found was fitting to what I intended to represent.
Used two bar charts, two column chart and a pie chart for the visualization. Created a bar and column chart interchangeably on data that represented a numerical variable along side a categorical variable. For instance, when I needed to visualize the relationship between product pricing and review:
Dashboard
Finally the dashboard!
Made the KPI's and the charts form the analysis I made on the data. My dashboard includes what I thought is of importance from the data I analyzed, sure you don't have to include every chart made, It should only have that which is essential to the stakeholders and maybe what they wish to know or what you wish to communicate as an analyst.
Recommendation and Insights
Do Larger Discounts Drive More Reviews?
From the data it seems as though higher discount drive more review as shown in the figure. High discounts which were discounts ranging from 41%-100%, but the highest recorded discount was 64%, so essentially the discount range from 41%-64% generated 30 reviews, which is the highest of the three categories.
This suggests that aggressive discounting can build customer trust or volume.
Do Highly Rated Products Attract More Engagement?
Yes, but the gap is modest.
Excellent-rated products lead in reviews, but interestingly, poorly-rated products carry the highest average discount (43%), suggesting discounts may be used to compensate for weak ratings, rather than reflecting genuine value.
Do Price and Rating Move Together?
There's a gentle positive relationship between price and rating. However, not sufficient for us to make an inference form this data only.
Best Performing Products (Ratings + Reviews)
The standout performers combining both high ratings and strong review counts:
Products Needing a Strategy Rethink
20 products have zero ratings and zero reviews. These span all price categories and carry discounts averaging ~38%, which clearly isn't working.
another category would be the products with the lowest reviews and ratings such as:
Recommended actions:
Run targeted campaigns to seed initial reviews
Consider bundling with top-performing products
Reassess whether the product-market fit is right before deepening discounts further.
Conclusion
This will serve as my lesson learnt from the whole process as I have gained one or two tips for later.
It seems like a ton of work but I believe it gets easier with time, so I will hold on to that. As long as something works use that, don't go over your head trying to remember what you learnt to apply it, and always remember you can consult, no man is an Island we all need each other at some point.
Top comments (0)