Onyx Data December 2023 Challenge – Data Analyst Portfolio Project
Onyx Data December 2023 Challenge! | Zoomcharts Mini Challenge For Power BI
About Project
This Data challenge is organized by Onyx Data in collaboration with ZoomCharts.
It is a Power BI Data Analytics Portfolio Project.
This month’s challenge is on Christmas Sales and Trends!
Christmas is the biggest festive holiday season and hence it attracts shoppers to spend money on food, clothes, gifts, toys and decorative items etc.
In this challenge I will be analysing the data from 2018 till 2023 of December and November months.
Guidelines for participating in the challenge are here: ZoomCharts
Data Cleaning
Replaced the blank values in Store ID column with NA.
Replaced blank values in Shipping Method with Offline mode.
As stated in data “ShippingMethod (Method of shipping, e.g., Standard, Express, Overnight, if online)”
Replaced balnk values in Delivery Time with 0.
As states in data “DeliveryTime (Number of days taken for delivery, if online)”.
Created a calculated column of Sales Mode with values “In Store” & “Online”.
Created a dim_date table.
Key Insights
Total Revenue Generated = 1584714
Refund Given = 803.3K
Actual Revenue Generated = 781373.5
Total Products Sold = 30106
Products Returned = 15271
Products Returned % = 50.7%
YoY Revenue Growth = 20.2%
Actual Discount given = 69545.7
Average CSAT Score = 2.98
Actual Products Sold = 14835
Average Delivery Time = 1.6 Days
Total Cities = 20
Performance Comparisons
| Highest | Value | Lowest | Value |
|---|---|---|---|
|
Highest Actual Revenue generating year is 2018 |
134.6k |
Lowest Actual Revenue generating year is 2019 |
123.4k |
|
Most refunds were issued was in year 2023 |
143k |
Least refunds were issued in year 2020 |
127.1k |
|
Highest product returned % was in year 2019 |
52.5% |
Lowest product returned % was in year 2020 |
48.5% |
|
Highest CSAT Average is of Decorations category |
3.01 |
Lowest CSAT average score is of Food category |
2.93 |
|
Highest Revenue Growth Rate % is of Food category |
21.4% |
Lowest Revenue growth rate is of Clothing category |
18.1% |
|
Highest Actual Revenue is generated by Toys category |
158.7k |
Lowest Actual Revenue is generated by Decorations category |
152.4k |
|
Highest Product return % is of Electronics category |
51.8% |
Lowest Product return % is of Clothing category |
49.3% |
|
Highest Discount given is on Electronics category |
14717.3 |
Lowest Discount given is on Clothing category |
13443.4 |
|
Highest transactions are done for Electronics products |
2053 |
Lowest transactions are done for Clothing |
1950 |
Shopping Trends
Most preferable mode of shopping is Online shopping mode with 54.2% of the customers order online.
In fact Online shopping mode is getting popular year on year.
Its growth is directly proportional to the Offline shopping mode.
While Offline Shopping mode was the top choice in year 2018 with 71.8%, now in year 2023 it is dropped to just 20.3%. The trend has shifted towards the Online Shopping Mode.
Online shopping mode seems to be the favourite and most convenient way to shop with 79.7% customers opted for it in year 2023.
Transactions trend:
The transactions trends shows that from 2018 till 2023 the yearly transactions in the given period are almost same every year.
Out of 10000 transactions, 16% to 17% transactions are done every year. There is nothing much changed in this pattern.
Revenue Growth:
The biggest drop in the actual revenue was in year 2019 with 8.4% negative growth rate.
The highest growth in the actual revenue was in year 2020 with the jump of 8.9%.
Delivery Time:
Average delivery time was the fastest in year 2018 with just 0.9 days.
While in 2023 this increases to 2.4 days.
Average delivery time is only increasing year on year. The main reason for this is that in year 2018 more than 70% of transactions were held offline in store. Which means the customers get their products on real time basis.
Now the trend is shifted towards online shopping and products are delivered to customers across different cities which increases the delivery time.
This is it from this project. I hope I was able to address most of the key insights in the most concise way.
Please comment and share this project on LinkedIn with your network if you find this project resourceful.